Почему запрос с LIKE '%…' или функцией от столбца в WHERE не использует индекс?
Проверила команда Сопровод · Обновлено
Короткий ответ
B-tree хранит значения столбца отсортированными и находит строки по началу значения. У LIKE '%ова' начало неизвестно, а extract(year FROM birth_date) — другое значение, которого в индексе нет, поэтому планировщик читает таблицу целиком. Помогают условие по самому столбцу, индекс по выражению, класс text_pattern_ops для префикса или триграммы pg_trgm.
Почему этот вопрос задают
Именно такие запросы чаще всего тормозят в рабочей базе, хотя индекс на столбце вроде бы есть. Интервьюер хочет услышать причину из устройства B-tree и исправление под каждый случай.
Как рассуждать
- Начните с устройства B-tree: значения отсортированы, поиск идет от начала значения
- Объясните, почему
'%ова'и функция от столбца не дают точки входа в индекс - Покажите план:
Seq ScanсFilterвместоIndex Cond - Предложите исправление под каждый случай: диапазон, индекс по выражению,
pg_trgm
Ответ с примером
B-tree упорядочивает значения столбца, как словарь. По LIKE 'Ив%' база находит место, где начинаются фамилии на «Ив», и читает только этот участок. Шаблон '%ова' задает лишь конец фамилии, начало может быть любым, точки входа нет, и планировщик выбирает Seq Scan. С функцией та же история: индекс построен по birth_date, условие проверяет extract(year FROM birth_date), а таких значений в индексе нет.
Регистратура поликлиники ищет пациентов в таблице на 20 000 строк. Если база работает не в локали C, обычный индекс для LIKE не годится, поэтому индекс по фамилии построен с классом text_pattern_ops. Год рождения перепишите диапазоном birth_date >= '1985-01-01' AND birth_date < '1986-01-01', и запрос возьмет индекс. Для lower(last_name) создайте индекс по выражению, для поиска по середине фамилии — GIN-индекс с gin_trgm_ops.
EXPLAIN (COSTS OFF) SELECT id FROM patients WHERE last_name LIKE 'Ив%';
EXPLAIN (COSTS OFF) SELECT id FROM patients WHERE last_name LIKE '%ова';
EXPLAIN (COSTS OFF)
SELECT id FROM patients WHERE extract(year FROM birth_date) = 1985; QUERY PLAN
----------------------------------------------------------------------------------
Bitmap Heap Scan on patients
Filter: (last_name ~~ 'Ив%'::text)
-> Bitmap Index Scan on patients_last_name_idx
Index Cond: ((last_name ~>=~ 'Ив'::text) AND (last_name ~<~ 'Иг'::text))
(4 rows)
QUERY PLAN
---------------------------------------
Seq Scan on patients
Filter: (last_name ~~ '%ова'::text)
(2 rows)
QUERY PLAN
-------------------------------------------------------------
Seq Scan on patients
Filter: (EXTRACT(year FROM birth_date) = '1985'::numeric)
(2 rows)Как ответить сильнее
Что добавит опытный кандидат
Что спросят следом
Доступно по подписке
Приемы опытных и готовые ответы
Частые ошибки
«Индекс не используется, потому что PostgreSQL его не видит, надо пересоздать»
Откройте план
EXPLAINи посмотрите на условие: если тамFilterс функцией или шаблон с процентом в начале, пересоздание индекса ничего не даст«Для поиска по середине строки хватит обычного B-tree»
Скажите, что B-tree ищет только по началу значения. Для
'%ова%'нужен GIN-индекс на триграммах из расширенияpg_trgm«
extract(year FROM birth_date) = 1985возьмет индекс поbirth_date»Перепишите условие диапазоном по самому столбцу:
birth_date >= '1985-01-01' AND birth_date < '1986-01-01'. Тогда в плане появитсяIndex Cond«
LIKEс шаблоном'Ив%'всегда идет по индексу»Уточните: если база работает не в локали C, обычный индекс не подойдет, нужен индекс с классом
text_pattern_ops
Сигналы для интервьюера
Сильный ответ
Объясняет поведение через устройство B-tree, читает план с Seq Scan и под каждый случай предлагает свое исправление
Тревожный сигнал
Знает правило про процент в начале шаблона, но не может его объяснить и не видит проблемы с функцией от столбца
Красный флаг
Предлагает создать еще один обычный индекс на тот же столбец и считает, что индекс работает для любого условия
Как еще могут спросить
- Почему индекс не работает, если в
WHEREстоит функция от столбца? - Почему поиск
LIKEс процентом в начале идет полным сканированием таблицы? - Как сделать быстрым поиск по части фамилии в PostgreSQL?
- В каких случаях PostgreSQL не использует существующий индекс?











