17.09.2024 08:05
CrushBy 24 6100 Источник

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


  1. danolivo
    17.09.2024 09:39
    #27303892
    +2

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

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

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

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

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

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


    1. CrushBy Автор
      17.09.2024 09:39
      #27303952

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

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

      Интересное замечание, что можно попытаться форсить ограничение уникальности в плане на совокупность столбцов группировки

      Вот тут мне и было интересно, что оптимизация вроде как самая базовая, и странно, что ее нет. Но ладно уникальность - вопрос еще почему совсем не уменьшается ожидаемое количество строк. Ведь PostgreSQL знает, что при группировке по docid количество записей уменьшится в среднем в 30 раз (если строки будут выбраны случайно). Зачем такая пессимистическая оценка ? Можно было бы уменьшать ожидание на меньший порядок, а тут вообще 1. И почему нет параметров, которые управляют этой оценкой.


      1. asdfddsa
        17.09.2024 09:39
        #27304036

        Зачем такая пессимистическая оценка ?

        Оно влияет на что-то в случае адекватных запросов? Ни в одном вашем примере не увидел запроса, который реально требует подзапросов.

        PS

        Приведите пример, где реально нужен подзапрос именно такого типа, а не join lateral. Может реально проблема...


        1. CrushBy Автор
          17.09.2024 09:39
          #27304100

          Пример же простой. Вывести для заказов на странице суммы по ним. Да, можно хранить из в самой таблице заказов, постоянно туда записывая. Но, во-первых, это дополнительное место. А, во-вторых, а что, если нужно посчитать не сумму заказов, а сумму посчитанную по какому-то сложному алгоритму из строк. Что тогда ? Хранить все возможные варианты сумм в таблице с заказами и их обновлять.

          Если же не хранить, то как без подзапросов посчитать ? Давайте вот на примере из статьи.


          1. unfilled
            17.09.2024 09:39
            #27308798
            +1

            Приведите пример, где реально нужен подзапрос именно такого типа, а не join lateral.

            На этот вопрос вы не ответили. Почему бы не использовать lateral?


      1. danolivo
        17.09.2024 09:39
        #27304142
        +1

        Вот тут мне и было интересно, что оптимизация вроде как самая базовая, и странно, что ее нет. 

        Это с какой колокольни смотреть. Я за много лет первый раз увидел такой пример. При том что делать это будет достаточно сложно - тут же уникальность только по всему набору группируемых столбцов обеспечивается. А что, если под группировкой лежит параметризованное сканирование - тогда на каждый ReScan мы можем получать разные результаты и формально, это уже не уникально - черт его знает, это пробовать нужно в коде.

        PostgreSQL знает, что при группировке по docid количество записей уменьшится в среднем в 30 раз

        Опять же: постгрес не смотрит на предысторию, только на базовые таблицы и их статистику. Это можно обойти на этапе исполнения, добавив какой-нибудь кастомный сборщик статистики в точке группировки, как DeWitt предлагал в 1998м. Но это опять-же делать нужно.

        Зачем такая пессимистическая оценка ? 

        А это, кстати, общий подход. Лучше пессимистическая оценка и долгий HashJoin, нежели оптимистичная и вечный NestLoop ;)

        почему нет параметров, которые управляют этой оценкой

        Есть, хинты называются - см. pg_hint_plan. А в ванилле пока и не планируется, ибо open-source не про деньги, а про технологии. Но частные хинты на селективность выражений там обсуждали лет пять назад и вроде без особого негатива ...


        1. CrushBy Автор
          17.09.2024 09:39
          #27304362

          Это с какой колокольни смотреть. Я за много лет первый раз увидел такой пример.

          Но это же самый простой пример. Просто 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. План так себе...


          1. danolivo
            17.09.2024 09:39
            #27304606

            Что ему мешает тоже самое делать с GROUP BY

            Ошибка восприятия. Эстимация выполняется по количеству ndistinct. Можно будет поразбираться, почему так совпало, но вангую, что дело в том, что в выражении соединенияline.docid = tmp_1.id - docid одновременно и отсортирован и тут же попадает в группировку, а еще, tmp_1.id де-факто уникально, что и приводит к такой корреляции значений. Но согласен, посмотреть в код стоит.

            Сделали бы хотя бы опцию для этого

            На большинство таких замечаний ответ один - Postgres - это про технологии. А всё, что кроме - это слишком дорого в поддержке и банально не интересно исследователю/разработчику. Например, когда пушишь что-то в ядро, ты фактически расписываешься в том, что лично (а не под гарантии компании) будешь поддерживать и развивать фичу. Open-source держится на персоналиях и личном интересе. Что мешает делать свой форк за деньги и сделать все, что хочется? Однако опыт Greenplum и прочих показывает, что затраты растут быстро, а уважают по прежнему ваниллу ;).


          1. danolivo
            17.09.2024 09:39
            #27306182

            Что ему мешает тоже самое делать с GROUP BY, а не брать, что количество записей вообще не изменится - это же нонсенс.

            Ответ оказался прост: величина ndistinct по полю line.docid равна 65тыс туплов. А количество туплов, которые приходят в группировку из джойна - 3474. Отсюда Postgres делает совершенно логичный вывод, что никаких 65тыс быть не может - значит сделаем по верхнему краю - 3474. Вот и совпадает :)


          1. danolivo
            17.09.2024 09:39
            #27306444

            Но это же самый простой пример. Просто 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) такой подзапрос и много чего ещё. Хотя сама по себе оценка улучшилась, это да.


            1. CrushBy Автор
              17.09.2024 09:39
              #27307044

              В частности, если просто вставить признак уникальности по ключам группировки, то получается вот такой монстра

              Понятно, что не самый оптимиальный, но уже гораздо лучше. Основной вопрос в том, а почему признака уникальности нет по умолчанию ? Не могу придумать ни одного случая, когда GROUP BY выдаст не уникальные ключи...

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

              Мы когда-то в lsFusion пробовали подключать Oracle. Да, он выполнял запросы во многих местах лучше. Но у него была другая проблема. По мере роста размера запроса время его планирования начинало просто улетать в неприемлимые значения. Для человека, который пишет на языке lsFusion, формирование SQL-запросов "спрятано". И то, что выглядит просто с точки зрения написания может спокойно компилироваться в запрос размером в 2МБ (как говорится, with great power comes great responsibility). И PostgreSQL за счет простого алгоритма планирования спокойно планирует его за 100-200 мс, а потом еще тупо выполняет как видит, например, за 1с. А вот у Oracle во многих случаях время планирования просто уходило за 5с (а бывало и значительнее). Поэтому, в частности, на Oracle пока забили.


              1. danolivo
                17.09.2024 09:39
                #27307620

                По мере роста размера запроса время его планирования начинало просто улетать в неприемлимые значения. 

                А вот это очень даже интересно. Я бы почитал статью на эту тему. А ещё, мне почитал бы про то, как справляется Postgres с 2МБ текстом запроса. Понятно, что такой текст не посмотришь глазами, но вот мета-информация, как-то: потребление памяти, время планирования, флеймграф с пиками по ресурсам, какие этапы планирования становятся самыми дорогими - вот это точно было бы интересно.

                добавим JOIN с нашей временной таблицей (кстати, платформа lsFusion при генерации запросов делает это автоматически)

                Вот эта фраза меня весьма напрягла, признаться. Используя временные таблицы вы отключаете возможность выполнить запрос (поддерево) с помощью параллельных воркеров. А это кроме неэффективности еще и большой риск - на большом количестве джойнов Postgres имеет тенденцию сваливаться в NestLoop (см. кратко здесь), а параллельные воркеры (пусть и случайно) форсируют использование HashJoin и делают время выполнения адекватным.

                Конечно, добавить параллельность для временных таблиц дело нехитрое, но это все равно строки кода в ядро - но все ли готовы выходить за рамки ваниллы?


                1. CrushBy Автор
                  17.09.2024 09:39
                  #27307956

                  А вот это очень даже интересно. Я бы почитал статью на эту тему. А ещё, мне почитал бы про то, как справляется 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 минут, "изнасиловав" при этом весь сервер.


                1. Veidt
                  17.09.2024 09:39
                  #27308374
                  +2

                  А ещё, мне почитал бы про то, как справляется Postgres с 2МБ текстом запроса.

                  Самое забавное, что такие запросы помогают выявить очень тяжело отлавливаемые баги. Помню лет 8 назад был баг, где запрос начинал выдавать неуникальные записи с какого то бодуна. Я все не мог поверить, что это баг postgres, очень долго искал проблему, но потом догадался проверить на других версиях. В итоге дальше долго делал воспроизводимый обезличенный кейс, заслал баг репортом, в итоге его пофиксили, причем даже с личным упоминанием в коммите :)

                  https://postgrespro.ru/list/thread-id/1305829


              1. danolivo
                17.09.2024 09:39
                #27310552

                почему признака уникальности нет по умолчанию ...

                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)


                1. CrushBy Автор
                  17.09.2024 09:39
                  #27310706

                  Получившийся EXPLAIN внизу, попробую на днях в сообщество закинуть изменение...

                  Супер. Если будете закидывать изменение, то скиньте, пожалуйста, ссылку (если будет возможность). Будем наблюдать.


                  1. danolivo
                    17.09.2024 09:39
                    #27313646

                    Ага, пожалуйста:

                    https://www.postgresql.org/message-id/50fe6779-ee2d-4256-bc64-cd661bc4029a@gmail.com

                    Там по ходу я осознал, что истинной уникальности там не гарантируется: вероятны различия в операторе сравнения GROUP-BY и оператора выше. Пример - для numeric можем иметь исходные значения:

                    +0, -0, 0., 0.0, ...

                    С точки зрения математики - одинаковые. Но что если оператор сравнения ориентируется на строковое представление величин в этой колонке? - тогда ситуация меняется. Может и ещё что более жизненное получится придумать, хорошенько поразмыслив.


                    1. CrushBy Автор
                      17.09.2024 09:39
                      #27313698

                      Спасибо.

                      Там по ходу я осознал, что истинной уникальности там не гарантируется

                      Но это крайне редкие случаи, которые возникают условно в 0,01% случаев.

                      С другой стороны, если поведение будет влиять только на планирование запросов, а не на результат, то лучше же, чтобы ошибка была в 0,01% случаев, чем в 99,99% ? Или это может привести к изменению результата ?

                      Понятно, что если это приведет к потере обратной совместимости, то вряд ли примут такой патч.


                      1. danolivo
                        17.09.2024 09:39
                        #27313838

                        Если бы влияло на результат, я бы даже не пытался делать: рассчитываем на 100% корректность результата, чтобы на практике иметь 99.9%.

                        А в смысле планирования может и пройдёт.


  1. pluzanov
    17.09.2024 09:39
    #27311578

    Сначала сделаем самым простым способом, который первый приходит в голову :

    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).


    1. CrushBy Автор
      17.09.2024 09:39
      #27313622

      Сразу с правильным планом. Или это особенности построения запросов в ORM?

      Да, действительно такое вариант есть, и он более простой. Но он работает только в простом случае (когда внутри простой GROUP BY по одному параметру). К сожалению, в более сложных случаях, так развернуть подзапрос нельзя. А в реальности проблемы возникают на более сложных запросах. В данном случае, я просто привел простой пример, чтобы не "нагружать" читателя.

      Кроме того, схема с LEFT JOIN в более сложных делает планы не очень предсказуемыми. Например, я добавил еще 3 LEFT JOIN с той же таблицой line. В результате план получается вот такой :

      Ладно, что там оценки рядов вообще странные. Но там реально местами получается декартово произведение строк, что может привести к "сваливанию" выполнения в что-то очень непредсказуемое. С подзапросами да, есть ошибка по статистике, но хоть выполнение будет более-менее понятное.


  1. LaRN
    17.09.2024 09:39
    #27312800

    Должно быть лучше, если в индекс line_doc добавить поле id и сделать этот индекс уникальным.


    1. CrushBy Автор
      17.09.2024 09:39
      #27313632

      Можете более подробно описать, что Вы имеете ввиду ? По таблице line есть уникальный индекс по id. А сам индекс line_doc никак уникальным сделать не получится.


  1. LaRN
    17.09.2024 09:39
    #27313856

    Можно вот такой индекс сделать

    CREATE UNIQUE INDEX line_doc ON line (docid, id);