Что такое expression index (например, CREATE INDEX ON users (lower(email)))? Когда он спасает?

Что такое expression index

Expression index хранит не значение колонки, а результат произвольного выражения — функции, оператора или комбинации. PostgreSQL вычисляет выражение при вставке/обновлении строки и сохраняет результат в индексе. При запросе планировщик использует индекс, если выражение в WHERE или ORDER BY точно совпадает с выражением индекса.

Главное ограничение: стабильность функции

Функция в индексе должна быть IMMUTABLE — возвращать одинаковый результат для одинаковых аргументов всегда, без зависимости от БД-состояния или текущего времени. STABLE и VOLATILE функции запрещены в выражении индекса.

-- lower() — IMMUTABLE, можно
CREATE UNIQUE INDEX idx_users_lower_email ON users (lower(email));

-- now() — STABLE, НЕЛЬЗЯ
-- ERROR: functions in index expression must be marked IMMUTABLE
-- CREATE INDEX ... ON orders (date_trunc('day', now()));

Кейс 1: case-insensitive email

-- Индекс на выражение
CREATE UNIQUE INDEX idx_users_lower_email ON users (lower(email));

-- Запрос ДОЛЖЕН использовать то же выражение
SELECT id, name FROM users WHERE lower(email) = lower($1);
-- ИЛИ (если клиент нормализует email перед отправкой)
SELECT id, name FROM users WHERE lower(email) = 'john@example.com';

-- Этот запрос НЕ использует expression index:
SELECT id, name FROM users WHERE email = 'john@example.com';
-- (потому что индекс по lower(email), а не по email)

Кейс 2: извлечение ключа из JSONB

-- Индекс на JSONB-поле (если поле используется в WHERE постоянно)
CREATE INDEX idx_orders_meta_source
ON orders ((metadata->>'source'));

-- Запрос с тем же выражением
SELECT * FROM orders WHERE (metadata->>'source') = 'mobile_app';

Кейс 3: нормализация телефона

-- Удаляем нецифровые символы
CREATE OR REPLACE FUNCTION normalize_phone(p text) RETURNS text
    LANGUAGE sql IMMUTABLE STRICT AS
$$
    SELECT regexp_replace(p, '[^0-9]', '', 'g');
$$;

CREATE INDEX idx_contacts_phone ON contacts (normalize_phone(phone));

SELECT * FROM contacts WHERE normalize_phone(phone) = normalize_phone($1);

Кейс 4: computed bucket для шардинга

CREATE INDEX idx_events_bucket ON events ((user_id % 16));
SELECT * FROM events WHERE (user_id % 16) = 5;

Проверка использования

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM users WHERE lower(email) = 'test@example.com';
-- Ожидаем: Index Scan using idx_users_lower_email on users

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

• Запрос должен использовать точно то же выражение — lower(email) и lower(trim(email)) дают разные индексы.
• При каждом UPDATE затронутой колонки PostgreSQL пересчитывает выражение и обновляет индекс — дороже, чем простой колоночный индекс.
• ORM (SQLAlchemy, Prisma) обычно не знают об expression index — запрос может быть сгенерирован без выражения и не использовать индекс.
• pg_dump включает выражение индекса дословно — при изменении функции индекс нужно пересоздать вручную.
• Статистика для expression index хранится отдельно; ANALYZE обновляет её, но иногда планировщик неверно оценивает selectivity — проверяйте pg_stats по имени индекса.
• UNIQUE expression index работает корректно только если функция детерминирована: два разных email, дающих одинаковый lower(), будут конфликтовать — что и нужно для case-insensitive uniqueness.
• Не путайте с generated columns (PostgreSQL 12+): генерированная колонка хранит значение в таблице и индексируется обычным образом; expression index хранит только в индексе.
• Функция с параметрами collation (lower(email COLLATE "C")) создаёт другой индекс, чем без collation — следите за совпадением.

• Создать индекс по email и ждать ускорения lower(email).
• Забыть UNIQUE для нормализованного email.
• Использовать нестабильное выражение.

• Просит написать индекс для case-insensitive email.
• Проверяет совпадение выражения в WHERE.
• Уточняет write overhead.