Иногда возникает ситуация, когда при наличии подходящего индекса планировщик PostgreSQL всё равно выбирает
Sequential Scan. Обычно это связано не с «ошибкой» базы данных, а с работой механизма,
который выбирает наиболее дешевый план выполнения запроса. PostgreSQL не обязан использовать индекс, именно
планировщик оценивает стоимость разных стратегий доступа к данным и выбирает ту, которая, по его расчетам,
потребует меньше ресурсов.
Стоимостная модель и селективность
Основой выбора плана в PostgreSQL является стоимостная модель. При построении плана выполнения запроса база оценивает:
- количество страниц, которые придется прочитать
- ожидаемое число строк
- стоимость случайного и последовательного чтения
- объем операций CPU
- вероятность нахождения данных в памяти
Если таблица небольшая, последовательное сканирование часто оказывается дешевле индексного доступа. В этом случае чтение индекса и последующие переходы к heap-страницам обходятся дороже, чем простой линейный проход по таблице.
Похожая ситуация возникает, когда запрос затрагивает значительную часть строк, планировщик может посчитать
Sequential Scan более выгодным вариантом. Конкретный порог установить не получится — решение
зависит от распределения данных, параметров стоимостной модели и физических характеристик хранения. Также на выбор
плана влияет корреляция данных. Если строки в heap физически расположены близко к порядку индекса, индексное
сканирование может оставаться эффективным даже при относительно большом количестве выбираемых строк.
Bitmap Heap Scan
PostgreSQL часто использует Bitmap Heap Scan — промежуточный вариант между Index Scan
и Sequential Scan.
Сначала база читает индекс и формирует bitmap нужных страниц, после чего обращается к heap уже более
последовательно. Такой подход снижает количество хаотичных чтений и особенно эффективен, когда запрос выбирает
заметную, но не слишком большую часть таблицы. В плане это выглядит как цепочка:
Bitmap Index Scan -> Bitmap Heap Scan, а не обычный Index Scan.
Функции над колонками
Следующая, довольно распространённая причина игнорирования индекса — использование функций над индексируемым полем.
Например: WHERE date(created_at) = current_date. При таком условии обычный B-tree индекс по
created_at не поможет, потому что планировщик будет работать не с исходным значением колонки,
а с результатом функции date(). В таких случаях PostgreSQL скорее всего выберет
Sequential Scan.
Чтобы избежать этого можно переписать условие в виде диапазона:
WHERE created_at >= current_date AND created_at < current_date + interval '1 day'
Тогда условие снова станет совместимым с индексом.
Похожая проблема возникает и с выражениями вроде: LOWER(column) = 'test_text'. Если используется
функция, индекс также должен быть построен по выражению:
CREATE INDEX idx_users_lower_email
ON users (LOWER(email));
Неявное приведение типов
Если типы операндов различаются, PostgreSQL может построить выражение с приведением типов, но оно уже не будет соответствовать индексу. В результате индекс становится неприменим.
Например: WHERE varchar_column = 666. В подобных ситуациях планировщик может отказаться от индексного
доступа даже при наличии подходящего индекса. Особенно часто такие проблемы встречаются в проектах с нестрогой
схемой типов или при активном использовании ORM.
Составные индексы и левый префикс
При работе с составными индексами (на несколько колонок) важно понимать принцип левого префикса — для индекса: (a, b, c) эффективное использование обычно возможно только тогда, когда условия начинаются с первых колонок индекса.
Например: WHERE a = 10 и WHERE a = 10 AND b = 20 будут вероятнее всего использовать
индекс.
А запрос: WHERE b = 20 в большинстве случаев уже не позволит эффективно использовать такой индекс.
Стоит также отметить, что после диапазонного условия (>, <, BETWEEN) PostgreSQL уже не может эффективно использовать следующие колонки составного индекса.
Index Skip Scan
В последних версиях (18+) PostgreSQL есть механизм Index Skip Scan, который позволяет частично использовать
составной индекс даже без фильтрации по первой колонке. Однако эффективность такого сканирования сильно зависит от
количества уникальных значений и распределения данных. Если пропущенная колонка содержит большое количество
уникальных значений, стоимость Skip Scan может быстро стать слишком высокой (и планировщик не будет
его использовать).
Поэтому поведение таких запросов желательно проверять через EXPLAIN ANALYZE, а не рассчитывать на
автоматическую оптимизацию.
Актуальность статистики
Планировщик принимает решения на основе статистики из pg_statistic. После массовых изменений данных
(bulk insert, массовых delete/update и т.п.) статистика перестаёт быть актуальной, и тогда PostgreSQL может
ошибочно оценить:
- количество строк
- селективность условий
- распределение значений
- стоимость различных типов сканирования
В результате база начинает строить неоптимальные execution plan. Во многих случаях проблема решается
с помощью: ANALYZE table_name; или корректной настройкой autovacuum/analyze.
Ограничения операторов
Очень часто проблемы возникают из-за того что везде используется только B-tree, но не все операторы с ним совместимы.
Например: LIKE '%value' не может эффективно использовать стандартный индекс, поскольку поиск
начинается не с начала строки.
Для таких задач обычно применяются: pg_trgm, GIN, GIST, полнотекстовые
индексы.
Аналогично, специализированных типов индексов требует работа с:
- массивами
- JSONB
- полнотекстовым поиском
- геоданными
EXPLAIN ANALYZE как основной инструмент диагностики
При анализе производительности не стоит полагаться на интуицию, удачу и самому пытаться предположить поведение
планировщика. В PostgreSQL практически любая диагностика должна начинаться с: EXPLAIN ANALYZE — он
показывает план и реальные метрики выполнения запроса.
Именно execution plan показывает:
- почему был выбран
Seq Scan - сколько строк ожидал увидеть планировщик
- насколько ошибочны оценки статистики
- где происходят основные потери времени
Во многих случаях причина становится очевидной уже после первого просмотра плана выполнения.