Индекс по выражению в PostgreSQL: lower(email), date_trunc и другие случаи

Одна из частых причин, почему PostgreSQL не использует индекс: в запросе колонка обернута в функцию.

Например, в таблице есть обычный индекс по email:

CREATE INDEX users_email_idx ON users (email);

Но запрос всё равно читает много строк:

SELECT *
FROM users
WHERE lower(email) = lower('Ivan.Petrov@example.com');

На первый взгляд кажется странным: ведь индекс по email есть. Но для PostgreSQL выражение lower(email) — это уже не просто колонка email. Это результат вычисления функции. Обычный B-tree индекс по email не хранит заранее рассчитанные значения lower(email), поэтому такой индекс обычно не подходит для быстрого поиска по этому условию.

Для таких случаев в PostgreSQL есть индекс по выражению — expression index, его также часто называют functional index.

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

Индекс по выражению — это индекс не по самой колонке, а по результату выражения над колонкой или несколькими колонками.

Простой пример:

CREATE INDEX users_email_lower_idx
ON users (lower(email));

Теперь PostgreSQL может использовать этот индекс для запроса:

SELECT *
FROM users
WHERE lower(email) = lower('Ivan.Petrov@example.com');

Индекс хранит не исходные значения email, а результат lower(email). Поэтому поиск по приведенному к нижнему регистру email становится индексируемым.

Упрощенно это можно представить так:

Когда запрос ищет lower(email) = 'ivan.petrov@example.com', PostgreSQL уже не обязан вычислять lower(email) для каждой строки таблицы. Он может искать по заранее построенному индексу.

Главный принцип: выражение в запросе должно совпадать с выражением в индексе

Это самое важное правило.

Если индекс создан так:

CREATE INDEX users_email_lower_idx
ON users (lower(email));

то запрос должен использовать то же выражение:

SELECT *
FROM users
WHERE lower(email) = 'ivan.petrov@example.com';

или так:

SELECT *
FROM users
WHERE lower(email) = lower($1);

А вот если в запросе появилось другое выражение, индекс может не подойти:

SELECT *
FROM users
WHERE trim(lower(email)) = 'ivan.petrov@example.com';

Для такого запроса нужен другой индекс:

CREATE INDEX users_email_trim_lower_idx
ON users (trim(lower(email)));

PostgreSQL не обязан догадываться, что в вашем приложении email всегда сохранен без пробелов. Планировщик работает с SQL-выражениями, статистикой и доступными индексами.

Пример 1. Поиск email без учета регистра

Самый популярный пример expression index — поиск email без учета регистра.

Допустим, есть таблица пользователей:

CREATE TABLE users (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       text NOT NULL,
    full_name   text,
    created_at  timestamptz NOT NULL DEFAULT now()
);

Обычный индекс:

CREATE INDEX users_email_idx
ON users (email);

будет полезен для запроса:

SELECT *
FROM users
WHERE email = 'Ivan.Petrov@example.com';

Но для case-insensitive поиска нужен другой подход:

SELECT *
FROM users
WHERE lower(email) = lower('Ivan.Petrov@example.com');

Создаем индекс по выражению:

CREATE INDEX CONCURRENTLY users_email_lower_idx
ON users (lower(email));

Теперь запрос можно проверить через EXPLAIN:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM users
WHERE lower(email) = lower('Ivan.Petrov@example.com');

В хорошем случае вы увидите в плане Index Scan или Bitmap Index Scan по индексу users_email_lower_idx.

Примерно так:

Index Scan using users_email_lower_idx on users
  Index Cond: (lower(email) = 'ivan.petrov@example.com'::text)

Уникальность email без учета регистра

Иногда нужно не только быстро искать email, но и запретить дубли с разным регистром:

Ivan.Petrov@example.com
ivan.petrov@example.com
IVAN.PETROV@example.com

Для этого можно создать уникальный индекс по выражению:

CREATE UNIQUE INDEX CONCURRENTLY users_email_lower_uq
ON users (lower(email));

Теперь PostgreSQL не позволит вставить два email, которые отличаются только регистром.

Если в таблице разрешены NULL-значения и индекс нужен только для заполненных email, можно сделать частичный уникальный индекс:

CREATE UNIQUE INDEX CONCURRENTLY users_email_lower_uq
ON users (lower(email))
WHERE email IS NOT NULL;

Такой индекс будет меньше, потому что в него попадут только строки, где email IS NOT NULL.

А может лучше citext?

В PostgreSQL есть расширение citext — case-insensitive text. Оно позволяет сделать колонку нечувствительной к регистру на уровне типа данных.

Пример:

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE users (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email  citext NOT NULL UNIQUE
);

После этого запрос:

SELECT *
FROM users
WHERE email = 'Ivan.Petrov@example.com';

будет работать без явного lower(email).

Что выбрать:

• если проект новый и email по бизнес-логике всегда должен сравниваться без учета регистра, можно рассмотреть citext;
• если схема уже существует, приложение уже работает с text, а нужно точечно ускорить конкретные запросы — индекс по lower(email) часто проще и безопаснее;
• если важны тонкости Unicode, collation и правила сравнения строк, решение лучше проверить отдельно на ваших данных.

Пример 2. Поиск по префиксу: lower(email) LIKE 'ivan%'

Индекс по lower(email) хорошо подходит для равенства:

WHERE lower(email) = 'ivan.petrov@example.com'

Но с LIKE есть нюансы.

Запрос по префиксу:

SELECT *
FROM users
WHERE lower(email) LIKE 'ivan%';

не всегда эффективно использует обычный B-tree индекс, особенно если база создана не с C locale. Для таких случаев можно использовать operator class text_pattern_ops:

CREATE INDEX CONCURRENTLY users_email_lower_pattern_idx
ON users (lower(email) text_pattern_ops);

Такой индекс рассчитан на запросы вида:

SELECT *
FROM users
WHERE lower(email) LIKE 'ivan%';

Но он не спасет запрос с поиском по середине строки:

SELECT *
FROM users
WHERE lower(email) LIKE '%petrov%';

Для поиска по подстроке чаще используют расширение pg_trgm и GIN-индекс:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX CONCURRENTLY users_email_lower_trgm_idx
ON users USING gin (lower(email) gin_trgm_ops);

После этого запросы такого вида могут стать значительно быстрее:

SELECT *
FROM users
WHERE lower(email) LIKE '%petrov%';

Важно: не стоит создавать сразу все варианты индексов. Сначала нужно понять реальные запросы приложения: точное равенство, поиск по префиксу или поиск по подстроке.

Пример 3. Индекс по date_trunc

Второй популярный случай — группировка или фильтрация по дню, месяцу или часу.

Например, есть таблица событий:

CREATE TABLE events (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id     bigint NOT NULL,
    event_type  text NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

Разработчик пишет запрос:

SELECT *
FROM events
WHERE date_trunc('day', created_at) = date_trunc('day', now());

На большой таблице такой запрос может быть тяжелым. PostgreSQL должен применить date_trunc к значениям created_at, а обычный индекс по created_at может оказаться бесполезным для такого условия.

Для фильтрации по датам чаще лучше использовать диапазон

Если задача — найти строки за конкретный день, обычно лучше не индекс по date_trunc, а обычный индекс по исходной колонке:

CREATE INDEX CONCURRENTLY events_created_at_idx
ON events (created_at);

А запрос писать через полуоткрытый интервал:

SELECT *
FROM events
WHERE created_at >= timestamptz '2026-07-01 00:00:00+00'
  AND created_at <  timestamptz '2026-07-02 00:00:00+00';

Такой запрос хорошо ложится на B-tree индекс по created_at.

Это один из самых частых практических советов: если вы фильтруете по периоду времени, не оборачивайте колонку в функцию, а задавайте диапазон по исходному значению.

Плохо:

WHERE date_trunc('day', created_at) = timestamp '2026-07-01 00:00:00'

Хорошо:

WHERE created_at >= timestamptz '2026-07-01 00:00:00'
  AND created_at <  timestamptz '2026-07-02 00:00:00'

Когда индекс по date_trunc всё-таки полезен

Индекс по date_trunc может быть полезен, если вы действительно часто работаете именно с временными бакетами:

• строите отчеты по дням, часам или месяцам;
• соединяете таблицы по временному бакету;
• часто фильтруете именно по выражению date_trunc('day', created_at);
• используете одинаковое выражение в большом количестве запросов;
• хотите ускорить выборку конкретного бакета, а не произвольного диапазона времени.

Пример для колонки timestamp without time zone:

CREATE INDEX CONCURRENTLY events_created_day_idx
ON events (date_trunc('day', created_at));

Запрос должен использовать то же выражение:

SELECT *
FROM events
WHERE date_trunc('day', created_at) = timestamp '2026-07-01 00:00:00';

Важный нюанс: date_trunc и timestamptz

С timestamp with time zone нужно быть осторожнее.

День, месяц или час для timestamptz зависят от часового пояса. Один и тот же момент времени может относиться к разным календарным дням в UTC, Москве, Амстердаме или Нью-Йорке.

Поэтому выражение вида:

CREATE INDEX events_created_day_idx
ON events (date_trunc('day', created_at));

для created_at timestamptz может привести к ошибке:

ERROR: functions in index expression must be marked IMMUTABLE

PostgreSQL требует, чтобы функции и операторы в определении индекса были immutable, то есть давали один и тот же результат при одних и тех же аргументах. Если результат зависит от настройки сессии, например от TimeZone, такое выражение не подходит для обычного expression index.

В PostgreSQL 16 появилась возможность использовать вариант date_trunc с явно указанным часовым поясом:

date_trunc('day', created_at, 'UTC')

Пример индекса:

CREATE INDEX CONCURRENTLY events_created_day_utc_idx
ON events (date_trunc('day', created_at, 'UTC'));

И запрос:

SELECT *
FROM events
WHERE date_trunc('day', created_at, 'UTC') = timestamptz '2026-07-01 00:00:00+00';

Если вы используете старую версию PostgreSQL или не уверены в поведении функции, проверьте выражение на тестовой базе. Если PostgreSQL не дает создать индекс из-за volatility, лучше не пытаться обходить это через сомнительные wrapper-функции с IMMUTABLE, если результат на самом деле зависит от внешних настроек.

Как проверить volatility функции

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

SELECT
    oid::regprocedure AS function_signature,
    CASE provolatile
        WHEN 'i' THEN 'immutable'
        WHEN 's' THEN 'stable'
        WHEN 'v' THEN 'volatile'
    END AS volatility
FROM pg_proc
WHERE proname IN ('lower', 'date_trunc')
ORDER BY oid::regprocedure::text;

Если функция stable или volatile, PostgreSQL не позволит использовать ее в выражении индекса.

Это важный защитный механизм. Индекс должен хранить значения, которые не меняются сами по себе от настроек сессии, текущего времени, локали или других внешних факторов.

Пример 4. Составной индекс с выражением

Expression index можно комбинировать с обычными колонками.

Например, в SaaS-приложении пользователи разделены по организациям:

CREATE TABLE users (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id   bigint NOT NULL,
    email       text NOT NULL,
    deleted_at  timestamptz
);

Типовой запрос:

SELECT *
FROM users
WHERE tenant_id = 42
  AND deleted_at IS NULL
  AND lower(email) = lower('admin@example.com');

Хороший индекс может выглядеть так:

CREATE INDEX CONCURRENTLY users_tenant_email_lower_active_idx
ON users (tenant_id, lower(email))
WHERE deleted_at IS NULL;

Здесь сразу несколько идей:

1. tenant_id стоит первым, потому что почти все запросы ищут пользователя внутри конкретной организации.
2. lower(email) нужен для поиска без учета регистра.
3. WHERE deleted_at IS NULL делает индекс частичным и исключает удаленные записи.

Запрос обязательно должен содержать условие, совместимое с предикатом частичного индекса:

SELECT *
FROM users
WHERE tenant_id = 42
  AND deleted_at IS NULL
  AND lower(email) = 'admin@example.com';

Если убрать deleted_at IS NULL, PostgreSQL может не использовать этот индекс, потому что индекс содержит только часть строк таблицы.

Пример 5. Индекс по JSONB-выражению

Еще один частый случай — поиск по полю внутри JSONB.

Допустим, есть таблица заказов:

CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payload     jsonb NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

В payload лежит внешний идентификатор:

{
  "external_id": "CRM-100045",
  "customer_id": 123,
  "source": "crm"
}

Запрос:

SELECT *
FROM orders
WHERE payload->>'external_id' = 'CRM-100045';

Для него можно создать индекс по выражению:

CREATE INDEX CONCURRENTLY orders_external_id_idx
ON orders ((payload->>'external_id'));

Обратите внимание на двойные скобки. Если индекс строится не по простой функции, а по произвольному выражению, его обычно заключают в дополнительные скобки.

Если поле числовое и вы хотите искать его как число, тип в индексе и запросе должен совпадать:

CREATE INDEX CONCURRENTLY orders_customer_id_idx
ON orders (((payload->>'customer_id')::bigint));

Запрос:

SELECT *
FROM orders
WHERE (payload->>'customer_id')::bigint = 123;

Но с приведением типов нужно быть осторожнее. Если в JSONB попадется значение, которое нельзя привести к bigint, запрос или построение индекса могут завершиться ошибкой.

Пример 6. Индекс по нормализованному значению

Иногда в базе хранятся значения в разном формате, а искать нужно по нормализованному виду.

Например, телефон:

+7 (999) 123-45-67
79991234567
8 999 123 45 67

Технически можно создать индекс по выражению, которое очищает номер от лишних символов:

CREATE INDEX CONCURRENTLY users_phone_digits_idx
ON users (regexp_replace(phone, '\D', '', 'g'));

И искать так:

SELECT *
FROM users
WHERE regexp_replace(phone, '\D', '', 'g') = '79991234567';

Но здесь нужно задать себе вопрос: не лучше ли хранить нормализованный телефон в отдельной колонке?

Если нормализация сложная, используется во многих местах и важна для бизнес-логики, отдельная колонка часто понятнее:

ALTER TABLE users
ADD COLUMN phone_digits text;

CREATE INDEX CONCURRENTLY users_phone_digits_idx
ON users (phone_digits);

Expression index хорош, когда выражение простое, стабильное и локально решает задачу поиска. Если выражение превращается в бизнес-правило, его часто лучше вынести в данные приложения или отдельную колонку.

Почему PostgreSQL всё равно может не использовать expression index

Сам факт наличия индекса не гарантирует, что PostgreSQL выберет его в плане.

Причины могут быть разными.

1. Запрос возвращает слишком много строк

Если условие не селективное, PostgreSQL может решить, что последовательное чтение таблицы дешевле.

Например:

SELECT *
FROM users
WHERE lower(email) LIKE 'a%';

Если таких строк много, индекс может не дать выигрыша.

2. Выражение в запросе отличается от выражения в индексе

Индекс:

CREATE INDEX users_email_lower_idx
ON users (lower(email));

Запрос:

SELECT *
FROM users
WHERE lower(trim(email)) = 'admin@example.com';

Для человека это может выглядеть почти одинаково. Для планировщика это разные выражения.

3. Частичный индекс не подходит под WHERE

Индекс:

CREATE INDEX users_active_email_lower_idx
ON users (lower(email))
WHERE deleted_at IS NULL;

Запрос:

SELECT *
FROM users
WHERE lower(email) = 'admin@example.com';

PostgreSQL не может использовать индекс, если из условия запроса не следует, что нужны только строки deleted_at IS NULL.

4. Используется неподходящий оператор

B-tree индекс по lower(email) хорошо подходит для равенства:

WHERE lower(email) = 'admin@example.com'

Но не для поиска по произвольной подстроке:

WHERE lower(email) LIKE '%admin%'

Для такого сценария лучше смотреть в сторону pg_trgm.

5. Устаревшая статистика

После массовых изменений данных статистика может плохо отражать реальное распределение значений.

Можно обновить статистику:

ANALYZE users;

Для диагностики всегда смотрите фактический план:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM users
WHERE lower(email) = 'admin@example.com';

Как найти expression indexes в базе

Посмотреть индексы по выражениям можно через pg_indexes:

SELECT
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE indexdef ILIKE '%lower(%'
   OR indexdef ILIKE '%date_trunc(%'
ORDER BY schemaname, tablename, indexname;

Более общий вариант — посмотреть все индексы конкретной таблицы:

SELECT
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename = 'users'
ORDER BY indexname;

Размер индексов:

SELECT
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
  AND relname = 'users'
ORDER BY pg_relation_size(indexrelid) DESC;

Если индекс большой, но idx_scan = 0 долгое время, это повод проверить, нужен ли он вообще. Но удалять индекс только по одному счетчику нельзя: возможно, он нужен для редкого, но критичного запроса или для ограничения уникальности.

Как безопасно создавать такие индексы в production

На больших таблицах не стоит бездумно запускать:

CREATE INDEX users_email_lower_idx
ON users (lower(email));

Для production чаще используют CONCURRENTLY:

CREATE INDEX CONCURRENTLY users_email_lower_idx
ON users (lower(email));

Такой вариант дольше выполняется, но меньше мешает параллельным операциям чтения и записи.

Для уникального индекса:

CREATE UNIQUE INDEX CONCURRENTLY users_email_lower_uq
ON users (lower(email));

Перед созданием уникального индекса нужно проверить дубли:

SELECT
    lower(email) AS normalized_email,
    count(*) AS rows_count,
    array_agg(id ORDER BY id) AS user_ids
FROM users
WHERE email IS NOT NULL
GROUP BY lower(email)
HAVING count(*) > 1
ORDER BY rows_count DESC;

Если дубли есть, CREATE UNIQUE INDEX завершится ошибкой.

Также полезно выставлять lock_timeout, чтобы DDL не завис в ожидании блокировки:

SET lock_timeout = '5s';

CREATE INDEX CONCURRENTLY users_email_lower_idx
ON users (lower(email));

Важно: CREATE INDEX CONCURRENTLY нельзя выполнять внутри обычного transaction block. Если ваш SQL-клиент автоматически оборачивает скрипты в транзакцию, это нужно отключить для такого DDL.

Частые ошибки

Ошибка 1. Создали индекс по колонке, а фильтруют по функции

CREATE INDEX users_email_idx
ON users (email);

SELECT *
FROM users
WHERE lower(email) = 'admin@example.com';

Нужен индекс по lower(email).

Ошибка 2. Создали expression index, но запрос написан иначе

CREATE INDEX users_email_lower_idx
ON users (lower(email));

SELECT *
FROM users
WHERE lower(trim(email)) = 'admin@example.com';

Нужно либо изменить запрос, либо создать индекс по фактически используемому выражению.

Ошибка 3. Используют date_trunc для фильтрации по дню

SELECT *
FROM events
WHERE date_trunc('day', created_at) = timestamp '2026-07-01 00:00:00';

Чаще лучше так:

SELECT *
FROM events
WHERE created_at >= timestamp '2026-07-01 00:00:00'
  AND created_at <  timestamp '2026-07-02 00:00:00';

Ошибка 4. Индексируют всё подряд

Expression index — не бесплатный. Он занимает место, замедляет вставки и обновления, требует обслуживания и может увеличивать нагрузку на autovacuum.

Перед созданием индекса стоит ответить на вопросы:

• какой конкретный запрос он ускоряет;
• как часто этот запрос выполняется;
• сколько строк он возвращает;
• есть ли уже похожий индекс;
• можно ли переписать запрос так, чтобы использовать существующий индекс;
• не лучше ли обычный индекс по исходной колонке.

Ошибка 5. Делают wrapper-функцию IMMUTABLE, хотя она не immutable

Иногда встречается соблазн создать свою функцию, пометить ее как IMMUTABLE и использовать в индексе.

Так делать опасно, если результат функции реально зависит от часового пояса, текущего времени, настроек сессии, таблиц-справочников или внешней среды.

PostgreSQL доверяет объявленной volatility. Если функция помечена неверно, индекс может начать возвращать логически неправильные результаты.

Короткая памятка

Используйте индекс по выражению, если:

• в WHERE, JOIN или ORDER BY регулярно используется выражение над колонкой;
• выражение простое и immutable;
• обычный индекс по колонке не помогает;
• запрос достаточно селективный;
• вы проверили эффект через EXPLAIN (ANALYZE, BUFFERS).

Не используйте expression index автоматически, если:

• можно переписать запрос через диапазон по исходной колонке;
• выражение сложное и фактически является бизнес-логикой;
• функция зависит от внешних настроек;
• запрос возвращает большую часть таблицы;
• индекс дублирует уже существующие индексы;
• проблема на самом деле не в индексе, а в плохой статистике, неверном join order или устаревшей архитектуре запроса.

Как pgtools помогает в таких задачах

При работе с expression indexes важно быстро переключаться между несколькими вещами:

• SQL-запросом;
• фактическим планом выполнения;
• DDL таблицы и индексов;
• диагностическими запросами к pg_indexes, pg_stat_user_indexes, pg_proc;
• проверкой блокировок при создании индекса;
• сравнением поведения запроса до и после изменения.

В pgtools удобно держать рядом SQL-редактор, результаты запросов и диагностические скрипты. Можно быстро проверить, какой индекс уже есть на таблице, выполнить EXPLAIN (ANALYZE, BUFFERS), посмотреть фактический план и убедиться, что PostgreSQL действительно использует новый индекс.

Для production-сценариев особенно полезно заранее готовить DDL аккуратно:

SET lock_timeout = '5s';

CREATE INDEX CONCURRENTLY users_email_lower_idx
ON users (lower(email));

А затем проверять результат:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM users
WHERE lower(email) = 'admin@example.com';

Вывод

Индекс по выражению в PostgreSQL — мощный инструмент, но он должен применяться точечно.

Он хорошо подходит для случаев вроде:

lower(email)

date_trunc('day', created_at)

payload->>'external_id'

tenant_id, lower(email)

Но главный вопрос всегда один: какое выражение реально используется в запросе?

Если запрос фильтрует по lower(email), обычный индекс по email не решит проблему. Если запрос фильтрует события за день, часто лучше использовать диапазон по created_at, а не индексировать date_trunc. Если поиск идет по JSONB-полю, можно индексировать конкретное выражение, но нужно следить за типами и качеством данных.

Хороший индекс начинается не с команды CREATE INDEX, а с анализа запроса:

EXPLAIN (ANALYZE)

Сначала посмотрите, что PostgreSQL делает сейчас. Потом создайте минимально нужный индекс. После этого снова проверьте план и фактическое время выполнения.

Так expression index превращается не в случайную оптимизацию, а в понятный и контролируемый инструмент ускорения PostgreSQL.