SQL Ready | Базы Данных on Telegram: “Почему порядок колонок в составном индексе важен…”

Почему порядок колонок в составном индексе важен!

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

Есть таблица:

CREATE TABLE orders (
    id BIGINT,
    user_id BIGINT,
    status VARCHAR(20),
    created_at TIMESTAMPTZ,
    amount NUMERIC(12,2)
);

Допустим, часто выполняется запрос:

SELECT
    id,
    created_at,
    amount
FROM orders
WHERE user_id = 1500
  AND created_at >= TIMESTAMPTZ '2026-09-01 00:00:00+00'
ORDER BY created_at;

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

CREATE INDEX idx_orders_user_created
ON orders (user_id, created_at);

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

B-tree позволяет сначала ограничить сканируемую часть индекса конкретным user_id:

user_id = 1500

После этого created_at задаёт диапазон уже внутри записей этого пользователя:

created_at >= TIMESTAMPTZ '2026-09-01 00:00:00+00'

Такой порядок также соответствует ORDER BY created_at: при фиксированном user_id PostgreSQL может получить строки из индекса уже в нужном порядке и при подходящем плане обойтись без отдельной сортировки.

Теперь поменяем порядок колонок:

CREATE INDEX idx_orders_created_user
ON orders (created_at, user_id);

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

created_at >= TIMESTAMPTZ '2026-09-01 00:00:00+00'

Условие по user_id всё ещё может участвовать в индексном сканировании, но уже не сокращает начальный диапазон B-tree так же эффективно, как в варианте (user_id, created_at). Разница особенно заметна, если за выбранный период накопились миллионы заказов разных пользователей.

Индекс (user_id, created_at) хорошо подходит и для запроса только по ведущей колонке:

WHERE user_id = 1500;

А также для сочетания равенства и диапазона:

WHERE user_id = 1500
  AND created_at >= TIMESTAMPTZ '2026-09-01 00:00:00+00';

Но запрос только по created_at обычно не получает от этого индекса того же преимущества, поскольку условие на ведущую колонку отсутствует:

WHERE created_at >= TIMESTAMPTZ '2026-09-01 00:00:00+00';

При этом индекс нельзя считать полностью бесполезным: начиная с PostgreSQL 18, в некоторых случаях планировщик может применить B-tree skip scan. Выбор зависит от статистики, количества различных значений ведущей колонки и оценки стоимости плана.

Поэтому порядок колонок в составном индексе выбирают под реальные условия запросов, а результат проверяют через план выполнения.

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    id,
    created_at,
    amount
FROM orders
WHERE user_id = 1500
  AND created_at >= TIMESTAMPTZ '2026-09-01 00:00:00+00'
ORDER BY created_at;

Важно, чтобы практика была показательной, таблица должна содержать достаточно данных, а статистика должна быть актуальной (ANALYZE orders;). На маленькой или пустой таблице PostgreSQL вполне может выбрать Seq Scan, и это будет нормальным поведением оптимизатора.

🔥 Вывод такой: для B-tree индекса (a, b) особенно эффективен сценарий, когда сначала ограничивается ведущая колонка a, а затем используется диапазон по b. Если запрос содержит равенство и диапазон, колонку с равенством часто имеет смысл поставить перед колонкой с диапазоном. Но окончательный выбор индекса должен подтверждаться реальным execution plan.

➡️ SQL Ready | #практика