Здесь я описываю результаты разработки одного расширения Postgres, которое сделал просто ради любопытства. Суть его состоит в автоматическом управлении расширенной статистикой по колонкам таблицы. Идея родилась в момент, когда заканчивая работу над очередным "умным" query-driven продуктом улучшения качества планирования Postgres я осознал, что архитектура этой СУБД пока ещё не готова к полностью автономной работе - автоматическому детектированию плохих планов и подстройки оптимизатора. Так может быть зайти с другой стороны, и попробовать сделать автономный data-driven помогатор?
Extended statistics - а что это?
Инструмент extended statistics позволяет указать Postgres, что по некоторому набору колонок таблицы нужно собирать дополнительные статистики. Зачем это нужно? - попробую быстро объяснить на примере открытой базы электростанций. Например, тип топлива (primary_fuel) используемый электростанцией неявно связан с названием страны (country). Поэтому, выполняя простой запрос:
SELECT count(*) FROM power_plants
WHERE country = '<XXX>' AND primary_fuel = 'Solar';мы увидим, что для Норвегии это число равно нулю, а для Испании - 243. Это очевидно для нас, поскольку связано с широтой, однако СУБД этого не знает и на этапе планирования запроса некорректно оценивает выборку (rows number): 93 для Норвегии и 253 для Испании. Если запрос окажется чуть более сложным и данные эстимации будут входом для какого-нибудь оператора JOIN, то это может привести к печальным последствиям. Расширенная статистика вычисляет совместное распределение значений в наборе колонок и позволяет обнаружить такие зависимости.
На самом деле, в ORM-ах встречаются ситуации и похуже. На примере базы электростанций это может быть совместное использование условий по полям country и country_long. Прочитав их описание всякий поймёт, что между этими полями есть прямое соответствие, и когда ORM делает группировку по обоим этим полям, мы получаем большую ошибку:
EXPLAIN (ANALYZE, COSTS ON, TIMING OFF, BUFFERS OFF, SUMMARY OFF)
SELECT country, country_long FROM power_plants
GROUP BY country, country_long;
HashAggregate (rows=3494 width=16) (actual rows=167.00 loops=1)
Group Key: country, country_long
-> Seq Scan on power_plants (rows=34936 width=16)
(actual rows=34936.00 loops=1)Человек бы такой запрос никогда не написал, но мы живем в эпоху AI, автоматически сгенерированные запросы не редкость и с этим придётся как-то справляться.
А что же extended statistics? Она позволяет нам определить три варианта статистики над нашими полями: Most Common Values (MCV), distinct и dependencies. В случае с фильтрами сканирования лучше всего работает MCV: если комбинация значений, которую мы хотим выбрать из таблицы, часто появляется в этой таблице, то оптимизатор будет получать точную оценку. Если же мы ищем редко встречающуюся комбинацию (как в случае с солнечными электростанциями в Норвегии) - то имея грубую оценку выборки ntupes/ndistinct, можно её уточнить, выкинув всё, что попало в MCV.
В случае же с необходимостью оценивать количество групп (операторыGROUP BY, DISTINCT, IncrementalSort, Memoize, Hash Join) оптимизатору очень хорошо помогает величина ndistinct на комбинацию колонок.
Теперь, чтобы посмотреть, какой эффект оказывает расширенная статистика на оценку оптимизатором величины выборки из таблицы, применим расширенную статистику к нашему случаю выполнив команды:
CREATE STATISTICS ON country,primary_fuel FROM power_plants;
ANALYZE;Можно заметить, что приведенный выше запрос сильно точнее предсказывает кардинальность выборки и группировки по этим двум полям: для Норвегии количество электростанций эстимируется в 1, Испании - 253 (можете проверить результат например на фильтрах country = 'RUS' или country = 'AUT'). Понятно, что таблица не самая большая, но инструмент рабочий.
При этом я крайне редко вижу применение extended statistics в реальной жизни. Наверняка одна из причин - это опасение, что ANALYZE будет занимать большое количество времени. Однако, как я подозреваю основная причина - сложность диагностики - когда и где создавать статистику.
Ищем подходящее определение статистики
Есть ли какие-то эмпирические правила, где и какую статистику создавать? Я сформулировал для себя два таких правила:
No.1: по определению индекса. Если DBA идет на риск, создавая индекс по какому-то набору колонок, то вполне возможно он ожидает, что в систему будут часто приходить запросы на выборку с условиями именно по этим колонкам. К тому же для него время выполнения таких запросов очевидно критично, что есть дополнительный аргумент в пользу повышения качества планов запросов. Конечно, не всегда имеется значимая ошибка эстимации для фильтров по нескольким колонкам и это очевидный минус данной эмпирики - статистика может быть сгенерирована напрасно. Также, весьма вероятно просто ожидается точечная выборка данных из таблицы и тогда эффект недоэстимации по составному фильтру нивелируется - имеет ли существенное значение 1 или 5 строк возвращать?
В связи с такими недостатками пришлось изобрести метод No.2: по шаблону фильтров реальных запросов. Здесь во-первых, нужно выбирать запросы-кандидаты базируясь на двух факторах: вкладе запроса в нагрузку на базу данных (можно использовать критерий pages-read) и наличию составных фильтров сканирования таблицы. Также, здесь было бы неплохо учитывать только те случаи, в которых реальное значение кардинальности оператора сканирования таблицы существенно отличается от предсказанного.
Этот подход более селективен в выборе потенциальных кандидатов для создания статистики и позволяет существенно сократить номенклатуру статистик, однако порождает непростые вопросы:
Момент создания статистик - если в подходе No.1 мы точно знаем, когда генерировать статистику - в момент создания индекса, то как быть со случаем No.2? Здесь придется либо делать это по некоторому таймеру, коллекционируя запросы в промежутках, либо вызывать команду вручную. Отсутствие сложного запроса, выполняющего расчет бонусов по итогам месяца в течение 29 дней, не означает, что мы не хотим выполнить его в разумные сроки в тридцатый день. Да, вклад такого запроса в общую нагрузку невелик, однако же бухгалтер может не захотеть ждать результата несколько часов!
Как почистить набор статистик - в предыдущем подходе мы удалим статистику вместе с индексом, то здесь все не так очевидно: если проблемный запрос вдруг перестал приходить, поскольку закончился, например, сезон продаж определенного популярного продукта, не означает, что через год он не появится снова. Таким образом, здесь может создаваться неопределенность и потенциальная нестабильность в работе оптимизатора СУБД.
Также непонятно, а насколько сильно должна отличаться селективность реальная и предсказанная, чтобы считать ее существенной - в 2 раза, 10 или 100?
Таким образом, я решил сначала написать код для простого в реализации подхода No.1, а для второго отработать технологию, разработав некий рекоммендатор, который базируясь на данных расширения pg_stat_statements и анализе планов выполненных запросов будет предлагать кандидатов на создание статистики. И самое главное - научить его показывать, почему он так считает.
Описание расширения
Идея расширения достаточно проста (см. репозиторий). Сначала нам нужен хук, в котором мы будем собирать идентификаторы создаваемых в базе объектов - я выбрал для этой цели object_access_hook.
Затем, нам нужно выбрать удобный момент для выборки из списка объектов только тех, что относятся к подходящим составным индексам и добавления в базу нового определения статистики - это оказалось удобно делать в хуке ProcessUtility_hook.
Поскольку расширенная статистика (типов distinct и dependencies) рассчитывается для всех возможных комбинаций колонок, то вычислительная сложность растёт очень быстро: если для трёх колонок количество distinct-статистик равно 4, а dependencies - 9, то для 8 колонок уже 247 и 1016 соответственно. Немудрено, что лимит на количество элементов статистики жёстко выставлен ядром Postgres в 8. Поэтому, чтобы избежать чрезмерной нагрузки на БД я ввёл параметр, ограничивающий количество элементов индекса, по которым будет создаваться определение статистики (см. параметр columns_limit) и параметр, определяющий, какие типы статистик включать в это определение (параметр stattypes).
В момент создания такой авто-статистики для неё дополнительно создается dependency в базе данных не только от самой таблицы, но и от индекса, послужившего шаблоном. Таким образом, при удалении индекса исчезает и статистика. Нужно ли создавать dependency от расширения, чтобы в момент вызова “DROP EXTENSION” удалялись все созданные статистики? - Вопрос открытый, поскольку расширение вполне может функционировать и как просто модуль, без вызова “CREATE EXTENSION” - таким образом эффект можно распространять сразу на все базы данных в кластере.
Чтобы отличать автоматически созданные статистики и не трогать то, что сделано кем-то другим создаётся объект-комментарий, содержащий имя нашей библиотеки вместе с именем статистики. Ну и до кучи - в интерфейс расширения добавлены функции pg_index_stats_remove / pg_index_stats_rebuild , которые позволяют удалить все статистики и перегенерировать заново. Это может оказаться полезным, к примеру, если схема данных была создана до загрузки модуля или изменились параметры БД.
Отдельная тема здесь - процедура снижения избыточности статистик. Поскольку индексов может быть много, то чтобы хоть как-то снизить вычислительные затраты для команды ANALYZE была придумана процедура выявления дубликатов (см. параметр pg_index_stats.compactify). Приведу пару примеров. Если в БД уже имеется индекс с определением t(x1,x2), то при создании индекса t(x2,x1) статистику создавать не нужно. Чуть более сложная ситуация возникает в случае, если индекс по t(x2,x1) создается тогда, когда уже есть индекс по t(x1,x2,x3) - здесь MCV-статистику создать нужно, она не будет избыточной, а вот distinct и dependencies можно исключить.
Эксперимент
Как водится, теория должна подтверждаться практикой, а код - его запуском на чём-то осмысленном. Готового нагруженного инстанса Postgres в тестовой эксплуатации или в проде у меня под рукой не было, поэтому для проверки пришлось отыскать залежавшийся дамп базы, отличительной чертой которой было то, что там накопилось большое количество таблиц (порядка 10 тыс.) при втрое большем количестве индексов. При этом активно использовались составные индексы - около 20 тыс. индексов с количеством колонок больше одной. Причем больше 1000 индексов имели пять и более колонок.
Как видите, клиент подходящий для исследований, жаль нет полезной нагрузки. Команда ANALYZE по такой базе выполнилась у меня за 22 секунды. С установленным расширением и дефолтным лимитом в пять колонок время ANALYZE составило уже 55 секунд. Сырые данные по времени ANALYZE базы данных (в секундах) в зависимости от лимита на количество колонок и типов собираемых статистик можно увидеть в таблице:
limit: | 2 | 4 | 5 | 6 | 8 |
MCV | 21 | 23 | 24 | 25 | 30 |
MCV, NDISTINCT | 27 | 37 | 51 | 68 | 137 |
MCV, NDISTINCT, DEPENDENCIES | 28 | 67 | 118 | 196 | 574 |
Заметно, что хранение всех возможных комбинаций колонок сильно сказывается на времени анализа. Особенно в случае с dependencies. Так что остается либо держать ограничение на уровне 3-5 колонок в статистике, либо двигаться в сторону подхода No.2. Ну и да, начинаешь понимать, почему в SQL Server изобрели отдельный воркер для обновления такой статистики - это действительно может быть весьма затратная процедура.
Что там с устранением избыточности? Поставим ещё один эксперимент:
SET pg_index_stats.columns_limit = 5;
SET pg_index_stats.stattypes = 'mcv, ndistinct, dependencies';
SET pg_index_stats.compactify = 'off';
SELECT pg_index_stats_rebuild();
ANALYZE;
pg_index_stats.compactify = 'on';
SELECT pg_index_stats_rebuild();
ANALYZE;Для объективного контроля количества стат. данных нам достаточно двух запросов:
-- Total number of stat items
SELECT sum(nelems) FROM (
SELECT array_length(stxkind,1) AS nelems
FROM pg_statistic_ext);
-- Total number of stat items grouped by stat type
SELECT elem, count(elem) FROM (
SELECT unnest(stxkind) elem FROM pg_statistic_ext
)
GROUP BY elem;Первый показывает общее количество элементов расширенных статистик в системе, а второй - разбивку по типам. Итак, посмотрим что получается со “сжатием” и без:
Параметр | Без сжатия | Со сжатием |
Время выполнения, с: | 141 | 123 |
Общее количество стат элементов: | 74353 | 61409 |
Суммарное количество элементов по типам статистик: | ||
MCV: | 24783 | 24089 |
DISTINCT: | 24783 | 18658 |
DEPENDENCIES: | 24783 | 18658 |
EXPRESSIONS: | 4 | 4 |
Эффект не бог весть какой - процентов 15 по времени и чуть больше по набору статистик, но тоже некая защита от корнер-кейсов, не так ли? Любопытно заметить, что сжатие снизило количество MCV-статистик: это значит, существует порядочное количество индексов, отличающихся только порядком следования колонок. Также, были созданы expression-статистики, которые мы ни разу даже не упоминали. Дело в том, что они создаются ядром Postgres автоматически, если определение расширенной статистики содержит выражение. Это не большая проблема, вероятно, но хотелось бы иметь возможность регулировать такое поведение.
Также, интересно было сравнить время анализа с имеющимся в моей компании - Postgres Professional - альтернативным сборщиком статистики - joinsel. Он по своей сути не является прямым конкурентом для extended statistics. Базируясь на определении индекса, он создает новый композитный тип в системе, по которому строится обычная статистика, хранящаяся в pg_statistic. Из плюсов - имеется MCV, гистограмма (а значит можно оценивать range-фильтры), задействованы стандартные механизмы ядра Postgres. Из надостатков - отсутствие dependencies-типа статистики и одно значение ndistinct на весь композитный тип (последнее в принципе легко устранимо). Посмотрим же, как быстро выполняется ANALYZE при наличии joinsel:
SET enable_compound_index_stats = 'on';
SELECT pg_index_stats_remove();
\timing on
ANALYZE;
Time: 41248.977 ms (00:41.249)Время ANALYZE ожидаемо увеличилось по сравнению с обычной постгрессовой статистикой, однако только в двое, что есть разумный компромисс. И основное преимущество здесь в том, что не нужно опасаться количества колонок в индексе - сложность будет нарастать линейно.
Итого
Общий вывод по подходу No.1 таков, что с известной осторожностью и аккуратным обращением с лимитами данный подход имеет право на жизнь.
Ещё один вывод - нужны доработки расширенной статистики в ядре, которые позволят более глубоко воздействовать на этот инструмент, сокращая (или расширяя) объём генерируемых стат данных.
А что там с помогатором и подходом No.2? Пришлось оставить до лучших времён. Если найдётся энтузиаст с большим количеством свободного времени и терпения - заходи, проконсультирую!
THE END.
9 Марта 2025 г., Мадрид, Испания.
