Составные индексы PostgreSQL и правило левого префикса

← назад к разделу

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

Как устроен составной индекс

Представьте телефонную книгу, отсортированную по фамилии, внутри фамилии — по имени, внутри имени — по отчеству. Найти всех «Ивановых» легко. Найти всех «Иванов Александров» тоже легко — просто переходим к нужным «Ивановым». А вот найти всех «Александров» без фамилии придётся перелистать всю книгу целиком.

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

Это и есть правило левого префикса: индекс используется только если в условии есть крайнее левое поле (или несколько полей подряд от начала).

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

Для WHERE b = ? без a планировщик выберет Seq Scan, потому что в дереве нет точки входа по b.

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

Порядок условий в WHERE-запросе не влияет на использование индекса — оптимизатор PostgreSQL сам расставляет условия в нужном порядке.

-- индекс (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))   ← прошли через дерево индекса
Filter:     (c = 3)                  ← проверяется после чтения строк

Index Cond — это навигация по дереву. Filter — дополнительная проверка уже прочитанных строк.

Как выбрать порядок полей

Здесь работает три правила, которые применяются вместе.

Сначала — поля с равенством

Поля, по которым в большинстве запросов стоит =, ставятся первыми. Это даёт максимальную точность навигации по дереву.

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

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

Пример: таблица orders с фильтрами по status (5 значений, но в 90% запросов) и customer_id (миллион значений, но только в 10% запросов). Лучше создать (status, created_at) для основных запросов и отдельный (customer_id) для остальных, чем один сложный (customer_id, status, created_at).

Range-условия — последними

После range-условия (>, <, BETWEEN, LIKE 'prefix%') дерево перестаёт использоваться для следующих полей.

-- хорошо: 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 уходит в Filter
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')

Во втором случае PostgreSQL прочтёт все строки за нужный период, а потом отфильтрует по status — вместо того чтобы сразу попасть в нужную ветку дерева.

Порядок совпадает с ORDER BY

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

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

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

Если индекс создан с ASC, а запрос просит DESC, PostgreSQL может идти по индексу в обратную сторону (Index Scan Backward). Это работает, но чуть медленнее.

Дублирующие индексы — лишняя нагрузка

Если у вас уже есть индекс (a, b, c), отдельный индекс (a) не нужен: любой запрос, который использовал бы (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 — это не всегда быстро

Когда PostgreSQL использует Index Only Scan, кажется, что «индекс сработал». Но это не всегда означает быстрый запрос.

Пример: есть индекс (status, created_at) и запрос:

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

В EXPLAIN это выглядит так:

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

Здесь created_at — второе поле. Без условия по status PostgreSQL просматривает весь индекс и применяет Filter. Rows Removed by Filter: 8420134 — это 8 миллионов строк-пустышек. Это полный перебор индекса, а не точный поиск.

Для такого запроса нужен отдельный индекс (created_at) или (created_at, status).

Ещё один момент: Heap Fetches > 0 в Index Only Scan означает, что PostgreSQL всё же ходил в таблицу за частью строк — видимо, карта видимости (visibility map) устарела. Это лечится запуском VACUUM.

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

Обычный составной индекс хранит в дереве все свои поля. Иногда нужно добавить поля только для чтения — без влияния на порядок в дереве и без раздувания ключа. Для этого есть INCLUDE.

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

-- PostgreSQL может ответить на запрос только из индекса, без обращения к таблице
SELECT customer_id, status, created_at, total
FROM orders
WHERE customer_id = ?;

Поля из INCLUDE:

не влияют на сортировку в дереве;
хранятся только в листьях индекса;
позволяют избежать дополнительного чтения таблицы (heap fetch).

Такой индекс называют покрывающим (covering index). Это полезно для запросов-отчётов, которые читают фиксированный набор колонок по одному условию.

FK без индекса — скрытая ловушка

PostgreSQL не создаёт индекс по внешнему ключу автоматически. Это означает, что при удалении родительской строки PostgreSQL полностью перебирает дочернюю таблицу — чтобы проверить, нет ли ссылок.

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 = ? = полный перебор order_item
CREATE INDEX ix_order_item_order ON order_item (order_id);

На большой таблице order_item без индекса — удаление одной родительской строки может занять секунды и заблокировать другие операции. Добавить индекс по FK-колонке нужно всегда.

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

Обычный индекс хранит значения колонки как есть. Если запросы фильтруют по результату функции — например, lower(email) для регистронезависимого поиска — нужен функциональный индекс.

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

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

То же работает с COALESCE, EXTRACT, вычисляемыми выражениями. Главное условие: запрос должен использовать то же выражение, что и в определении индекса.

Коротко

Составной индекс (a, b, c) — это телефонная книга по a → b → c. Поиск без a = листать всё подряд.
Работает только на левый префикс: WHERE a работает, WHERE b — нет.
Порядок условий в WHERE не важен — оптимизатор переставит.
Сначала поля с =, потом range (>, <, BETWEEN). Range первым — остальные поля уйдут в Filter.
Если запрос часто сортирует — согласуй ORDER BY с порядком и направлением в индексе.
(a) при наличии (a, b, c) — лишний: занимает место, замедляет запись.
Index Only Scan с большим Rows Removed by Filter — это перебор всего индекса, не точный поиск.
INCLUDE — покрывающий индекс: дополнительные поля без раздувания ключа.
FK без индекса → полный перебор дочерней таблицы при удалении родителя.
Функциональный индекс нужен, если фильтруешь по lower(), COALESCE() и т.п. — запрос должен использовать то же выражение.

Что почитать дальше

Типы индексов PostgreSQL — B-tree, GIN, GiST, BRIN: когда какой выбрать.
Селективность и EXPLAIN ANALYZE — как PostgreSQL решает, использовать ли индекс.
VACUUM, autovacuum и bloat — почему Heap Fetches > 0 и как лечить.
Именование объектов — соглашение ix_<table>_<columns>.