Индексы для аналитика: почему фильтр по дате быстрый, а по lower(email) нет

Мира · маркетолог магазина настолок «Кубик»

Разработчики говорят, что повесили индекс на дату заказа, а мой отчёт за день всё равно читает всю таблицу. И ещё: поддержка ищет клиента по почте, люди пишут её то с большой буквы, то с маленькой. Как искать?

В прошлом уроке мы научились видеть в плане Seq Scan и переписывать запросы, которые много раз читают одно и то же. Теперь случай, когда запросу правда нужны несколько строк из большой таблицы. Тут помогает индекс, но только если запрос написан так, чтобы база смогла им воспользоваться.

Предметный указатель

В конце толстой книги есть указатель: слова по алфавиту и номера страниц. Чтобы найти «окно», ты не листаешь 900 страниц, а открываешь указатель на букву «О». Индекс в базе устроен так же: значения колонки разложены по порядку, у каждого ссылка на строку.

Но указатель помогает, только если ищешь слово в том виде, в котором оно записано. Если спросить «все слова, у которых третья буква «к»», алфавитный порядок бесполезен, придётся листать весь указатель. С индексом то же: он ускоряет поиск по самой колонке, а не по выражению от неё.

Индексы в учебных базах есть только на первичных ключах. Остальные создаём сами, в песочнице это можно: у каждой песочницы своя копия базы.

CREATE INDEX orders_created_idx ON orders (created_at);

Голая колонка

Отчёт Миры за 1 мая был написан так:

EXPLAIN
SELECT count(*) FROM orders WHERE created_at::date = '2026-05-01';

Aggregate  (cost=147.41..147.42 rows=1 width=8)
  ->  Seq Scan on orders  (cost=0.00..147.32 rows=33 width=0)
        Filter: ((created_at)::date = '2026-05-01'::date)

Индекс по created_at есть, а база читает таблицу целиком. Индекс хранит значения created_at, а в условии стоит created_at::date, другое значение. Чтобы сравнить его с датой, базе надо вычислить его для каждой строки. С date_trunc('month', created_at) = '2026-05-01' и extract(year FROM created_at) = 2026 та же беда.

Лечение: перепиши условие так, чтобы колонка осталась голой, а все вычисления ушли на другую сторону. Такое условие называют sargable:

EXPLAIN
SELECT count(*) FROM orders
WHERE created_at >= '2026-05-01' AND created_at < '2026-05-02';

Aggregate  (cost=8.73..8.74 rows=1 width=8)
  ->  Index Only Scan using orders_created_idx on orders  (cost=0.28..8.68 rows=20 width=0)
        Index Cond: ((created_at >= '2026-05-01 ...') AND (created_at < '2026-05-02 ...'))

Стоимость 8,74 вместо 147. Заказов за день 18, из 6555 база прочитала только их. Правило диапазона: начало включительно, конец следующего периода строго меньше. Так в отчёт попадают все секунды дня и ни одной лишней.

Когда индекс не нужен

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

EXPLAIN SELECT count(*) FROM orders WHERE created_at >= '2025-01-01';

План снова Seq Scan: под условие подходят все 6555 заказов, и прыгать по индексу к каждой строке дороже, чем прочитать таблицу подряд. Это не ошибка, база посчитала правильно. Индекс нужен для узких выборок: день, клиент, один заказ. Подробный список причин, почему индекс не используется, есть в уроке для разработчиков.

Почта в разном регистре

Теперь поиск по почте. Первое желание тоже убрать функцию с колонки: вместо lower(email) = 'anna.tihonova@yandex.ru' написать email = 'anna.tihonova@yandex.ru'. Индекс заработает. Но ответ изменится:

SELECT id, email FROM customers
WHERE lower(email) IN ('anna.tihonova@yandex.ru', 'aleksey.belov@yandex.ru');

id	email
3286	Aleksey.belov@yandex.ru
1111	Anna.tihonova@yandex.ru
2717	aleksey.belov@yandex.ru
718	anna.tihonova@yandex.ru

У одного человека два аккаунта с почтой в разном регистре, таких пар в «Кубике» 19, их мы чистили в боссе блока D. Голое email = … найдёт только один аккаунт из пары. Здесь функция над колонкой нужна по смыслу, и убирать её нельзя.

Выход: индекс не по колонке, а по выражению.

CREATE INDEX customers_lower_email_idx ON customers (lower(email));

Теперь lower(email) = '…' в плане идёт через Bitmap Index Scan on customers_lower_email_idx. Индекс хранит уже вычисленные lower(email), и условие с ним совпадает. Создавать индексы в рабочей базе обычно просят у разработчиков, но аналитику полезно знать, какой индекс просить. Какие индексы есть у таблицы, покажет системное представление:

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'customers';