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