В Brew была кнопка «войти по e-mail», и был индекс по столбцу email. Логично же — ищем по почте, индекс на месте. Но логин нечувствителен к регистру: пользователь, зарегистрированный как Alice@Brew.example, входит, набирая alice@brew.example. Чтобы это совпало, бэкенд писал WHERE lower(email) = lower($1). И запрос внезапно поехал по Seq Scan, хотя индекс по email стоял прямо тут. Никто его не удалял — Postgres просто не мог им воспользоваться: индекс хранит email как есть, а в условии стоит lower(email) — это другое значение, которого в индексе нет.

Цель юнита — понять класс условий, которые «выключают» индекс (их называют non-sargable — non Search ARGument ABLE), и главный приём починки: индекс по выражению. Это объясняет частую загадку «индекс есть, а EXPLAIN показывает Seq Scan».

Почему функция поверх столбца выключает индекс

Обычный B-tree-индекс по email — это отсортированный справочник значений столбца email. Он умеет отвечать на вопросы про сам email: «равно», «больше», «начинается с». Но lower(email) — это уже результат функции, а не значение столбца; в индексе таких значений нет. Чтобы проверить lower(email) = 'alice@...', Postgres обязан взять каждую строку, посчитать lower(email) и сравнить — а это и есть полный проход Seq Scan.

Тот же капкан срабатывает во всех условиях, где столбец завёрнут в вычисление:

• WHERE lower(email) = ..., WHERE date(created_at) = ... — функция поверх столбца.
• WHERE price + 100 > 500 — арифметика над столбцом (надо писать price > 400).
• WHERE email LIKE '%@brew.example' — ведущий %: индекс сортирует по началу строки, а у нас неизвестно начало.

Правило простое: оставь столбец «голым» по одну сторону сравнения, перенеси всю математику на сторону константы — тогда условие снова sargable, и индекс включается.

Индекс по выражению

Но lower(email) для регистронезависимого поиска нужен по-настоящему — «голым» его не сделать. Тогда индексируют само выражение:

CREATE INDEX accounts_lab_lower_email_idx ON accounts_lab (lower(email));

Теперь в индексе лежат уже посчитанные значения lower(email), отсортированные. Запрос WHERE lower(email) = 'alice@...' совпадает с выражением индекса дословно — и план берёт Index Scan по нему. Важная деталь: выражение в запросе должно совпасть с выражением в индексе буква в букву (lower(email), а не lower(email || '')), иначе планировщик их не свяжет.

⚠️ Для регистронезависимого равенства есть и альтернатива — тип citext (case-insensitive text): сравнения по нему сразу нечувствительны к регистру, и обычный индекс работает. Но citext — это расширение и решение на уровне схемы; индекс по lower(email) ничего не меняет в типах и потому проще встроить в существующую таблицу.

Карта non-sargable условий

Капкан всегда один: столбец завёрнут в вычисление, и индекс по «голому» столбцу к нему не подходит. Лечение — либо развернуть условие обратно, либо проиндексировать само выражение:

Что показывает наш код

demo.sql строит лабораторный стол accounts_lab (200 000 e-mail в смешанном регистре) с обычным индексом по email и объясняет три запроса:

-- Q1: голый столбец → обычный индекс работает (Index Scan)
SELECT * FROM accounts_lab WHERE email = 'User150000@Brew.example';
-- Q2: lower(email) с тем же индексом → Seq Scan, Rows Removed by Filter: 199999
SELECT * FROM accounts_lab WHERE lower(email) = 'user150000@brew.example';
 
CREATE INDEX accounts_lab_lower_email_idx ON accounts_lab (lower(email));
 
-- Q3: тот же запрос → Index Scan по индексу-выражению
SELECT * FROM accounts_lab WHERE lower(email) = 'user150000@brew.example';

Q1 и Q3 одинаково быстры (Index Scan), хотя ищут по-разному; между ними — Q2 с тем же условием, что и Q3, но без подходящего индекса: Seq Scan и 199 999 выброшенных строк.

Запуск

docker compose up -d
make lecture L=06-indexing-and-explain/06-03-when-indexes-dont-help

Вывод:

== Q1) точное равенство email = ... — обычный индекс работает (Index Scan) ==
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Index Scan using accounts_lab_email_idx on accounts_lab (actual rows=1.00 loops=1)
   Index Cond: (email = 'User150000@Brew.example'::text)
   Index Searches: 1
 
 
== Q2) lower(email) = ... с тем же индексом — Seq Scan (условие non-sargable) ==
                         QUERY PLAN                         
------------------------------------------------------------
 Seq Scan on accounts_lab (actual rows=1.00 loops=1)
   Filter: (lower(email) = 'user150000@brew.example'::text)
   Rows Removed by Filter: 199999
 
 
== создаём индекс по ВЫРАЖЕНИЮ lower(email) ==
 
== Q3) тот же lower(email) = ... — теперь Index Scan по индексу-выражению ==
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Index Scan using accounts_lab_lower_email_idx on accounts_lab (actual rows=1.00 loops=1)
   Index Cond: (lower(email) = 'user150000@brew.example'::text)
   Index Searches: 1

Q1 (email = ..., голый столбец) идёт Index Scan'ом по обычному индексу. Q2 — то же lower(email) = ..., что и Q3, но индекса под выражение ещё нет: Seq Scan, Rows Removed by Filter: 199999 (прочитали всё, чтобы найти одного). После CREATE INDEX ... (lower(email)) запрос Q3 совпал с выражением индекса дословно и пошёл Index Scan'ом. Индекс был — но «не тот».

Заборчик

Что мы упростили:

• Чинили один запрос. В проде сначала смотрят, какие запросы вообще горячие (pg_stat_statements), и индексируют под них, а не «на всякий случай»: индекс по выражению, как и любой, замедляет запись и занимает место.
• Функция в индексе обязана быть IMMUTABLE (одинаковый результат на одинаковом входе). lower() такая; а now() или зависящие от часового пояса выражения индексировать нельзя (про волатильность функций — в 09-05).
• Ведущий % в LIKE голым столбцом не лечится. Нужны отдельные механизмы: text_pattern_ops для префиксного поиска или триграммы pg_trgm для поиска по подстроке — это уже модуль 07.
• Выбор lower() vs citext, ревизия «спящих» и раздутых индексов — это сопровождение схемы, которое в большой системе ведёт твой DBA.

Твоя задача — узнавать non-sargable условие в своём запросе (столбец завёрнут в функцию/арифметику) и либо разворачивать его, либо ставить индекс по выражению.

Что забрать с собой

• Обычный индекс по столбцу не помогает, если в условии столбец завёрнут в функцию или арифметику (lower(col), col + 1) — условие становится non-sargable, план срывается в Seq Scan.
• Лечение по умолчанию: оставь столбец голым, перенеси вычисление на сторону константы.
• Если функция нужна по-настоящему (регистронезависимый поиск) — индекс по выражению CREATE INDEX ... (lower(email)); запрос обязан совпасть с выражением дословно.
• Функция в индексе по выражению должна быть IMMUTABLE.
• Ведущий % в LIKE голым столбцом не лечится — нужны text_pattern_ops/pg_trgm (модуль 07).

Дальше — 06-04 «Частичные, покрывающие и уникальные индексы»: индекс только по части строк (WHERE), INCLUDE-столбцы и Index-Only Scan, который вообще не ходит в таблицу.