Комментарии 24
Интересное замечание, что можно попытаться форсить ограничение уникальности в плане на совокупность столбцов группировки. Непонятно, почему в сообществе до сих пор этого не сделали (подводные камни?), но скорее всего ответ в том, что никто не жаловался ;)
Что касаемо запросов - то в ситуации с tmp и исходной таблицей нет никакой магии:
1) Различий в алгоритмах эстимации между временными и невременными таблицами нет.
2) Автор возможно полагает, что эстимация выражения JOIN'a происходит с учетом нижележащего дерева запроса. Увы, это не так - селективность оценивается только по статистике таблиц, из которых взяты сравниваемые колонки и по количеству туплов, которые должны придти из поддерева слева и справа.
Магия здесь скорее всего в MCV статистике: по tmp она, вероятно, покрывает все значения в таблице, а по большой - только малую долю таблицы. Таким образом при джойне с tmp мы имеем хороший прогноз по совпадениям и можем предположить появление большого количества дубликатов из иннера нашего LEFT JOIN.
Когда же используется исходная таблица, то алгоритм подсчета статистики, сматчив MCV слева и справа, попадает на большую неопределенность и с оставшейся частью данных ему приходится эстимировать селективность джойна через количество distinct значений в колонке. И здесь мы получаем низкую селективность, вроде 1/1E6* 1/4.5E5, что скорее всего дает оценку меньше 1. А по формуле селективности LEFT JOIN, у вас количество результирующих туплов будет не меньше, чем количество туплов в левом входе. Отсюда и имеем цифру 50, которая "магически" точна.
Когда же используется исходная таблица, то алгоритм подсчета статистики, сматчив MCV слева и справа, попадает на большую неопределенность и с оставшейся частью данных ему приходится эстимировать селективность джойна через количество distinct значений в колонке.
Согласен, логично. Непонятно только, как это можно было легко использовать в качестве workaround для временной таблицы.
Интересное замечание, что можно попытаться форсить ограничение уникальности в плане на совокупность столбцов группировки
Вот тут мне и было интересно, что оптимизация вроде как самая базовая, и странно, что ее нет. Но ладно уникальность - вопрос еще почему совсем не уменьшается ожидаемое количество строк. Ведь PostgreSQL знает, что при группировке по docid количество записей уменьшится в среднем в 30 раз (если строки будут выбраны случайно). Зачем такая пессимистическая оценка ? Можно было бы уменьшать ожидание на меньший порядок, а тут вообще 1. И почему нет параметров, которые управляют этой оценкой.
Зачем такая пессимистическая оценка ?
Оно влияет на что-то в случае адекватных запросов? Ни в одном вашем примере не увидел запроса, который реально требует подзапросов.
PS
Приведите пример, где реально нужен подзапрос именно такого типа, а не join lateral. Может реально проблема...
Пример же простой. Вывести для заказов на странице суммы по ним. Да, можно хранить из в самой таблице заказов, постоянно туда записывая. Но, во-первых, это дополнительное место. А, во-вторых, а что, если нужно посчитать не сумму заказов, а сумму посчитанную по какому-то сложному алгоритму из строк. Что тогда ? Хранить все возможные варианты сумм в таблице с заказами и их обновлять.
Если же не хранить, то как без подзапросов посчитать ? Давайте вот на примере из статьи.
Вот тут мне и было интересно, что оптимизация вроде как самая базовая, и странно, что ее нет.
Это с какой колокольни смотреть. Я за много лет первый раз увидел такой пример. При том что делать это будет достаточно сложно - тут же уникальность только по всему набору группируемых столбцов обеспечивается. А что, если под группировкой лежит параметризованное сканирование - тогда на каждый ReScan мы можем получать разные результаты и формально, это уже не уникально - черт его знает, это пробовать нужно в коде.
PostgreSQL знает, что при группировке по docid количество записей уменьшится в среднем в 30 раз
Опять же: постгрес не смотрит на предысторию, только на базовые таблицы и их статистику. Это можно обойти на этапе исполнения, добавив какой-нибудь кастомный сборщик статистики в точке группировки, как DeWitt предлагал в 1998м. Но это опять-же делать нужно.
Зачем такая пессимистическая оценка ?
А это, кстати, общий подход. Лучше пессимистическая оценка и долгий HashJoin, нежели оптимистичная и вечный NestLoop ;)
почему нет параметров, которые управляют этой оценкой
Есть, хинты называются - см. pg_hint_plan. А в ванилле пока и не планируется, ибо open-source не про деньги, а про технологии. Но частные хинты на селективность выражений там обсуждали лет пять назад и вроде без особого негатива ...
Это с какой колокольни смотреть. Я за много лет первый раз увидел такой пример.
Но это же самый простой пример. Просто JOIN с GROUP BY. Собственно, а что потом еще можно делать с GROUP BY, кроме как JOIN по ключам ? И собственно большая часов подзапросов - это именно GROUP BY. Я конечно понимаю, что может у меня профдеформация, но видимо я что-то не понимаю...
При том что делать это будет достаточно сложно - тут же уникальность только по всему набору группируемых столбцов обеспечивается.
Так сделали бы хотя бы частный случай, когда группировка по одной колонкой. С одним значением в IN же сделали эвристику (как в примере в статье).
А что, если под группировкой лежит параметризованное сканирование - тогда на каждый ReScan мы можем получать разные результаты и формально, это уже не уникально - черт его знает, это пробовать нужно в коде.
Ну опять же - это редкий случай. И для каждого Rescan там все равно внутри будут уникальные значения.
Опять же: постгрес не смотрит на предысторию, только на базовые таблицы и их статистику.
Не очень понимаю, где противоречие. В WHERE же он оценивает количество записей на выходе. Что ему мешает тоже самое делать с GROUP BY, а не брать, что количество записей вообще не изменится - это же нонсенс.
А это, кстати, общий подход. Лучше пессимистическая оценка и долгий HashJoin, нежели оптимистичная и вечный NestLoop ;)
Есть такое. Уважаю :) Но, если я не хочу получить Nested Loop, то я просто в запросе могу его отключить. Более того, насколько я помню, у нас в lsFusion даже такая "оптимизация" есть. Выполняем запрос - долго не выполняется, значит скорее всего ошибся и ушел в Nested Loop. Отменяем запрос, отключаем его и отправляем повторно.
Но это же не повод вообще делать все пессимистично. Сделали бы хотя бы опцию для этого.
Есть, хинты называются - см. pg_hint_plan. А в ванилле пока и не планируется, ибо open-source не про деньги, а про технологии. Но частные хинты на селективность выражений там обсуждали лет пять назад и вроде без особого негатива ...
Ну вставлять в каждом запросе с GROUP BY - так себе идея. Но тут же проблема в другом. В этом расширении, как я понимаю, можно задавать хинты по тому, как выполнять, но нельзя задать хинт именно со статистикой. То есть фактически, когда ты знаешь, что PostgreSQL вот здесь ошибется, то дальше нужно все оставшиеся узлы самому "планировать" и везде давать хинты. То есть фактически повторять планировщик запросов PostgreSQL. План так себе...
Что ему мешает тоже самое делать с GROUP BY
Ошибка восприятия. Эстимация выполняется по количеству ndistinct. Можно будет поразбираться, почему так совпало, но вангую, что дело в том, что в выражении соединенияline.docid = tmp_1.id - docid одновременно и отсортирован и тут же попадает в группировку, а еще, tmp_1.id де-факто уникально, что и приводит к такой корреляции значений. Но согласен, посмотреть в код стоит.
Сделали бы хотя бы опцию для этого
На большинство таких замечаний ответ один - Postgres - это про технологии. А всё, что кроме - это слишком дорого в поддержке и банально не интересно исследователю/разработчику. Например, когда пушишь что-то в ядро, ты фактически расписываешься в том, что лично (а не под гарантии компании) будешь поддерживать и развивать фичу. Open-source держится на персоналиях и личном интересе. Что мешает делать свой форк за деньги и сделать все, что хочется? Однако опыт Greenplum и прочих показывает, что затраты растут быстро, а уважают по прежнему ваниллу ;).
Что ему мешает тоже самое делать с GROUP BY, а не брать, что количество записей вообще не изменится - это же нонсенс.
Ответ оказался прост: величина ndistinct по полю line.docid равна 65тыс туплов. А количество туплов, которые приходят в группировку из джойна - 3474. Отсюда Postgres делает совершенно логичный вывод, что никаких 65тыс быть не может - значит сделаем по верхнему краю - 3474. Вот и совпадает :)
Но это же самый простой пример. Просто JOIN с GROUP BY.
Попробовал набросать WIP патч, чтобы крепче спать ночью. Инфры под такую оптимизацию в постгресе совсем нет. В частности, если просто вставить признак уникальности по ключам группировки, то получается вот такой монстра:
Nested Loop Left Join (cost=3.47..121.14 rows=50 width=36) (actual time=0.261..3.221 rows=50 loops=1)
Join Filter: (sq.docid = tmp.id)
Rows Removed by Join Filter: 1225
-> Seq Scan on tmp (cost=0.00..1.50 rows=50 width=4) (actual time=0.021..0.047 rows=50 loops=1)
-> Materialize (cost=3.47..118.90 rows=1 width=36) (actual time=0.005..0.055 rows=26 loops=50)
Storage: Memory Maximum Storage: 19kB
-> Subquery Scan on sq (cost=3.47..118.89 rows=1 width=36) (actual time=0.222..2.478 rows=50 loops=1)
-> GroupAggregate (cost=3.47..118.89 rows=3474 width=36) (actual time=0.220..2.452 rows=50 loops=1)
Group Key: line.docid
-> Merge Join (cost=3.47..58.10 rows=3474 width=15) (actual time=0.142..1.799 rows=500 loops=1)
Merge Cond: (line.docid = tmp_1.id)
-> Index Scan using line_doc on line (cost=0.43..145872.49 rows=4500000 width=15) (actual time=0.071..1.241 rows=501 loops=1)
-> Sort (cost=2.91..3.04 rows=50 width=4) (actual time=0.057..0.082 rows=50 loops=1)
Sort Key: tmp_1.id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on tmp tmp_1 (cost=0.00..1.50 rows=50 width=4) (actual time=0.014..0.027 rows=50 loops=1)
Planning Time: 0.959 ms
Execution Time: 3.399 msТо есть, нужно научиться пробрасывать эту уникальность через Subquery наверх, научиться упрощать (flatten) такой подзапрос и много чего ещё. Хотя сама по себе оценка улучшилась, это да.
В частности, если просто вставить признак уникальности по ключам группировки, то получается вот такой монстра
Понятно, что не самый оптимиальный, но уже гораздо лучше. Основной вопрос в том, а почему признака уникальности нет по умолчанию ? Не могу придумать ни одного случая, когда GROUP BY выдаст не уникальные ключи...
Что касается дополнительных оптимизаций, то да, это конечно большой простор для творчества. Другое дело, что тут конечно да, надо соблюдать баланс между затратами на оптимизации и самими результатами.
Мы когда-то в lsFusion пробовали подключать Oracle. Да, он выполнял запросы во многих местах лучше. Но у него была другая проблема. По мере роста размера запроса время его планирования начинало просто улетать в неприемлимые значения. Для человека, который пишет на языке lsFusion, формирование SQL-запросов "спрятано". И то, что выглядит просто с точки зрения написания может спокойно компилироваться в запрос размером в 2МБ (как говорится, with great power comes great responsibility). И PostgreSQL за счет простого алгоритма планирования спокойно планирует его за 100-200 мс, а потом еще тупо выполняет как видит, например, за 1с. А вот у Oracle во многих случаях время планирования просто уходило за 5с (а бывало и значительнее). Поэтому, в частности, на Oracle пока забили.
По мере роста размера запроса время его планирования начинало просто улетать в неприемлимые значения.
А вот это очень даже интересно. Я бы почитал статью на эту тему. А ещё, мне почитал бы про то, как справляется Postgres с 2МБ текстом запроса. Понятно, что такой текст не посмотришь глазами, но вот мета-информация, как-то: потребление памяти, время планирования, флеймграф с пиками по ресурсам, какие этапы планирования становятся самыми дорогими - вот это точно было бы интересно.
добавим JOIN с нашей временной таблицей (кстати, платформа lsFusion при генерации запросов делает это автоматически)
Вот эта фраза меня весьма напрягла, признаться. Используя временные таблицы вы отключаете возможность выполнить запрос (поддерево) с помощью параллельных воркеров. А это кроме неэффективности еще и большой риск - на большом количестве джойнов Postgres имеет тенденцию сваливаться в NestLoop (см. кратко здесь), а параллельные воркеры (пусть и случайно) форсируют использование HashJoin и делают время выполнения адекватным.
Конечно, добавить параллельность для временных таблиц дело нехитрое, но это все равно строки кода в ядро - но все ли готовы выходить за рамки ваниллы?
А вот это очень даже интересно. Я бы почитал статью на эту тему. А ещё, мне почитал бы про то, как справляется Postgres с 2МБ текстом запроса. Понятно, что такой текст не посмотришь глазами, но вот мета-информация, как-то: потребление памяти, время планирования, флеймграф с пиками по ресурсам, какие этапы планирования становятся самыми дорогими - вот это точно было бы интересно.
К сожалению, это не так просто воспроизвести, но возможно, когда будет время, попробую. А именно записать flamegraph для именно одного запроса (так как там рядом выполняются еще ряд других запросов).
Но важно понимать, что запрос там получается достаточно специфический (с точки зрения структуры). Он появляется, если на lsFusion написали какую-то сложную логику вычислений, когда на форму добавляется очень сложное вычисление (которое задействует кучу объектов, множество группировок по разным полям и так далее). Но даже в таком случае, при открытии формы скомпилируются относительно небольшой запрос.
Основная сложность появится тогда, когда пользователь начнет что-то менять. Проблема в том, что нельзя изменения пользователя просто записать в базу через INSERT/UPDATE/DELETE (не держать же открытой транзакцию на все время интерактива). Поэтому lsFusion автоматически все изменения на время складывает во временные таблицы (добавление/удаление объектов, изменения полей). Но дальше, чтобы пользователю показать исходное вычисленное значение, но с учетом сделанных (не сохраненнных) пользователем изменений, то в исходный запрос будут добавлено кучу разных JOIN/UNION и прочего с этими временными таблицами. В итоге его размер может вырасти многократно и достигнуть 2МБ.
Более того, даже 2МБ это не предел (в платформе это просто в настройках). Иногда, когда срабатывало такое ограничение, вместо изменения логики, когда не было времени разбираться, то мы просто поднимали ограничение до 8МБ, и все более менее нормально работало (по крайней мере, пользователи не жаловались).
Поэтому на практике, там в запросе было много небольших временных таблиц, и чаще всего в Nested Loop просто не скатывалось.
Вот эта фраза меня весьма напрягла, признаться. Используя временные таблицы вы отключаете возможность выполнить запрос (поддерево) с помощью параллельных воркеров. А это кроме неэффективности еще и большой риск - на большом количестве джойнов Postgres имеет тенденцию сваливаться в NestLoop
Тут два аспекта. У многих JOIN'ов проблема будет только, если их много на одном уровне запроса/подзапроса. А это достаточно редко. Чаще всего много JOIN получалось только, когда на одной форме вытягивается таблица с большим количеством колонок из разных других таблиц. Но тогда JOIN получается на самом высоком уровне, и там уже Nested Loop не получается, а получается просто Seq Scan с Hash Join (собственно это и есть основная боль, описанная в статье).
Что касается параллелизма, то да, с временными таблицами он в большинстве случаев отключается. Но тут сложный философский вопрос. Когда у нас где-то начинается нехватка ресурсов, то первым делом я обычно вообще отключаю любой параллелизм. Делается это для того, чтобы один пользователь не мог положить весь сервер. То есть даже, если он сделает какое-то кривое действие, которое сделает запрос огромной сложности, то лучше, чтобы он тихонько себе шуршал, загружая один CPU (хоть и ротируя shared buffers), чем, когда включатся Parallel workers и решат, что это очень важный пользователь - давайте все ресурсы отдадим ему, тем самым сильно просадив всех оставшихся.
В нашем же случае, он "повисит", а потом, если ему надоест, то он отменит его. Ну, а если ему очень надо, то подождет, и получит свои данные условно через 5 часов, вместо 30 минут, "изнасиловав" при этом весь сервер.
А ещё, мне почитал бы про то, как справляется Postgres с 2МБ текстом запроса.
Самое забавное, что такие запросы помогают выявить очень тяжело отлавливаемые баги. Помню лет 8 назад был баг, где запрос начинал выдавать неуникальные записи с какого то бодуна. Я все не мог поверить, что это баг postgres, очень долго искал проблему, но потом догадался проверить на других версиях. В итоге дальше долго делал воспроизводимый обезличенный кейс, заслал баг репортом, в итоге его пофиксили, причем даже с личным упоминанием в коммите :)
почему признака уникальности нет по умолчанию ...
And finally! Получилось - действительно, вы правы: для случая одной колонки там всего восемь строчек добавить. Получившийся EXPLAIN внизу, попробую на днях в сообщество закинуть изменение...
Hash Right Join (cost=5.59..164.89 rows=50 width=36)
Hash Cond: (line.docid = tmp.id)
-> GroupAggregate (cost=3.47..118.89 rows=3474 width=36)
Group Key: line.docid
-> Merge Join (cost=3.47..58.10 rows=3474 width=15)
Merge Cond: (line.docid = tmp_1.id)
-> Index Scan using line_doc on line (cost=0.43..145872.49 rows=4500000 width=15)
-> Sort (cost=2.91..3.04 rows=50 width=4)
Sort Key: tmp_1.id
-> Seq Scan on tmp tmp_1 (cost=0.00..1.50 rows=50 width=4)
-> Hash (cost=1.50..1.50 rows=50 width=4)
-> Seq Scan on tmp (cost=0.00..1.50 rows=50 width=4)Получившийся EXPLAIN внизу, попробую на днях в сообщество закинуть изменение...
Супер. Если будете закидывать изменение, то скиньте, пожалуйста, ссылку (если будет возможность). Будем наблюдать.
Ага, пожалуйста:
https://www.postgresql.org/message-id/50fe6779-ee2d-4256-bc64-cd661bc4029a@gmail.com
Там по ходу я осознал, что истинной уникальности там не гарантируется: вероятны различия в операторе сравнения GROUP-BY и оператора выше. Пример - для numeric можем иметь исходные значения:
+0, -0, 0., 0.0, ...
С точки зрения математики - одинаковые. Но что если оператор сравнения ориентируется на строковое представление величин в этой колонке? - тогда ситуация меняется. Может и ещё что более жизненное получится придумать, хорошенько поразмыслив.
Спасибо.
Там по ходу я осознал, что истинной уникальности там не гарантируется
Но это крайне редкие случаи, которые возникают условно в 0,01% случаев.
С другой стороны, если поведение будет влиять только на планирование запросов, а не на результат, то лучше же, чтобы ошибка была в 0,01% случаев, чем в 99,99% ? Или это может привести к изменению результата ?
Понятно, что если это приведет к потере обратной совместимости, то вряд ли примут такой патч.
Сначала сделаем самым простым способом, который первый приходит в голову :
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).
Сразу с правильным планом. Или это особенности построения запросов в ORM?
Да, действительно такое вариант есть, и он более простой. Но он работает только в простом случае (когда внутри простой GROUP BY по одному параметру). К сожалению, в более сложных случаях, так развернуть подзапрос нельзя. А в реальности проблемы возникают на более сложных запросах. В данном случае, я просто привел простой пример, чтобы не "нагружать" читателя.
Кроме того, схема с LEFT JOIN в более сложных делает планы не очень предсказуемыми. Например, я добавил еще 3 LEFT JOIN с той же таблицой line. В результате план получается вот такой :
Ладно, что там оценки рядов вообще странные. Но там реально местами получается декартово произведение строк, что может привести к "сваливанию" выполнения в что-то очень непредсказуемое. С подзапросами да, есть ошибка по статистике, но хоть выполнение будет более-менее понятное.
Должно быть лучше, если в индекс line_doc добавить поле id и сделать этот индекс уникальным.
Можно вот такой индекс сделать
CREATE UNIQUE INDEX line_doc ON line (docid, id);
Странное поведение планировщика запросов PostgreSQL