Есть ли разница в порядке атрибутов при создании составного индекса и как выбирать порядок

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

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

Как отвечать

Разница есть, и большая. Составной B-tree индекс отсортирован сначала по первой колонке, внутри неё по второй и так далее, как телефонный справочник по фамилии и имени. Отсюда правило левого префикса: индекс (a, b, c) эффективно работает для условий по a, по a и b, по всем трём, но не для запроса только по b.

Как выбирать порядок:

• сначала колонки с равенством: условия вида = и IN ставят впереди, они сужают поиск до одного участка индекса;
• потом сортировка или диапазон: колонка из ORDER BY сразу после равенств позволяет отдать строки без отдельной сортировки. Колонка с диапазоном (>, BETWEEN) обычно последняя из значимых: всё, что после неё, для поиска уже почти не используется;
• по реальным запросам: индекс (user_id, created_at) обслуживает и поиск по пользователю, и его ленту по дате, а индекс (created_at, user_id) для ленты почти бесполезен;
• селективность: важна для решения, строить ли индекс вообще, но правило «самую селективную колонку первой» вторично по отношению к типу условий.

Проверяйте выбор через EXPLAIN (ANALYZE, BUFFERS) в PostgreSQL. Колонки, нужные только в SELECT, можно добавить через INCLUDE, тогда запрос обойдётся без чтения таблицы.

В ClickHouse порядок колонок в ORDER BY таблицы MergeTree определяет разреженный первичный индекс и сжатие. Документация советует ставить колонки с низкой кардинальностью раньше: так лучше работает пропуск гранул и плотнее сжимаются данные.

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

Говорят, что порядок не важен, «база сама разберётся». Некоторые СУБД умеют skip scan по нелидирующей колонке, но он хорош, только если в первой колонке мало различных значений.

Ставят колонку с диапазоном первой, и условие равенства по второй уже не сужает поиск.

Заводят индексы (a, b) и (a) одновременно. Второй почти всегда лишний: его работу делает префикс первого, а обновлять при записи приходится оба.