Обычный индекс работает только тогда, когда база может сравнить значение напрямую с колонкой. Если в условии
WHERE применяется функция к полю, оптимизатор часто не может использовать обычный индекс.Таблица:
users(id, email)
Создадим индекс на email:
CREATE INDEX idx_users_email
ON users(email);
Теперь простой поиск по точному значению использует этот индекс:
SELECT *
FROM users
WHERE email = 'test@mail.com';
Но в реальных проектах часто нужна нормализация данных. Например, искать email без учёта регистра через
LOWER():SELECT *
FROM users
WHERE LOWER(email) = 'test@mail.com';
Проблема в том, что PostgreSQL должен применить
LOWER() к каждой строке, а потом сравнить результат. Обычный индекс по email для такого условия уже не подходит.Решение — создать индекс не на колонку, а на результат выражения:
CREATE INDEX idx_users_lower_email
ON users(LOWER(email));
Теперь база хранит вычисленные значения в индексе и может быстро находить совпадения:
EXPLAIN ANALYZE
SELECT *
FROM users
WHERE LOWER(email) = 'test@mail.com';
Та же техника работает и для других преобразований. Например, если часто ищем заказы по году создания:
CREATE INDEX idx_orders_year
ON orders(EXTRACT(YEAR FROM created_at));
Теперь запросы по этому выражению могут использовать индекс вместо полного прохода таблицы.
Индекс нужно создавать под реальные запросы. Лишние увеличивают время
INSERT/UPDATE и занимают место.🔥 Индексы по выражениям полезны, когда одно и то же вычисление постоянно используется в
WHERE, JOIN или ORDER BY.➡️ SQL Ready | #практика