Порядок столбцов в составном индексе

Лена вошла в кабинет Макса и без предисловий развернула ноутбук. На этот раз в её глазах не было замешательства. Был азарт.

— Макс, смотри. Индекс по (user_id, status). Запрос: WHERE user_id = ? AND status = ?. План идеальный, Index Cond по обоим полям. Я меняю их местами в индексе — (status, user_id) — план почти не меняется. Логично. Но стоит мне поменять равенство на диапазон WHERE user_id = ? AND status > ? — и магия ломается. Индекс (user_id, status) справляется, а (status, user_id) — нет, улетает в Filter. Я, кажется, понимаю, почему. Но я не чувствую, почему.

Макс откинулся в кресле. Он смотрел не на экран, а на Лену. Это уже был вопрос не только о правилах, но о природе самых правил.

— Ты пытаешься думать об индексе как о наборе инструментов, — медленно начал он. — А нужно думать о нём как о языке. У каждого индекса есть своя грамматика. Свой синтаксис. Порядок столбцов — это его алфавит.

Он взял карандаш и лист бумаги.

— Представь, что индекс — это словарь. Индекс (фамилия, имя) — это идеально отсортированный справочник. Сначала по фамилии, потом, внутри каждой фамилии, по имени.

• WHERE фамилия = 'Иванов' AND имя = 'Пётр'. Ты открываешь секцию «И», находишь «Иванов», внутри неё — «Пётр». Это прямой путь. Index Cond по обоим полям.

• WHERE фамилия = 'Иванов'. Ещё проще. Находишь всех Ивановых. Index Cond по первому полю.

— Это очевидно, — кивнула Лена.

— А теперь, — Макс поднял палец, — WHERE имя = 'Пётр'. Что ты будешь делать с этим словарём?

Лена нахмурилась.

— Придётся… искать Петра внутри каждой фамилии.

— Вот. Такой словарь не даёт одного компактного диапазона только по имени. PostgreSQL может предпочесть Seq Scan, полный проход по индексу или, в новых версиях и подходящих условиях, skip scan. А теперь — самое интересное. WHERE фамилия > 'Иванов': ты находишь точку старта и читаешь дальше. Если попросить WHERE фамилия = 'Иванов' AND имя > 'Пётр', оба ключа задают узкий непрерывный диапазон.

Он вернулся к её примеру.

— Твой индекс (user_id, status) — это словарь по пользователям, а внутри каждого пользователя — по статусу. Он задаёт компактный диапазон для «дай статусы этого пользователя начиная с processed». Индекс (status, user_id) начинает с диапазона статусов. Условие по user_id может присутствовать в Index Cond, но после диапазона по первому ключу часто хуже сокращает объём чтения. Конкретно будет ли это Filter, обычный index scan или skip scan, покажет версия PostgreSQL и статистика.

Лена молчала, глядя в одну точку. Потом медленно произнесла:

— Порядок столбцов — это не просто приоритет. Это структура повествования. Мы заранее говорим базе, по какому сценарию будем запрашивать данные. (A, B) — это история про А, в которой есть детали про Б. А (B, A) — совсем другая история.

— Именно, — кивнул Макс. — Ты перестала искать «правильный» порядок. Ты начала думать о том, какую историю хочешь рассказать. И это уровень, на котором оптимизация становится искусством.

Что запомнить

• Порядок столбцов в индексе — это не приоритет, а лексикографический порядок, как в словаре.

• Равенства по ведущим столбцам и затем диапазон обычно задают самый компактный участок B-tree. Условие по последующему столбцу без ведущего часто слабее, но современные версии могут использовать skip scan — проверяйте EXPLAIN.

• Подходящий порядок может не только ускорить поиск, но и убрать отдельную сортировку, если направление, NULLS FIRST/LAST и ведущие условия совместимы с ORDER BY.