Composite-индексы и левый префикс — порядок колонок

Опирается на правила: PG-I-010 … PG-I-018 из PostgreSQL Style Guide → раздел Composite-индексы и левый префикс.

Важно знать

• Левый префикс: индекс (a, b, c) работает на WHERE a, WHERE a AND b, WHERE a AND b AND c.
• WHERE b или WHERE c — индекс не работает (Seq Scan).
• Порядок в WHERE не важен — оптимизатор переставляет.
• Первым = (равенство), последним — range (>, <, BETWEEN).
• ORDER BY — порядок и направление полей должны совпадать с индексом.
• Дубликаты: (a) после (a, b, c) — не нужен, лишняя нагрузка.
• Index Only Scan на count(*) — не «индекс работает», а полный проход индекса.
• INCLUDE — покрывающий индекс без раздувания ключа.
• FK без индекса — DELETE родителя = seq-scan дочерней.

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

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

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

Составной B-tree (a, b, c) физически отсортирован по a, внутри каждого a — по b, внутри каждого 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 = ? без a — планировщик выберет Seq Scan.

Порядок в WHERE не важен

PG-I-011: оптимизатор сам переставляет.

-- индекс (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 — проверяется после чтения строк.

Правила порядка полей

PG-I-012..014:

Первым — самое частое равенство

-- Если 90% запросов фильтруют по status:
CREATE INDEX ix_orders_status_created ON orders (status, created_at);

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

Range — последними

PG-I-013: после range следующие поля не используются как ключ.

-- ✓
CREATE INDEX ix_orders_status_created ON orders (status, created_at);
WHERE status = 'NEW' AND created_at > now() - interval '1 day'
-- Index Cond: (status = 'NEW') AND (created_at > ...)

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

ORDER BY совпадает с индексом

PG-I-014:

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, а запрос DESC — Index Scan Backward (работает, но медленнее)

Селективность вторична

«Самое селективное первым» — частый совет, который вводит в заблуждение.

Правильнее: первым то, по чему чаще всего идёт =, потом — селективность.

Пример: orders с фильтрами по status (5 значений, низкая селективность, но в 90% запросов) и customer_id (миллион значений, высокая селективность, в 10% запросов).

Лучше:

• (status, created_at) для основных запросов.
• Отдельный (customer_id) для оставшихся.

Чем один сложный (customer_id, status, created_at).

Дубликаты

PG-I-015: не создавайте (a), если есть (a, b, c).

Дубликаты:

• Занимают место.
• Замедляют 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;

Index Only Scan на count(*)

PG-E-010: не «индекс работает» — полный проход индекса.

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

С индексом (status, created_at) created_at второе поле. 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

Признаки полного прохода:

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

PG-E-011: для реальной скорости нужен (created_at) или (created_at, status).

PG-E-012: Heap Fetches > 0 на Index Only Scan — устаревший visibility map. Помогает VACUUM.

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

PG-I-016:

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:

• Не участвуют в дереве (не влияют на сортировку).
• Хранятся в листьях.
• Доступны без heap fetch.

Полезно для отчётов и денормализованных view.

FK обязательно индексируем

PG-I-017: PG не строит индекс по FK автоматически.

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

-- Без этого DELETE FROM order_doc WHERE id = ? = seq-scan на order_item
CREATE INDEX ix_order_item_order ON order_item (order_id);

Без индекса на большой child-таблице — секунды-минуты блокировки при удалении родителя.

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

PG-I-018:

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

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

Для регулярных фильтров по выражению: LOWER, COALESCE, EXTRACT, etc.

Что запрещено

Антипаттерн	Правило	Что взамен
Composite-индекс без анализа левого префикса	PG-I-010	начать с самого частого =
Range первым в composite	PG-I-013	range последним
(a) при наличии (a, b, c)	PG-I-015	удалить
FK без индекса	PG-I-017	CREATE INDEX ix_<child>_<fk>
LOWER(email) без functional index	PG-I-018	functional index
INCLUDE для всех select-columns	PG-I-016	только часто читаемых
Доверять Index Only Scan без анализа Filter	PG-E-010	проверять Index Cond:
Heap Fetches > 0 после миграции	PG-E-012	VACUUM/autovacuum

Куда дальше

• PG → Composite-индексы — нормативные формулировки.
• Типы индексов — какой тип брать.
• Как выбрать индекс и считать селективность — критерии.
• EXPLAIN ANALYZE — проверить план.
• VACUUM, autovacuum и bloat — visibility map.
• Именование объектов — ix_<table>_<columns>.
• UUID и идентификаторы — UUID FK обязательно индекс.