К пятнадцатому модулю ты уже знаешь, как устроены B-tree, GIN, BRIN, как они хранятся на страницах, как planner оценивает их стоимость. Не хватает одного куска — как принимать решение о наборе индексов для конкретной нагрузки. Это не про устройство индекса, это про продуктовую стратегию: на каких колонках, в каком порядке, какие — partial, какие — covering, какие — лишние.

Главное «нельзя забыть»: индекс — это не «бесплатно ускорить SELECT». Каждый индекс — это дополнительная запись при INSERT/UPDATE/DELETE, дополнительные WAL-байты, и дополнительный bloat, который должен убирать autovacuum. На write-heavy таблицах суммарная стоимость десяти индексов может превзойти выгоду от их использования. Поэтому стратегия — это всегда баланс между «ускорить чтение» и «не убить запись».

Single-column vs composite

Самый частый вопрос: «у меня есть запрос WHERE a = X AND b = Y — делать два отдельных индекса по a и b или один composite (a, b)?»

Два отдельных: planner может объединить их через BitmapAnd. Сначала Bitmap Index Scan по idx_a, потом по idx_b, потом пересечение bitmap’ов, потом heap fetch. Это работает, но требует двух прохода по индексам и intersect-операции.

Composite (a, b): один проход по индексу, прямой переход к нужной паре. На порядки эффективнее, если оба условия в WHERE.

Composite выгоднее, когда оба условия часто встречаются вместе. Если запросы делятся на «иногда по a, иногда по b» — отдельные индексы лучше, чтобы каждый имел shape под свой запрос.

Order колонок в composite — критично

Composite (a, b) и (b, a) — это два разных индекса. Они дают разную производительность даже на одних и тех же запросах.

Правило:

• WHERE a = X — да (leading column);
• WHERE a = X AND b = Y — да;
• WHERE a = X AND b BETWEEN ... — да;
• WHERE b = Y — нет (b — не leading);
• WHERE a BETWEEN ... AND b = Y — частично: a-range используется, b — нет.

Принцип выбора order: более селективная колонка с предикатом равенства — первой. Колонка с range-предикатом — последней.

Пример: WHERE country = 'RU' AND signup_date >= '2024-01-01'. Если country имеет 10 значений (плохая селективность), а signup_date распределена — (country, signup_date) лучше. Equality по country быстро сужает; range по signup_date работает на остатке.

Если у тебя equality на оба — лучше высокую селективность вперёд: (email, country) лучше, чем (country, email), потому что email уникален.

Partial index: вместо «индекса по всему — индекс по 10%»

Partial index — это индекс с собственным WHERE-предикатом. В него попадают только те строки, что удовлетворяют предикату. Остальные просто не входят в индекс.

CREATE INDEX orders_pending_idx
  ON orders (placed_at)
  WHERE status = 'pending';

Если 99% orders имеют статус delivered, а запросы про pending — основные, partial index по WHERE status = 'pending' будет в 100 раз меньше обычного индекса по (status, placed_at). Меньше IO, меньше места в shared_buffers, меньше cost обновления.

Условия эффективности:

1. Предикат в индексе совпадает с предикатом в запросе (или строго общее). Postgres проверяет соответствие.
2. Доля строк, попадающих в partial — меньше 10-20% от всей таблицы. Иначе выгоды нет.
3. Предикат stable: не зависит от текущего времени, юзера, переменной. Иначе на каждом запросе planner не сможет proof’нуть соответствие.

Anti-case: WHERE created_at > NOW() - INTERVAL '7 days' — кажется, что отличный кандидат на partial. Но NOW() — не stable, и Postgres не сможет использовать этот partial. Решение — использовать абсолютную дату и периодически (раз в день/неделю) пересоздавать индекс.

Covering index: INCLUDE для index-only scan

PostgreSQL 11+ ввёл синтаксис INCLUDE для «несортируемой» части индекса:

CREATE INDEX orders_customer_idx
  ON orders (customer_id) INCLUDE (placed_at, total_cents);

Колонки в INCLUDE — это payload, который хранится в листовых страницах индекса, но не участвует в сортировке и не может использоваться в WHERE для поиска. Их единственная задача — позволить index-only scan.

Когда использовать:

• Запросы вида SELECT placed_at, total_cents FROM orders WHERE customer_id = ? — постоянная нагрузка, ускорение через avoid heap-fetch.
• Колонки в INCLUDE редко меняются (иначе индекс будет переписываться при каждом UPDATE).
• Размер payload разумный — не INCLUDE (huge_jsonb).

Когда НЕ использовать:

• Если payload-колонка часто меняется — каждый UPDATE = записать строку в основной heap + в каждый соответствующий индекс. Big bloat.
• Если запросов вида «дай payload по этому ключу» мало — экономия не стоит расходов на write-path.

Когда composite — а когда несколько отдельных

Один из самых частых вопросов на ревью: «у меня есть (a, b) composite. Нужно ли отдельный индекс по b?»

Ответ зависит от набора запросов:

• Если все запросы используют WHERE a AND b или WHERE a — отдельный по b не нужен. Composite уже всё покрывает (для запроса по a композит работает как индекс по prefix).
• Если есть запрос WHERE b = X без a — нужен отдельный индекс по b. Composite не сработает.

Anti-case: «на всякий случай добавим композит по (a, b) и индексы по a и b». Получается тройное хранение, тройной write-cost, и большая часть запросов всё равно использует только один из трёх. Удали дубликаты.

Симметричная задача: есть (a, b, c). Нужны ли (a, b) и (a) дополнительно? Нет — B-tree composite поддерживает поиск по любому prefix. (a, b, c) обслуживает WHERE a, WHERE a AND b, WHERE a AND b AND c. Дополнительные индексы по prefix’ам — пустая трата места.

Index maintenance: реальная цена

Каждый индекс — это:

• +N байт на каждую вставку (запись в страницу индекса, плюс возможные split’ы).
• +M байт WAL на каждую модификацию (WAL пишет все индексные изменения).
• +K страниц bloat при UPDATE’ах (особенно — non-HOT, когда колонка в индексе меняется).
• +P секунд на каждый VACUUM (vacuum обходит каждый индекс).

На таблице с 5 индексами один INSERT — это 6 страничных операций (1 heap + 5 индексов). При высоком write-rate индексы становятся узким горлом.

Эту цену легко измерить в реальном времени. EXPLAIN (ANALYZE, BUFFERS) на INSERT покажет, сколько страниц затронуто для каждого индекса. Сделаем сравнительный замер: вставка с двумя индексами против вставки с одним.

Как находить лишние индексы:

SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan < 100  -- или 0 за месяц uptime
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan = 0 за длительный период — кандидат на удаление. Перед удалением: проверь, что это не индекс под constraint (PRIMARY KEY / UNIQUE), их удалять нельзя.

Выбор типа индекса: B-tree, GIN, BRIN, Hash

До этого мы говорили исключительно о B-tree. Это правильный default, но не единственный вариант. Короткое напоминание (детали — в модуле 03):

• B-tree — equality и range на скалярных типах. Покрывает 95% случаев.
• GIN — множественные значения в одной строке (массивы, jsonb, full-text). Например, WHERE tags @> ARRAY['urgent'] или WHERE doc @@ to_tsquery('postgres').
• BRIN — для очень больших таблиц с natural correlation между физическим порядком и значением колонки. Идеален для time-series: 1000× меньше B-tree, но работает только для range-предикатов и только при корреляции.
• Hash — equality only, нет порядка, нет range. До PostgreSQL 10 был unsafe для crash recovery — теперь WAL-logged. Полезен очень редко: B-tree-по-equality почти всегда сравним по скорости и универсальнее.
• GiST/SP-GiST — геометрия, exclusion constraints, custom типы.

Главный сигнал «не B-tree»: запросы вида WHERE array_col @> ?, WHERE jsonb_col ? 'key', WHERE ts_vector @@ to_tsquery(...). Эти операторы B-tree не поддерживает (нет линейного порядка для проверки «содержит»), и planner не сможет использовать B-tree даже если он есть.

Стратегия: пошаговый алгоритм

Когда добавляешь индекс — отвечай на эти вопросы по порядку:

1. Какие запросы он ускоряет? Конкретные SELECT ... с EXPLAIN, не «на всякий случай». Если нет конкретного запроса — индекс не нужен.
2. Это equality, range или mixed? От этого зависит, нужен ли composite, и order колонок.
3. Доля строк, удовлетворяющих предикату? Если меньше 20% и предикат stable — рассматривай partial.
4. Нужны ли в SELECT-листе ещё колонки? Если 2-3 фиксированных — рассматривай INCLUDE для index-only scan.
5. Write-overhead приемлем? На write-heavy таблице (более 1000 writes/sec) каждый новый индекс — серьёзное решение.
6. Есть ли близкий по содержимому существующий индекс? Если (a, b, c) уже есть, (a, b) — лишний (composite использует prefix).

Чек-лист

• Composite (a, b) выгоден при WHERE a AND b парных запросах. Leading column — equality, trailing — range.
• Order в composite критичен: (a,b) ≠ (b,a). Используй для leading самую селективную equality-колонку.
• Partial index хорош при доле меньше 20% строк под stable-предикатом. Не используй с NOW()/CURRENT_USER.
• INCLUDE (covering) — для постоянных запросов SELECT-листа. Только если payload-колонки редко меняются.
• Каждый индекс — write-cost. На write-heavy таблице 10+ индексов превращаются в bottleneck.
• idx_scan = 0 в pg_stat_user_indexes — кандидат на DROP (кроме constraint-индексов).
• Алгоритм: реальный запрос → equality/range → доля строк → SELECT-лист → write-overhead → existing prefix.