🟢 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 до 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
• [[Як оптимізувати повільні запити]] → вплив статистики на план