На проекте внедрения машины баз данных Tantor XData у одного из заказчиков мы периодически анализировали топ длительных запросов к СУБД, чтобы следить за производительностью системы. Делаем мы это с помощью модуля «Расширенная аналитика» Платформы Tantor. Вот так выглядел топ запросов за 23 декабря:

На первом месте по длительности был запрос, который выполнялся в среднем 45 секунд целых 657 раз за день. Провалимся в него и посмотрим план запроса:

Для удобства будем оперировать текстовым вариантом плана. Вот так выглядит сам текст запроса:

SELECT
    min(t1._period)
FROM
    _accumrg93090 t1
WHERE
    t1._fld3457 = 0::numeric AND
    t1._recordertref = '\\000\\000\\006U'::bytea AND
    t1._recorderrref = '\\220\\275\\000PV\\221/\\330\\021\\360F2\\0215\\242R'::bytea
HAVING
    NOT min(t1._period) IS NULL;

А вот его план, который был получен путем выполнения запроса в psql:

Result  (cost=41.30..41.31 rows=1 width=8) (actual time=40215.806..40215.807 rows=0 loops=1)
  One-Time Filter: ((InitPlan 1).col1 IS NOT NULL)
  InitPlan 1
    ->  Limit  (cost=0.28..41.30 rows=1 width=8) (actual time=40215.804..40215.805 rows=0 loops=1)
          ->  Index Only Scan using _accumrg93090_1 on _accumrg93090 t1  (cost=0.28..13601149.12 rows=331597 width=8) (actual time=40215.803..40215.803 rows=0 loops=1)
                Index Cond: ((_fld3457 = '0'::numeric) AND (_recordertref = '\\x00000655'::bytea) AND (_recorderrref = '\\x90bd005056912fd811f04631fc40d22c'::bytea))
                Heap Fetches: 0
Planning Time: 0.228 ms
Execution Time: 40215.940 ms

По тексту запроса найдем контекст вызова в 1С. У заказчика была развернута система мониторинга 1С «Алькир», которая в том числе собирала все запросы длительностью более 0.1 секунды. За рассматриваемый день были отобраны все события DBPOSTGRS длительностью более 40 секунд:

И среди найденных событий ТЖ запрос быстро нашелся: он вызывался по следующему контексту:

Исходя из контекста стала понятна логика того, что происходит: внешняя обработка, запускаемая как регламентное задание, удаляет помеченные на изменение объекты, а при их удалении происходит стандартная проверка подсистемы БСП «Даты запрета изменений», которая определяет запросом минимальную дату движения документа по регистру _accumrg93090. И если эта дата будет в закрытом периоде, то удаление объекта должно быть отменено.

Разберем, почему этот запрос выполняется так долго. План показывает проблему: это операция сканирования индекса accumrg930901, которая занимает основное время выполнения запроса:

->  Index Only Scan using _accumrg93090_1 on _accumrg93090 t1  (cost=0.28..13601149.12 rows=331597 width=8) (actual time=40215.803..40215.803 rows=0 loops=1)

Индекс accumrg930901 состоит из таких полей: fld3457, period, recordertref, recorderrref, lineno. Красным выделено поле индекса, из-за которого Index scan выполняется так долго: поскольку условие отбора не содержит поля period, при выполнении запроса приходится просматривать индекс целиком, чтобы проверить условия фильтрации по полям recordertref и recorderrref.

В таблице есть более подходящий индекс accumrg930902: fld3457, recordertref, recorderrref, lineno. Он почти идентичен первому индексу, но не содержит поля _period, а значит, идеально подходит под условия запроса. Но почему планировщик выбирает не его?

Result  (cost=41.30..41.31 rows=1 width=8) (actual time=40215.806..40215.807 rows=0 loops=1)
  One-Time Filter: ((InitPlan 1).col1 IS NOT NULL)
  InitPlan 1
    ->  Limit  (cost=0.28..41.30 rows=1 width=8) (actual time=40215.804..40215.805 rows=0 loops=1)
          ->  Index Only Scan using _accumrg93090_1 on _accumrg93090 t1  (cost=0.28..13601149.12 rows=331597 width=8) (actual time=40215.803..40215.803 rows=0 loops=1)

Видим, что планировщик верно оценил, что стоимость поиска по данному индексу будет очень большой — 13601149.12, при этом общая стоимость запроса — всего 41.31. Дело в том, что первое поле индекса _fld3457 является общим реквизитом и имеет всего лишь одно уникальное значение:

select n_distinct from pg_stats where tablename = '_accumrg93090' and attname = '_fld3457' \gx
-[ RECORD 1 ]-
n_distinct | 1

Учитывая это, планировщик понимает, что вторым полем в индексе является period, и согласно тексту запроса необходимо выбрать его одно минимальное значение — min(t1.period). Данные в индексе по полю period упорядочены по возрастанию, значит, прочитанные данные операцией Index scan будут упорядочены по возрастанию касательно поля period, и можно применить оператор Limit, чтобы выбрать всего одну строку. На этом операторе стоимость запроса резко падает — с 13601149.12 до 41.30, и общая стоимость плана получается такой же небольшой. При планировании запроса планировщик также перебирает план с использованием индекса accumrg930902, и раз он выбирает не его, то очевидно, что общая стоимость получается выше.

С подобным кейсом мы уже встречались, см. статью «СУБД Tantor Postgres 17.5: Повышение точности статистики без увеличения default_statistics_target». Опыт подсказывал и понимание причины проблемы, и резонные следующие шаги. 

В плане запроса можно обратить внимание на ошибку в оценке количества выбираемых строк:

->  Index Only Scan using _accumrg93090_1 on _accumrg93090 t1  (cost=0.28..13601149.12 rows=331597 width=8) (actual time=40215.803..40215.803 rows=0 loops=1)

Вместо планируемых 331 597 строк фактически было выбрано 0. Это указывает на неравномерное распределение данных по полю recorderrref в таблице. Это поле – ссылка на регистратор, а в еще индексе есть поле lineno(НомерСтроки), и распределение было бы равномерным, если бы, к примеру,  каждый регистратор добавлял бы в таблицу примерно равное количество строк. Однако на практике такого не бывает, распределение данных по этому полю обычно выглядит так:

Заметно весьма неравномерное распределение (long-tail distribution): большинство регистраторов создали всего 1–10 записей каждый, в то время как несколько «тяжеловесов» сгенерировали сотни тысяч строк. Планировщик не видит эту неравномерность, и предполагает 'среднее' распределение.

По таблице _accumrg93090 имеем следующие данные:

  • Количество записей – 761 734 070

  • Количество различных значений _recorderrref – 2 354 514

  • 90% регистраторов _recorderrref имеют в среднем по 250 записей

  • Топ-100 регистраторов _recorderrref = 21% от всех записей

Планировщик при планировании запросов ориентируется на значительно меньшее значение различных recorderrref – всего 123 тыс. (и это при defaultstatistics_target = 1000):

select n_distinct from pg_stats where tablename = '_accumrg93090' and attname = '_recorderrref' \gx
-[ RECORD 1 ]------
n_distinct | 123584

Недооценка может приводить к  тому, что при планировании запроса на этапе определения оптимального плана по-настоящему оптимальные планы могут откидываться из-за высокой стоимости, на которую как раз влияет оценка количества выбираемых строк. Ведь чем больше строк нужно выбрать, тем больше нужно потратить ресурсов CPU, и тем выше будет стоимость оператора. А ведь фактически выбирается 0 строк! Можно предположить, что точнее будет статистика, тем меньше выбираемых строк будет оценивать планировщик, и тем меньше стоимость плана.

В Tantor Postgres увеличить точность скалярных статистик можно параметром STATMULTIPLIER, который позволяет изменить формулу выбора количества данных для расчета статистики следующим образом:

300 * default_statistics_target * STATMULTIPLIER

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

Первым делом мы в 10 раз увеличили размер анализируемой выборки для расчета статистики по полю _recorderrref и выполнили для этого следующую команду:

alter table _accumrg93090 alter column _RecorderRRef  SET STATMULTIPLIER 10;

Далее рассчитали статистику — теперь планировщик делает более точную оценку количества различных _recorderrref в таблице:

select n_distinct from pg_stats where tablename = '_accumrg93090' and attname = '_recorderrref' \gx
-[ RECORD 1 ]------
n_distinct | 830112

830 тыс. вместо 123 тыс. Выполняем запрос еще раз и смотрим план:

Result  (cost=135.97..135.98 rows=1 width=8) (actual time=40005.904..40005.906 rows=0 loops=1)
  One-Time Filter: ((InitPlan 1).col1 IS NOT NULL)
  InitPlan 1
    ->  Limit  (cost=0.28..135.97 rows=1 width=8) (actual time=40005.902..40005.903 rows=0 loops=1)
          ->  Index Only Scan using _accumrg93090_1 on _accumrg93090 t1  (cost=0.28..13597986.28 rows=100216 width=8) (actual time=40005.900..40005.901 rows=0 loops=1)
                Index Cond: ((_fld3457 = '0'::numeric) AND (_recordertref = '\\x00000655'::bytea) AND (_recorderrref = '\\x90bd005056912fd811f04631fc40d22c'::bytea))
                Heap Fetches: 0
Planning Time: 4.513 ms
Execution Time: 40006.036 ms

Что изменилось:

  1. Оценка строк стала более точной: 100216 ближе к нулю, чем 331597 :)

  2. Общая стоимость запроса возросла с 41.31 до 135.98. Почему так? Если кратко — более высокая точность статистики привела к тому, что оператор Limit изменил свою оценку в большую сторону.

Что не изменилось: наш запрос выполняется все так же долго. Увеличим размер анализируемой выборки еще на 5 пунктов:

alter table _accumrg93090 alter column _RecorderRRef  SET STATMULTIPLIER 15;

Оценка количества различных _recorderrref:

select n_distinct from pg_stats where tablename = '_accumrg93090' and attname = '_recorderrref' \gx
-[ RECORD 1 ]------------
n_distinct | 1.085026e+06

Теперь выполним наш запрос:

Aggregate  (cost=114.71..114.72 rows=1 width=8) (actual time=0.017..0.017 rows=0 loops=1)
  Filter: (min(_period) IS NOT NULL)
  Rows Removed by Filter: 1
  ->  Index Scan using _accumrg93090_2 on _accumrg93090 t1  (cost=0.28..114.51 rows=204 width=8) (actual time=0.016..0.016 rows=0 loops=1)
        Index Cond: ((_fld3457 = '0'::numeric) AND (_recordertref = '\\x00000655'::bytea) AND (_recorderrref = '\\x90bd005056912fd811f04631fc40d22c'::bytea))
Planning Time: 4.845 ms
Execution Time: 0.042 ms

Запрос выполнился моментально: вместо исходных 40 215.940 мс — всего лишь 0.042 мс. Почти в миллион раз быстрее!

Теперь выбирается индекс accumrg930902, стоимость выборки по которому ничтожно мала по сравнению с accumrg930901: 114.51 вместо 13601149.12. И общая стоимость запроса — 114.72 — меньше чем 135.98, которая была при STATMULTIPLIER 10 по неоптимальному индексу.

Ради эксперимента мы увеличили STATMULTIPLIER еще несколько раз, чтобы посмотреть, как изменятся оценки планировщика:

STATMULTIPLIER

estimated rows

Стоимость оператора Index Scan using_accumrg93090_2

n_distinct по полю _recorderrref

15

204

114.51

1.085026e+06

20

172

96.84

1.280717e+06

30

142

79.64

1.555516e+06

Вывод очевиден: более точная статистика позволяет планировщику повышать качество планирования.

Как же подобрать значение STATMULTIPLIER? Универсальной рекомендации нет. Конкретно в рассмотренном случае планировщик стал выбирать более оптимальный план, когда значение n_distinct поля recorderrref из pgstats достигло примерно 50% от фактического количества различных значений recorderrref в таблице. Также может пригодиться запрос, который вернет количество ndistinct, рассчитываемое статистикой: 

select
    count(distinct _recorderrref) as n_distinct
from
(
    select
        _recorderrref as _recorderrref
    from _accumrg93090
    order by
        random()
    limit SAMPLE_SIZE
);

где SAMPLE_SIZE = 300 default_statistics_target STATMULTIPLIER

О разработке STATMULTIPLIER подробно рассказывал разработчик «Тантор Лабс» Артем Бугаенко на конференции PG Bootcamp Russia 2025. К слову, следующая конференция PG BootCamp пройдет в марте 2026 г. в Москве, и сейчас проходит сбор заявок на выступления от потенциальных спикеров.