Partial, expression и covering-индексы

Таблица tasks: 100M строк, 95% из них status = ‘done’. Дашборд показывает только открытые задачи. Полный B-tree индекс на status обслуживает 100M записей, большинство из которых никогда не запрашиваются горячо. Partial-индекс WHERE status = 'open' содержит 5M записей — в 20 раз меньше, в 20 раз дешевле по write-cost, в 20 раз меньше на диске. Это один из самых недоиспользованных рычагов производительности в Postgres.

Partial-индексы

Partial-индекс — это B-tree (или другой тип), построенный только над строками, соответствующими WHERE-условию.

-- Только pending-orders — обычно малая доля таблицы
CREATE INDEX ON orders(user_id) WHERE status = 'pending';

-- Только активные пользователи (soft-delete паттерн)
CREATE INDEX ON users(email) WHERE deleted_at IS NULL;

-- Feature flag — только включённые строки
CREATE INDEX ON subscriptions(plan_id) WHERE feature_x_enabled = true;

Преимущества:

• Меньший размер: если 5% строк соответствуют предикату, индекс занимает ~5% от полного.
• Меньше write-overhead: индекс трогается только при изменении строк, соответствующих предикату.
• Быстрее поиск: меньше leaf-страниц для сканирования.

Ограничение: запросы ОБЯЗАНЫ включать WHERE-условие, идентичное предикату индекса (или более строгое), чтобы планировщик использовал partial-индекс. WHERE user_id = $1 AND status = 'pending' использует индекс; WHERE user_id = $1 — нет.

Partial unique индексы — мощный паттерн:

-- Email уникален среди не-удалённых пользователей
CREATE UNIQUE INDEX ON users (email) WHERE deleted_at IS NULL;
-- Позволяет soft-delete пользователя и re-register того же email
-- Невозможно с простым UNIQUE constraint на email

Expression-индексы

Expression-индекс строится на результате функции, применённой к столбцу, а не на самом столбце.

-- Без индекса: LOWER() применяется к каждой строке, индекс не используется
SELECT * FROM users WHERE LOWER(email) = 'alice@x.com';

-- С expression-индексом: планировщик использует его
CREATE INDEX ON users (LOWER(email));

Распространённые паттерны:

• Case-insensitive поиск: LOWER(email), UPPER(name)
• Truncation даты: DATE_TRUNC('day', created_at) для группировки по дням
• JSON path extraction: (payload->>'event_type') для JSONB-полей
• Вычисляемые значения: любое выражение, совпадающее с формой запроса

Expression-индексы — способ сделать быстрыми функциональные запросы без изменения схемы.

Covering-индексы с INCLUDE

INCLUDE (Postgres 11+) добавляет столбцы в leaf-страницы индекса без включения их в ключ сортировки. Результат: индекс становится “covering” для запросов, которым нужны эти столбцы в projection, что позволяет index-only scan (без fetch из heap).

-- Ключевые столбцы: workspace_id, created_at DESC (sort key)
-- INCLUDE: id, total_cents (payload для projection — не в sort key)
CREATE INDEX CONCURRENTLY idx_orders_ws_recent
  ON orders (workspace_id, created_at DESC)
  WHERE status = 'pending'
  INCLUDE (id, total_cents);

Запрос SELECT id, total_cents FROM orders WHERE workspace_id = $1 AND status = 'pending' ORDER BY created_at DESC LIMIT 50 отвечается только из индекса. Нет fetch из heap. Только 50 leaf-записей.