Зачем нужен ANALYZE?

На основе этой статистики стоимостной оптимизатор запросов (Cost-Based Optimizer, CBO) рассчитывает стоимость различных вариантов выполнения SQL-запроса и выбирает наиболее быст...

🟢 Junior Level

ANALYZE — это служебная команда PostgreSQL, которая собирает статистическую информацию о распределении данных в таблицах и сохраняет её в системных каталогах (прежде всего в представлении pg_stats).

На основе этой статистики стоимостной оптимизатор запросов (Cost-Based Optimizer, CBO) рассчитывает стоимость различных вариантов выполнения SQL-запроса и выбирает наиболее быстрый план: использовать ли индекс (Index Scan), сканировать ли таблицу подряд (Seq Scan), или какой алгоритм соединения таблиц выбрать (Hash Join, Merge Join, Nested Loop).

graph LR
    A["Таблица (Миллионы строк)"] --> B["ANALYZE (Случайная выборка)"]
    B --> C["Каталог pg_stats (Статистика)"]
    C --> D["Оптимизатор CBO"]
    D --> E["Оптимальный план выполнения (Index Scan vs Seq Scan)"]

Наглядная бытовая аналогия

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

Базовый синтаксис

-- 1. Собрать статистику по всей текущей базе данных:
ANALYZE;

-- 2. Собрать статистику по конкретной таблице:
ANALYZE orders;

-- 3. Собрать статистику только по определённым колонкам:
ANALYZE orders (status, created_at);

-- 4. Собрать статистику с выводом детального лога выполнения:
ANALYZE VERBOSE orders;

[!NOTE] ANALYZE не очищает мёртвые строки и не освобождает память (в отличие от VACUUM). Команда VACUUM ANALYZE объединяет обе операции: сначала удаляет мусор, затем обновляет статистику.

🟡 Middle Level

Как ANALYZE собирает данные: Метод резервуарной выборки

Важнейшая особенность: ANALYZE никогда не сканирует таблицу целиком! Для таблиц любого размера (хоть 100 МБ, хоть 10 ТБ) алгоритм использует репрезентативную случайную выборку (Reservoir Sampling):

\[\text{Количество строк в выборке} = 300 \times \text{default\_statistics\_target}\]

По умолчанию параметр default_statistics_target = 100. Это означает, что для таблицы любого объёма считывается всего: $300 \times 100 = \mathbf{30\,000}\text{ строк!}$

Благодаря этому ANALYZE даже на миллиардной таблице отрабатывает за несколько секунд и не создает ощутимой нагрузки на дисковую подсистему.

Ключевые метрики в представлении pg_stats

Вся собранная статистика доступна системному администратору и разработчику в представлении pg_stats:

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

Колонка `pg_stats`	Смысл для планировщика запросов
`n_distinct`	Количество уникальных значений. Если число положительное (например, `10`) — ровно 10 значений. Если отрицательное (например, `-1.0`) — колонка на 100% уникальна (PK, UUID).
`most_common_vals` (MCV)	Список самых часто встречающихся значений в колонке (например, `['PAID', 'NEW']`).
`most_common_freqs` (MCF)	Доля строк, приходящаяся на каждое частое значение (например, `[0.85, 0.12]`).
`histogram_bounds`	Границы гистограммы равной глубины для оценки диапазонов (`BETWEEN`, `>`, `<`) для значений, не вошедших в MCV.
`correlation`	Степень корреляции физического порядка строк на диске с логическим порядком значений (от -1.0 до +1.0). Корреляция близкая к 1.0 делает `Index Scan` сверхбыстрым.

Автоматический сбор: Autovacuum / Autoanalyze

Фоновый демон autovacuum запускает анализ таблицы автоматически, когда количество изменённых строк (INSERT, UPDATE, DELETE) превышает пороговое значение:

\[\text{Порог Autoanalyze} = \text{autovacuum\_analyze\_threshold} + (\text{autovacuum\_analyze\_scale\_factor} \times \text{reltuples})\]

По умолчанию: threshold = 50, scale_factor = 0.1 (10% строк).

Критический сценарий в Java-разработке: Post-Deployment CPU Spike

Один из самых частых инцидентов при выкатке новых версий Java-микросервисов:

Миграция БД (Liquibase / Flyway): В рамках релиза создаётся новая таблица и скриптом загружаются справочные данные или переносится 10 млн исторических заказов.
Сервис запускается, и на него подаётся пользовательский трафик.
Катастрофа: Поскольку autoanalyze ещё не успел запуститься, оптимизатор считает, что в таблице 0 строк (или дефолтное значение).
Оптимизатор строит план, выбирая Seq Scan или Nested Loop вместо индексного поиска.
CPU базы подскакивает до 100%, время ответа REST API возрастает с 10 мс до 15 секунд, приложение падает с таймаутами пула HikariCP.

Золотое правило Java Backend: Завершать любую миграцию Liquibase/Flyway с массовой вставкой данных явным вызовом ANALYZE target_table;. При использовании Spring Batch после пакетной загрузки вызывать jdbcTemplate.execute("ANALYZE " + tableName);.

🔴 Senior Level

Проблема асимметрии данных (Data Skew) и тонкая настройка статистики

Если колонка имеет сильно перекошенное распределение (например, статус заказа: 99% — COMPLETED, и по 0.05% на остальные 20 редких статусов), базовой выборки в 100 слотов гистограммы недостаточно: планировщик может ошибиться в оценке кардинальности редкого статуса в 100 раз.

-- Увеличение точности сбора статистики для конкретной проблемной колонки:
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
-- Теперь выборка составит 300 * 1000 = 300 000 строк, а гистограмма вместит до 1000 значений.

ANALYZE orders; -- Применить изменения

Расширенная многомерная статистика (Multivariate Statistics, PG 10+)

По умолчанию PostgreSQL исходит из постулата: «Значения в разных колонках абсолютно независимы». В реальных данных колонки почти всегда коррелируют:

\[\text{Селективность} = P(\text{make} = \text{'Audi'}) \times P(\text{model} = \text{'A6'}) = 0.02 \times 0.01 = \mathbf{0.0002}\]

В реальности же модель A6 бывает только у марки Audi, и реальная селективность составляет $0.01$ (ошибка оценки в 50 раз!). Планировщик ожидает 5 строк, а получает 250, ошибочно выбирая губительный Nested Loop.

Решение — создание расширенной статистики через CREATE STATISTICS:

-- 1. Functional Dependencies (Функциональная зависимость между колонками, PG 10+)
CREATE STATISTICS stat_cars_make_model (dependencies) ON make, model FROM cars;

-- 2. Ndiverse / N-Distinct (Оценка числа уникальных пар значений, PG 10+)
CREATE STATISTICS stat_users_city_zip (ndistinct) ON city, zip_code FROM users;

-- 3. MCV Lists (Многомерные списки частых комбинаций, PG 13+)
CREATE STATISTICS stat_orders_status_priority (mcv) ON status, priority FROM orders;

-- Применение:
ANALYZE cars;
ANALYZE users;
ANALYZE orders;

Статистика по выражениям (Functional Indexes)

Если в запросах активно используются выражения (WHERE LOWER(email) = ? или WHERE created_at::date = ?), планировщик не может корректно оценить селективность по исходной колонке email.

Создание функционального индекса автоматически заставляет ANALYZE собирать статистику по результату вычисления функции:

CREATE INDEX idx_users_email_lower ON users(LOWER(email));
ANALYZE users;
-- Теперь в pg_statistic появятся данные о распределении значений LOWER(email)!

Мониторинг актуальности статистики

-- Поиск таблиц с сильно устаревшей статистикой:
SELECT 
    schemaname,
    relname,
    n_live_tup,
    n_mod_since_analyze,
    ROUND(100.0 * n_mod_since_analyze / NULLIF(n_live_tup, 0), 2) AS mod_ratio_pct,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_mod_since_analyze > 50000
ORDER BY n_mod_since_analyze DESC;

🎯 Шпаргалка для интервью

Обязательно знать (Первые 30 секунд ответа)

Функция: ANALYZE производит статистический анализ распределения данных в таблицах (n_distinct, MCV, гистограммы) и сохраняет его в pg_stats.
Зачем: На основе этой статистики планировщик (CBO) решает, использовать ли индексы, как соединять таблицы (Hash Join vs Nested Loop) и в каком порядке.
Алгоритм: Использует случайную выборку ($300 \times \text{statistics_target} \approx 30\,000$ строк), а не сканирует всю таблицу целиком, поэтому работает мгновенно.
Блокировки: Берёт слабую блокировку ShareUpdateExclusiveLock, позволяя параллельно выполнять любые SELECT, INSERT, UPDATE, DELETE.
Временные таблицы: Демон autovacuum не видит временные таблицы (TEMP TABLE). Для них ANALYZE обязателен вручную.

Частые уточняющие вопросы на засыпку

«Почему запрос сразу после массового INSERT или COPY начинает жутко тормозить?» Ответ: Статистика планировщика устарела. Оптимизатор считает таблицу пустой или крошечной и выбирает последовательное чтение (Seq Scan) или Nested Loop. Вызов явного ANALYZE table_name; сразу восстанавливает актуальные планы.
«Как научить планировщик понимать зависимость между колонками (например, страна и город)?» Ответ: Через механизм расширенной статистики (Extended Statistics): CREATE STATISTICS s_geo (dependencies) ON country, city FROM addresses; с последующим выполнением ANALYZE addresses;.
«Что делать, если колонка имеет сильный перекос данных (Data Skew) и планировщик ошибается?» Ответ: Увеличить размер гистограммы для этой колонки командой ALTER TABLE t ALTER COLUMN col SET STATISTICS 500; (дефолт 100) и запустить ANALYZE.
«В чём разница между ANALYZE и EXPLAIN ANALYZE?» Ответ: Команда ANALYZE собирает статистику о содержимом таблицы и записывает её в системный каталог. Команда EXPLAIN ANALYZE реально выполняет конкретный SQL-запрос и выводит фактическое время и ресурсы каждого узла дерева выполнения.

Красные флаги (НЕ говорить на интервью)

❌ «ANALYZE сканирует всю таблицу от начала до конца» — грубейшая ошибка; используется случайная выборка 30 000 строк.
❌ «ANALYZE удаляет мёртвые строки» — путаница с VACUUM. ANALYZE занимается исключительно статистикой.
❌ «autovacuum сам мгновенно обновит статистику после заливки 50 млн строк в проде» — нет, порог 10% сработает с задержкой, и в этот период сервис может лечь из-за неоптимальных планов.

Связанные темы

Что такое кардинальность индекса → селективность и метрики pg_stats
Что такое VACUUM в PostgreSQL → процесс очистки мусора
Что такое explain plan → как статистика влияет на планирование запроса