Leading-column rule: почему порядок столбцов в composite-индексе важен

Как устроен B-tree внутри

Прежде чем проектировать composite-индексы правильно, нужно понять, что физически означает «отсортировано по (a, b, c)» — потому что именно физический layout делает правило неизбежным, а не произвол Postgres.

B-tree — сбалансированное дерево, где каждый узел — disk page (8KB) с отсортированным списком ключей. Для composite-индекса (a, b, c) ключ — это кортеж (a, b, c), отсортированный лексикографически: сначала по a, при равных a — по b, при равных (a, b) — по c.

Для поиска по индексу планировщик должен начать с leading-столбца. Только зная значение a, он может перейти к правой ветке дерева. Без значения a вся структура бесполезна — как искать в телефонной книге, зная только второе имя, но не фамилию.

Проектирование composite-индексов

Правило: leading-столбец должен быть столбцом, который присутствует в фильтре большинства горячих запросов. Затем вторичные столбцы — по убыванию частоты фильтрации.

Когда видишь, что индекс «должен работать», но EXPLAIN показывает Seq Scan — первым делом проверяй: запрос фильтрует по leading-столбцу или нет? Если нет — вот твой баг.

Пример: tenant-scoped дашборд

-- Запрос: список задач в проекте
SELECT id, title FROM tasks
WHERE workspace_id = $1 AND status = 'open'
ORDER BY created_at DESC;

-- Правильный индекс: workspace_id — tenant-граница (всегда присутствует)
CREATE INDEX ON tasks (workspace_id, status, created_at DESC);

-- Неправильный: status первым — только небольшая часть запросов фильтрует ТОЛЬКО по status
CREATE INDEX ON tasks (status, workspace_id, created_at DESC);

Один composite vs два single-column индекса

Один composite (a, b) обычно лучше двух single (a) и (b):

• Меньше overhead на запись (одна структура вместо двух).
• Меньше суммарный размер.
• Composite обслуживает запросы по a (использует только leading-часть) И запросы по a, b.

Два single-column нужны, когда: запросы часто фильтруют только по a ИЛИ только по b (не вместе). Тогда ни один composite не обслуживает оба.

Кардинальность не определяет порядок

Распространённое заблуждение: «ставь столбец с большей кардинальностью первым». Это неверно. Порядок определяется паттерном запросов:

• Если workspace_id (5 значений) всегда присутствует в фильтре, он идёт первым.
• Если user_id (1M значений) встречается в фильтре редко, он идёт последним или в отдельный индекс.

Единственный источник правды — EXPLAIN ANALYZE на реальных запросах с реалистичными параметрами.