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

Утро. Макс заходит в офис с двумя кружками кофе — одна для себя, вторая для Лены. Та уже сидит за компьютером и хмуро разглядывает EXPLAIN своего запроса.

— Ну что, готова узнать, почему твой запрос до сих пор тормозит? — Макс ставит кружку перед ней. — Вчера я обещал рассказать про индексы.

Лена показала на экран:

— Вот! Я сделала таблицу на тесте, создала индекс по user_id, но PostgreSQL всё равно делает Seq Scan! Он что, тупой?

Макс разворачивает монитор и смотрит на план запроса Лены:

EXPLAIN SELECT * FROM my_tasks WHERE user_id = 1;

— Да нет же, — Макс смеётся. — Он просто знает то, чего не знаешь ты. Есть четыре ключевых ситуации, когда оптимизатор сознательно выбирает полное сканирование таблицы вместо использования индекса:

1. Маленькие таблицы (Seq Scan выгоднее)

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

2. Фильтрация по функции от поля (требуется специальный индекс - функциональный)

Примеры:

SELECT * FROM users WHERE lower(email) = 'max@example.com';
SELECT * FROM users WHERE age + 5 > 30;

Обычные индексы по email и age не обязаны подходить к условиям по выражениям.

Почему: Обычный индекс хранит исходные значения. Postgres не может сопоставить вычисляемое выражение с обычным индексом.

Некоторые выражения планировщик способен алгебраически упростить, но полагаться на это без плана нельзя.

Решение: Создать функциональный индекс, который заранее вычисляет нужное преобразование:

CREATE INDEX idx_users_email_lower ON users (lower(email));

3. Неселективные условия (индекс не даёт выгоды)

Примеры:

-- Плохо (нет селективности):
SELECT * FROM my_tasks WHERE status = 'closed'; -- 98% строк

-- Хорошо (высокая селективность):
SELECT * FROM my_tasks WHERE status = 'new'; -- < 1% строк

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

Универсального порога в 5% или 10% нет. Решение зависит от ширины строк, корреляции, числа страниц, кэша, стоимости случайного чтения и того, какие столбцы нужно вернуть. Смотрите план на своих данных.

4. Устаревшая статистика (оптимизатор “не в курсе”)

Обычно Postgres самостоятельно обновляет статистику (через autovacuum). Но в некоторых ситуациях статистика может устареть или быть неактуальной. Например, если ты только что залила миллион строк — Postgres живёт в прошлом. Посмотреть можно, например, так:

SELECT schemaname, relname, last_analyze, last_autoanalyze
FROM pg_stat_all_tables
WHERE relname = 'твоя_таблица';  -- или без условия для списка всех таблиц

Собрать статистику принудительно:

ANALYZE твоя_таблица;

Лена отставила кружку:

— Спасибо, теперь стало понятнее, где копать. А покажешь, что делать, если индекс есть, но запрос всё равно медленный?

— Конечно! — Макс ухмыляется. — Завтра разберём Partial Indexes и Index Only Scan.

Вечером Макс записал в своём дневнике:

«Лена уже сама создает индексы . Теперь главное — не дать ей создать индекс на каждое поле…»