Введение
Решения планировщика запросов о том, использовать индекс или полный просмотр, какой порядок JOIN выбрать, опираются на статистику — приблизительные сведения о распределении значений в столбцах таблицы, собранные и периодически обновляемые самой СУБД. В статье разберём, как эта статистика влияет на качество планов выполнения, и что происходит, когда статистика устаревает относительно реального, изменившегося содержимого таблицы.
Концепция
Статистика обычно включает количество строк таблицы, количество уникальных значений в каждом столбце, распределение наиболее частых значений, и приблизительную гистограмму распределения остальных значений — это позволяет планировщику оценивать, сколько строк отберёт конкретное условие WHERE без необходимости реально выполнять запрос для подсчёта. Статистика собирается не автоматически при каждом изменении данных (что было бы слишком дорого), а периодически, либо автоматически фоновым процессом (autovacuum/ANALYZE в PostgreSQL), либо явно по команде разработчика/администратора — устаревшая статистика, не отражающая реальное, изменившееся за время с последнего сбора распределение данных, может привести планировщик к существенно неоптимальным решениям.
Пример кода: Статистика оптимизатора и
-- Явный сбор/обновление статистики для конкретной таблицы (синтаксис PostgreSQL)
ANALYZE orders;
-- Просмотр текущей статистики для конкретного столбца
SELECT attname, n_distinct, most_common_vals
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'customer_id';
-- Типичный сценарий устаревшей статистики — массовая загрузка данных без последующего ANALYZE
BEGIN;
-- Массовая вставка миллиона новых строк, существенно меняющих распределение данных
COPY orders FROM '/path/to/bulk_data.csv' WITH (FORMAT csv);
COMMIT;
-- Без явного ANALYZE планировщик может продолжать опираться на СТАРУЮ статистику,
-- собранную до этой массовой загрузки, принимая субоптимальные решения для новых запросов
ANALYZE orders; -- явное обновление статистики сразу после массового изменения объёма данных
-- Увеличение детальности статистики для столбца с особенно сложным/неравномерным распределением
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 500; -- больше "образцов" для гистограммы
ANALYZE orders; -- применение изменённой настройки требует повторного сбора статистики
Пояснения к коду
ANALYZE orders явно запускает пересбор статистики для указанной таблицы — это особенно важно выполнять явно сразу после массовых, объёмных изменений данных (как показано во втором примере с COPY), поскольку фоновый, автоматический процесс сбора статистики (autovacuum) обычно срабатывает по своему собственному расписанию/порогу изменений, и между массовым изменением данных и следующим автоматическим запуском этого процесса может пройти заметное время, в течение которого планировщик продолжает опираться на уже неактуальную статистику. SET STATISTICS 500 показывает более тонкую настройку — увеличение детальности (количества «образцов», используемых для построения гистограммы распределения) специально для столбцов с особенно сложным, неравномерным распределением значений, где стандартная детальность статистики может быть недостаточной для точной оценки селективности условий по этому столбцу.
Подводные камни
- Полное отсутствие понимания связи между планом выполнения запроса (статья 416) и статистикой, на основе которой этот план был построен — неожиданно неэффективный план выполнения часто объясняется не недостатком подходящего индекса (статьи 413-415), а именно устаревшей или недостаточно детальной статистикой, заставляющей планировщик ошибочно оценивать селективность условий и выбирать на основе этой неверной оценки субоптимальную стратегию выполнения, даже при наличии вполне подходящего индекса.
- Отсутствие явного
ANALYZEпосле массовых операций изменения данных (массовая загрузка, массовое удаление, существенное изменение распределения значений в большом количестве строк) в надежде, что автоматический сбор статистики (autovacuum) сработает достаточно быстро самостоятельно — для критичных по времени операций (где важно, чтобы последующие запросы сразу же получали оптимальные планы выполнения, отражающие реальное, изменившееся состояние данных) явный, немедленныйANALYZEсразу после массового изменения — надёжная, рекомендуемая практика, а не опциональная «дополнительная подстраховка». - Недооценка значимости статистики для столбцов с сильно неравномерным распределением значений (например, столбец статуса заказа, где 95% значений — «completed», а оставшиеся 5% распределены между несколькими другими статусами) — стандартная, не особо детальная статистика может недостаточно точно отражать такое неравномерное распределение, что может привести к неоптимальным решениям планировщика именно для запросов, фильтрующих по редким, не самым частым значениям такого столбца; для таких случаев увеличение детальности статистики (как в примере
SET STATISTICS) может заметно улучшить качество принимаемых планировщиком решений. - Слепое доверие оценкам планировщика (
cost,rowsв выводеEXPLAIN) как точным, гарантированным значениям, забывая, что это именно оценки, основанные на статистике, которая по своей природе приблизительна и может отклоняться от реального количества строк, особенно для сложных, многоусловных запросов или запросов с несколькими последовательными JOIN, где небольшие неточности оценки на каждом шаге могут накапливаться и существенно искажать итоговую оценку — для критичных запросов реальное, измеренное поведение черезEXPLAIN ANALYZE(статья 416) важнее одних лишь предварительных оценок планировщика.