Поиск lower(email) читает таблицу: индекс на выражение

Поиск пользователя регистронезависим: приложение выполняет lower(email) = lower(:email). На email уже есть B-tree индекс, но выборка не ускорилась. В индексе записаны исходные строки, а запрос сравнивает результаты функции.

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

Как изменить решение

CREATE INDEX users_email_lower_idx ON users (lower(email));
EXPLAIN
SELECT id FROM users
WHERE lower(email) = lower('User@example.org');

Индекс делает доступным поиск по преобразованному значению. Если система требует уникальности именно по этому правилу, индекс можно создать UNIQUE, предварительно разобрав существующие коллизии. Обычный индекс на email такую уникальность не выражает.

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

Что проверить на своих данных

Проверьте пару адресов, отличающихся регистром, адрес с пробелом и точное повторение. Затем сравните план до и после индекса на реалистичном объёме. Запросы с LIKE и ведущим процентом — другая форма поиска: этот пример не превращает B-tree в индекс под произвольную подстроку.

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

Практика дальше

Следующая практика — запросы, где правильность зависит от нескольких источников и граничных случаев. В курсе «Продвинутый SQL» вы решаете 109 задач на JOIN, окна и CTE, сверяете отчёты и собираете витрину интернет-магазина. Запросы выполняются в браузере на SQLite с автоматической проверкой.

Посмотреть программу и практику курса

Источник: Поиск lower(email) читает таблицу — официальная документация.

Другие задачи по теме: PostgreSQL: запись, блокировки и планы запросов.