Составной индекс и leftmost prefix

Составной индекс хранит несколько колонок как один ключ. Сравнение идёт слева направо: сначала первая колонка, при равенстве — вторая. Это leftmost prefix: как фамилия в телефонной книге. Сначала ищете Ивановых, потом внутри — Сергея. Без фамилии книгу не откроете на всех «Сергеях».

Правило следует из устройства дерева, а не из доброй воли планировщика. Индекс (a, b, c) полезен для условий по a, по a+b, по a+b+c. Только по b или только по c классический B-tree как prefix не работает.

Пример. Индекс (фамилия, имя). Запрос «все Ивановы» работает. «Ивановы Сергей» — тоже. Запрос «все Сергеи» по этому индексу как prefix не ищется.

На бэкенде типичный горячий запрос — равенство по сущности плюс диапазон по времени: заказы пользователя за период, события сессии, лента. Один правильно упорядоченный составной индекс закрывает и фильтр, и ORDER BY … LIMIT. Два отдельных индекса по каждой колонке этого часто не дают. Postgres может сделать BitmapAnd (пересечение двух индексных битмапов), но сортировку «бесплатно» вы не получите.

Порядок колонок в индексе — решение под конкретный WHERE. Не «все фильтруемые поля подряд». Ошиблись порядком — индекс есть, в EXPLAIN его нет.

Leftmost prefix

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

В новых версиях Postgres появляются элементы skip scan (пропуск по неведущей колонке). На практике всё ещё считают: без ведущей колонки классический B-tree не берётся, пока не доказали обратное EXPLAIN.

Равенство левее, диапазон правее

Колонки равенства ставят левее, диапазон — правее. Индекс (user_id, created_at) закрывает WHERE user_id = $1 AND created_at > $2. И сортировку по created_at для одного пользователя: внутри одного user_id время уже лежит по порядку. LIMIT 20 может остановиться рано.

Пример. Лента пользователя: WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20. Индекс (user_id, created_at) — сначала нашли пользователя, потом пошли по времени. Индекс (created_at, user_id) для того же запроса слабее: диапазон по времени широкий, user_id внутри него перемешан.

Наоборот, (created_at, user_id) для фильтра «один пользователь + период» почти не помогает. Планировщик либо просканирует много листьев с Filter, либо уйдёт в Seq Scan.

CREATE INDEX idx_events_user_ts ON events (user_id, created_at);

-- берёт индекс как prefix
SELECT *
FROM events
WHERE user_id = 42
  AND created_at >= '2024-01-01';

SELECT *
FROM events
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

-- не берёт как leftmost prefix
SELECT *
FROM events
WHERE created_at >= '2024-01-01';

Избыточные индексы и направление сортировки

Два индекса (a) и (a, b) часто дублируют работу: составной уже покрывает leftmost по a. Узкий индекс имеет смысл, если он заметно меньше, иначе устроен INCLUDE, или если fillfactor и паттерн записи другие. Не удаляйте «лишний» индекс вслепую — сначала pg_stat_user_indexes и реальные запросы.

Индекс (a, b ASC) умеет backward scan по b. Смесь ORDER BY a, b DESC при возрастающем a уже может потребовать сортировки. Направление колонок в CREATE INDEX задают под реальный ORDER BY.