500 мс на планирование: как статистика PostgreSQL замедлила запрос в 20 раз

[OlegIct]

default_statistics_target всего 500

в 5 раз больше, чем значение по умолчанию, это много. Какое время планирования при значении по умолчанию?

What can you do? Not much, but still:1. Reduce the statistical target on tables causing the problem

Вы дали правильное решение. Жалоба человека в pgsql-performance свелась к: «я поменял значения по умолчанию и у меня стало медленно работать, что делать»

[danolivo]

Умолчания говорят ровно ни о чем - для больших таблиц этого явно недостаточно - если их хранить в отсортированном виде, а статистику кэшировать, то, полагаю, тысяч до 5 элементов таких явных эффектов мы бы не заметили.

[alex7six]

Мы в Tantor Postgres с подобной проблемой сталкивались.
Подробнее о решении здесь и здесь.
Есть еще одна проблема долгого планирования, которая связана не со статистикой, а с перебором варианта соединения таблиц. Текст запроса - https://disk.360.yandex.ru/d/q57qF5eoy6KQNg, план выполнения - https://explain.tensor.ru/archive/explain/4a10aec20a04cd5cfa312221e61a47f1:0:2025-08-06#explain
Planning time при разных значениях join_collapse_limit и from_collapse_limit:
12 - 591 ms
10 - 155 ms
8 - 58 ms
Возможно здесь могло помочь кеширование плана запроса или оптимизация перебора вариантов соединения таблиц? Это не единственный такой пример, уже много таких случаев встречал

[danolivo]

Мы в Tantor Postgres с подобной проблемой сталкивались.

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

Возможно здесь могло помочь кеширование плана запроса 

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

[pg_ilia]

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

На момент написания статьи и в версии 17.6 мы действительно использовали сортировку перед сравнением. Однако сейчас реализован подход, описанный по второй ссылке : мы строим хэш-таблицу по MCV-статистике одного столбца и затем ищем совпадения по значениям MCV другого столбца. После этого изменения сопоставление MCV больше не опирается на вложенные циклы с большим количеством повторных вызовов byteaeq(), и в профилировании его вклад в CPU больше не выделяется.

[danolivo]

Отлично. Тогда остается только вторая часть вопроса: кэшируете ли вы статистику (детостированную, распакованную, хэшированную)? Поскольку, как показано в данном примере, 200+ раз делать одно и то же для одной колонки может быть накладно.

Копаясь в исходниках этих двух форков я заметил, что в одном из них реализовано переиспользование статистики, хоть и в достаточно простом виде, а в другом - нет (((.

[pg_ilia]

Отлично. Тогда остается только вторая часть вопроса: кэшируете ли вы статистику (детостированную, распакованную, хэшированную)? Поскольку, как показано в данном примере, 200+ раз делать одно и то же для одной колонки может быть накладно.

Нет, сейчас мы не кэшируем статистику. После перехода на хэш-поиск для сопоставления MCV время планирования заметно сократилось. Фактически основная проблема была решена сменой алгоритма, поэтому мы пока не видим дальнейшие оптимизации целесообразными.

[danolivo]

хм, сомнительно. Кэширование статистики в ‘референсном форке’ Postgres снизило время планирования с 500мс, до 80. Это значит, что хэширование может дать 80-> 20. Вроде как эффект одного уровня? У меня нет вашего кода чтобы проверить, но выглядит как потенциально полезная фича.

[pg_ilia]

Я когда впервые увидел проблему с долгим временем планирования при вычислении селективности в джойнах, я тоже пробовал кэшировать детостанные данные на время двойного цикла в eqseljoin. Но применение хэш-поиска сокращает время сильнее. Поэтому на хэш-поиске и остановился. Быть может реально в 'референсном форке' лучше будет.

Скидываю код с хэш-поиском

[danolivo]

Проверил. Здесь этот патч портированный на 17.5 - неплохо было бы кстати бэкпортировать его и в 1С-ных энтерпрайзах. Получилось как и прогнозировалось - порядка 70мс на планировании. Лучше, но не всё - вспомним, что статистики могут быть весьма тяжёлые (и скалярный MCV это только одна из них). Поэтому меня пока греет идея хранить 'горячие' данные в подготовленном виде.

[alex7six]

Мы в Tantor Postgres его уже давно портировали. Задача возникла как раз также из кейса 1С. У меня даже проблемный план сохранился, вот он - https://explain.tensor.ru/archive/explain/81c9c7b81127e66de65c8ebe146e15f4:0:2025-05-30

[danolivo]

Действительно, в указанном примере распаковывать статистику приходится нечасто, а гистограммы вообще не нужны. Значит он мог детектировать только проблему обхода MCV массива.