Как создать составной индекс с учетом порядка полей и как это влияет на поиск в B-tree?

Что проверяют

Понимаете ли вы, как B-tree физически упорядочивает ключи и почему от этого зависит, какие запросы индекс обслужит. Следом дают пару запросов и просят выбрать порядок колонок или показывают план с Seq Scan и спрашивают, почему индекс не взялся.

Как отвечать

Индекс CREATE INDEX idx_orders_user ON orders (user_id, status, created_at) хранит записи отсортированными сначала по user_id, внутри него по status, дальше по created_at. Отсюда правило левого префикса:

• используется полностью: WHERE user_id = ? AND status = ? AND created_at > ?;
• используется частично: WHERE user_id = ?, а также user_id вместе со status;
• не годится для прямого поиска: запрос только по status или только по created_at.

Порядок подбирается под запросы: сначала колонки с условием равенства, потом колонка с диапазоном. После диапазона следующая колонка уже не сужает спуск по дереву и проверяется как фильтр внутри просмотренного участка. Тот же индекс закрывает ORDER BY status, created_at при фиксированном user_id без отдельной сортировки. Для смешанного направления, например ORDER BY a ASC, b DESC, нужен индекс (a, b DESC).

Правило «самую селективную колонку первой» не абсолютное: важнее, какие колонки есть во всех запросах и где стоит диапазон.

Из Java индекс описывают в миграции Flyway или Liquibase, а не через генерацию схемы Hibernate. Проверка всегда одна: EXPLAIN ANALYZE на реальных данных.

Где спотыкаются

Создают по индексу на каждую колонку вместо одного составного. PostgreSQL умеет склеивать их через BitmapAnd, но это заметно дороже одного прохода по составному индексу, а каждый лишний индекс ещё и замедляет запись.

Ставят колонку с диапазоном первой: индекс (created_at, user_id) для запроса «заказы пользователя за месяц» заставит просмотреть месяц заказов всех пользователей.

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