re:re:actable
Урок 104 минуты чтения

Почему индекс может не использоваться

На этой странице
Кратко

Планировщик PostgreSQL выбирает план по оценке стоимости, и индекс не используется в двух принципиально разных ситуациях. Индекс не применим к условию: функция или выражение над столбцом (lower(email), date(created_at)), несовпадение типов, LIKE '%abc', условие не на левый префикс составного индекса, OR по разным столбцам, отличие условия запроса от условия частичного индекса. Индекс применим, но невыгоден: условие выбирает большую долю строк (низкая селективность), таблица маленькая, неверная оценка из-за устаревшей статистики или коррелированных столбцов. Диагностика — EXPLAIN (ANALYZE, BUFFERS): сравнить оценку и факт, проверить Index Cond и Filter. Для эксперимента можно временно SET enable_seqscan = off, но не для продакшена.

Вопрос

Почему PostgreSQL может не использовать индекс и как это исправить?

Индекс не применим

ПричинаПримерИсправление
Функция над столбцомWHERE lower(email) = $1Индекс по lower(email)
Вычисление над столбцомWHERE price * 1.2 > 100WHERE price > 100 / 1.2
Дата из timestampWHERE date(created_at) = $1Диапазон >= $1 AND < $1 + 1 day
Несовпадение типовWHERE phone = 79991234567 (столбец text)Передать правильный тип
ПодстрокаWHERE title LIKE '%phone%'pg_trgm + GIN
Не левый префиксиндекс (user_id, created_at), запрос WHERE created_at > $1Отдельный индекс или другой порядок
OR по разным столбцамWHERE email = $1 OR phone = $2Индексы на оба (BitmapOr) или UNION
Частичный индексиндекс WHERE deleted_at IS NULL, в запросе условия нетДобавить условие в запрос
NOT, <>WHERE status <> 'done'Частичный индекс по нужным статусам, IN (...)
CollationLIKE 'abc%' при ru_RU.UTF-8text_pattern_ops или COLLATE "C"

Индекс невыгоден

ПричинаЧто происходит
Низкая селективностьWHERE is_active для 95% строк — Seq Scan честно дешевле
Маленькая таблицаПара страниц читаются быстрее индекса
Устаревшая статистикаПланировщик думает, что строк 10, а их 1 млн (или наоборот)
Коррелированные столбцыОценка независимых условий занижена/завышена
LIMIT и неверная оценкаПланировщик ожидает быстро найти N строк по индексу сортировки, а их мало — сканирует всё

Диагностика

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 42 AND created_at > now() - interval '7 days';
  • rows=5 vs actual rows=50000 — проблема оценки → ANALYZE orders, расширенная статистика.
  • Filter: (...) с Rows Removed by Filter при Index Scan — индекс сужает поиск не полностью; добавить столбец в индекс.
  • Seq Scan и actual rows — малая доля таблицы → индекс не применим, ищите причину из первой таблицы.

Обновление статистики

ANALYZE orders;
 
-- больше детализации для перекошенного столбца
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;
 
-- зависимости между столбцами
CREATE STATISTICS orders_city_country (dependencies, ndistinct) ON city, country FROM orders;
ANALYZE orders;

Эксперимент

BEGIN;
SET LOCAL enable_seqscan = off;     -- только для проверки гипотезы в текущей транзакции
EXPLAIN ANALYZE SELECT ...;         -- если с индексом действительно быстрее — проблема в оценках или настройках
ROLLBACK;

На SSD часто уменьшают random_page_cost (по умолчанию 4) до 1.1–1.5, чтобы планировщик реалистичнее оценивал случайный доступ.

Prepared statements и generic plan

После нескольких выполнений prepared statement PostgreSQL может перейти на generic plan, не учитывающий конкретное значение параметра. Для перекошенных данных (status = $1) это иногда даёт плохой план — помогает plan_cache_mode = force_custom_plan для сессии или запроса.

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

  • Считать, что Seq Scan — всегда плохо.
  • Хинты «заставить индекс» вместо исправления запроса или статистики.
  • Тестировать на маленькой локальной базе.
  • Не делать ANALYZE после массовой загрузки или миграции данных.

Что важно помнить

  • Две группы причин: индекс не применим или невыгоден.
  • Условие должно совпадать с тем, как построен индекс.
  • Оценки планировщика зависят от статистики.

Мини-шпаргалка

  • Без функций над столбцом.
  • Типы параметров = типы столбцов.
  • ANALYZE, SET STATISTICS, CREATE STATISTICS.
  • EXPLAIN (ANALYZE, BUFFERS).
  • random_page_cost для SSD.

Документация

PostgreSQL: Examining Index Usage, PostgreSQL: Planner Statistics.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам