В каких случаях составной индекс на три колонки используется в базе данных?

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

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

Как отвечать

Возьмите B-tree индекс по (a, b, c). Он отсортирован сначала по a, внутри по b, внутри по c, как справочник по фамилии, имени и отчеству. Отсюда поведение:

• полностью: a = ? AND b = ? AND c = ?, причём порядок условий в WHERE не важен, планировщик их переставит;
• по префиксу: a = ? или a = ? AND b = ?, то есть любой левый префикс;
• равенство, затем диапазон: a = ? AND b = ? AND c > ? индекс обслужит целиком, а в a = ? AND b > ? AND c = ? поиск идёт по a и b, условие на c проверяется фильтром по записям индекса;
• сортировка: WHERE a = ? ORDER BY b, c отдаёт строки в нужном порядке без отдельной сортировки;
• покрытие: если запрос читает только a, b, c, PostgreSQL может сделать Index Only Scan и не ходить в таблицу.

Запросы по одному b, одному c или по паре b и c обычным поиском индекс не обслужит. Некоторые СУБД умеют skip scan (в PostgreSQL он появился в 18-й версии, в MySQL и Oracle есть давно), но он выгоден, только когда у a мало различных значений.

Правило порядка: сначала колонки с равенством, в конце колонка с диапазоном, и всё под реальные запросы. В Python-проекте индекс объявляется в Django через models.Index(fields=["a", "b", "c"]) в Meta.indexes или в SQLAlchemy через Index, а проверяется через QuerySet.explain() или EXPLAIN ANALYZE в psql.

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

Думают, что важен порядок условий в WHERE. Важен порядок колонок в самом индексе.

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

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