---

🟢 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 = 'Kyiv';

-- Планувальник дивиться pg_stats:
-- n_distinct = 500 (500 міст)
-- most_common_vals = ['Kyiv', 'Lviv', 'Odesa', ...]
-- most_common_freqs = [0.3, 0.2, 0.05, ...]
-- → Kyiv = 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 = ‘UA’ AND city = ‘Kyiv’
• Планувальник помилявся в 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,
    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
• Як оптимізувати повільні запити → вплив статистики на план