До сих пор мы говорили про индексы по одной колонке. Но в реальных запросах фильтры почти всегда комбинируются: WHERE country = 'RU' AND status = 'delivered', WHERE customer_id = 42 AND placed_at > '2025-01-01'. У PostgreSQL есть два способа покрыть такие запросы индексами — и они работают принципиально по-разному. Этот урок — про выбор между ними.

Choice между «один composite индекс на все колонки» и «несколько отдельных индексов, по одному на колонку» — это, пожалуй, главная архитектурная развилка при работе с индексами. И ответ почти всегда зависит от паттерна запросов, который ты ожидаешь. Поэтому работа начинается не с CREATE INDEX, а с вопроса «какие у меня самые частые / самые тяжёлые запросы?».

Composite index: один индекс, несколько колонок

PostgreSQL умеет строить B-tree, ключом которого служит кортеж из нескольких колонок:

CREATE INDEX idx_orders_customer_placed
  ON orders(customer_id, placed_at);

Под капотом это то же самое B-tree, что и раньше — но ключ теперь составной. Сортировка идёт сначала по customer_id, потом — внутри одинаковых customer_id — по placed_at. Это похоже на телефонный справочник, отсортированный сначала по фамилии, потом по имени.

Чем это полезно? Если запрос — WHERE customer_id = 2 AND placed_at > '2025-03-01', мы спускаемся в дереве сразу к нужному customer_id, потом по placed_at фильтруем диапазон — два уровня иерархии работают вместе. На таблице в миллион строк это огромная экономия.

Leftmost prefix rule: только слева направо

Главное правило composite индекса: он работает, только если запрос использует префикс ключа слева направо.

Индекс на (customer_id, placed_at):

• WHERE customer_id = 5 — работает (используется первая колонка)
• WHERE customer_id = 5 AND placed_at > '...' — работает (используются обе)
• WHERE customer_id = 5 ORDER BY placed_at — работает (фильтр + сортировка идут в порядке индекса)
• WHERE placed_at > '...' — не работает (пропустили первую колонку)
• WHERE placed_at > '...' ORDER BY customer_id — не работает (тот же случай)

Почему? Представь: ты пришёл в телефонный справочник, отсортированный по фамилии-имени, и ищешь «всех Александров». Фамилии разные — Александров может быть в любом месте справочника. Алфавитный порядок по фамилиям тебе не помогает — придётся читать все страницы подряд. Это и есть Seq Scan.

То же самое с индексом (customer_id, placed_at) и запросом WHERE placed_at > '...': для каждого customer_id свой диапазон placed_at, и они не отсортированы между собой. Спуститься по дереву негде.

Отсюда — важное правило проектирования:

Порядок колонок и ORDER BY

Особенно мощный паттерн: composite индекс, в котором первые колонки — те, по которым фильтруем, а последняя — та, по которой сортируем.

-- Запрос: последние 10 заказов клиента
SELECT * FROM orders
WHERE customer_id = 5
ORDER BY placed_at DESC
LIMIT 10;

С индексом (customer_id, placed_at):

1. Спускаемся к ветке customer_id = 5. Все эти ключи лежат рядом, отсортированные по placed_at.
2. Идём по последнему листу влево (или используем Index Scan Backward), читаем 10 ключей.
3. Достаём 10 строк из heap.

Без индекса (или с индексом только по customer_id):

1. Спускаемся к customer_id = 5, читаем все заказы клиента.
2. Сортируем их по placed_at в памяти.
3. Берём первые 10.

Если у клиента 5 заказов — разница незаметна. Если 100 000 — индексированный путь стоит копейки, а неиндексированный читает всю выборку и сортирует.

Два отдельных индекса vs один составной

Альтернатива composite — два отдельных индекса по разным колонкам. Когда какая стратегия лучше?

Если у тебя два индекса — (customer_id) и (placed_at) — и запрос фильтрует по обеим колонкам, PostgreSQL может использовать стратегию

1. По индексу (customer_id) строится битовая карта страниц, где есть нужный customer_id.
2. По индексу (placed_at) — битовая карта страниц с нужным диапазоном дат.
3. Битовые карты пересекаются: AND-комбинация.
4. По итоговой карте — Bitmap Heap Scan: читаем только страницы из карты.

Это очень мощный механизм для OR-условий (WHERE a = 1 OR b = 2) и неожиданных комбинаций. Минус — он медленнее, чем composite на запросах, которые всегда комбинируют те же самые колонки.

Правило большого пальца:

• Composite index — когда есть стабильный паттерн запросов: A AND B, A AND B AND ORDER BY C. Самый быстрый случай.
• Два отдельных индекса — когда колонки используются и вместе, и по отдельности; когда есть OR-условия; когда комбинации непредсказуемы.
• Композиция обоих — иногда стоит держать и composite (A, B), и отдельный (B), чтобы покрыть запросы и по префиксу, и по второй колонке. Цена — лишнее место и записи.

На таблице из 20 заказов план почти наверняка будет Seq Scan — оптимизатор честно выбирает то, что дешевле. На таблице в миллион — увидишь либо Bitmap Heap Scan, либо Index Scan (если один из фильтров достаточно селективен).

Включающие индексы (INCLUDE)

Ещё одна полезная конструкция, которая близка к composite, но решает другую задачу — INCLUDE:

CREATE INDEX idx_orders_customer_with_status
  ON orders(customer_id) INCLUDE (status);

Здесь customer_id — это ключ индекса (по нему сортировка и поиск), а status — полезная нагрузка, которая лежит рядом в листе индекса, но не участвует в сортировке. Зачем это нужно? Чтобы запрос SELECT status FROM orders WHERE customer_id = 5 мог обойтись только индексом — без обращения к heap. Это

INCLUDE имеет смысл, когда:

• Колонка часто читается, но никогда не фильтруется/не сортируется.
• Делать её частью composite ключа было бы избыточно (увеличило бы размер индекса больше, чем нужно).

Покрывающий индекс: всё ради Index Only Scan

Когда мы говорим про composite + INCLUDE, в воздухе витит концепция

Например, запрос:

SELECT customer_id, status FROM orders WHERE customer_id = 5;

Если индекс — (customer_id) INCLUDE (status), в нём есть и ключ, и нужный status. PG спускается в индекс, читает листы, отдаёт результат — никаких чтений heap. Это Index Only Scan, самый быстрый план для read-heavy запросов.

Минусы покрывающих индексов:

• Больше размер. Каждая дополнительная колонка в INCLUDE добавляет место.
• Запись дороже. UPDATE колонки в INCLUDE требует обновления индекса (даже если ключ не меняется).
• Стабильны только при VACUUM. Visibility Map должна быть актуальна, чтобы Index Only Scan не «деградировал» к обычному Index Scan.

Балланс — как всегда: на горячем read-heavy запросе с миллионами вызовов — покрывающий индекс окупается. На редком запросе раз в день — нет смысла.

В выводе обрати внимание на тип узла — на маленькой таблице может всё ещё быть Seq Scan, но на большой обычно становится Index Only Scan.

Сортировка DESC в composite

Ещё одна тонкость: composite index по умолчанию хранит ключи в порядке возрастания всех колонок. Если ты хочешь ORDER BY customer_id ASC, placed_at DESC — обычный индекс (customer_id, placed_at) не идеально подходит. PG может прочитать его «вперёд» для customer_id и «назад» для placed_at, но это менее эффективно.

Решение — явно указать порядок при создании:

CREATE INDEX idx_orders_cust_placed_desc
  ON orders(customer_id ASC, placed_at DESC);

Тогда ORDER BY customer_id ASC, placed_at DESC LIMIT 10 идёт по индексу строго в нужном порядке, без обратного прохода.

На практике эта оптимизация редко нужна — но знать про неё стоит, потому что иногда в коде встречаются явные ... DESC в индексах, и теперь ты знаешь, зачем.

Анти-паттерн: «индекс на всё подряд»

Самая частая ошибка после знакомства с composite — создать индекс по каждой возможной комбинации колонок. На таблице с 5 колонками это 120 индексов перестановок, плюс тысячи комбинаций подмножеств. Каждый замедляет запись и занимает место. И в большинстве своём не используется.

Здравая стратегия:

1. Сначала пиши запросы, потом смотри EXPLAIN. Не угадывай заранее.
2. Создавай индекс только под реально частые или реально тяжёлые запросы.
3. Слежение за реальной статистикой использования: pg_stat_user_indexes показывает idx_scan — сколько раз индекс был использован. Индексы с idx_scan = 0 за месяц — кандидаты на удаление.

Мини-фреймворк выбора composite vs набор индексов

Это упрощённый алгоритм, который работает в 80% случаев:

1. Один-два частых запроса со стабильным набором фильтров (A, B, C)? → composite (A, B, C) в порядке селективности. Часто + INCLUDE остальных читаемых колонок.
2. Запросы фильтруют то по A, то по B, то по A+B, непредсказуемо? → два отдельных индекса по A и B. PG скомбинирует через Bitmap.
3. Часто OR-условия по разным колонкам? → точно два отдельных индекса. Composite не помогает.
4. Запросы с ORDER BY по колонке X, фильтр по Y, top-N? → composite (Y, X) — фильтр сужает, потом сортировка бесплатна.
5. Большая таблица + soft-delete? → partial index с WHERE deleted_at IS NULL.

Этот фреймворк намеренно простой. В реальности встретишь и более хитрые комбинации, и тут уже работает EXPLAIN ANALYZE: создал индекс — проверил план — оставил или удалил.

Чек-лист

• Composite index — B-tree с ключом-кортежем; сортировка лексикографическая, слева направо.
• Leftmost prefix rule: используется только префикс ключа. (A, B, C) помогает A, A,B, A,B,C. Не помогает B, C, B,C.
• Самую селективную колонку — слева. Колонку, по которой сортируешь — справа.
• Два отдельных индекса + Bitmap Index Scan — гибче, особенно для OR и непредсказуемых комбинаций.
• INCLUDE (col) добавляет колонку в лист индекса без участия в ключе — позволяет Index Only Scan.
• Покрывающий индекс — covering index — позволяет ответить только из индекса, без heap.
• Можно задавать ASC/DESC по каждой колонке в индексе — для специфических ORDER BY.
• Не создавай индексы превентивно. Создавай под реальные запросы, проверяй idx_scan в pg_stat_user_indexes.
• Мини-фреймворк: 1 стабильный запрос → composite; OR и непредсказуемость → отдельные индексы.