PostgreSQL: composite-индексы и левый префикс

Самая частая ошибка с индексами — не «индекса нет», а «индекс есть, но не работает в этом запросе». Причина почти всегда — порядок полей в составном индексе.

1. Главное правило: левый префикс

Составной B-tree-индекс (a, b, c) физически отсортирован по a, внутри каждого значения a — по b, внутри каждого b — по c. Это телефонная книга по фамилии-имени-отчеству.

PG-I-010 Индекс работает только на левый префикс полей.

Индекс (a, b, c):

Запрос	Использует индекс?
WHERE a = ?	да, эффективно
WHERE a = ? AND b = ?	да
WHERE a = ? AND b = ? AND c = ?	да
WHERE a = ? AND c = ?	по a — да, c доводит как Filter
WHERE b = ?	нет (или полный проход индекса)
WHERE c = ?	нет (или полный проход индекса)
WHERE b = ? AND c = ?	нет

Для WHERE b = ? или WHERE c = ? планировщик обычно выберет Seq Scan — индекс не подходит. В редких случаях — Index Only Scan, но это будет полный проход индекса (см. ниже).

2. Порядок написания в WHERE не важен

PG-I-011 Оптимизатор сам переставляет условия WHERE. Порядок в SQL — косметика.

-- индекс (a, b, c)
WHERE a = 1 AND b = 2 AND c = 3   -- использует индекс
WHERE c = 3 AND b = 2 AND a = 1   -- использует индекс точно так же
WHERE b = 2 AND a = 1             -- использует индекс (как WHERE a = 1 AND b = 2)

В EXPLAIN:

Index Cond: ((a = 1) AND (b = 2) AND (c = 3))    -- ушли в индекс
Filter:     (c = 3)                                -- осталось пост-фильтром

Index Cond — то, что используется деревом для поиска. Filter — то, что проверяется уже после чтения строк.

3. Правила выбора порядка полей

PG-I-012 Первым ставим поле, по которому чаще всего идёт WHERE с равенством (=).

Это даёт максимум селективности через дерево.

PG-I-013 Поля с диапазонами (>, <, BETWEEN, LIKE 'x%') — последними.

После range-условия следующие поля индекса перестают использоваться как ключ:

CREATE INDEX ix_orders_status_created ON orders (status, created_at);

-- хорошо: status = (равенство) → created_at > (диапазон)
WHERE status = 'NEW' AND created_at > now() - interval '1 day'
-- Index Cond: (status = 'NEW') AND (created_at > ...)

CREATE INDEX ix_orders_created_status ON orders (created_at, status);
-- плохо: range по created_at отрубает использование status
WHERE status = 'NEW' AND created_at > now() - interval '1 day'
-- Index Cond: (created_at > ...)
-- Filter:     (status = 'NEW')   ← seq-фильтр после range-сканирования

PG-I-014 ORDER BY — порядок и направление полей должны совпадать с индексом, иначе сортировка делается отдельно.

CREATE INDEX ix_msg_user_at ON messages (user_id, created_at DESC);

-- индекс используется и для фильтра, и для сортировки:
SELECT * FROM messages WHERE user_id = ? ORDER BY created_at DESC LIMIT 20;

-- сортировка не «бесплатная»: индекс ASC по created_at, запрос DESC:
CREATE INDEX ix_msg_user_at_asc ON messages (user_id, created_at);
SELECT * FROM messages WHERE user_id = ? ORDER BY created_at DESC LIMIT 20;
-- Index Scan Backward — работает, но только если все ORDER BY-поля идут в одной развертке.

4. Селективность — вторично

«Самое селективное поле — первым» — частый совет, который вводит в заблуждение. Правильнее: первым то, по чему чаще всего идёт =, и только потом — выбор по селективности среди равно-частых.

Пример: на orders (status, created_at, customer_id):

• по status — равенство, низкая селективность (всего 5 значений), но фильтр почти в каждом запросе.
• по customer_id — равенство, высокая селективность, но реже в WHERE.

Если 90% запросов — WHERE status = 'NEW' AND created_at > ? и только 10% — WHERE customer_id = ? — индекс (status, created_at) плюс отдельный (customer_id) лучше, чем один сложный (customer_id, status, created_at).

5. Дублирующиеся индексы

PG-I-015 Не создавайте (a), если уже есть (a, b, c) — это покрывает запросы по a.

Дубликаты:

• занимают место;
• замедляют INSERT/UPDATE/DELETE (каждая запись обновляет каждый индекс);
• путают планировщика — он может выбрать неоптимальный.

Проверка:

SELECT indexrelname, indrelid::regclass, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_index
JOIN pg_stat_user_indexes USING (indexrelid)
ORDER BY indrelid, indkey;

Глазами пройтись по группам с одинаковым indrelid и общим префиксом indkey — кандидаты на удаление.

6. Ловушка с count(1) и Index Only Scan

Запрос:

SELECT count(1) FROM orders WHERE created_at > now() - interval '7 days';

И индекс (status, created_at). По правилу левого префикса этот индекс не должен помочь — created_at второе поле, нет status в WHERE.

Тем не менее в EXPLAIN вы увидите:

Index Only Scan using ix_orders_status_created on orders
  Filter: (created_at > '2026-04-30')
  Rows Removed by Filter: 8420134
  Heap Fetches: 0

PG-E-010 Это не «индекс работает». Это Index Only Scan — планировщик выбрал полный проход индекса, потому что:

1. count(1) не требует данных из таблицы — нужен только факт строки.
2. Индекс содержит обе колонки (status, created_at) — можно ответить на запрос, не трогая heap.
3. Полный проход индекса дешевле полного Seq Scan по таблице (индекс физически меньше).

Признаки в плане:

• Filter: (не Index Cond:) по основному фильтрующему полю — индекс не используется деревом.
• Heap Fetches: 0 — таблица не читалась.
• Rows Removed by Filter — большое число, потому что просканировали весь индекс.

PG-E-011 Если на этом запросе нужна реальная скорость — нужен индекс по created_at (или (created_at, status)). Тогда план превратится в Index Scan с Index Cond: (created_at > ...) — будут читаться только нужные страницы индекса, а не все.

PG-E-012 Если Heap Fetches > 0 на Index Only Scan — устаревший visibility map. Помогает VACUUM. Без актуального VM Index Only Scan превращается в обычный Index Scan с дёрганьем таблицы — может оказаться медленнее Seq Scan.

7. Покрывающий индекс (INCLUDE)

PG-I-016 Если по индексу часто читают одни и те же дополнительные колонки — INCLUDE ускоряет, не раздувая ключ.

CREATE INDEX ix_orders_customer_inc
    ON orders (customer_id) INCLUDE (status, created_at, total);

-- запрос ниже выполняется как Index Only Scan, без обращения к таблице:
SELECT customer_id, status, created_at, total
FROM orders
WHERE customer_id = ?;

Колонки в INCLUDE не участвуют в дереве (не влияют на сортировку), но хранятся в листьях. Полезно для отчётов и денормализованных view.

8. Индексы на foreign key

PG-I-017 PostgreSQL не строит индекс по FK автоматически. Если по FK идут джойны / удаления родителя — индекс нужен.

Без индекса: DELETE FROM parent WHERE id = ? запускает seq-scan по child-таблице, чтобы проверить ON DELETE правило. На большой child — это секунды-минуты блокировки.

CREATE TABLE order_item (
    id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id bigint NOT NULL REFERENCES order_doc(id) ON DELETE CASCADE
);

CREATE INDEX ix_order_item_order ON order_item (order_id);

9. Функциональный индекс

PG-I-018 Если регулярно фильтруют по выражению (LOWER(...), COALESCE(...), EXTRACT(...)) — функциональный индекс.

CREATE INDEX ix_account_email_lower ON account (lower(email));

-- запросы должны точно совпадать с выражением индекса:
SELECT * FROM account WHERE lower(email) = lower('IVAN@EXAMPLE.COM');

Альтернатива — citext (см. Строки).

10. Создание индекса в продакшене — CONCURRENTLY

PG-I-019 В продакшен-миграциях — всегда CREATE INDEX CONCURRENTLY и DROP INDEX CONCURRENTLY.

Без CONCURRENTLY CREATE INDEX берёт SHARE lock на таблицу — блокирует все INSERT/UPDATE/DELETE до конца построения. На больших таблицах — десятки минут даунтайма.

CONCURRENTLY строит индекс в два прохода без блокировки записи. Минусы:

• нельзя в транзакции (Liquibase: runInTransaction="false");
• если индекс сломался при построении (INVALID), нужно явно дропнуть и повторить;
• занимает в 2–3 раза больше времени, чем без CONCURRENTLY (но не блокирует).

Подробно — в статье про миграции (третья волна).

---

Чек-лист на ревью

• [ ] Порядок полей соответствует частоте запросов: =-поля впереди, range/sort — последними.
• [ ] Нет дубликатов: (a) поверх (a, b, c).
• [ ] FK покрыт индексом, если по нему джойнят / удаляют родителя.
• [ ] Регулярные выражения (LOWER, COALESCE) обёрнуты функциональным индексом.
• [ ] В продакшен-миграциях — CREATE INDEX CONCURRENTLY.
• [ ] Подозрительные Index Only Scan с Rows Removed by Filter в десятки тысяч — пересмотреть индекс.
• [ ] Heap Fetches > 0 на Index Only Scan — запустить VACUUM.

---

Связанные

• Числа и точность — bigint vs int для PK / FK.
• UUID и идентификаторы — почему UUID v7 лучше для btree-индекса.
• Антипаттерны.