Запрос выполняется за 25 мс, но планируется 500 мс. База небольшая, в запросе 9 таблиц, default_statistics_target всего 500. Откуда такой разрыв?
Недавно в pgsql-performance поступил именно такой кейс. Разбор показал неожиданного виновника: статистика по колонкам таблиц в pg_statistic.
В Postgres для оценки трудоёмкости этапов выполнения запроса мы используем MCV, Histogram, distinct и др статистики, хранящиеся в таблице pg_statistic. Если по-умолчанию, статистическая выборка не превышает 100 элементов, то для больших таблиц нам требуется сильно больше. И 1000 - 2500 элементов не выглядит большим числом для репрезентации, например, 10 млрд. строк. Соответственно возникает вопрос: а не случится ли так, что большой массив элементов, например, MCV существенно повлияет на трудоёмкость планирования запроса, даже если выполняется он ничтожно малое время?
Наблюдаем типичный 1С-ный автогенерируемый запрос (см. query.sql). Даже default_statistics_target ниже рекомендуемых для 1С величин - всего-то 500 элементов. В этом запросе Я насчитал 12 джойнов, однако 9 из них раскиданы по примитивным подпланам и пространство поиска ограничено всего тремя джойнами - не бог весть что. Взглянув в EXPLAIN можно увидеть, что в процессе планирования трогается всего 5 буферных страниц - это совсем немного. При этом на альтернативном форке Postgres, которых развелось нынче много, при практически идентичном эксплейне время планирования сильно меньше - порядка 80 мс. Будем использовать эту альтернативную реализацию в качестве контрольного образца ;).
Первое подозрение очевидно - разработчики могли расширить пространство поиска оптимизатора и он просто перебирает множество дополнительных путей. Поэтому я сразу пошёл смотреть в flamegraph. Он оказался весьма похож для варианта с долгим планированием и с быстрым в альтернативном форке Postgres'a. Детальное изучение показало, что пространство поиска в обоих случаях расширено стандартными для русскоязычных форков фичами Joinsel и 'Append over IndexScan', но на этом всё. Также можно заметить, что узким местом является операция сравнения byteaeq(), которая триггерится функцией оценки стомости индекса cost_index() и toast_raw_datum_size(). А дёргает её оптимизатор, перебирая все возможные варианты индексов по всем возможным выражениям - а это не только те выражения, что явно указаны в запросе, но и математически выводимые из операций равенства "классы эквивалентности".
Но много ли в запросе выражений?
Три колонки: inforg10621::fld10622rref, inforg10621::fld15131rref и inforg8199::fld8200_rrref задействованы в 20 выражениях, 15 из которых - join clauses. Учитывая количество индексов - только эти две таблицы имеют на двоих восемь индексов - становится понятно, что количество рассматриваемых комбинаций может быть большим.
Но как подтвердить догадку? Как много раз оптимизатор в реальности обращался к статистике по таблицам? Такой информации в текущем PostgreSQL не предусмотрено. Поэтому я обратился к своему проекту - pg_index_stats, который используя хуки ядра Postgres (relation_stats_hook и get_index_stats_hook) собирает такую статистику и выводит на EXPLAIN. Итого имеем в нашем случае следующий результат, а для альтернативного форка несколько другой:
Колонка | Обращений (1C) | Обращений (alt) | MCV | Histogram | ndistinct | width |
|---|---|---|---|---|---|---|
inforg10621.fld10622rref | 217 | 138 | 500 | 501 | 1073 | 17 |
inforg8199.fld8201rref | 195 | 105 | 500 | 501 | 1104 | 20 |
inforg10621.fld809 | 156 | 156 | 1 | — | 1 | 3 |
inforg8199.fld809 | 128 | 128 | 1 | — | 1 | 3 |
reference312.fld809 | 36 | 36 | 1 | — | 1 | 3 |
reference127.fld809 | 31 | 31 | 1 | — | 1 | 3 |
inforg10621.fld15131rref | 12 | 12 | 161 | — | 161 | 17 |
inforg8199.fld8200_rtref | 10 | 10 | 16 | 12 | 28 | 8 |
inforg8199.fld8200_type | 10 | 10 | 1 | — | 1 | 5 |
Можно заметить, что к статистике по четырем полям обращались больше 100 раз каждое, а для колонки fld10622rref оптимизатор фетчил, распаковывал и использовал статистику 217 раз! И если в случае с числовым почти уникальным fld809 это не особо важно, поскольку в нем отсутствует гистограмма и MCV, то в других случаях приходится детостить и разжимать солидные массивы. Альтернативный форк обращается к статистике примерно в два раза реже - прилично, хотя не до конца объясняет существенный разрыв во времени планирования.
Ок, давайте копать дальше. А что за статистика у нас имеется и в каком объёме?
Дамп статистики по обеим версиям Postgres (здесь, здесь) показывает, что для наших таблиц действительно имеются массивы MCV и Histograms до 500 элементов по нескольким колонкам. Распакованный размер их составляет десятки килобайт (а в сжатом больше 2кб) и очевидно, что для извлечения их требуется детост и декомпрессия перед использованием. Неужели так много раз нам требуется вытащить конкретную большую статистику и использовать её? В конце-концов, в Postgres имеется кэширование, которое позволяет вычислять селективность конкретного выражения только один раз ... .
Однако у нас есть два очевидных кандидата - колонки fld10622rref и fld8201rref. Попробуем проверить догадку, обнулив механически статистику по ним и посмотрим, что получится.
UPDATE pg_statistic SET stanumbers1 = CASE WHEN stakind1 = 1 THEN NULL ELSE stanumbers1 END, stavalues1 = CASE WHEN stakind1 = 1 THEN NULL ELSE stavalues1 END, stakind1 = CASE WHEN stakind1 = 1 THEN 0 ELSE stakind1 END, stanumbers2 = CASE WHEN stakind2 = 1 THEN NULL ELSE stanumbers2 END, stavalues2 = CASE WHEN stakind2 = 1 THEN NULL ELSE stavalues2 END, stakind2 = CASE WHEN stakind2 = 1 THEN 0 ELSE stakind2 END WHERE (starelid = '_inforg10621'::regclass AND staattnum = ( SELECT attnum FROM pg_attribute WHERE (attrelid = '_inforg10621'::regclass AND attname = '_fld10622rref'))) OR (starelid = '_inforg8199'::regclass AND staattnum = ( SELECT attnum FROM pg_attribute WHERE (attrelid = '_inforg8199'::regclass AND attname = '_fld8201rref')));
EXPLAIN показывает время планирования на уровне 30мс:
Planning: Buffers: shared hit=5 Memory: used=4030kB allocated=4096kB Planning Time: 31.347 ms Execution Time: 0.237 ms
Если удалить вообще всю статистику командой:
DELETE * FROM pg_statistic;
то мы увидим теоретический минимум времени планирования этого запроса:
Planning: Buffers: shared hit=5 Memory: used=3932kB allocated=4096kB Planning Time: 18.477 ms Execution Time: 0.421 ms
что вполне согласуется с временем планирования на альтернативном форке.
Итак, повышенное время выполнения вызвано статистикой, однако чем конкретно: накладными расходами на распаковку статистики или многократным проходом по длинным спискам элементов MCV или гистограммы? - вероятно, здесь имеется вклад от обоих факторов.
Косвенно, влияние многократного хождения по массиву MCV подтверждает тот факт, что если сменить тип хранения колонок в pg_statistic с EXTENDED на EXTERNAL, то не меняется ровным счётом ничего:
DELETE * FROM pg_statistic; SET allow_system_table_mods = 'on'; ALTER TABLE pg_statistic ALTER COLUMN stavalues1 SET STORAGE EXTERNAL; ... VACUUM ANALYZE;
Итак, можно заключить, что проблема возникла из-за увеличения пространства поиска оптимизатора, вызванного в свою очередь увеличением номенклатуры индексов и размера статистики таблиц. И то и другое вполне легально и может возникнуть не только в 1С. Другое дело, что не вполне очевидно, как часто такое замедление может привести к реальным проблемам - запрос не потребляет много дисковых ресурсов или памяти, поэтому особо не мешает соседям.
И что же делать?
Первое, что стоит сделать - это реализовать систему кэширования часто используемой объёмной статистики. Это можно реализовать даже расширением (по аналогии с тем, как я выше собирал обращения к статистике в pg_index_stats). Подобный код не будет слишком сложным - обычный модуль, аллоцирующий сегмент DSM под хэш-таблицу и распакованную статистику.
Также, стоит поискать баланс и вероятно хранить MCV в отсортированном виде (если позволяет тип данных) начиная с некоторого размера массива, чтобы обеспечивать быстрый матчинг элементов слева и справа при эстимации JOIN выражений и быстрый поиск конкретного значения при эстимации фильтров.
Второе - можно уменьшить размер статистики на проблемной таблице или колонке:
ALTER TABLE table_name ALTER COLUMN column_name SET STATISTICS 0;
Тут сразу возникает вопрос: а как детектировать проблемное место? Универсального ответа нет - прогонять EXPLAIN сомнительного запроса с и без статистики, а потом проделать тот же анализ, что я делал выше.
Ну и конечно, писать вендору - ведь нет предела совершенству!
THE END.
Турция, Истамбул, 26 января 2026 г.
