🟢 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 (одно последовательное чтение vs 950 000 случайных).

PostgreSQL хранит Most Common Values (MCV) и может выбирать разные планы для разных значений!

Типичные ошибки

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
• [[Что такое составной индекс]] → составная кардинальность
• [[Как оптимизировать медленные запросы]] → влияние статистики на план