В одной из предыдущих статей я описывал проблемы, которые возникают при работе с временными таблицами. Тогда я вкратце описывал, почему нам приходится их так часто использовать. В частности, одной из причин была неправильная работа планировщика запросов в 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. Это также делается автоматически платформой, когда время выполнения запрос превышает какой-то порог.
Комментарии (24)
pluzanov
17.09.2024 09:39Сначала сделаем самым простым способом, который первый приходит в голову :
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А разве вот так не проще?
SELECT tmp.id, SUM(line.amount) AS amount FROM tmp LEFT JOIN line ON line.docid = tmp.id GROUP BY 1;Сразу с правильным планом. Или это особенности построения запросов в ORM?
О кривом расчете n_distinct для line.docid читал здесь. Вроде ничего лучше не придумали, как вручную выставлять долю через alter table .. alter column .. set (n_disctinct= -0.xxx).
CrushBy Автор
17.09.2024 09:39Сразу с правильным планом. Или это особенности построения запросов в ORM?
Да, действительно такое вариант есть, и он более простой. Но он работает только в простом случае (когда внутри простой GROUP BY по одному параметру). К сожалению, в более сложных случаях, так развернуть подзапрос нельзя. А в реальности проблемы возникают на более сложных запросах. В данном случае, я просто привел простой пример, чтобы не "нагружать" читателя.
Кроме того, схема с LEFT JOIN в более сложных делает планы не очень предсказуемыми. Например, я добавил еще 3 LEFT JOIN с той же таблицой line. В результате план получается вот такой :
Ладно, что там оценки рядов вообще странные. Но там реально местами получается декартово произведение строк, что может привести к "сваливанию" выполнения в что-то очень непредсказуемое. С подзапросами да, есть ошибка по статистике, но хоть выполнение будет более-менее понятное.
LaRN
17.09.2024 09:39Должно быть лучше, если в индекс
line_docдобавить поле id и сделать этот индекс уникальным.CrushBy Автор
17.09.2024 09:39Можете более подробно описать, что Вы имеете ввиду ? По таблице line есть уникальный индекс по id. А сам индекс line_doc никак уникальным сделать не получится.
LaRN
17.09.2024 09:39Можно вот такой индекс сделать
CREATE UNIQUE INDEX line_doc ON line (docid, id);
danolivo
Интересное замечание, что можно попытаться форсить ограничение уникальности в плане на совокупность столбцов группировки. Непонятно, почему в сообществе до сих пор этого не сделали (подводные камни?), но скорее всего ответ в том, что никто не жаловался ;)
Что касаемо запросов - то в ситуации с tmp и исходной таблицей нет никакой магии:
1) Различий в алгоритмах эстимации между временными и невременными таблицами нет.
2) Автор возможно полагает, что эстимация выражения JOIN'a происходит с учетом нижележащего дерева запроса. Увы, это не так - селективность оценивается только по статистике таблиц, из которых взяты сравниваемые колонки и по количеству туплов, которые должны придти из поддерева слева и справа.
Магия здесь скорее всего в MCV статистике: по tmp она, вероятно, покрывает все значения в таблице, а по большой - только малую долю таблицы. Таким образом при джойне с tmp мы имеем хороший прогноз по совпадениям и можем предположить появление большого количества дубликатов из иннера нашего LEFT JOIN.
Когда же используется исходная таблица, то алгоритм подсчета статистики, сматчив MCV слева и справа, попадает на большую неопределенность и с оставшейся частью данных ему приходится эстимировать селективность джойна через количество distinct значений в колонке. И здесь мы получаем низкую селективность, вроде 1/1E6* 1/4.5E5, что скорее всего дает оценку меньше 1. А по формуле селективности LEFT JOIN, у вас количество результирующих туплов будет не меньше, чем количество туплов в левом входе. Отсюда и имеем цифру 50, которая "магически" точна.
CrushBy Автор
Согласен, логично. Непонятно только, как это можно было легко использовать в качестве workaround для временной таблицы.
Вот тут мне и было интересно, что оптимизация вроде как самая базовая, и странно, что ее нет. Но ладно уникальность - вопрос еще почему совсем не уменьшается ожидаемое количество строк. Ведь PostgreSQL знает, что при группировке по docid количество записей уменьшится в среднем в 30 раз (если строки будут выбраны случайно). Зачем такая пессимистическая оценка ? Можно было бы уменьшать ожидание на меньший порядок, а тут вообще 1. И почему нет параметров, которые управляют этой оценкой.
asdfddsa
Оно влияет на что-то в случае адекватных запросов? Ни в одном вашем примере не увидел запроса, который реально требует подзапросов.
PS
Приведите пример, где реально нужен подзапрос именно такого типа, а не join lateral. Может реально проблема...
CrushBy Автор
Пример же простой. Вывести для заказов на странице суммы по ним. Да, можно хранить из в самой таблице заказов, постоянно туда записывая. Но, во-первых, это дополнительное место. А, во-вторых, а что, если нужно посчитать не сумму заказов, а сумму посчитанную по какому-то сложному алгоритму из строк. Что тогда ? Хранить все возможные варианты сумм в таблице с заказами и их обновлять.
Если же не хранить, то как без подзапросов посчитать ? Давайте вот на примере из статьи.
unfilled
На этот вопрос вы не ответили. Почему бы не использовать lateral?
danolivo
Это с какой колокольни смотреть. Я за много лет первый раз увидел такой пример. При том что делать это будет достаточно сложно - тут же уникальность только по всему набору группируемых столбцов обеспечивается. А что, если под группировкой лежит параметризованное сканирование - тогда на каждый ReScan мы можем получать разные результаты и формально, это уже не уникально - черт его знает, это пробовать нужно в коде.
Опять же: постгрес не смотрит на предысторию, только на базовые таблицы и их статистику. Это можно обойти на этапе исполнения, добавив какой-нибудь кастомный сборщик статистики в точке группировки, как DeWitt предлагал в 1998м. Но это опять-же делать нужно.
А это, кстати, общий подход. Лучше пессимистическая оценка и долгий HashJoin, нежели оптимистичная и вечный NestLoop ;)
Есть, хинты называются - см. pg_hint_plan. А в ванилле пока и не планируется, ибо open-source не про деньги, а про технологии. Но частные хинты на селективность выражений там обсуждали лет пять назад и вроде без особого негатива ...
CrushBy Автор
Но это же самый простой пример. Просто JOIN с GROUP BY. Собственно, а что потом еще можно делать с GROUP BY, кроме как JOIN по ключам ? И собственно большая часов подзапросов - это именно GROUP BY. Я конечно понимаю, что может у меня профдеформация, но видимо я что-то не понимаю...
Так сделали бы хотя бы частный случай, когда группировка по одной колонкой. С одним значением в IN же сделали эвристику (как в примере в статье).
Ну опять же - это редкий случай. И для каждого Rescan там все равно внутри будут уникальные значения.
Не очень понимаю, где противоречие. В WHERE же он оценивает количество записей на выходе. Что ему мешает тоже самое делать с GROUP BY, а не брать, что количество записей вообще не изменится - это же нонсенс.
Есть такое. Уважаю :) Но, если я не хочу получить Nested Loop, то я просто в запросе могу его отключить. Более того, насколько я помню, у нас в lsFusion даже такая "оптимизация" есть. Выполняем запрос - долго не выполняется, значит скорее всего ошибся и ушел в Nested Loop. Отменяем запрос, отключаем его и отправляем повторно.
Но это же не повод вообще делать все пессимистично. Сделали бы хотя бы опцию для этого.
Ну вставлять в каждом запросе с GROUP BY - так себе идея. Но тут же проблема в другом. В этом расширении, как я понимаю, можно задавать хинты по тому, как выполнять, но нельзя задать хинт именно со статистикой. То есть фактически, когда ты знаешь, что PostgreSQL вот здесь ошибется, то дальше нужно все оставшиеся узлы самому "планировать" и везде давать хинты. То есть фактически повторять планировщик запросов PostgreSQL. План так себе...
danolivo
Ошибка восприятия. Эстимация выполняется по количеству ndistinct. Можно будет поразбираться, почему так совпало, но вангую, что дело в том, что в выражении соединения
line.docid = tmp_1.id - docid одновременно и отсортирован и тут же попадает в группировку, а еще, tmp_1.id де-факто уникально, что и приводит к такой корреляции значений. Но согласен, посмотреть в код стоит.На большинство таких замечаний ответ один - Postgres - это про технологии. А всё, что кроме - это слишком дорого в поддержке и банально не интересно исследователю/разработчику. Например, когда пушишь что-то в ядро, ты фактически расписываешься в том, что лично (а не под гарантии компании) будешь поддерживать и развивать фичу. Open-source держится на персоналиях и личном интересе. Что мешает делать свой форк за деньги и сделать все, что хочется? Однако опыт Greenplum и прочих показывает, что затраты растут быстро, а уважают по прежнему ваниллу ;).
danolivo
Ответ оказался прост: величина ndistinct по полю line.docid равна 65тыс туплов. А количество туплов, которые приходят в группировку из джойна - 3474. Отсюда Postgres делает совершенно логичный вывод, что никаких 65тыс быть не может - значит сделаем по верхнему краю - 3474. Вот и совпадает :)
danolivo
Попробовал набросать WIP патч, чтобы крепче спать ночью. Инфры под такую оптимизацию в постгресе совсем нет. В частности, если просто вставить признак уникальности по ключам группировки, то получается вот такой монстра:
То есть, нужно научиться пробрасывать эту уникальность через Subquery наверх, научиться упрощать (flatten) такой подзапрос и много чего ещё. Хотя сама по себе оценка улучшилась, это да.
CrushBy Автор
Понятно, что не самый оптимиальный, но уже гораздо лучше. Основной вопрос в том, а почему признака уникальности нет по умолчанию ? Не могу придумать ни одного случая, когда GROUP BY выдаст не уникальные ключи...
Что касается дополнительных оптимизаций, то да, это конечно большой простор для творчества. Другое дело, что тут конечно да, надо соблюдать баланс между затратами на оптимизации и самими результатами.
Мы когда-то в lsFusion пробовали подключать Oracle. Да, он выполнял запросы во многих местах лучше. Но у него была другая проблема. По мере роста размера запроса время его планирования начинало просто улетать в неприемлимые значения. Для человека, который пишет на языке lsFusion, формирование SQL-запросов "спрятано". И то, что выглядит просто с точки зрения написания может спокойно компилироваться в запрос размером в 2МБ (как говорится, with great power comes great responsibility). И PostgreSQL за счет простого алгоритма планирования спокойно планирует его за 100-200 мс, а потом еще тупо выполняет как видит, например, за 1с. А вот у Oracle во многих случаях время планирования просто уходило за 5с (а бывало и значительнее). Поэтому, в частности, на Oracle пока забили.
danolivo
А вот это очень даже интересно. Я бы почитал статью на эту тему. А ещё, мне почитал бы про то, как справляется Postgres с 2МБ текстом запроса. Понятно, что такой текст не посмотришь глазами, но вот мета-информация, как-то: потребление памяти, время планирования, флеймграф с пиками по ресурсам, какие этапы планирования становятся самыми дорогими - вот это точно было бы интересно.
Вот эта фраза меня весьма напрягла, признаться. Используя временные таблицы вы отключаете возможность выполнить запрос (поддерево) с помощью параллельных воркеров. А это кроме неэффективности еще и большой риск - на большом количестве джойнов Postgres имеет тенденцию сваливаться в NestLoop (см. кратко здесь), а параллельные воркеры (пусть и случайно) форсируют использование HashJoin и делают время выполнения адекватным.
Конечно, добавить параллельность для временных таблиц дело нехитрое, но это все равно строки кода в ядро - но все ли готовы выходить за рамки ваниллы?
CrushBy Автор
К сожалению, это не так просто воспроизвести, но возможно, когда будет время, попробую. А именно записать flamegraph для именно одного запроса (так как там рядом выполняются еще ряд других запросов).
Но важно понимать, что запрос там получается достаточно специфический (с точки зрения структуры). Он появляется, если на lsFusion написали какую-то сложную логику вычислений, когда на форму добавляется очень сложное вычисление (которое задействует кучу объектов, множество группировок по разным полям и так далее). Но даже в таком случае, при открытии формы скомпилируются относительно небольшой запрос.
Основная сложность появится тогда, когда пользователь начнет что-то менять. Проблема в том, что нельзя изменения пользователя просто записать в базу через INSERT/UPDATE/DELETE (не держать же открытой транзакцию на все время интерактива). Поэтому lsFusion автоматически все изменения на время складывает во временные таблицы (добавление/удаление объектов, изменения полей). Но дальше, чтобы пользователю показать исходное вычисленное значение, но с учетом сделанных (не сохраненнных) пользователем изменений, то в исходный запрос будут добавлено кучу разных JOIN/UNION и прочего с этими временными таблицами. В итоге его размер может вырасти многократно и достигнуть 2МБ.
Более того, даже 2МБ это не предел (в платформе это просто в настройках). Иногда, когда срабатывало такое ограничение, вместо изменения логики, когда не было времени разбираться, то мы просто поднимали ограничение до 8МБ, и все более менее нормально работало (по крайней мере, пользователи не жаловались).
Поэтому на практике, там в запросе было много небольших временных таблиц, и чаще всего в Nested Loop просто не скатывалось.
Тут два аспекта. У многих JOIN'ов проблема будет только, если их много на одном уровне запроса/подзапроса. А это достаточно редко. Чаще всего много JOIN получалось только, когда на одной форме вытягивается таблица с большим количеством колонок из разных других таблиц. Но тогда JOIN получается на самом высоком уровне, и там уже Nested Loop не получается, а получается просто Seq Scan с Hash Join (собственно это и есть основная боль, описанная в статье).
Что касается параллелизма, то да, с временными таблицами он в большинстве случаев отключается. Но тут сложный философский вопрос. Когда у нас где-то начинается нехватка ресурсов, то первым делом я обычно вообще отключаю любой параллелизм. Делается это для того, чтобы один пользователь не мог положить весь сервер. То есть даже, если он сделает какое-то кривое действие, которое сделает запрос огромной сложности, то лучше, чтобы он тихонько себе шуршал, загружая один CPU (хоть и ротируя shared buffers), чем, когда включатся Parallel workers и решат, что это очень важный пользователь - давайте все ресурсы отдадим ему, тем самым сильно просадив всех оставшихся.
В нашем же случае, он "повисит", а потом, если ему надоест, то он отменит его. Ну, а если ему очень надо, то подождет, и получит свои данные условно через 5 часов, вместо 30 минут, "изнасиловав" при этом весь сервер.
Veidt
Самое забавное, что такие запросы помогают выявить очень тяжело отлавливаемые баги. Помню лет 8 назад был баг, где запрос начинал выдавать неуникальные записи с какого то бодуна. Я все не мог поверить, что это баг postgres, очень долго искал проблему, но потом догадался проверить на других версиях. В итоге дальше долго делал воспроизводимый обезличенный кейс, заслал баг репортом, в итоге его пофиксили, причем даже с личным упоминанием в коммите :)
https://postgrespro.ru/list/thread-id/1305829
danolivo
And finally! Получилось - действительно, вы правы: для случая одной колонки там всего восемь строчек добавить. Получившийся EXPLAIN внизу, попробую на днях в сообщество закинуть изменение...
CrushBy Автор
Супер. Если будете закидывать изменение, то скиньте, пожалуйста, ссылку (если будет возможность). Будем наблюдать.
danolivo
Ага, пожалуйста:
https://www.postgresql.org/message-id/50fe6779-ee2d-4256-bc64-cd661bc4029a@gmail.com
Там по ходу я осознал, что истинной уникальности там не гарантируется: вероятны различия в операторе сравнения GROUP-BY и оператора выше. Пример - для numeric можем иметь исходные значения:
+0, -0, 0., 0.0, ...
С точки зрения математики - одинаковые. Но что если оператор сравнения ориентируется на строковое представление величин в этой колонке? - тогда ситуация меняется. Может и ещё что более жизненное получится придумать, хорошенько поразмыслив.
CrushBy Автор
Спасибо.
Но это крайне редкие случаи, которые возникают условно в 0,01% случаев.
С другой стороны, если поведение будет влиять только на планирование запросов, а не на результат, то лучше же, чтобы ошибка была в 0,01% случаев, чем в 99,99% ? Или это может привести к изменению результата ?
Понятно, что если это приведет к потере обратной совместимости, то вряд ли примут такой патч.
danolivo
Если бы влияло на результат, я бы даже не пытался делать: рассчитываем на 100% корректность результата, чтобы на практике иметь 99.9%.
А в смысле планирования может и пройдёт.