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

Почему индекс из трёх колонок может не ускорить запрос

В PostgreSQL 16 индекс (tenant_id, status, created_at) подходит для одних запросов к заказам, но может не помочь получить последние заказы арендатора по времени, если фильтра по статусу нет. Ошибка не в количестве колонок: важен их порядок и то, какие условия реально стоят в запросе. Ниже — практические варианты для B-tree и критерии, по которым выбрать подходящий.

Рассматриваем PostgreSQL 16 и обычные B-tree-индексы. Допустим, таблица orders хранит tenant_id, status, created_at, id и total. Типичный запрос ленты — найти заказы одного арендатора в определённом статусе и показать новые первыми.

Сначала выпишите запросы, а не колонки

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

Например, эти запросы похожи, но предъявляют разные требования к индексу:

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

SELECT id, created_at, total
FROM orders
WHERE tenant_id = 42
ORDER BY created_at DESC
LIMIT 50;

Для первого можно проверить индекс (tenant_id, status, created_at DESC). Второму нужен порядок строк по времени внутри арендатора, независимо от статуса. В индексе с status между tenant_id и created_at записи разных статусов не собраны в общий порядок по дате — такой индекс не гарантирует нужную сортировку.

Не спрашивайте, какой столбец «самый селективный». Спросите, какие условия запрос задаёт слева направо и какой порядок результата ему нужен.

Три схемы индекса для разных запросов

Это не рейтинг «лучше — хуже». Если основной запрос — первый пример, начните с проверки такого индекса:

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

Если чаще нужна лента без статуса, проверьте отдельный вариант:

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

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

Как выбрать порядок колонок

Равенства — перед диапазоном и сортировкой

Для B-tree в PostgreSQL 16 ограничения по ведущим колонкам особенно важны: равенства на колонках слева, а затем условие диапазона по следующей колонке обычно лучше всего ограничивают участок индекса для чтения. Например, запрос с tenant_id = 42, status = 'paid' и сортировкой по created_at соответствует последовательности «два равенства, затем дата».

Если оба первых поля всегда заданы через равенство, перестановка tenant_id и status не обязательно изменит пользу для конкретно этого запроса. Но она меняет поддержку запросов, которые используют только одно из них. Индекс, начинающийся с tenant_id, может пригодиться запросам только по арендатору; обратный порядок — запросам только по статусу. Выбирайте по реальным шаблонам, не по одной строке SQL.

Не ставьте колонку в середину «на всякий случай»

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

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

Покрывающие колонки — не фильтры

В PostgreSQL 16 можно добавить поля через INCLUDE, чтобы индекс содержал значения, нужные для выборки, но не превращал их в ключи сортировки или поиска:

CREATE INDEX orders_tenant_created_cover_idx
ON orders (tenant_id, created_at DESC)
INCLUDE (status, total);

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

Что проверить до и после создания

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

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

Что ломается чаще всего

• Ожидание, что любой префикс ускорится одинаково. Индекс (tenant_id, status, created_at) не заменяет автоматически (tenant_id, created_at) для сортировки всех заказов арендатора по времени.
• Порядок выбрали по уникальности поля. Селективность важна для плана, но не отменяет порядок ключей, необязательные условия и требуемую сортировку.
• Появился лишний индекс на каждую комбинацию. Чтение может выиграть, но запись станет дороже, а место на диске будет расти. Удаляйте только после проверки статистики использования и нагрузки.
• Сверили только наличие Index Scan. Само название узла не показывает, сколько строк индекс прочитал и сколько отбросил позже. Смотрите на actual rows, фильтры и буферы.
• Не учли изменение данных. Индекс, который был полезен на тестовой выборке, может проиграть после изменения распределения статусов или роста таблицы. Повторяйте измерение на актуальных данных.

Термин. Селективность — способность условия отсеять значительную часть строк. Условие по редкому статусу часто селективнее, чем по распространённому, но это не универсальная подсказка для первого места в индексе: учитывайте весь запрос.

Когда что выбирать

• Если почти каждый запрос задаёт арендатора и статус, а затем сортирует по дате, тестируйте (tenant_id, status, created_at).
• Если главная операция — лента арендатора, а статус необязателен, тестируйте (tenant_id, created_at); фильтрация по статусу может потребовать чтения дополнительных записей.
• Если есть независимые запросы по отдельным полям и мало устойчивых сочетаний условий, сравните отдельные индексы и bitmap-планы с одним составным индексом.
• Если нужно только вернуть дополнительные поля, сначала проверьте, оправдывает ли выигрыш добавление их через INCLUDE; не путайте покрытие выборки с поиском по этим колонкам.

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

Следующий шаг: измерьте запросы, а не красоту схемы

Возьмите три-пять самых частых запросов к таблице, сохраните планы с EXPLAIN (ANALYZE, BUFFERS) и составьте матрицу: какие условия заданы, какая сортировка нужна, сколько строк возвращается. Затем добавляйте по одному варианту индекса и повторяйте замеры. Отдельно наблюдайте за задержкой записи и размером индексов после выкладки. Так вы проверите не «идеальный порядок колонок вообще», а конкретный выигрыш для вашей базы.

Поведение и рекомендации здесь относятся к B-tree в PostgreSQL 16. Для другой основной версии сверяйте детали с её документацией; план и результаты всё равно проверяйте на собственной нагрузке.