Странное поведение планировщика запросов PostgreSQL / forpes.ru

Главная
Странное поведение планировщика запросов PostgreSQL

Странное поведение планировщика запросов PostgreSQL +7

17.09.2024 08:05

CrushBy 1 413 Источник

В одной из предыдущих статей я описывал проблемы, которые возникают при работе с временными таблицами. Тогда я вкратце описывал, почему нам приходится их так часто использовать. В частности, одной из причин была неправильная работа планировщика запросов в PostgreSQL. Многие из проблем планировщика запросов (и не только PostgreSQL) были также описаны в статье Почему не SQL. В этой статье я покажу еще один достаточно простой и часто используемый случай, когда планировщик ошибается, что может приводить к значительному росту потребления ресурсов.

Проблема воспроизводится на последней стабильной на данный момент версии PostgreSQL - 16.4. При этом используются стандартные настройки PostgreSQL. Я пробовал менять разные настройки, но мне не удалось добиться правильного плана в общем случае, поскольку в данном случае проблема скорее логическая, а не в определении стоимости вычислений. Однако, каждый может легко воспроизвести эту ситуацию локально и попробовать поиграться с настройками.

Рассмотрим простую доменную логику, в которой есть документы и их строки. Для каждой строки вводится сумма. Строки лежат в отдельной таблице и ссылаются на документ :

CREATE TABLE doc (id int PRIMARY KEY);
CREATE TABLE line (id int PRIMARY KEY, docId int, amount numeric);
CREATE INDEX line_doc ON line (docid);

Заполним таблицу тестовыми данными. Сгенерируем 100.000 документов, имеющих 10, 20, 50 и 100 строк в равной пропорции :

INSERT INTO doc (id) SELECT generate_series AS id FROM generate_series(1, 100000);
INSERT INTO line (id, docId, amount) (SELECT docId*100 + id AS id, docId, random() AS amount FROM generate_series(1, 10) AS id, generate_series(1, 25000) AS docid);
INSERT INTO line (id, docId, amount) (SELECT docId*100 + id AS id, docId, random() AS amount FROM generate_series(1, 20) AS id, generate_series(25001, 50000) AS docid);
INSERT INTO line (id, docId, amount) (SELECT docId*100 + id AS id, docId, random() AS amount FROM generate_series(1, 50) AS id, generate_series(50001, 75000) AS docid);
INSERT INTO line (id, docId, amount) (SELECT docId*100 + id AS id, docId, random() AS amount FROM generate_series(1, 100) AS id, generate_series(75001, 100000) AS docid);

Запустим ANALYZE, чтобы PostgreSQL собрал правильную статистику для них :

ANALYZE doc;
ANALYZE line;

Получим план для простого запроса на получение строк по конкретному документу :

SELECT id FROM line WHERE docId = 4353;

Index Scan using line_doc on line (cost=1.73..12.03 rows=70 width=4)
Index Cond: (docid = 4353)

Сразу видим, что PostgreSQL - пессимист, так как считает, что получит 70 записей на выходе. На тестовых данных в среднем в каждом документе записей приблизительно в два раза меньше. Это не критично, а в некоторых случаях даже полезно. PostgreSQL можно понять, но конкретно в нашем случае такой пессимизм будет только усугублять проблему.

Далее попробуем сделать простой, с точки зрения разработчика, запрос :

SELECT docId, SUM(amount) FROM line WHERE docId IN (3531,6572) GROUP BY 1;

Он просто получает два документа вместе с суммой по строкам для каждого из них. Но что же мы видим в плане :

GroupAggregate (cost=1.73..35.03 rows=139 width=36)
Group Key: docid
-> Index Scan using line_doc on line (cost=1.73..23.91 rows=139 width=15)
Index Cond: (docid = ANY ('{3531,6572}'::integer[]))

Планировщик запросов считает, что для этих двух документов он выберет 139 строк (что соответствует статистике предыдущего запроса), но в результате группировки по документам у него будет те же 139(!!) документов. Хотя, очевидно, что документов будет максимум 2. В результате статистика не соответствует реальной почти в 70 раз.

Кстати, если сделать запрос с одним документом, то статистика уже правильная (видимо есть какая-то эвристика в планировании) :

SELECT docId, SUM(amount) FROM line WHERE docId IN (3531) GROUP BY 1;

GroupAggregate (cost=1.73..12.79 rows=1 width=36)
-> Index Scan using line_doc on line (cost=1.73..12.03 rows=70 width=15)
Index Cond: (docid = 3531)

При чем эта оптимизация для одного значения появилась только в последних версиях PostgreSQL. До 15й версии ее еще не было, и планировщик считал, что у него будет 70 записей.

Дальше рассмотрим следующую задачу. Нам нужно будет вывести пользователю страницу списка документов, состоящую из 50 записей и содержащую суммы по каждому документу. Для решения этой задачи запишем коды всех документов на одной странице в отдельную временную таблицу :

CREATE TEMPORARY TABLE tmp (id int PRIMARY KEY);
INSERT INTO tmp (id) SELECT * FROM generate_series(1, 50);
ANALYZE tmp;

Наконец, сформируем запрос по получению сумм документа, используя подзапрос для вычисления сумм по каждому документу. Сначала сделаем самым простым способом, который первый приходит в голову :

SELECT tmp.id,
       sq.amount
FROM tmp
LEFT JOIN
  (SELECT docid,
          SUM(amount) AS amount
   FROM line
   GROUP BY 1) sq ON sq.docid = tmp.id

К сожалению, PostgreSQL не догадывается, что ему надо рассчитать суммы по всего 50 документам, и он рассчитывает их по вообще всем документам в таблице :

Hash Right Join  (cost=155816.90..163627.72 rows=16168 width=36)
  Hash Cond: (line.docid = tmp.id)
  ->  Finalize HashAggregate  (cost=155811.30..159691.74 rows=64674 width=36)
        Group Key: line.docid
        ->  Gather  (cost=135115.62..151930.86 rows=129348 width=36)
              Workers Planned: 2
              ->  Partial HashAggregate  (cost=134115.62..137996.06 rows=64674 width=36)
                    Group Key: line.docid
                    ->  Parallel Seq Scan on line  (cost=0.00..96615.82 rows=1874990 width=15)
  ->  Hash  (cost=2.60..2.60 rows=50 width=4)
        ->  Seq Scan on tmp  (cost=0.00..2.60 rows=50 width=4)

Это, конечно же, является не самым оптимальным планом, поэтому мы немного поможем PostgreSQL, и добавим JOIN с нашей временной таблицей (кстати, платформа lsFusion при генерации запросов делает это автоматически) :

SELECT tmp.id,
       sq.amount
FROM tmp
LEFT JOIN
  (SELECT docid,
          SUM(amount) AS amount
   FROM line
   JOIN tmp ON tmp.id = docid
   GROUP BY 1) sq ON sq.docid = tmp.id

В результате план получается гораздо лучше :

Hash Right Join  (cost=8.82..744.26 rows=870 width=36)
  Hash Cond: (line.docid = tmp.id)
  ->  GroupAggregate  (cost=3.22..527.25 rows=3479 width=36)
        Group Key: line.docid
        ->  Merge Join  (cost=3.22..248.93 rows=3479 width=15)
              Merge Cond: (line.docid = tmp_1.id)
              ->  Index Scan using line_doc on line  (cost=1.73..453359.63 rows=4499977 width=15)
              ->  Index Only Scan using tmp_pkey on tmp tmp_1  (cost=0.56..5.86 rows=50 width=4)
  ->  Hash  (cost=2.60..2.60 rows=50 width=4)
        ->  Seq Scan on tmp  (cost=0.00..2.60 rows=50 width=4)

Однако, несмотря на то, что план фактически оптимальный, есть одна большая проблема со статистикой. Во-первых, при группировке по документу PostgreSQL считает, что документов будет столько же, сколько и строк (3479). То есть, как и в примерах выше, ошибается в 70 раз. А, во-вторых, после выполнения LEFT JOIN с временной таблицей tmp, он не догадывается, что в подзапросе будут уникальные ключи. Очевидно, что в результате не может быть записей больше, чем в исходной таблице, однако PostgreSQL ожидает, что там будет 870 записей, что более чем в 15 раз выше правильной статистики.

В простом случае такая ошибка в статистике не так критична. Однако, если запрос усложняется, то эта ошибка может приводить уже к совсем неправильным планам запросов. Например, для теста, чтобы не усложнять логику добавим LEFT JOIN с той же самой таблице еще 2 раза (как если бы нужно было посчитать суммы из других таблиц). Кроме того, добавим еще считывание поля из исходной таблицы doc :

SELECT tmp.id,
       sq.amount,
       sq2.amount,
       sq3.amount,
       d1.id
FROM tmp
LEFT JOIN
  (SELECT docid,
          SUM(amount) AS amount
   FROM line
   JOIN tmp ON tmp.id = docid
   GROUP BY 1) sq ON sq.docid = tmp.id

LEFT JOIN
  (SELECT docid,
          SUM(amount) AS amount
   FROM line
   JOIN tmp ON tmp.id = docid
   GROUP BY 1) sq2 ON sq2.docid = tmp.id
   
LEFT JOIN
  (SELECT docid,
          SUM(amount) AS amount
   FROM line
   JOIN tmp ON tmp.id = docid
   GROUP BY 1) sq3 ON sq3.docid = tmp.id

LEFT JOIN doc d1
	ON tmp.id = d1.id

Получаем вот такой план :

Hash Left Join  (cost=1824.83..2788.04 rows=263256 width=104)
  Hash Cond: (tmp.id = sq3.docid)
  ->  Hash Left Join  (cost=914.89..1715.05 rows=15134 width=72)
        Hash Cond: (tmp.id = sq2.docid)
        ->  Merge Left Join  (cost=4.95..795.76 rows=870 width=40)
              Merge Cond: (tmp.id = line.docid)
              ->  Merge Left Join  (cost=1.73..15.76 rows=50 width=8)
                    Merge Cond: (tmp.id = d1.id)
                    ->  Index Only Scan using tmp_pkey on tmp  (cost=0.56..5.86 rows=50 width=4)
                    ->  Index Only Scan using doc_pkey on doc d1  (cost=1.17..10028.77 rows=100000 width=4)
              ->  GroupAggregate  (cost=3.22..527.25 rows=3479 width=36)
                    Group Key: line.docid
                    ->  Merge Join  (cost=3.22..248.93 rows=3479 width=15)
                          Merge Cond: (line.docid = tmp_1.id)
                          ->  Index Scan using line_doc on line  (cost=1.73..453359.63 rows=4499977 width=15)
                          ->  Index Only Scan using tmp_pkey on tmp tmp_1  (cost=0.56..5.86 rows=50 width=4)
        ->  Hash  (cost=701.20..701.20 rows=3479 width=36)
              ->  Subquery Scan on sq2  (cost=3.22..701.20 rows=3479 width=36)
                    ->  GroupAggregate  (cost=3.22..527.25 rows=3479 width=36)
                          Group Key: line_1.docid
                          ->  Merge Join  (cost=3.22..248.93 rows=3479 width=15)
                                Merge Cond: (line_1.docid = tmp_2.id)
                                ->  Index Scan using line_doc on line line_1  (cost=1.73..453359.63 rows=4499977 width=15)
                                ->  Index Only Scan using tmp_pkey on tmp tmp_2  (cost=0.56..5.86 rows=50 width=4)
  ->  Hash  (cost=701.20..701.20 rows=3479 width=36)
        ->  Subquery Scan on sq3  (cost=3.22..701.20 rows=3479 width=36)
              ->  GroupAggregate  (cost=3.22..527.25 rows=3479 width=36)
                    Group Key: line_2.docid
                    ->  Merge Join  (cost=3.22..248.93 rows=3479 width=15)
                          Merge Cond: (line_2.docid = tmp_3.id)
                          ->  Index Scan using line_doc on line line_2  (cost=1.73..453359.63 rows=4499977 width=15)
                          ->  Index Only Scan using tmp_pkey on tmp tmp_3  (cost=0.56..5.86 rows=50 width=4)

В плане мы видим, что ошибка “накапливается” и тут PostgreSQL ожидает 263256 записей (хотя больше 50 их быть не может). Однако, оптимизатор СУБД догадывается переставить местами выполнения JOIN, и считывает поля из таблицы doc только для записей из временной таблицы (см. строки 7-10 в плане). Проблема в том, что если количество JOIN будет большим (в частности, больше, чем параметр join_collapse_limit, который по умолчанию равен 8), то такая оптимизация может и не помочь.

Смоделируем такую ситуацию, просто выставив в явную параметр join_collapse_limit равным единице, и запустив тот же самый запрос :

SET join_collapse_limit=1;


Hash Left Join  (cost=12873.00..16545.19 rows=263256 width=104)
  Hash Cond: (tmp.id = d1.id)
  ->  Hash Left Join  (cost=1828.70..2736.54 rows=263256 width=100)
        Hash Cond: (tmp.id = sq3.docid)
        ->  Hash Left Join  (cost=918.76..1663.55 rows=15134 width=68)
              Hash Cond: (tmp.id = sq2.docid)
              ->  Hash Right Join  (cost=8.82..744.26 rows=870 width=36)
                    Hash Cond: (line.docid = tmp.id)
                    ->  GroupAggregate  (cost=3.22..527.25 rows=3479 width=36)
                          Group Key: line.docid
                          ->  Merge Join  (cost=3.22..248.93 rows=3479 width=15)
                                Merge Cond: (line.docid = tmp_1.id)
                                ->  Index Scan using line_doc on line  (cost=1.73..453359.63 rows=4499977 width=15)
                                ->  Index Only Scan using tmp_pkey on tmp tmp_1  (cost=0.56..5.86 rows=50 width=4)
                    ->  Hash  (cost=2.60..2.60 rows=50 width=4)
                          ->  Seq Scan on tmp  (cost=0.00..2.60 rows=50 width=4)
              ->  Hash  (cost=701.20..701.20 rows=3479 width=36)
                    ->  Subquery Scan on sq2  (cost=3.22..701.20 rows=3479 width=36)
                          ->  GroupAggregate  (cost=3.22..527.25 rows=3479 width=36)
                                Group Key: line_1.docid
                                ->  Merge Join  (cost=3.22..248.93 rows=3479 width=15)
                                      Merge Cond: (line_1.docid = tmp_2.id)
                                      ->  Index Scan using line_doc on line line_1  (cost=1.73..453359.63 rows=4499977 width=15)
                                      ->  Index Only Scan using tmp_pkey on tmp tmp_2  (cost=0.56..5.86 rows=50 width=4)
        ->  Hash  (cost=701.20..701.20 rows=3479 width=36)
              ->  Subquery Scan on sq3  (cost=3.22..701.20 rows=3479 width=36)
                    ->  GroupAggregate  (cost=3.22..527.25 rows=3479 width=36)
                          Group Key: line_2.docid
                          ->  Merge Join  (cost=3.22..248.93 rows=3479 width=15)
                                Merge Cond: (line_2.docid = tmp_3.id)
                                ->  Index Scan using line_doc on line line_2  (cost=1.73..453359.63 rows=4499977 width=15)
                                ->  Index Only Scan using tmp_pkey on tmp tmp_3  (cost=0.56..5.86 rows=50 width=4)
  ->  Hash  (cost=5044.30..5044.30 rows=100000 width=4)
        ->  Seq Scan on doc d1  (cost=0.00..5044.30 rows=100000 width=4)

Мы видим, что PostgreSQL начал выполнять JOIN в том же порядке, что и в самом запросе. В результате, из-за ошибки в статистике планировщик запросов начал считать, что выгоднее будет делать seq scan (см. последние 2 строки плана) всей таблицы doc с последующим hash join. И это было бы правильно, если первая часть запроса действительно давала бы 260 тысяч записей. Правда на практике там будет 50 записей, и гораздо выгоднее будет простой пробег по индексу.

Если вместо временной таблицы tmp использовать обычную таблицу, то результат не изменится. Однако, если вместо временной таблицы использовать основную таблицу с WHERE, то внезапно планирование становится правильным :

SELECT doc.id,
       sq.amount
FROM doc
LEFT JOIN
  (SELECT docid,
          SUM(amount) AS amount
   FROM line
   JOIN tmp ON tmp.id = docid
   GROUP BY 1) sq ON sq.docid = doc.id
WHERE doc.id >= 1 AND doc.id <= 50;

Merge Left Join  (cost=4.39..744.12 rows=52 width=36)
  Merge Cond: (doc.id = line.docid)
  ->  Index Only Scan using doc_pkey on doc  (cost=1.17..7.51 rows=52 width=4)
        Index Cond: ((id >= 1) AND (id <= 50))
  ->  GroupAggregate  (cost=3.22..527.25 rows=3479 width=36)
        Group Key: line.docid
        ->  Merge Join  (cost=3.22..248.93 rows=3479 width=15)
              Merge Cond: (line.docid = tmp.id)
              ->  Index Scan using line_doc on line  (cost=1.73..453359.63 rows=4499977 width=15)
              ->  Index Only Scan using tmp_pkey on tmp  (cost=0.56..5.86 rows=50 width=4)

Как видим, PostgreSQL уже ожидает 52 ряда. Причем он точно также ошибается с определением количества рядов во вложенном подзапросе. Однако, после JOIN он уже не думает, что количество записей увеличится. И это все при том, что фактически нет никакой связи между таблицей doc и вложенным подзапросом. В подзапросе используются только таблицы line и tmp, которые с точки зрения схемы базы данных никак не связаны с исходной таблицей doc. К сожалению, у меня пока нет объяснения такого поведения планировщика запросов. Использовать же во внешнем запросе исходную таблицу с WHERE тоже не очень хорошо, так как в случае сложного фильтра PostgreSQL может также ошибиться и получить неправильную статистику на количество записей. При JOIN с временной таблицей он по крайней мере точно знает, сколько в ней записей.

Я рассмотрел только самый простой случай группировки во вложенном запросе. На практике, бывают значительно более сложные подзапросы, на которых ошибка в планировании также воспроизводится. К счастью, эта проблема встречается не так часто, поскольку оптимизация PostgreSQL с переставлением JOIN, как правило, помогает. Однако, время от времени мы натыкаемся на такие неэффективные запросы.

В платформе lsFusion эта проблема легко исправляется путем материализации вложенного подзапроса без изменения логики работы приложения. Но это имеет и негативные эффекты : увеличение количества хранимых полей увеличивает размер базы данных, а также замедление транзакции на запись в базу данных. Еще одним вариантом решения проблемы является предварительная запись вложенных запросов во временные таблицы с последующим запуском для них ANALYZE. Это также делается автоматически платформой, когда время выполнения запрос превышает какой-то порог.

Комментарии (1)

danolivo
17.09.2024 09:39
#27303892
Интересное замечание, что можно попытаться форсить ограничение уникальности в плане на совокупность столбцов группировки. Непонятно, почему в сообществе до сих пор этого не сделали (подводные камни?), но скорее всего ответ в том, что никто не жаловался ;)

Что касаемо запросов - то в ситуации с tmp и исходной таблицей нет никакой магии:

1) Различий в алгоритмах эстимации между временными и невременными таблицами нет.

2) Автор возможно полагает, что эстимация выражения JOIN'a происходит с учетом нижележащего дерева запроса. Увы, это не так - селективность оценивается только по статистике таблиц, из которых взяты сравниваемые колонки и по количеству туплов, которые должны придти из поддерева слева и справа.

Магия здесь скорее всего в MCV статистике: по tmp она, вероятно, покрывает все значения в таблице, а по большой - только малую долю таблицы. Таким образом при джойне с tmp мы имеем хороший прогноз по совпадениям и можем предположить появление большого количества дубликатов из иннера нашего LEFT JOIN.

Когда же используется исходная таблица, то алгоритм подсчета статистики, сматчив MCV слева и справа, попадает на большую неопределенность и с оставшейся частью данных ему приходится эстимировать селективность джойна через количество distinct значений в колонке. И здесь мы получаем низкую селективность, вроде 1/1E6* 1/4.5E5, что скорее всего дает оценку меньше 1. А по формуле селективности LEFT JOIN, у вас количество результирующих туплов будет не меньше, чем количество туплов в левом входе. Отсюда и имеем цифру 50, которая "магически" точна.

Странное поведение планировщика запросов PostgreSQL +7

Комментарии (1)

danolivo