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

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

PG-I-030 Селективность колонки = доля разных значений в таблице. Чем выше, тем эффективнее индекс по этой колонке.

Формально: selectivity = distinct_values / total_rows.

Примеры:

Колонкаdistincttotalselectivity
id (PK)1 000 0001 000 0001.0 — идеально
email998 0001 000 0000.998 — почти идеально
customer_id50 0001 000 0000.05 — средне
status (5 значений)51 000 0000.000005 — низкая
is_deleted21 000 0000.000002 — почти нулевая

PG-I-031 Индекс на низкоселективной колонке (is_deleted, status, country в одной стране) бесполезен. Планировщик увидит, что условие WHERE is_deleted = false отдаст 98% таблицы — выгоднее прочитать всю таблицу одним проходом, чем 98% случайных IO через индекс.

2. Как посмотреть селективность

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) — это доля от строк (-0.05 = 5% уникальных, т.е. 50К на 1M).
  • 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}
null_frac         | 0

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

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

PG-I-032 Запрос с равенством, отдающий < 5–10% таблицы, обычно идёт через индекс.

Доля строк по условиюИндекс?
< 1%Да, точно
1–5%Скорее всего да
5–20%Зависит от размера строки и кеша
> 20%Скорее всего нет (seq-scan дешевле)
> 50%Точно нет

Точные пороги определяются:

  • Размером строки (узкие строки → больше fits в один heap-блок → seq-scan дешевле).
  • effective_cache_size (косвенно — сколько данных PG считает в кеше).
  • Стоимостью random vs sequential IO (random_page_cost vs seq_page_cost).

PG-I-033 На SSD имеет смысл снизить random_page_cost с дефолтных 4.0 до 1.1. Дефолт рассчитан под HDD и часто заставляет планировщик предпочесть seq-scan, когда индекс действительно быстрее.

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

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

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

  • WHERE a = ? — селективность колонки a.
  • WHERE a = ? AND b = ? — комбинированная селективность a × b (приближённо).

Пример: (status, created_at) на таблице 1M заказов.

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

PG-I-034 Композитный индекс может ожить там, где single-column бесполезен. Низкоселективное status сужает пространство только до 62% таблицы, range по created_at уже внутри этой выборки сужает до 0.3%. Однако нужно, чтобы оба условия были в WHERE.

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

Планировщик опирается на статистику, собранную через ANALYZE. Если статистика устарела или нерепрезентативна — план кривой.

PG-I-035 После массовой загрузки / INSERT-ов / UPDATE-ов — ANALYZE руками.

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

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

PG-I-036 Если в данных есть редкие пиковые значения (бизнес-выбросы), увеличьте default_statistics_target для конкретной колонки.

-- по умолчанию PG сэмплит 100 значений для most_common_vals
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

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

6. Планировщик: проверка через EXPLAIN

PG-E-020 EXPLAIN ANALYZE показывает реальное и оценочное число строк. Расхождение в 10x+ = криво посчитано.

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 — оценка планировщика (оценил, что вернёт 400К).
  • actual rows=42137 — реальность (вернулось 42К).
  • Разница в 10x → планировщик ошибся → выбрал seq-scan, хотя был возможен Index Scan.

Решение: ANALYZE orders; (обновить статистику) → план должен пересчитаться.

7. Multi-column statistics (расширенная)

PG-I-037 Для коррелирующих колонок — CREATE STATISTICS с зависимостями.

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

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

После этого планировщик понимает зависимость и оценивает корректнее.

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

Когда видишь медленный запрос с подходящим (на бумаге) индексом — пройти по списку:

  1. EXPLAIN ANALYZE показывает Seq Scan вместо Index Scan?
  2. Сравнить rows= оценку и actual rows=. Расхождение 10x+ → ANALYZE.
  3. Посмотреть pg_stats для фильтрующей колонки — n_distinct, most_common_freqs. Низкая селективность → индекс не поможет, нужен другой подход.
  4. Проверить random_page_cost — на SSD должен быть 1.1, не 4.0.
  5. Проверить, что колонка в индексе действительно стоит первой в композитном (или хотя бы первой в левом префиксе по условию).
  6. Для условий типа WHERE LOWER(email) = ... — нужен functional index.
  7. Для частичных предикатов (WHERE deleted = false) — partial index.
  8. Проверить корреляцию колонок — может нужны CREATE STATISTICS.

Связанные

  • Composite-индексы — порядок полей.
  • Типы индексов — выбор B-tree vs GIN vs GiST.
  • Чтение EXPLAIN — узлы плана и метрики.