pg_statistic, ANALYZE и производственная наблюдаемость

Запрос, выполнявшийся за 5 мс вчера, сегодня занимает 4 секунды. В приложении ничего не изменилось. План другой. Причина почти всегда одна: статистика устарела и планировщик сделал другой выбор. Знание того, как Postgres строит и использует статистику, — вот как предотвратить следующий инцидент.

Внутри pg_statistic

При запуске ANALYZE orders Postgres выборочно сканирует таблицу (по умолчанию 300 × default_statistics_target = 30,000 строк при целевом значении 100) и вычисляет статистику на уровне колонок, хранящуюся в pg_statistic (читается через pg_stats):

Как планировщик использует это для WHERE x = 42:

1. Есть ли 42 в MCV-списке? Используем его точную частоту напрямую.
2. Не MCV? Находим бакет гистограммы, содержащий 42, предполагаем равномерное распределение внутри бакета.
3. Комбинируем с reltuples (счётчик строк в pg_class) для получения оценки количества строк.

Для диапазонных предикатов (x > 100) гистограмма интегрируется по диапазону. Для многоколоночных предикатов планировщик по умолчанию предполагает независимость и перемножает селективности — что неверно для коррелированных колонок (рассматривается в уроке 05).

Чтайте каталог напрямую:

SELECT tablename, attname, n_distinct, most_common_vals, most_common_freqs,
       histogram_bounds, correlation
FROM pg_stats
WHERE tablename = 'orders';

ANALYZE: когда запускать

Autovacuum планирует ANALYZE для каждой таблицы на основе:

autovacuum_analyze_threshold (умолчание 50 строк) +
autovacuum_analyze_scale_factor × reltuples (умолчание 0.1 = 10%)

На таблице из 100M строк autovacuum анализирует только после 10M изменений строк — слишком редко для таблиц с косыми или быстро меняющимися распределениями данных. Операционные исправления:

-- Снизьте scale factor для горячих таблиц:
ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.02);

-- Поднимите целевой показатель статистики для косых колонок:
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

Ручной ANALYZE выполняется за секунды даже на таблицах из 100M строк (он делает выборку, а не полное сканирование). Всегда запускайте его:

• После массовых вставок или больших UPDATE, сдвигающих распределения
• После изменений схемы, добавляющих колонки
• В post-deploy хуках перед возобновлением трафика

Опции EXPLAIN для диагностики

Помимо базового плана, несколько опций EXPLAIN необходимы:

• FORMAT JSON | XML | YAML | TEXT — JSON для инструментов (explain.depesz.com, explain.dalibo.com, pganalyze)
• VERBOSE — добавляет списки выходных колонок для каждого узла
• SETTINGS (PG 12+) — печатает не-дефолтные GUC планировщика; диагностирует дрейф окружения между staging и production
• WAL (PG 13+) — показывает байты WAL, сгенерированные оператором
• GENERIC_PLAN (PG 16+) — планирует параметризованный запрос без примерных значений; необходим для диагностики подготовленных операторов (рассматривается в уроке 06)
• SERIALIZE (PG 17+) — включает стоимость сериализации строк для клиента; сокращает разрыв между полным временем EXPLAIN ANALYZE и наблюдаемой клиентом задержкой

auto_explain и pg_stat_statements

Два расширения, которые должны быть на каждом production Postgres:

pg_stat_statements записывает каждый выполненный запрос (нормализованный по параметрам), отслеживая calls, total_exec_time, mean_exec_time, rows и счётчики буферов. Запрос: SELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20. Эти top-20 по total_exec_time — ваши цели оптимизации — запрос за 2 мс, вызванный 10M раз, важнее запроса за 500 мс, вызванного дважды.

auto_explain автоматически логирует EXPLAIN ANALYZE для любого запроса, превышающего порог длительности:

-- Дополнения к postgresql.conf:
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = true
auto_explain.log_buffers = true
auto_explain.log_format = 'json'
auto_explain.sample_rate = 0.01   -- 1% медленных запросов для ограничения объёма логов

Медленные запросы попадают в логи Postgres с полными планами ANALYZE + BUFFERS, без необходимости воспроизводить их на staging. Вместе они отвечают на вопрос «какие запросы медленные и почему» без инструментирования приложения.