В предыдущих уроках мы строили индекс «как есть» — на колонке, на нескольких колонках, с INCLUDE. Но в реальных базах часто оказывается, что большая часть строк не нужна в индексе, или что мы ищем по функции от колонки, а не по самой колонке. Для этого есть два паттерна:

Partial index: индексируем подмножество

Допустим, у тебя таблица заказов orders на 100M строк. Из них 99% — в статусах delivered/refunded/cancelled (закрытые), и только 1% (~1M) — активные: pending/paid/shipped. Все «горячие» запросы в приложении смотрят только активные:

SELECT * FROM orders
WHERE status IN ('pending', 'paid', 'shipped')
  AND placed_at >= NOW() - INTERVAL '7 days';

Если ты сделаешь обычный индекс (status, placed_at), он будет покрывать все 100M строк — индекс на 5-10 GiB, из которых 99% бесполезных. Partial index делает магию:

CREATE INDEX orders_active_idx
ON orders (placed_at)
WHERE status IN ('pending', 'paid', 'shipped');

В этот индекс попадают только строки с активными статусами — 1M вместо 100M, размер ~100 MiB вместо 10 GiB. Запросы по активным заказам идут через partial index, остальные — обходят его (если он вообще не подходит).

Когда оптимизатор использует partial index

Главная тонкость: чтобы оптимизатор выбрал partial index, предикат запроса должен соответствовать (или быть строго уже) предикату индекса. Postgres делает proof-procedure: пытается доказать, что для каждой строки, удовлетворяющей WHERE запроса, выполняется WHERE индекса.

• CREATE INDEX ... WHERE status IN ('pending', 'paid', 'shipped').
• Запрос WHERE status = 'paid' — подходит, потому что 'paid' ∈ ('pending', 'paid', 'shipped').
• Запрос WHERE status = 'delivered' — не подходит.
• Запрос WHERE status IN ('paid', 'shipped') — подходит, подмножество.
• Запрос WHERE status != 'delivered' — может не подойти: оптимизатор не всегда умеет доказать, что status != 'delivered' → status ∈ ('pending', 'paid', 'shipped', 'cancelled', 'refunded'), и без матча не использует partial. Это «тонкая» оптимизация.

Правило: предикат в запросе должен быть синтаксически близок или вложен в предикат индекса. Иначе оптимизатор пасует. Это известное ограничение Postgres-планнера.

Демонстрация partial index

Partial-индекс должен быть в разы меньше, чем таблица.

В плане увидишь Index Scan using orders_active_idx.

Тут увидишь Seq Scan (или какой-то другой план), потому что partial-индекс не покрывает delivered.

Expression index: индексируем функцию

Вторая ситуация — когда поиск идёт не по колонке, а по функции от неё. Классика — case-insensitive email:

SELECT * FROM users WHERE lower(email) = '[email protected]';

Обычный индекс CREATE INDEX ON users (email) не используется этим запросом, потому что WHERE lower(email) = ... — это не предикат на email, а предикат на lower(email). Postgres не знает, как доказать, что значение lower(x) = 'a' соответствует диапазону email в индексе.

Решение — expression index:

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

Теперь B+tree сортирует строки по значению lower(email), а не по email. Запрос WHERE lower(email) = '[email protected]' использует индекс напрямую.

Ограничения expression index

1. Выражение должно быть IMMUTABLE. Можно lower(text), (jsonb_col ->> 'key'), date_trunc('day', ts) — всё, что возвращает одинаковый результат для одинакового ввода. Нельзя now(), random(), или функции, помеченные VOLATILE/STABLE.

2. Запрос должен использовать ровно ту же функцию. WHERE lower(email) = ... — да. WHERE upper(email) = ... — нет. WHERE email ILIKE 'a@b' — теоретически да, но оптимизатор обычно не догадывается.

3. Размер индекса = размер выражения. Если ты индексируешь md5(json_col::text), в листе будет 32-байтная строка md5, а не оригинальный JSON. Может быть и плюсом (компактнее), и минусом (потеря исходных данных).

Демонстрация expression index

Должно стать Index Scan using customers_email_lower_idx.

Комбо: partial + expression

Эти два паттерна можно совмещать. Пример: индекс только по активным заказам, по округлённому до дня времени:

CREATE INDEX orders_active_daily_idx
ON orders (date_trunc('day', placed_at))
WHERE status IN ('pending', 'paid', 'shipped');

Запрос SELECT count(*) FROM orders WHERE date_trunc('day', placed_at) = '2024-06-15' AND status = 'paid' пойдёт через этот индекс. Размер — крошечный, эффективность — максимальная.

Применимость expression: реальные случаи

Кроме lower(email), expression-индексы решают много типичных задач. Несколько примеров из практики:

1. Поиск по JSON-полю: CREATE INDEX ON events ((payload->>'event_type')). Запрос WHERE payload->>'event_type' = 'login' использует индекс. Без него — Seq Scan на каждый отчёт.

2. Поиск по дате без времени: CREATE INDEX ON orders (date_trunc('day', placed_at)). Запросы WHERE date_trunc('day', placed_at) = '2024-06-15' идут через индекс.

3. Полнотекстовый поиск (упрощённый): CREATE INDEX ON articles (to_tsvector('russian', body)). Хотя для full-text лучше GIN-индекс (см. модуль 3), B-tree expression тоже работает на простых случаях.

4. Сложные вычислимые ключи: CREATE INDEX ON users (md5(email || phone)). Уникальность по композитному ключу без открытия данных в индексе.

5. Партиционирование по hash (вручную, до партиционных схем): CREATE INDEX ON events ((hashtext(user_id::text) % 16)). Используется для имитации шардинга на одной БД.

Все они требуют, чтобы WHERE использовал то же выражение. Postgres не делает преобразований вида «a*2 = 10 => a = 5» — это строгий syntactic match.

Unique partial: enforcement подмножества

Очень мощный приём — комбинация UNIQUE + partial. Допустим, тебе нужно: «в системе может быть только один активный пользователь с данным email», но deleted пользователи могут переиспользовать email (soft-delete). Обычный UNIQUE (email) это не позволит — будет конфликт с удалённым.

Решение:

CREATE UNIQUE INDEX users_email_active_unique
ON users (email)
WHERE deleted_at IS NULL;

Теперь уникальность проверяется только среди активных строк. Создал пользователя [email protected], soft-удалил его (deleted_at = NOW()), создал нового с тем же email — всё ОК. Это намного чище, чем «email + status» как UNIQUE-ключ, и не требует никаких триггеров.

Применимость огромна: ровно один активный заказ на пользователя, один primary phone на контакт, единственная default-настройка на проект. Везде, где «уникальность среди подмножества».

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

1. Statistics не очень дружат с partial. ANALYZE собирает статистики по полной таблице, и оптимизатор иногда плохо оценивает селективность partial-индекса. Если видишь, что запрос «должен» идти через partial, но идёт через Seq Scan — попробуй ANALYZE, и проверь pg_stat_user_indexes (idx_scan).

2. NULL в WHERE предиката. WHERE col IS NOT NULL в partial-индексе работает, но запросы с WHERE col = X не всегда матчатся, если оптимизатор не понимает «X не NULL implies col IS NOT NULL». Иногда нужно явно дублировать условие в запросе.

3. Expression index ломает UPDATE паттерн. Если ты UPDATE’ишь колонку, по которой построен expression index, индекс перестраивается даже если выражение даёт то же значение. Например, UPDATE users SET email = email всё равно ребилдит индексы — Postgres не «знает», что значение не изменилось.

4. Partial-индекс + JOIN. Если ты JOIN’ишь две таблицы и partial-предикат живёт в одной из них, оптимизатор может не «протолкнуть» предикат — и индекс не использует. Помогает явное добавление условия в WHERE.

Эволюция: hot rows + cold rows

Третий мощный паттерн с partial — разделение «горячих» и «холодных» данных через индексы. Допустим, у тебя events — миллиард событий за 5 лет. 99% запросов смотрят только последние 30 дней (dashboards, monitoring, alerts). Остальные 1% — это исторические отчёты, которые могут позволить себе secs.

Стратегия:

-- горячий индекс — маленький, быстрый, всегда в RAM
CREATE INDEX events_recent_idx
ON events (occurred_at, user_id)
WHERE occurred_at >= '2024-01-01';

-- холодные данные обходятся Seq Scan'ом или партиционированием

Через год тебе нужно «сдвинуть окно». Это не так просто — partial-индекс не умеет рекурсивно обновлять свой предикат. Решение: создать новый индекс с новым окном, дождаться его билда, дропнуть старый. Не страшно, но требует процесса:

CREATE INDEX CONCURRENTLY events_recent_idx_v2
ON events (occurred_at, user_id)
WHERE occurred_at >= '2025-01-01';

DROP INDEX CONCURRENTLY events_recent_idx;
ALTER INDEX events_recent_idx_v2 RENAME TO events_recent_idx;

В production-приложениях это вынесено в quarterly maintenance task. Альтернатива — партиционирование по occurred_at, где каждая партиция имеет свой PK-индекс, и старые партиции автоматически попадают в archive.

Чек-лист

• Partial index (CREATE INDEX ... WHERE ...) индексирует только строки, удовлетворяющие предикату. Размер обычно в разы меньше полного.
• Чтобы оптимизатор использовал partial, предикат запроса должен синтаксически совпадать или быть вложен в предикат индекса.
• Expression index (ON t (lower(email))) индексирует результат функции, а не колонку. Запрос обязан использовать ту же функцию.
• Expression должно быть IMMUTABLE — lower, date_trunc, (jsonb->>'k') подходят; now(), random() — нет.
• Partial + expression можно комбинировать для максимальной эффективности.
• Diagnostic: idx_scan в pg_stat_user_indexes показывает, реально ли индекс используется.