Почему база данных может не использовать индекс при фильтрации, если индекс есть

Что проверяют

Знаете ли вы, что план выбирает не автор запроса, а планировщик по оценке стоимости, и умеете ли читать EXPLAIN. Для бэкендера это вопрос о том, разберётся ли он с медленной ручкой сам, не вызывая администратора базы.

Как отвечать

Разделить две разные ситуации: индекс не подходит по форме запроса и индекс подходит, но планировщик посчитал его дороже.

Форма запроса мешает, когда:

• над колонкой стоит функция или выражение: where lower(email) = $1, where date(created_at) = $1, where price * 2 > 100. Индекс построен по колонке, а не по выражению, нужен функциональный индекс;
• типы не совпадают и происходит неявное приведение, например колонка текстовая, а параметр числовой;
• фильтр не по левому префиксу составного индекса: индекс по паре (a, b) не поможет условию только по b;
• шаблон с ведущим процентом в like, регистронезависимое сравнение, поиск внутри json без подходящего оператора.

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

Правильный ход ответа: не гадать, а показать explain (analyze, buffers) и сравнить ожидаемое число строк с фактическим. Разошлись на порядок, значит дело в статистике, а не в индексе.

Где спотыкаются

Говорят, что индекс всегда быстрее полного сканирования. Если запрос возвращает половину таблицы, обход через индекс дороже, и отказ от него это верное решение, а не дефект.

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

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