Составные индексы в PostgreSQL: чек-лист перед продакшеном

Составной индекс в PostgreSQL 16 легко настроить под один запрос и промахнуться с остальными: порядок колонок влияет на то, какие условия индекс сможет использовать и сможет ли он поддержать сортировку. Перед выкладкой проверьте не только план целевого запроса, но и нагрузку на запись, похожие индексы и стоимость построения.

Начните с реального запроса, а не с названий колонок

Предположим, приложение выбирает последние заказы одного магазина с заданным статусом:

SELECT id, created_at, total
FROM public.orders
WHERE tenant_id = 42
  AND status = 'paid'
  AND created_at >= TIMESTAMP '2025-01-01 00:00:00'
ORDER BY created_at DESC
LIMIT 50;

Составной индекс для такого запроса должен отражать его фильтры и порядок результата. Но сначала соберите несколько частых запросов к той же таблице: возможно, они фильтруют только по магазину, только по статусу или сортируют заказы иначе. Индекс оценивают по рабочей нагрузке, а не по одной удачной строке из плана.

Условия в примере — равенства по `tenant_id` и `status`, затем диапазон по `created_at`. Для B-tree PostgreSQL 16 это естественный кандидат на индекс `(tenant_id, status, created_at)`. Направление `DESC` можно указать, если оно соответствует частой сортировке. Для запроса с равенствами по первым колонкам такой индекс может читать записи в порядке `created_at DESC`.

Порядок колонок — это не рейтинг их важности. Он задаёт, какую часть дерева индекс поможет найти для конкретных фильтров и сортировок.

Три варианта и цена каждого

Для приведённого запроса можно рассмотреть три подхода. Какой выбрать, зависит от того, какие запросы действительно встречаются в логах и сколько стоит дополнительный индекс для записи и хранения.

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

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

Проверьте план до и после

Для диагностики сравните план запроса до создания индекса и после. В тестовой среде запустите:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total
FROM public.orders
WHERE tenant_id = 42
  AND status = 'paid'
  AND created_at >= TIMESTAMP '2025-01-01 00:00:00'
ORDER BY created_at DESC
LIMIT 50;

ANALYZE в этой команде действительно выполняет запрос. Не запускайте её бездумно на тяжёлом запросе в production: сначала оцените нагрузку и выберите безопасное окно. Смотрите не только на то, появился ли `Index Scan`, но и на фактическое число строк, время, чтение блоков и расхождение оценок планировщика с реальностью.

Планировщик вправе выбрать последовательное чтение даже при наличии индекса. Так бывает, например, когда таблица небольшая или условие возвращает значительную долю строк. Само наличие индекса не доказывает, что он полезен.

Чек-лист перед выкладкой

1. Соберите запросы-кандидаты. Зафиксируйте реальные условия фильтрации, сортировку, лимит и частоту каждого запроса. Проверка: у вас есть не один пример, а короткий список типичных запросов с production-нагрузки или тестового сценария.
2. Сопоставьте порядок колонок с фильтрами. Сначала проверьте поля с равенством, затем диапазонные условия и сортировку. Не забывайте запросы, которые не задают часть ведущих колонок. Проверка: индекс соответствует нескольким важным запросам или вы ясно понимаете, зачем он нужен только одному.
3. Сравните планы и фактическую работу. Используйте `EXPLAIN` для оценки плана, а `EXPLAIN (ANALYZE, BUFFERS)` — для измерений в контролируемых условиях. Проверка: выигрыш виден по фактическим строкам, времени или чтению блоков, а не только по названию узла плана.
4. Оцените цену записи и хранения. Индекс занимает место и обновляется при изменении индексируемых данных. Проверка: на тестовом профиле измерены размер нового индекса и влияние на вставки и обновления, а не только скорость чтения.
5. Проверьте дублирование и развёртывание. Сверьте определение нового индекса с уже существующими: более широкий индекс может частично пересекаться с узким, но не считайте их взаимозаменяемыми без проверки нагрузки. Проверка: есть план создания, наблюдения за ошибками и удаления индекса, который не дал пользы.

Что ломается на практике

Индекс подходит только запросу из примера

Индекс `(tenant_id, status, created_at)` не становится универсальным индексом для каждой колонки. Если другой экран ищет только по `status`, проверьте его план отдельно: он может получить мало пользы от такого порядка. Не добавляйте ещё одно поле автоматически — сначала подтвердите проблему измерением.

Сортировка требует другого порядка

Если запрос сортирует по нескольким полям или использует разные направления сортировки, проверьте точное выражение `ORDER BY`. Индекс, подходящий для сортировки по времени, не обязательно устранит сортировку по второму полю. Сверяйте план целевого запроса, а не предполагайте, что любой подходящий фильтр заодно решит сортировку.

Индекс создаётся в неподходящее время

Обычное создание индекса может мешать параллельной работе с таблицей. PostgreSQL поддерживает CREATE INDEX CONCURRENTLY для построения с меньшим воздействием на обычные операции, но оно выполняется дольше и имеет ограничения: например, команду нельзя запускать внутри блока транзакции. При сбое может остаться невалидный индекс, который нужно проверить и обработать по документации PostgreSQL 16.

Для крупной рабочей таблицы заранее проверьте регламент релиза, свободное место, длительность построения на похожем объёме и мониторинг блокировок. Не превращайте оценку «на тестовой базе было быстро» в гарантию для production.

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

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