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

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

Понимаете ли вы, что планировщик выбирает план по оценке стоимости, а не по факту наличия индекса, и умеете ли читать вывод EXPLAIN ANALYZE, сравнивая оценку с фактом. Для Java-позиции есть свой слой: доходит ли до базы ровно тот запрос, который вы написали, после ORM и пула соединений.

Как отвечать

Развести случаи на «план верный» и «план сбит».

Верный план получается, когда запрос возвращает большую долю таблицы. Доступ по индексу это случайные обращения к страницам с данными, и начиная с некоторой доли выборки последовательное чтение дешевле. То же самое на маленькой таблице, которая целиком лежит в кэше.

План сбивается обычно по одной из трёх причин:

• устарела статистика. После массовой заливки анализ не прошёл, и оценка числа строк расходится с фактом на порядки. Видно прямо в плане по расхождению ожидаемых и реальных строк;
• предикат не ложится на индекс: функция или приведение типа над колонкой, шаблон с ведущим процентом, условие через or по разным колонкам, фильтр только по второй колонке составного индекса;
• запрос подготовленный. После нескольких выполнений база может перейти на общий план, посчитанный без знания конкретного значения. В Java это не теория, а настройка драйвера, у pgjdbc за переход отвечает prepareThreshold.

Как разбираться:

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
SET enable_seqscan = off;  -- только чтобы сравнить стоимости

Если с выключенным последовательным чтением индексный план всё равно оценён дороже, чинить надо статистику или сам индекс.

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

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

Не замечают приведения типов. Параметр ушёл строкой в числовую колонку или число попало в колонку другого числового типа, и индекс перестал подходить. В плане это видно по явному приведению внутри условия.

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