Почему PostgreSQL может не использовать индекс
Давно не писала про БД, сегодня захотелось рассмотреть случаи, когда индекс в БД есть, но для конкретных запросов почему-то не работает.
🌟 В запросе используется функция над индексируемым полем
Допустим, у нас есть таблица пользователей users:
id
name
email
и обычный B-tree индекс по полю email.
Пользователь может указать email при регистрации в любом регистре:
[email protected] или [email protected].
❎Если при поиске в запросе используется функция приведения к нижнему регистру для самой колонки email:
SELECT id, email, name
FROM users
WHERE lower(email) = '[email protected]';
То обычный индекс по email не сработает, потому что у БД построена отсортированная структура для email, а не для lower(email). Вызов lower(email) приведет к сканированию всей таблицы с приведением каждого адреса к нижнему регистру.
Что делать:
✔️ при записи сохранять email сразу в нижнем регистре:
WHERE email = '[email protected]';
✔️ если нужно хранить email в исходном виде, можно создать индекс по функции:
CREATE INDEX idx_users_email_lower ON users (lower(email));
Тогда запрос с lower(email) сможет использовать этот индекс.
✔️ Отмечу, что функция не над полем, а над параметром — не проблема для использования индекса. То есть если в БД хранятся email уже в нижнем регистре, а сам запрос такой:
WHERE email = lower('[email protected]')
— это ок для использования индекса.
🌟 В запросе используется поиск по середине строки LIKE '%...'
✔️ Если поиск идет по началу строки:
SELECT id, email, name
FROM users
WHERE name LIKE 'Иван%';
То индекс по name может быть использован (особенно если учесть нюансы, связанные с локалью).
❎Но если поиск идет по любой части слова:
WHERE name LIKE '%ван%';
❎Или поиск регистронезависимый:
WHERE name ILIKE 'иван%';
то стандартный B-tree индекс по name не подойдет.
Можно рассмотреть GIN или GiST индексы с расширением pg_trgm
🌟В запросе используется преобразование типов у индексируемого поля
Например, есть таблица с данными платежей payments с полями:
id bigint
description text
payment_type text
created_date timestamp
Пользователь хочет найти платежи за 30.06.2026
❎Если запрос содержит приведение типа не для значения, а для самого поля created_date, вот так:
SELECT id, description, created_date
FROM payments
WHERE created_date::date = DATE '2026-06-30';
То индекс по полю created_date не сработает, потому что условие написано не по самой колонке created_date, а по выражению created_date::date
✔️ Конечно, это немного утрированный пример, фильтрацию по датам обычно пишут диапазонами, так тут и стоило сделать. А дату с фронта привести к timestamp в коде бэка.
SELECT id, description, created_date
FROM payments
WHERE created_date >= timestamp '2026-06-30 00:00:00'
AND created_date < timestamp '2026-07-01 00:00:00';
🌟В запросе не используется первое поле составного индекса
Например, в таблице payments составной индекс на поля (payment_type, created_date).
✔️ Для запроса с поиском по первому полю (payment_type) или обоим полям (payment_type, created_date), индекс сработает:
SELECT id, description, payment_type, created_date
FROM payments
WHERE payment_type = 'СБП'
AND created_date >= timestamp '2026-06-01 00:00:00'
AND created_date < timestamp '2026-07-01 00:00:00';
❎ А для запроса с поиском только по второму полю индекса (created_date) составной индекс либо не будет использован либо будет неэффективен:
SELECT id, description, payment_type, created_date
FROM payments
WHERE created_date >= '2026-06-01 00:00:00'
AND created_date < '2026-07-01 00:00:00';
В Postgres 18+ при поиске по второму полю составного индекса может быть использован механизм оптимизации skip scan, но это сильно зависит от количества различных значений в первом поле.
Поэтому если часто ищут отдельно по created_date, стоит рассмотреть отдельный индекс для него.
Продолжение ⬇️
