🟢 Junior Level

Кардинальність — це кількість унікальних значень у колонці таблиці.

Навіщо це знати: планувальник PostgreSQL використовує кардинальність, щоб вирішити — використати індекс чи прочитати таблицю цілком. Висока кардинальність = багато унікальних значень = індекс ефективний. Низька = мало унікальних значень = швидше за все, Seq Scan буде швидшим за індекс.

Проста аналогія: Уявіть мішок з кульками різних кольорів. Кардинальність — це скільки в мішку кульок різних кольорів (не рахуючи кількість кожного кольору).

Основна ідея:

• Висока кардинальність = багато унікальних значень (наприклад, email)
• Низька кардинальність = мало унікальних значень (наприклад, стать: M/F)
• Індекси ефективніші на полях з високою кардинальністю

Приклад:

-- Таблиця users (1,000,000 рядків)

-- Висока кардинальність
SELECT COUNT(DISTINCT email) FROM users;  -- ~1,000,000 (унікальні)

-- Низька кардинальність
SELECT COUNT(DISTINCT gender) FROM users; -- 2 (M та F)

-- Індекс на email — чудовий вибір ✅
-- Індекс на gender — поганий вибір ❌

Коли це важливо:

• При виборі полів для індексування
• При аналізі, чому індекс не використовується
• При оптимізації повільних запитів

---

🟡 Middle Level

Кардинальність vs Селективність

Ці поняття пов’язані, але різняться:

Термін	Визначення	Приклад
Кардинальність	Кількість унікальних значень	COUNT(DISTINCT city) = 500
Селективність	Кардинальність / Загальна кількість	500 / 1,000,000 = 0.0005

Чим вища кардинальність → тим вища селективність → тим ефективніший індекс.

Чому: висока селективність означає, що запит поверне мало рядків. Index Scan читає 3-4 сторінки B-tree + 1 сторінку Heap на знайдений рядок. Якщо рядків 100 000 з 1 000 000 — це 100 000 випадкових читань Heap, що повільніше за одне послідовне читання всієї таблиці (Seq Scan).

Як PostgreSQL оцінює кардинальність

PostgreSQL збирає статистику через команду ANALYZE (виконується автоматично):

-- Подивитися статистику по колонках
SELECT
    attname as column_name,
    n_distinct,              -- Оцінка унікальності
    correlation              -- Кореляція з фізичним порядком
FROM pg_stats
WHERE tablename = 'users'
  AND attname = 'email';

n_distinct:

• Якщо > 0 — абсолютна кількість унікальних значень
• Якщо < 0 (наприклад, -1) — частка від загального числа (-1 = 100% унікальні)

Вплив на вибір плану виконання

-- Висока кардинальність (email)
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- Plan: Index Scan (знайде 1 рядок з 1 млн)

-- Низька кардинальність (status)
EXPLAIN SELECT * FROM users WHERE status = 'ACTIVE';
-- Plan: Seq Scan (поверне 500,000 рядків — індекс невигідний)

Поріг перемикання (орієнтовний):

% рядків у вибірці	План
< 1-5%	Index Scan
5-20%	Bitmap Index Scan (збирає TID в бітову карту, потім читає heap — менше випадкових I/O ніж Index Scan)
> 20%	Seq Scan

Data Skew (Перекіс даних)

-- Таблиця orders: 1,000,000 рядків
SELECT status, COUNT(*)
FROM orders
GROUP BY status;

-- Результат:
-- ACTIVE:   950,000 (95%)
-- PENDING:   40,000 (4%)
-- ERROR:     10,000 (1%)

-- Запит 1: WHERE status = 'ERROR' → Index Scan (рідкісне значення)
-- Запит 2: WHERE status = 'ACTIVE' → Seq Scan (часте значення)

Чому різні плани: PostgreSQL зберігає MCV (Most Common Values) — список найчастіших значень з їх частотою. Для ‘ERROR’ (1%) планувальник знає, що знайде ~10 000 рядків з 1 млн — Index Scan вигідний. Для ‘ACTIVE’ (95%) — 950 000 рядків, що дешевше прочитати Seq Scan (одне послідовне читання проти 950 000 випадкових).

Типові помилки

1. Індексування полів з низькою кардинальністю

   -- ❌ Погано: всього 2 значення
   CREATE INDEX idx_users_gender ON users(gender);
   -- Seq Scan все одно буде обраний для більшості запитів
   

2. Ігнорування складної кардинальності

   -- Окремо: city (500), age (80) — середня кардинальність
   -- Разом: (city, age) → висока кардинальність комбінації!
   CREATE INDEX idx_users_city_age ON users(city, age);
   

3. Неактуальна статистика

   -- Після масової вставки статистика застаріла
   -- Планувальник може обрати неправильний план
   
   -- Рішення: оновити статистику
   ANALYZE users;
   

Практичний приклад

-- Перевірка кардинальності колонок
SELECT
    attname as column_name,
    n_distinct,
    CASE
        WHEN n_distinct < 0 THEN 'Висока (' || ABS(n_distinct) * 100 || '%)'
        WHEN n_distinct < 100 THEN 'Низька'
        WHEN n_distinct < 10000 THEN 'Середня'
        ELSE 'Висока'
    END as cardinality_level
FROM pg_stats
WHERE tablename = 'users';

---

🔴 Senior Level

Як Postgres оцінює кардинальність: Deep Dive

Статистика зберігається в pg_statistic (доступ через pg_stats):

Ключові поля:

1. n_distinct

   -- Оцінка унікальності
   -- Приклади:
   -- -1.0 = 100% унікальних (кожне значення унікальне)
   -- -0.5 = 50% унікальних
   -- 100 = рівно 100 унікальних значень
   

2. most_common_vals (MCV)

   -- Топ найчастіших значень та їх частота
   -- Приклад для status:
   -- MCV: ['ACTIVE', 'PENDING', 'ERROR']
   -- Freq: [0.95, 0.04, 0.01]
   
   -- Планувальник використовує це для точної оцінки:
   -- WHERE status = 'ERROR' → Index Scan (1% рядків)
   -- WHERE status = 'ACTIVE' → Seq Scan (95% рядків)
   

3. histogram_bounds

   -- Розподіл значень для не-MCV значень
   -- Розбиває діапазон на кошики (за замовчуванням 100)
   -- Використовується для оцінки селективності діапазонів
   

4. correlation

   -- Число від -1 до 1
   -- Показує, наскільки фізичний порядок рядків
   -- збігається з логічним порядком значень в індексі
   
   -- correlation = 1.0 → рядки на диску відсортовані так само, як в індексі
   -- correlation = 0.0 → повний хаос (Random I/O)
   -- correlation = -1.0 → відсортовані у зворотному порядку
   

Extended Statistics (PostgreSQL 10+)

Проблема: PostgreSQL припускає незалежність колонок.

-- Запит
SELECT * FROM cars
WHERE make = 'Honda' AND model = 'Civic';

-- Планувальник думає:
-- P(make='Honda') = 0.1
-- P(model='Civic') = 0.05
-- P(both) = 0.1 * 0.05 = 0.005 (0.5%)

-- Реальність:
-- Якщо make='Honda', то model='Civic' набагато ймовірніше!
-- P(both) = 0.03 (3%) — в 6 разів більше!

Рішення: Extended Statistics

-- 1. Functional Dependencies
-- Якщо знаючи модель, ми знаємо марку
CREATE STATISTICS s_make_model (dependencies)
ON make, model FROM cars;

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

-- 3. MCV для комбінацій (PG 13+)
-- Найчастіші комбінації значень
CREATE STATISTICS s_status_priority (mcv)
ON status, priority FROM orders;

-- Застосовуємо
ANALYZE cars;
ANALYZE users;
ANALYZE orders;

На PG 12 і нижче тип mcv недоступний — використовуйте dependencies та ndistinct.

Перевірка:

-- Подивитися розширену статистику
SELECT * FROM pg_stats_ext
WHERE tablename = 'cars';

-- Конкретно ndistinct
SELECT
    stxname,
    stxkeys,
    stxndistinct
FROM pg_statistic_ext
WHERE stxname = 's_city_age';

Вплив statistics_target

-- За замовчуванням: 100 кошиків для гістограми
SHOW default_statistics_target;

-- Для колонок з «рваним» розподілом збільшуємо точність
ALTER TABLE users ALTER COLUMN email SET STATISTICS 1000;
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;

-- Оновлюємо статистику
ANALYZE users;
ANALYZE orders;

-- ⚠️ Ціна: ANALYZE виконуватиметься довше
-- Але планування стане точнішим

Edge Cases

1. Кореляція вбиває індекс

   -- Перевірка
   SELECT attname, correlation
   FROM pg_stats
   WHERE tablename = 'orders'
     AND attname = 'created_at';
   
   -- correlation = 0.05 → Random I/O
   -- Рішення: CLUSTER (пересортувати таблицю)
   CLUSTER orders USING idx_orders_created;
   -- ⚠️ Блокує таблицю! Робіть в maintenance window
   

2. Застаріла статистика

   -- Перевірка: коли останній раз був ANALYZE
   SELECT
       relname,
       last_analyze,
       last_autoanalyze,
       n_mod_since_analyze  -- Змін з останнього ANALYZE
   FROM pg_stat_user_tables
   WHERE n_mod_since_analyze > 100000;  -- Багато змін!
   
   -- Рішення
   ANALYZE VERBOSE orders;
   

3. Partitioned Tables

   -- Для партиційованих таблиць статистика збирається на кожній партиції
   -- Планувальник може помилятися в оцінці
   -- Рішення: збільшити statistics_target на ключових партиціях
   ALTER TABLE orders_y2024 ALTER COLUMN created_at SET STATISTICS 1000;
   

Продуктивність

Вплив на планування:

statistics_target	Точність плану	Час ANALYZE
10 (мінімум)	Низька	Швидко
100 (за замовчуванням)	Хороша	Нормально
1000	Відмінна	В 10x довше
10000	Максимальна	В 100x довше

Memory Footprint статистики:

• Кожен кошик гістограми: ~24 байта
• При statistics_target = 100: ~2.4 КБ на колонку
• При 1000: ~24 КБ на колонку
• Вплив на RAM: мінімальний

Production Experience

Реальний сценарій #1: Data Skew вбиває продуктивність

• Лог-платформа: 500 млн записів
• Таблиця: level (DEBUG: 80%, INFO: 15%, ERROR: 5%)
• Проблема: Запити по ERROR були повільними
• Причина: statistics_target = 100, MCV список не покривав усі рівні
• Рішення:

  ALTER TABLE logs ALTER COLUMN level SET STATISTICS 1000;
  ANALYZE logs;
  

• Результат: Планувальник став точно оцінювати селективність, Index Scan для ERROR

Реальний сценарій #2: Correlated Columns

• E-commerce: замовлення по (country, city)
• Проблема: Планувальник помилявся в оцінці на 2 порядки
• Причина: city сильно залежить від country (Москва тільки в Росії)
• Рішення:

  CREATE STATISTICS s_country_city (ndistinct, dependencies)
  ON country, city FROM orders;
  ANALYZE orders;
  

• Результат: Оцінка рядків стала точною, планувальник обрав правильний Join

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;

-- 2. Перевірка correlation
SELECT
    attname,
    ROUND(correlation::numeric, 3) as correlation,
    CASE
        WHEN ABS(correlation) < 0.3 THEN 'LOW - Random I/O'
        WHEN ABS(correlation) < 0.7 THEN 'MEDIUM'
        ELSE 'HIGH - Sequential I/O'
    END as assessment
FROM pg_stats
WHERE tablename = 'orders'
ORDER BY ABS(correlation) ASC;

-- 3. Перевірка n_distinct
SELECT
    attname,
    n_distinct,
    CASE
        WHEN n_distinct < 0 THEN 'High (' || ABS(n_distinct) * 100 || '%)'
        WHEN n_distinct < 100 THEN 'Low'
        WHEN n_distinct < 10000 THEN 'Medium'
        ELSE 'High'
    END as cardinality
FROM pg_stats
WHERE tablename = 'users';

-- 4. Перевірка extended statistics
SELECT
    stxname,
    stxkeys::regclass[],
    stxndistinct,
    stxdependencies
FROM pg_statistic_ext;

Best Practices

1. Моніторте n_mod_since_analyze — якщо > 10% таблиці, робіть ANALYZE
2. Збільшуйте statistics_target для колонок з аномальним розподілом
3. Використовуйте Extended Statistics для корельованих колонок
4. Перевіряйте correlation — < 0.3 означає Random I/O
5. Не забувайте ANALYZE після масової вставки/видалення
6. MCV список повинен покривати > 80% даних для точного планування

Резюме для Senior

• Кардинальність — ключовий вхідний параметр для оптимізатора
• n_distinct, MCV, histogram, correlation — 4 стовпи статистики
• Data Skew — часта причина неправильних планів; перевіряйте MCV
• Extended Statistics (PG 10+) вирішує проблему корельованих колонок
• correlation < 0.3 → Random I/O → подумайте про CLUSTER
• statistics_target — баланс між точністю та часом ANALYZE
• Регулярно перевіряйте pg_stats та pg_statistic_ext

---

🎯 Шпаргалка для співбесіди

Обов’язково знати:

• Кардинальність = кількість унікальних значень; Селективність = кардинальність / всього рядків
• n_distinct: -1 = 100% унікальні, 500 = рівно 500 унікальних
• MCV (Most Common Values): топ частих значень + частота — для skewed даних
• Correlation: -1..1 — наскільки фізичний порядок збігається з логічним
• correlation = 0 → Random I/O (хаос на диску), correlation = 1 → Sequential I/O
• Extended Statistics (PG 10+): dependencies, ndistinct, mcv для корельованих колонок
• statistics_target = 100 (за замовчуванням), 1000 — для skewed колонок (в 10x довше ANALYZE)

Часті уточнюючі питання:

• «Чому індекс на status (ACTIVE/PENDING) не використовується?» → Низька кардинальність, Seq Scan швидший
• «Що буде, якщо statistics_target занадто низький?» → Планувальник помиляється в оцінці rows
• «Як вирішити проблему корельованих колонок (country, city)?» → CREATE STATISTICS ... (dependencies)
• «Що означає correlation = 0.05?» → Майже хаотичний порядок → CLUSTER або новий індекс

Червоні прапорці (НЕ говорити):

• ❌ «Індекс на полі з 2 значеннями (M/F) прискорить пошук» (Seq Scan все одно швидший)
• ❌ «n_distinct = -1 значить немає унікальних значень» (навпаки — 100% унікальні!)
• ❌ «statistics_target не впливає на продуктивність» (впливає на час ANALYZE та точність планів)

Пов’язані теми:

• [[Для чого потрібні індекси]] → селективність та кардинальність
• [[Навіщо потрібен ANALYZE]] → збір статистики, n_distinct, MCV, histogram
• [[Що таке складений індекс]] → складна кардинальність
• [[Як оптимізувати повільні запити]] → вплив статистики на план