Составной индекс в PostgreSQL: как выбрать порядок колонок

Порядок колонок — это не рейтинг селективности

В PostgreSQL 16 индекс по колонкам (tenant_id, status, created_at) и индекс по (status, tenant_id, created_at) — не взаимозаменяемые списки одних и тех же полей. Первый удобен для поиска по арендатору и может пригодиться запросам только по нему; второй ставит впереди статус. Если выбрать порядок по принципу «сначала колонка с большим числом уникальных значений», индекс может оказаться бесполезен для самого важного запроса.

Сравним варианты на таблице заказов и разберём, как принять решение по запросам, фильтрам и сортировкам, а не по одному показателю. Примеры относятся к PostgreSQL 16 и B-tree-индексам — самому распространённому типу индекса для таких условий.

Как PostgreSQL использует составной B-tree

Для B-tree важен левый край индекса. Условия равенства по первым колонкам позволяют сузить область поиска; после них ограничить её помогает условие диапазона по первой колонке без равенства. Условие по более правой колонке тоже может проверяться по индексу, но само по себе обычно не сужает просматриваемый диапазон так же эффективно.

Например, индекс (tenant_id, status, created_at) хорошо соответствует поиску по всем трём полям. Запрос только по status не получает того же преимущества: первая колонка индекса не задана. А запрос по tenant_id может использовать его левый префикс, даже если про статус ничего не известно.

Селективность¹ полезна при анализе условий, но не задаёт порядок колонок автоматически. Сначала выясните, какие запросы должны использовать индекс и какие из них важнее.

Составной индекс оптимизируют под форму запросов, а не под красивый порядок колонок в схеме.

Три варианта для заказов

Допустим, приложение часто показывает последние заказы выбранного клиента внутри конкретного магазина, а иногда строит список всех заказов магазина без фильтра по статусу.

Вариант 1: равенства, затем сортировка

CREATE INDEX orders_tenant_status_created_idx
ON orders (tenant_id, status, created_at DESC);

SELECT id, created_at
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

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

Вариант 2: магазин, затем дата

CREATE INDEX orders_tenant_created_idx
ON orders (tenant_id, created_at DESC);

Этот порядок подходит запросу «последние заказы магазина», в том числе если статус не задан. Если статус всё же фильтруют, PostgreSQL может проверить его для найденных строк, но индекс не упорядочен по статусу внутри магазина. При большом числе заказов такой фильтр может потребовать прочитать больше записей.

Вариант 3: статус первым

CREATE INDEX orders_status_tenant_created_idx
ON orders (status, tenant_id, created_at DESC);

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

Сравнение подходов

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

Как выбрать порядок для своего проекта

1. Соберите запросы, а не только названия полей. Зафиксируйте реальные условия WHERE, сортировку и ограничение результата. Проверьте, задают ли запросы равенство, диапазон или необязательный фильтр. Результат шага — список конкретных форм запросов.
2. Сгруппируйте обязательные равенства. Для колонок, которые почти всегда сравниваются через равенство вместе, проверьте оба порядка. Если запросы встречаются и по отдельности, поставьте в начало ту колонку, чей левый префикс нужен важному запросу. Результат — кандидат с понятной причиной, а не догадкой о селективности.
3. Проверьте диапазон и сортировку. Условие вроде created_at >= ... и сортировка по дате могут изменить полезный порядок после колонок равенства. Убедитесь, что между требуемым порядком сортировки и началом индекса не стоит поле, по которому запрос не фильтрует. Результат — индекс, который соответствует форме запроса.
4. Сравните планы на данных проекта. Посмотрите планы через EXPLAIN (ANALYZE, BUFFERS) для репрезентативных запросов и параметров. Сравните фактическое время, число прочитанных строк и блоков, а также наличие отдельной сортировки. Результат — измерение на ваших данных; сам факт использования индекса ещё не означает, что запрос стал быстрее.
5. Проверьте цену записи. После добавления индекса сравните скорость типичных вставок и обновлений, а также размер индекса. Оставляйте его, если выигрыш нужным запросам оправдывает эту цену. Результат — проверенный выбор, а не коллекция индексов «на всякий случай».

Когда какой вариант выбирать

• Выбирайте (tenant_id, status, created_at), если основной экран всегда задаёт магазин и статус, а затем показывает свежие заказы.
• Выбирайте (tenant_id, created_at), если важнее список свежих заказов магазина, а фильтр статуса необязателен или не критичен по скорости.
• Ставьте статус первым, только если запросы без условия по статусу не входят в целевую нагрузку или для них предусмотрен другой путь.

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

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

Индекс есть, но сортировка осталась

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

Колонка фильтра стоит не первой

Индекс (tenant_id, status) не превращается в полноценный индекс по одному статусу. Если запросы по статусу важны, измерьте их отдельно и оцените индекс с таким левым префиксом; не добавляйте его автоматически без проверки частоты и стоимости записи.

На тесте быстро, в проде медленно

План зависит от распределения значений и объёма данных. Проверяйте не один удобный магазин, а несколько характерных случаев — в том числе крупного арендатора, у которого записей намного больше среднего. В EXPLAIN (ANALYZE, BUFFERS) смотрите фактические строки и чтение блоков, а не только выбранный тип сканирования.

Следующий шаг

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

¹ Селективность — способность условия отсеивать строки. Чем меньше строк остаётся после фильтра, тем выше его селективность; сама по себе она не определяет лучший порядок колонок в составном индексе.