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

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

Сначала проверьте, что именно сделал планировщик

Для чтения используйте EXPLAIN (ANALYZE, BUFFERS) и смотрите не только на наличие узла Index Scan. Индекс может участвовать в плане как Bitmap Index Scan, после которого PostgreSQL читает строки таблицы через Bitmap Heap Scan. Это тоже использование индекса.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM orders
WHERE customer_id = 42;

ANALYZE в этой команде действительно выполняет запрос. Проверяйте таким способом безопасные запросы на чтение, а на рабочей базе учитывайте их нагрузку. Сравните оценку количества строк (rows) с фактическим числом (actual rows), а также число прочитанных блоков в BUFFERS.

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

Восемь причин, почему индекс не попал в план

1. Таблица слишком маленькая

Чтобы пройти по индексу, PostgreSQL читает его записи, а затем часто обращается к таблице за самими строками. Для небольшой таблицы последовательное чтение может потребовать меньше операций. Это не ошибка: планировщик оценивает стоимость, а не старается использовать каждый созданный индекс.

Проверьте фактическое число строк и блоков в плане. Не создавайте индекс только ради того, чтобы увидеть Index Scan: на маленькой таблице выигрыш может быть нулевым или отрицательным.

2. Условие возвращает слишком много строк

Если фильтр отбирает значительную часть таблицы, последовательное чтение может быть дешевле множества обращений к строкам по индексу. Например, индекс по статусу мало помогает запросу, который выбирает почти все заказы со статусом, преобладающим в таблице.

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

3. Статистика устарела или не отражает распределение данных

Планировщик оценивает количество строк по статистике. После массовой загрузки, удаления или резкого изменения распределения значений оценка может разойтись с реальностью. В плане это видно, когда rows сильно отличается от actual rows.

Проверьте, когда таблицу анализировали, и при необходимости обновите статистику командой ANALYZE orders;. Если ошибка касается конкретного столбца со множеством неравномерно распределённых значений, изучите настройки статистики для него по документации PostgreSQL 17; не повышайте их без измерения.

4. Выражение в запросе не совпадает с индексируемым столбцом

Обычный индекс по email не равен индексу по выражению lower(email). Запрос, который сравнивает результат функции, может не использовать индекс по исходному значению. То же бывает, когда к столбцу применяют преобразование типа или другую функцию.

Сопоставьте выражение в фильтре с определением индекса. Если регистронезависимый поиск — постоянное требование, можно рассмотреть индекс по тому же выражению и убедиться, что запрос использует его в том же виде. Не оборачивайте столбец в функцию автоматически: сначала проверьте конкретный план.

5. Запрос не использует ведущий столбец составного индекса

Для индекса по (customer_id, created_at) важен порядок столбцов. Такой индекс обычно полезен для фильтрации по customer_id или по нему вместе с датой. Запрос только по created_at может не получить от него ожидаемой пользы.

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

6. Шаблон поиска не подходит для B-tree

B-tree может помогать поиску по префиксу вроде name LIKE 'Ann%', но условие name LIKE '%Ann%' начинается с подстановочного знака и обычно не позволяет сделать такой поиск по диапазону индекса. На возможности поиска по префиксу также влияет сортировка и используемый класс операторов.

Если нужен поиск подстроки, проверьте подходящий тип индекса и операторы в документации PostgreSQL 17. Не заменяйте один индекс другим, не сверив его с реальным условием: для поиска префикса и произвольной подстроки требования отличаются.

7. Условие частичного индекса не доказано для запроса

Частичный индекс содержит строки, удовлетворяющие заданному условию, например только активные записи. Запрос должен включать условие, из которого планировщик может вывести, что нужные строки входят в этот индекс. Если условие отличается или его нельзя доказать при планировании, индекс может не подойти.

Особенно внимательно проверьте подготовленные запросы с параметрами: для общего плана значение параметра может быть неизвестно, поэтому PostgreSQL не всегда может сопоставить его с условием частичного индекса. Сравните план конкретного запроса и проверьте, не мешает ли обобщённый план.

8. Индекс не соответствует требуемой сортировке

Индекс может фильтровать строки, но не избавлять от сортировки. Если запрос сортирует по другому столбцу или сочетанию столбцов, порядок записей в индексе может не совпасть с требуемым. В плане останется узел Sort, а при высокой стоимости всего запроса планировщик может выбрать последовательное чтение.

Проверьте столбцы и направления в ORDER BY, порядок столбцов индекса и наличие Sort в плане. PostgreSQL умеет читать B-tree в обратном направлении, но это не делает любой индекс подходящим для любой комбинации сортировок.

Симптомы и проверки

План без индекса — не диагноз. Сначала выясните, сколько строк PostgreSQL ожидал прочитать и сколько прочитал на самом деле.

Что может сломать выводы

План зависит от данных, статистики и параметров конкретного запроса. Один и тот же SQL может выполняться по-разному на тестовой и рабочей базе, а результат для одного значения фильтра — отличаться от результата для другого. Поэтому не переносите выводы из теста на прод без проверки объёма и распределения данных.

Не отключайте последовательное сканирование как постоянное решение. В PostgreSQL настройка, запрещающая планировщику выбирать такой путь, может помочь проверить гипотезу, но сама по себе не исправляет неверную статистику, неподходящий индекс или невыгодный запрос. А сравнивать стоит не только время, но и фактические строки, блоки и стабильность плана на разных значениях параметров.

Что делать дальше

1. Снимите план с EXPLAIN (ANALYZE, BUFFERS) для конкретного запроса и безопасных параметров.
2. Сравните оценку и фактическое число строк, найдите, где возникает фильтрация, сортировка или чтение большого числа блоков.
3. Проверьте определение индекса, выражения запроса, порядок столбцов и актуальность статистики.
4. После изменения повторите замер на репрезентативных данных и проследите за временем запроса и нагрузкой на чтение.

Инвалидация кешей, блокировки и влияние конкурентной нагрузки требуют отдельной диагностики; здесь речь только о выборе плана для запроса.