🟢 Junior Level

ANALYZE — команда, которая собирает статистику о данных в таблице, чтобы PostgreSQL мог выбирать оптимальные планы выполнения запросов.

Простая аналогия: Представьте, что вы собираетесь в поездку. Без статистики (какие дороги, пробки, расстояния) вы будете выбирать маршрут наугад. ANALYZE — это как GPS, который знает состояние всех дорог и помогает выбрать лучший путь.

Что делает:

• Считает количество уникальных значений в колонках
• Запоминает самые частые значения
• Оценивает распределение данных
• Сохраняет эту информацию в pg_stats

Пример:

-- Собрать статистику по всей базе
ANALYZE;

-- По конкретной таблице
ANALYZE users;

-- По конкретным колонкам
ANALYZE users (email, city);

-- С подробностями
ANALYZE VERBOSE users;

Важно: ANALYZE только собирает статистику. VACUUM ANALYZE делает и то, и другое: очищает dead tuples И собирает статистику.

Зачем:

• Без статистики планировщик “гадает” и может выбрать медленный план
• Со статистикой: знает, что Index Scan быстрее для 1 строки, а Seq Scan для 10,000

---

🟡 Middle Level

Как ANALYZE собирает статистику

ANALYZE не сканирует всю таблицу! Он использует случайную выборку:

Объём выборки = 300 × statistics_target

По умолчанию (target = 100):
  300 × 100 = 30,000 строк

Для таблицы 1 млрд строк:
  Всё равно 30,000 строк (быстро!)

Что попадает в pg_stats

-- Посмотреть статистику
SELECT * FROM pg_stats WHERE tablename = 'users';

Ключевые поля:

Поле	Описание
n_distinct	Оценка уникальности (-1 = 100% уникальны)
most_common_vals	Топ частых значений (MCV — Most Common Values)
most_common_freqs	Частота каждого из топ значений
correlation	Насколько физический порядок совпадает с логическим (-1 to 1)
histogram_bounds	Границы корзин для оценки диапазонов

Как планировщик использует статистику

-- Запрос
SELECT * FROM users WHERE city = 'Moscow';

-- Планировщик смотрит pg_stats:
-- n_distinct = 500 (500 городов)
-- most_common_vals = ['Moscow', 'SPb', 'Kazan', ...]
-- most_common_freqs = [0.3, 0.2, 0.05, ...]
-- → Moscow = 30% таблицы → Seq Scan (слишком много для индекса)

-- Запрос
SELECT * FROM users WHERE email = 'test@example.com';

-- Планировщик смотрит pg_stats:
-- n_distinct = -1 (100% уникальны)
-- → 1 строка из 1 млн → Index Scan!

Autovacuum и ANALYZE

-- Авто-ANALYZE запускается сам
SHOW autovacuum_analyze_threshold;     -- 50 строк
SHOW autovacuum_analyze_scale_factor;  -- 0.1 (10%)

-- Запускается, когда:
-- изменённых строк > 50 + 0.1 * total_rows

-- Для таблицы 1000 строк:
-- 50 + 0.1 * 1000 = 150 изменений → ANALYZE

Типичные проблемы

1. Устаревшая статистика

   -- После массовой вставки статистика неактуальна
   COPY users FROM '/data/users.csv';  -- 1 млн строк
   -- Статистика старая! Планировщик "слепой"
      
   -- Решение
   ANALYZE users;
   

2. Временные таблицы

   -- Autovacuum НЕ видит временные таблицы!
   CREATE TEMP TABLE temp_data AS SELECT ...;
      
   -- Нужен ручной ANALYZE
   ANALYZE temp_data;
   

3. Низкая точность

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

Практические примеры

-- Проверка, когда последний раз был ANALYZE
SELECT 
    relname,
    last_analyze,
    last_autoanalyze,
    n_mod_since_analyze  -- Изменений с последнего ANALYZE
FROM pg_stat_user_tables
WHERE relname = 'orders';

-- Если n_mod_since_analyze большой → пора ANALYZE

---

🔴 Senior Level

Алгоритм выборки

Reservoir Sampling:

PostgreSQL использует случайную выборку СТРАНИЦ (не строк):
1. Случайно выбирает страницы из таблицы
2. Сканирует все строки на выбранных страницах
3. Строит статистику по выборке

Выборка страниц даёт более равномерное покрытие, учитывая локальность
данных (строки на одной странице обычно связаны по смыслу).

Преимущество: 
  - O(1) время независимо от размера таблицы
  - Репрезентативная выборка

Недостаток:
  - Для очень "рваного" распределения может быть неточным

statistics_target: тонкая настройка

-- По умолчанию: 100 (30,000 строк выборки)
SHOW default_statistics_target;

-- Увеличить для проблемных колонок
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ALTER TABLE users ALTER COLUMN email SET STATISTICS 500;

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

-- Цена:
-- target = 100:   ANALYZE 3 секунды
-- target = 1000:  ANALYZE 30 секунд
-- target = 10000: ANALYZE 5 минут

Когда увеличивать:

• Колонка с аномальным распределением (Data Skew — перекос данных: например, 90% строк имеют status=’ACTIVE’, 10% — 20 других статусов)
• Планировщик часто ошибается в оценке rows
• MCV (Most Common Values) не покрывает > 80% данных

Расширенная статистика (Multivariate Statistics)

Проблема: PostgreSQL предполагает независимость колонок.

-- Запрос
SELECT * FROM cars WHERE make = 'Honda' AND model = 'Civic';

-- Планировщик (наивно):
-- P(Honda) = 0.1
-- P(Civic) = 0.05
-- P(both) = 0.1 × 0.05 = 0.005 (0.5%)

-- Реальность:
-- model зависит от make!
-- P(both) = 0.03 (3%) — в 6 раз больше!
-- Из-за ошибки в 6 раз планировщик может выбрать Nested Loop (ожидая 5
-- строк вместо 300), когда Hash Join был бы в 100 раз быстрее.

Решение: CREATE STATISTICS

-- 1. Functional Dependencies (PG 10+)
-- Зная модель, знаем марку
CREATE STATISTICS s_make_model (dependencies) 
ON make, model FROM cars;

-- 2. N-Distinct (PG 10+)
-- Кардинальность комбинации
CREATE STATISTICS s_city_age (ndistinct) 
ON city, age FROM users;

-- 3. MCV Lists (PG 13+)
-- На PG 12 и ниже тип `mcv` не поддерживается → ANALYZE выдаст ошибку.
-- Топ комбинаций значений
CREATE STATISTICS s_status_priority (mcv) 
ON status, priority FROM orders;

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

Проверка:

-- Посмотреть расширенную статистику
SELECT * FROM pg_stats_ext WHERE tablename = 'cars';

-- Конкретно зависимости
SELECT 
    stxname,
    stxkeys,
    stxdependencies
FROM pg_statistic_ext;

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

-- Функциональный индекс
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- ANALYZE соберёт статистику по РЕЗУЛЬТАТУ функции!
ANALYZE users;

-- Теперь планировщик точно оценивает:
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
-- → Знает селективность LOWER(email), а не email

pg_stat_progress_analyze (PG 13+)

-- Мониторинг ANALYZE на больших таблицах
SELECT 
    pid,
    relid::regclass as table_name,
    phase,
    sample_blks_total,
    sample_blks_scanned,
    ext_stats_computed,
    ROUND(100.0 * sample_blks_scanned / NULLIF(sample_blks_total, 0), 2) as progress_pct
FROM pg_stat_progress_analyze;

-- Phase:
-- 'acquiring sample rows'
-- 'acquiring inherited sample rows'
-- 'computing statistics'
-- 'computing extended statistics'

На PG 12 и ниже мониторинг прогресса ANALYZE недоступен. Оценивайте время по размеру таблицы: ~1 сек на 1 млн строк при default_statistics_target.

“Слепые зоны” статистики

1. Межтабличные связи

PostgreSQL НЕ хранит статистику о JOIN между таблицами!
Оценка размера JOIN = перемножение селективностей

→ Если таблицы коррелируют → оценка ошибочна
→ Решение: CTE или Materialized Views

2. Partitioned Tables

-- Для партицированных таблиц:
-- Статистика собирается на КАЖДОЙ партиции
-- Планировщик может ошибиться в общей оценке

-- Решение:
-- Увеличить statistics_target на ключевых партициях
ALTER TABLE orders_y2024 ALTER COLUMN created_at SET STATISTICS 1000;

3. Time-Series Data

-- Данные меняются со временем
-- Статистика устаревает между ANALYZE

-- Решение:
-- Чаще запускать ANALYZE для time-series таблиц
ALTER TABLE logs SET (
    autovacuum_analyze_scale_factor = 0.01  -- 1% вместо 10%
);

Edge Cases

1. ANALYZE блокировки

   -- ANALYZE берёт ShareUpdateExclusiveLock
   -- Позволяет: SELECT, INSERT, UPDATE, DELETE
   -- Блокирует: другой ANALYZE, VACUUM, ALTER TABLE
      
   -- Два ANALYZE одновременно → один ждёт
   

2. Inheritance

   -- Для таблиц с наследованием:
   ANALYZE parent_table;  -- Только родитель
   ANALYZE;               -- Всю БД, включая детей
      
   -- Или отдельно:
   ANALYZE child_table_1;
   ANALYZE child_table_2;
   

3. Foreign Tables

   -- Для внешних таблиц (FDW):
   -- ANALYZE может быть очень медленным (запрос к удалённому серверу)
      
   -- Можно импортировать статистику
   IMPORT FOREIGN SCHEMA public
   FROM SERVER remote_server
   INTO local_schema;
   

Performance Impact

Неправильная статистика → плохой план:

Ошибка в оценке	Результат
Занижена в 10x	Index Scan вместо Seq Scan
Завышена в 10x	Seq Scan вместо Index Scan
Неправильный JOIN order	Nested Loop вместо Hash Join

Production Experience

Реальный сценарий #1: Массовая вставка без ANALYZE

• ETL: загрузили 50 млн строк в orders
• Запросы стали в 100 раз медленнее
• Причина: статистика старая (таблица была пустой)
• Планировщик думал: “таблица пустая → Seq Scan быстр”
• Реальность: 50 млн строк → Seq Scan катастрофа
• Решение: ANALYZE orders; → 2 секунды, всё починилось

Реальный сценарий #2: Correlated columns

• Аналитика: WHERE country = ‘RU’ AND city = ‘Moscow’
• Планировщик ошибался в 50 раз (предполагал независимость)
• Решение: CREATE STATISTICS s_country_city (dependencies) ON country, city
• Результат: точная оценка, правильный план

Monitoring

-- 1. Проверка актуальности статистики
SELECT 
    relname,
    last_analyze,
    last_autoanalyze,
    n_mod_since_analyze,
    CASE 
        WHEN n_mod_since_analyze > 100000 THEN 'NEEDS ANALYZE'
        ELSE 'OK'
    END as status
FROM pg_stat_user_tables
ORDER BY n_mod_since_analyze DESC
LIMIT 20;

-- 2. Проверка statistics_target
SELECT 
    attname,
    attname,
    CASE 
        WHEN attstattarget = -1 THEN 'Default (' || current_setting('default_statistics_target') || ')'
        ELSE attstattarget::text
    END as statistics_target
FROM pg_attribute
WHERE attrelid = 'orders'::regclass
  AND attnum > 0
  AND NOT attisdropped;

-- 3. Extended statistics
SELECT * FROM pg_stats_ext;

-- 4. Progress
SELECT * FROM pg_stat_progress_analyze;

Best Practices

1. ANALYZE после массовой вставки/удаления
2. Увеличивайте statistics_target для skewed колонок
3. CREATE STATISTICS для коррелированных колонок
4. Мониторьте n_mod_since_analyze — > 10% = время ANALYZE
5. Не полагайтесь только на autovacuum для горячих таблиц
6. Проверяйте планы через EXPLAIN ANALYZE после ANALYZE
7. Временные таблицы → ручной ANALYZE
8. Partitioned tables → ANALYZE на ключевых партициях

Резюме для Senior

• ANALYZE = сбор статистики через случайную выборку (300 × target строк)
• statistics_target — баланс между точностью и временем
• Extended Statistics (PG 10+) решает проблему коррелированных колонок
• MCV Lists (PG 13+) для топ комбинаций значений
• Межтабличные связи — слепая зона планировщика
• Массовые изменения → всегда ANALYZE после
• Мониторьте n_mod_since_analyze и accuracy планов
• pg_stat_progress_analyze (PG 13+) для мониторинга на больших таблицах

---

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

Обязательно знать:

• ANALYZE: случайная выборка 300 × statistics_target строк (не сканирует всю таблицу)
• pg_stats: n_distinct, most_common_vals (MCV), histogram_bounds, correlation
• statistics_target = 100 (по умолчанию) → 30,000 строк выборки
• Autovacuum запускает ANALYZE когда: изменённых > 50 + 0.1 × total_rows
• Extended Statistics (PG 10+): dependencies (коррелированные колонки), ndistinct (составная кардинальность), mcv (PG 13+, топ комбинаций)
• Алгоритм: Reservoir Sampling → O(1) время независимо от размера таблицы
• Слепые зоны: межтабличные связи, партицированные таблицы, time-series данные
• pg_stat_progress_analyze (PG 13+): мониторинг прогресса
• Статистика по выражениям: ANALYZE собирает статистику по результату функции (LOWER(email))

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

• «Почему запрос стал медленным после массовой вставки?» → Статистика устарела → ANALYZE
• «Как решить проблему коррелированных колонок?» → CREATE STATISTICS ... (dependencies)
• «Когда увеличивать statistics_target?» → Skewed данные, планировщик ошибается
• «Анализирует ли PG временные таблицы?» → Нет, нужен ручной ANALYZE

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

• ❌ «ANALYZE сканирует всю таблицу» (нет, случайная выборка!)
• ❌ «Autovacuum всегда актуален» (после массовой вставки — нет)
• ❌ «Extended Statistics решает все проблемы» (межтабличные связи — слепая зона)
• ❌ «Временные таблицы анализируются сами» (нет, autovacuum их не видит)

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

• [[Что такое кардинальность индекса]] → n_distinct, MCV, correlation
• [[Что такое VACUUM в PostgreSQL]] → Autovacuum + ANALYZE
• [[Как оптимизировать медленные запросы]] → влияние статистики на план