Составные индексы в PostgreSQL: рабочие приёмы

В PostgreSQL 16 индекс по колонкам (shop_id, status, created_at) может быстро находить оплаченные заказы магазина за период, но обычно не заменит индекс по одному created_at для запросов по всем магазинам. В составном индексе важны порядок колонок и форма запроса: неверный выбор увеличивает место на диске и цену записи, не ускоряя нужную выборку.

Начните не с индекса, а с запроса

Составной индекс — один индекс с несколькими ключевыми колонками. Примеры ниже рассчитаны на PostgreSQL 16 и условную таблицу orders с полями order_id, shop_id, status, created_at и total. Перед созданием индекса найдите конкретный медленный запрос и зафиксируйте его условия, сортировку и поля, которые он возвращает.

Для примера возьмём такой запрос:

SELECT order_id, created_at, total
FROM orders
WHERE shop_id = 42
  AND status = 'paid'
  AND created_at >= TIMESTAMP '2025-01-01'
ORDER BY created_at DESC NULLS LAST
LIMIT 50;

Здесь два равенства, ограничение по дате, сортировка по дате и лимит. Это полезнее знать заранее, чем просто видеть, что таблица большая.

Три подхода и цена каждого

1. Равенства — перед диапазоном

Для B-tree индекса обычно удачный старт — колонки с проверками на равенство, затем колонка, по которой запрос задаёт диапазон. Для примера подойдёт индекс (shop_id, status, created_at): сначала PostgreSQL ограничивает поиск одним магазином и статусом, затем — датой. Если запросы чаще задают магазин и дату, но не статус, порядок (shop_id, created_at, status) может соответствовать нагрузке лучше. Но условие по status после диапазона, как правило, уже не сужает просматриваемый участок индекса так же эффективно.

Для показанного запроса с сортировкой можно создать такой индекс:

CREATE INDEX orders_shop_status_created_idx
ON orders (shop_id, status, created_at DESC NULLS LAST);

Он соответствует фильтрам и направлению сортировки. Не добавляйте в индекс все колонки «на будущее»: широкие индексы занимают больше места и увеличивают работу при изменении строк.

2. Добавить поля для чтения через INCLUDE

Если запрос часто возвращает order_id и total, их можно добавить как включённые, а не ключевые колонки:

CREATE INDEX orders_shop_status_created_cover_idx
ON orders (shop_id, status, created_at DESC NULLS LAST)
INCLUDE (order_id, total);

Поля из INCLUDE доступны для чтения из индекса, но не участвуют в поиске и сортировке. Это может позволить index-only scan — чтение результата без обращения к строкам таблицы. Но гарантий нет: PostgreSQL должен также проверить видимость строк, и результат зависит, среди прочего, от состояния visibility map. Сравните план, а не только состав индекса.

3. Убрать постоянный фильтр частичным индексом

Если нужный экран почти всегда показывает только оплаченные заказы, можно рассмотреть индекс меньшего размера, который хранит записи с этим статусом:

CREATE INDEX orders_paid_shop_created_idx
ON orders (shop_id, created_at DESC NULLS LAST)
INCLUDE (order_id, total)
WHERE status = 'paid';

Здесь status не занимает место среди ключей, а индекс включает только строки, удовлетворяющие предикату. Обратная сторона: такой индекс полезен лишь для запросов, предикат которых планировщик может сопоставить с условием индекса. Если приложение передаёт статус параметром подготовленного запроса, планировщик не всегда сможет доказать соответствие частичному индексу. Проверяйте план именно для того способа выполнения, который использует приложение.

Хороший составной индекс — не тот, в котором больше колонок. Это индекс, порядок и условия которого совпадают с запросом, который действительно тормозит.

Как выбрать между вариантами

Два отдельных индекса по shop_id и status не являются автоматической заменой составному. PostgreSQL иногда объединяет их, но такое выполнение может потребовать отдельной сортировки. И наоборот, индекс (shop_id, status, created_at) не стоит создавать, если основные запросы ищут только по status: без ограничения по ведущему shop_id его полезность для такого поиска ограничена.

Сноска. Ведущий ключ — первая колонка в определении составного индекса. Для B-tree порядок ключей имеет значение: индекс по (a, b) естественно поддерживает поиск по a и по паре (a, b), но не равнозначен индексу по одному b.

Проверяйте план на запросе приложения

Снимите план до создания индекса и после. Для запроса из примера:

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, created_at, total
FROM orders
WHERE shop_id = 42
  AND status = 'paid'
  AND created_at >= TIMESTAMP '2025-01-01'
ORDER BY created_at DESC NULLS LAST
LIMIT 50;

ANALYZE запускает запрос и показывает фактические времена и число строк, а BUFFERS — сведения о чтении блоков. Сравните план, число прочитанных строк и блоков, время выполнения и наличие лишней сортировки. Учитывайте, что на маленькой таблице PostgreSQL может выбрать последовательное чтение и без индекса — сам по себе такой план не доказывает ошибку.

Тестируйте с реалистичными данными и параметрами. Один план для магазина с десятком заказов не обязательно подходит для магазина с миллионами записей. Не публикуйте единственный замер как универсальную гарантию ускорения: результат зависит от данных, частоты чтения и записи, кеша и конкурирующей нагрузки.

Что ломается и где индекс не поможет

• Неподходящий порядок ключей. Проверьте, есть ли в запросе условия на ведущие колонки. Если приложение ищет по дате без магазина, индекс, начинающийся с shop_id, может не решить задачу.
• Индекс есть, сортировка осталась. Сверьте колонку и направление сортировки. Для смешанного порядка, например a ASC, b DESC, может понадобиться соответствующее направление для каждого ключа; порядок по умолчанию не всегда подходит.
• Частичный индекс игнорируется. Убедитесь, что условие запроса подходит предикату индекса и что план строится в условиях реального приложения, включая подготовленные запросы.
• План не изменился. Сравните планы и оценку количества строк с фактическим результатом. Причина может быть в малом размере таблицы, неточной статистике или том, что запрос читает значительную часть данных.
• Запись стала дороже. Каждый дополнительный индекс нужно поддерживать при изменениях данных. Удалите неиспользуемые или дублирующие индексы только после проверки запросов и метрик, а не по названию.

Составной индекс не заменяет настройку статистики, переписывание тяжёлого запроса или анализ объёма возвращаемых данных. Если запрос забирает половину таблицы, индексный проход может оказаться не быстрее последовательного.

Что делать дальше

Выберите несколько самых дорогих запросов в рабочей нагрузке, сгруппируйте их по фильтрам и сортировке, затем проверяйте по одному кандидату на индекс. Для каждого сохраняйте план, фактическое время и число прочитанных блоков до и после. После этого оцените не только чтение, но и цену записи: размер индексов, скорость вставок и обновлений. Инвалидация кеша и настройка пула соединений — отдельные темы; здесь речь только о подборе B-tree индексов под запрос.