Индекс есть, а планировщик его не берёт

Запрос тормозит. Смотрим план — Seq Scan. Индекс на колонке есть. Дальше обычно происходит одно из двух: либо начинают добавлять ещё индексы, либо пишут SET enable_seqscan = off и уходят.

Оба хода мимо. Планировщик не сломан и не глуп. Он посчитал, что полный проход дешевле, и в большинстве случаев он посчитал правильно — по тем данным, которые у него были.

Планировщик считает, а не знает

Стоимостной планировщик оценивает каждый вариант выполнения и берёт самый дешёвый по оценке. Оценка строится на статистике: сколько строк в таблице, сколько различных значений в колонке, как они распределены, какая доля страниц лежит в кэше.

Из этого следует главное: планировщик ошибается там, где ошибается статистика. Не в логике выбора, а во входных данных для неё.

Отсюда практический порядок разбора, который экономит часы.

Сначала — сравнить оценку с фактом

EXPLAIN показывает план. EXPLAIN ANALYZE выполняет запрос и показывает, сколько строк реально прошло через каждый узел.

Смотреть надо не на общее время. Смотреть надо на расхождение между rows= в оценке и actual rows= в факте.

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

Когда оценка и факт сходятся, а запрос всё равно медленный — это уже другой разговор, и там действительно может не хватать индекса.

Пять причин, по которым индекс не берётся

Выборка слишком широкая. Если запрос достаёт больше нескольких процентов таблицы, поиск по индексу проигрывает. Каждая строка через индекс — это переход к куче за данными, и на большой доле дешевле прочитать таблицу подряд. Это не патология, это правильный выбор.

Статистика устарела. Массовая загрузка, миграция, резкий рост — и планировщик работает по позавчерашней картине мира. ANALYZE на таблице занимает секунды и решает вопрос. Стоит проверять первым, потому что дешевле всего.

Условие не совпадает с индексом. Функция поверх колонки — WHERE lower(email) = ... — не даст использовать обычный индекс по email, нужен индекс по выражению. То же с несовпадением типов: сравнение bigint с числовым литералом, который вывелся как numeric, может отрезать индекс молча.

Порядок колонок в составном индексе. Индекс по (a, b) работает для условия по a и для a вместе с b. Для условия только по b он бесполезен. Это следствие того, что индекс — упорядоченное дерево, а не набор колонок.

Перекос распределения. Колонка «статус», где девяносто восемь процентов строк в значении done. Для редкого значения индекс идеален, для частого — вреден. Планировщик, если у него есть гистограмма, разберётся сам и выберет по-разному для разных значений. Если статистики не хватает, он выберет одинаково и ошибётся на одном из двух.

Два индекса, про которые забывают

Частичный. Индекс не обязан покрывать всю таблицу.

CREATE INDEX ON orders (created_at) WHERE status = 'pending';

На таблице, где девяносто восемь процентов заказов завершены, такой индекс в пятьдесят раз меньше обычного, обновляется только при записи подходящих строк и полностью помещается в кэш. Классический случай — очередь внутри таблицы: активных записей всегда мало, а таблица растёт вечно.

Покрывающий. Если индекс содержит все колонки, нужные запросу, база может ответить, не заглядывая в саму таблицу.

CREATE INDEX ON orders (customer_id) INCLUDE (total, created_at);

Разница здесь не в проценте, а в кратности: обычный поиск по индексу для каждой найденной строки идёт в кучу за данными, и на выборке в тысячу строк это тысяча случайных чтений.

Оговорка, без которой совет вредит: в PostgreSQL сканирование только по индексу работает, когда карта видимости говорит, что страница не менялась. На таблице с интенсивной записью это условие часто не выполняется, и выигрыш исчезает.

Корреляция колонок — самая частая причина плохой оценки

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

Реальные данные редко независимы. Город и почтовый индекс связаны жёстко: city = 'Москва' AND zip = '101000' даёт не произведение долей, а примерно то же, что одно условие по индексу. Планировщик недооценивает результат в разы, выбирает вложенный цикл и получает на порядок больше итераций, чем ждал.

Это чинится расширенной статистикой:

CREATE STATISTICS orders_city_zip (dependencies)
  ON city, zip FROM orders;
ANALYZE orders;

Приём малоизвестный, а закрывает он, по моим наблюдениям, изрядную долю случаев «планировщик сошёл с ума». Стоит проверять его раньше, чем начинать переписывать запрос.

Чего я стараюсь не делать

Не отключаю выбор плана флагами. enable_seqscan = off — это диагностический инструмент, чтобы посмотреть, что планировщик считает вторым вариантом и почём. Как решение в проде он означает: мы зафиксировали план, который был хорош для сегодняшних данных, и он испортится вместе с ростом, только теперь молча.

Не добавляю индекс на каждую колонку из WHERE. У индексов есть обратная сторона, о которой вспоминают редко: каждая запись в таблицу обновляет каждый индекс. Таблица с двенадцатью индексами пишется втрое медленнее и занимает вдвое больше места. На таблице, которая пишется чаще, чем читается, лишний индекс — прямой убыток.

Не оптимизирую по EXPLAIN без ANALYZE. Оценка без факта — это мнение планировщика о самом себе.

Порядок, которым я иду

1. EXPLAIN ANALYZE, найти узел с максимальным расхождением оценки и факта.
2. Если расхождение большое — ANALYZE таблицу и повторить. Часто заканчивается здесь.
3. Если расхождение осталось — понять, почему статистика не описывает данные. Обычно это либо коррелирующие колонки, либо перекос.
4. И только теперь думать про индекс, и думать вместе с ценой записи.

Порядок важен. Начав с четвёртого шага, вы получите таблицу с десятком индексов и ту же самую проблему, потому что причина была на втором.