Что такое функциональный (expression) индекс в PostgreSQL? Приведите пример индекса по lower(email) и объясните, когда он нужен?

Короткий ответ

Функциональный индекс строится не по значению колонки напрямую, а по результату выражения/функции от неё (например, lower(email)); он нужен, когда запросы фильтруют по результату такого выражения, а не по исходному значению — обычный индекс на email в этом случае не используется планировщиком для условия с lower(email).

Как это работает подробнее

PostgreSQL может строить B-tree индекс по любому детерминированному выражению, вычисляя и храня в индексе результат для каждой строки; планировщик использует индекс только при точном совпадении формы выражения в условии WHERE.

Пример кода

example.sqlКопировать

CREATE INDEX idx_users_lower_email ON users (lower(email));
EXPLAIN SELECT * FROM users WHERE lower(email) = lower('Alice@Example.com');
-- Index Scan using idx_users_lower_email вместо Seq Scan по всей таблице

Что использовать на практике

• Практика. Типичный пример — регистронезависимый поиск по email: вместо LOWER(email) = LOWER(:input) с полным сканированием строят CREATE INDEX ON users (lower(email)) и фильтруют по WHERE lower(email) = lower(:input); альтернатива — тип citext.

Подводные камни

• Ошибка. Создают обычный индекс на email, а в запросах используют LOWER(email), из-за чего индекс не применяется; рассинхронизируют выражение в запросе и в индексе (например, индекс по lower(trim(email)), а запрос фильтрует только по lower(email)).