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

В одной из предыдущих статей я описывал проблемы, которые возникают при работе с временными таблицами. Тогда я вкратце описывал, почему нам приходится их так часто использовать. В частности, одной из причин была неправильная работа планировщика запросов в 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. Это также делается автоматически платформой, когда время выполнения запрос превышает какой-то порог.