← назад к разделу

«Индекс есть, но запрос всё равно медленный» — одна из самых частых жалоб при работе с PostgreSQL. Почти всегда причина в селективности: PostgreSQL сам решает, выгодно ли использовать индекс или проще прочитать всю таблицу. Разберём, как он это решает и что делать, если он решает неверно.

Почему PostgreSQL иногда не использует индекс

Представьте таблицу из миллиона заказов. У каждого заказа есть поле status с пятью значениями: NEW, PAID, SHIPPED, DELIVERED, CANCELLED. Большинство заказов — DELIVERED, примерно 62%.

Вы добавили индекс на status и запрашиваете WHERE status = 'DELIVERED'. PostgreSQL смотрит на индекс и видит: он приведёт к 620 000 строкам из 1 000 000. Чтобы получить каждую из них, нужно прыгнуть в случайное место на диске. Это медленнее, чем просто прочитать всю таблицу последовательно. Поэтому PostgreSQL выбирает seq-scan — и он прав.

Ключевой термин здесь — селективность.

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

Селективность — это доля уникальных значений в колонке относительно общего числа строк:

селективность = количество_уникальных_значений / всего_строк
КолонкаУникальныхВсего строкСелективность
id (первичный ключ)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 — почти нулевая

Чем выше селективность, тем меньше строк возвращает условие — и тем полезнее индекс.

Индекс на is_deleted или status практически бесполезен: запрос WHERE is_deleted = false вернёт 98% таблицы. PostgreSQL разумно предпочтёт seq-scan.

Как PostgreSQL принимает решение

Планировщик оценивает процент строк, который вернёт условие запроса, и сравнивает стоимость двух путей:

Доля строк по условиюИндекс?
менее 1%да, точно
1–5%скорее всего да
5–20%зависит от размера строки и кеша
более 20%скорее всего нет
более 50%точно нет

На решение влияют ещё два параметра: сколько данных PostgreSQL считает закешированным (effective_cache_size) и во сколько раз случайное чтение дороже последовательного (random_page_cost).

Настройка random_page_cost на SSD

По умолчанию random_page_cost = 4.0 — это значение рассчитано под жёсткие диски, где случайное чтение в четыре раза медленнее последовательного. На SSD разница почти исчезает.

Если не обновить этот параметр, PostgreSQL будет систематически выбирать seq-scan там, где индекс реально быстрее.

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

Значение 1.1 — стандартная рекомендация для SSD.

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

PostgreSQL хранит статистику по каждой колонке в системном представлении pg_stats:

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-значений.

Пример результата для колонки status:

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}

По этой статистике понятно: WHERE status = 'DELIVERED' вернёт 62% строк — seq-scan. А WHERE status = 'PAID' вернёт 4% — индекс может помочь.

Составной индекс там, где одиночный бесполезен

Колонка status с пятью значениями сама по себе малополезна для индексирования. Но если добавить вторую колонку с высокой селективностью, картина меняется.

Составной индекс (status, created_at):

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

Селективности двух условий перемножаются. Поэтому составной индекс может работать там, где одиночный бесполезен — но оба условия должны присутствовать в запросе.

Как проверить, что происходит в реальности

EXPLAIN ANALYZE показывает и план, и фактическое выполнение:

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 — реальное количество строк.

Расхождение почти в десять раз означает, что статистика устарела и планировщик принял решение на основе неверных данных.

Когда статистика устаревает и что делать

Запустить ANALYZE вручную

Autovacuum запускает сбор статистики автоматически, когда в таблице изменилось около 10% строк. При массовой загрузке данных он не успевает. После крупной загрузки стоит запустить вручную:

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

Увеличить глубину статистики

По умолчанию PostgreSQL хранит статистику по 100 наиболее частым значениям (statistics_target = 100). Если в колонке редкие пики — например, country_code с 99% значений RU и ещё 50 редких стран — они не попадут в статистику, и планировщик будет неверно оценивать их селективность.

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

После этого PostgreSQL будет хранить до 1000 наиболее частых значений для этой колонки.

Коррелирующие колонки

Бывает, что две колонки связаны между собой: например, country_code и currency. Страна Россия — почти всегда рубль. PostgreSQL по умолчанию считает колонки независимыми и вычисляет селективность как произведение двух условий — это завышает оценку в десятки раз.

Для таких случаев есть расширенная статистика:

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

После этого планировщик учитывает зависимость между колонками и оценивает селективность WHERE country_code = 'RU' AND currency = 'RUB' правильно — не как произведение, а как вероятность одного из условий.

Частые ошибки

Индекс на булевой колонке. WHERE is_deleted = false — почти всегда 98%+ строк. Индекс не поможет. Если нужен частичный охват, лучше сделать частичный индекс: CREATE INDEX ON orders (created_at) WHERE deleted_at IS NULL — он охватывает только актуальные строки.

Игнорировать расхождение rows и actual rows. Десятикратное расхождение в EXPLAIN ANALYZE — сигнал, что статистика устарела. Решение: ANALYZE.

Оставить random_page_cost = 4.0 на SSD. Планировщик будет занижать полезность индексов. Один ALTER SYSTEM это чинит.

Не запускать ANALYZE после массовой загрузки. Autovacuum не успевает за быстрой вставкой. После ETL или миграции — запустить вручную.

Коротко

  • Селективность = уникальных значений / всего строк. Выше — лучше для индекса.
  • Индекс на is_deleted или status с 5 значениями — почти бесполезен: планировщик выберет seq-scan.
  • Правило: менее 5% строк по условию → индекс, более 20% → seq-scan.
  • На SSD обязательно: random_page_cost = 1.1 (дефолт 4.0 — под жёсткий диск).
  • Составной индекс (status, created_at) может работать там, где (status) бесполезен.
  • pg_stats показывает, что знает планировщик: n_distinct, most_common_freqs.
  • Расхождение rows vs actual rows в 10 раз в EXPLAIN ANALYZE → запустить ANALYZE.
  • После массовой загрузки — ANALYZE вручную; autovacuum не успевает.
  • Для колонок с редкими пиками: увеличить statistics_target до 1000.
  • Коррелирующие колонки (country_code + currency) → CREATE STATISTICS ... (dependencies).

Что почитать дальше

  • Составные индексы в PostgreSQL — порядок колонок и левый префикс.
  • EXPLAIN ANALYZE — как читать план запроса детально.
  • VACUUM и autovacuum — когда и как обновляется статистика.