Как выбрать индекс и считать селективность

Опирается на правила: PG-I-030 … PG-I-037, PG-E-020 из PostgreSQL Style Guide → раздел Как выбрать индекс и считать селективность.

Важно знать

Селективность = distinct_values / total_rows. Чем выше, тем эффективнее индекс.

Индекс на низкоселективной колонке (is_deleted, status) — бесполезен.

< 5-10% таблицы через = — обычно индекс; > 20% — обычно seq-scan.

random_page_cost: 1.1 на SSD (дефолт 4.0 — под HDD).

Композитный индекс может работать там, где single-column бесполезен.

ANALYZE после массовой загрузки — autovacuum не успевает.

default_statistics_target: 1000 для колонок с редкими пиками.

Расхождение rows vs actual rows в 10x+ — статистика устарела.

Коррелирующие колонки — CREATE STATISTICS с dependencies.

«Индекс есть, но запрос медленный» — почти всегда история про селективность. PostgreSQL не использует индекс, если считает seq-scan дешевле. UCP формулирует — как считать и проверять.

Что такое селективность

PG-I-030: формально selectivity = distinct_values / total_rows.

Колонка	distinct	total	selectivity
`id` (PK)	1 000 000	1 000 000	1.0 — идеально
`email`	998 000	1 000 000	0.998 — почти идеально
`customer_id`	50 000	1 000 000	0.05 — средне
`status` (5 значений)	5	1 000 000	0.000005 — низкая
`is_deleted`	2	1 000 000	0.000002 — почти нулевая

PG-I-031: индекс на низкоселективной колонке бесполезен.

WHERE is_deleted = false отдаст 98% таблицы. Выгоднее прочитать всю таблицу одним проходом, чем 98% случайных IO через индекс.

Как посмотреть статистику

SELECT
    attname,
    n_distinct,
    most_common_vals,
    most_common_freqs,
    null_frac
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'orders';

n_distinct — оценка уникальных значений. Положительное = абсолютное число; отрицательное (-0.05) = доля (5% уникальных).
most_common_vals — массив самых частых значений.
most_common_freqs — частоты (0..1).
null_frac — доля NULL.

Пример:

attname           | status
n_distinct        | 5
most_common_vals  | {DELIVERED,SHIPPED,NEW,CANCELLED,PAID}
most_common_freqs | {0.62, 0.18, 0.10, 0.06, 0.04}

62% — DELIVERED → запрос WHERE status = 'DELIVERED' пойдёт seq-scan. А WHERE status = 'PAID' (4%) — индекс может зайти.

Когда индекс точно поможет

PG-I-032:

Доля строк	Индекс?
`< 1%`	да, точно
`1-5%`	скорее всего да
`5-20%`	зависит от размера строки и кеша
`> 20%`	скорее всего нет
`> 50%`	точно нет

Зависимости:

Размер строки (узкие → больше fits в heap-блок → seq-scan дешевле).
effective_cache_size (сколько PG считает в кеше).
random_page_cost vs seq_page_cost.

random_page_cost на SSD

PG-I-033:

ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();

Дефолт 4.0 рассчитан под HDD. На SSD random IO почти такой же быстрый, как sequential — 1.1 корректнее.

Без снижения планировщик предпочтёт seq-scan, когда индекс реально быстрее.

Композитный индекс и селективность

PG-I-034:

Для (a, b, c) селективность по левому префиксу:

WHERE a — селективность a.
WHERE a AND b — a × b приближённо.

Пример: (status, created_at):

WHERE status = 'DELIVERED' — 62% → seq-scan.
WHERE status = 'DELIVERED' AND created_at > now() - interval '1 day' — 62% × 0.5% = 0.3% → индекс пойдёт идеально.

Композитный может работать там, где single-column бесполезен — но оба условия должны быть в WHERE.

Когда статистика врёт

ANALYZE после массовой загрузки

PG-I-035:

ANALYZE orders;                            -- одна таблица
ANALYZE orders (status, created_at);      -- конкретные колонки
ANALYZE;                                   -- весь schema

Autovacuum запускает ANALYZE сам, когда изменилось ~10% таблицы. На быстрых нагрузках не успевает.

Увеличить statistics_target

PG-I-036:

-- default 100; увеличиваем до 1000 для колонки
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

Пример: country_code — 99% RU, остальные 1% — 50 разных стран. Без увеличения статистики PG не увидит мелкие страны в most_common_vals, селективность для них оценена неверно.

EXPLAIN проверка

PG-E-020:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'PAID';

Seq Scan on orders  (cost=0.00..18334.00 rows=400000 width=...) (actual time=0.012..89.123 rows=42137 loops=1)
  Filter: (status = 'PAID')
  Rows Removed by Filter: 957863

rows=400000 — оценка планировщика.
actual rows=42137 — реальность.
Разница 10x → планировщик ошибся → выбрал seq-scan, хотя индекс был возможен.

Решение: ANALYZE orders;.

Multi-column statistics

PG-I-037: для коррелирующих колонок.

CREATE STATISTICS stats_orders_geo (dependencies, ndistinct)
    ON country_code, currency FROM orders;
ANALYZE orders;

Пример: country_code и currency коррелированы (страна → валюта). Селективность WHERE country_code = 'RU' AND currency = 'RUB' равна country_code = 'RU', не произведению. PostgreSQL по умолчанию считает независимыми и завышает селективность в 10-50 раз.

Чек-лист «индекс не работает»

EXPLAIN ANALYZE показывает Seq Scan вместо Index Scan?
Сравнить rows= оценку и actual rows=. Расхождение 10x+ → ANALYZE.
pg_stats для колонки — n_distinct, most_common_freqs. Низкая селективность → нужен другой подход.
random_page_cost на SSD — 1.1?
В composite — колонка стоит первой в левом префиксе по условию?
Для WHERE LOWER(email) = ... — functional index?
Для частичных предикатов — partial index?
Корреляция колонок — CREATE STATISTICS?

Что запрещено

Антипаттерн	Правило	Что взамен
Индекс на `is_deleted boolean`	`PG-I-031`	partial index с `WHERE deleted_at IS NULL`
Индекс на `status` (5 значений) без фильтра	`PG-I-031`	composite или partial
`random_page_cost = 4.0` на SSD	`PG-I-033`	`1.1`
`ANALYZE` не после массовой загрузки	`PG-I-035`	руками
`statistics_target = 100` для редких пиков	`PG-I-036`	`1000`
Игнорировать расхождение `rows`/`actual rows`	`PG-E-020`	`ANALYZE`
Коррелирующие колонки без `CREATE STATISTICS`	`PG-I-037`	`dependencies`
«Селективность первой» как абсолютное правило	`PG-I-034`	«частое = первым», селективность вторична

Куда дальше

PG → Селективность — нормативные формулировки.
Типы индексов — какой брать.
Composite-индексы — порядок полей.
EXPLAIN ANALYZE — детальный анализ.
VACUUM и autovacuum — когда статистика обновляется.
Мониторинг и slow queries — pg_stat_statements.