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

Java · Go · Node · Python

Составные индексы PostgreSQL: почему индекс есть, но не работает, правило левого префикса, порядок полей (равенство → range → ORDER BY), покрывающий INCLUDE и FK без индекса.

Опубликовано 27 июня 2026 обновлено 25 сентября 2026 ~14 мин чтения

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

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

Индекс (status, created_at) — это сначала ветки по статусу, а внутри ветки записи по дате. Есть условие по status — вход сразу в нужную ветку и чтение подряд. Нет его — войти в нужную ветку не по чему, и дерево приходится пройти целиком, отбрасывая лишнее по дороге.

Обязательно

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

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

Составной 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 = ?`	то же самое

Причина в устройстве дерева: записи в нём упорядочены сначала по a, и без известного a войти в нужное место дерева невозможно. Что выберет планировщик дальше — вопрос стоимости. Иногда он всё же полезет в индекс и просмотрит его целиком, потому что индекс меньше таблицы; иногда честно прочитает таблицу. Оба варианта — перебор, разница только в том, что перебирается.

Отдельная оговорка про свежие версии. В PostgreSQL 18 у планировщика появился ещё и «пропускающий» проход по индексу, который перебирает значения a и по каждому ищет b, — он помогает, когда различных a немного.

Раскладку записей в индексе видно обычным запросом. На таблице заказов ORDER BY status, created_at даёт ровно тот порядок, в котором лежат записи индекса (status, created_at).

живой пример

SELECT status, created_at, total_amount
FROM orders
ORDER BY status, created_at
LIMIT 8;

Запустить

Запуск примеров доступен в платном доступе. Там этот же код выполняется прямо в статье: редактор, запуск и проверка рядом с абзацем. Три дня бесплатно →

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

живой пример

SELECT status, created_at
FROM orders
WHERE created_at >= '2026-08-10 00:00:00'
  AND created_at < '2026-08-15 00:00:00'
ORDER BY status, created_at;

Запустить

Подходящих строк пять, и все пять в разных статусах. В дереве они лежат в пяти разных местах — собрать их можно, только пройдя по всем группам.

Порядок в 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 видно разницу между условием, которое проверяется по индексу, и условием-фильтром. Делит их не порядок в запросе, а то, входит колонка в индекс или нет. Вот план запроса WHERE a = 1 AND b = 2 AND d = 3 по тому же индексу (a, b, c) — колонки d в нём нет:

Index Cond: ((a = 1) AND (b = 2))   ← проверено по записям индекса
Filter:     (d = 3)                  ← колонки d в индексе нет
Rows Removed by Filter: 19999        ← столько строк прочитано из таблицы зря

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 > ...)

-- плохо: диапазон первым, точка входа теряется
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 > ...) AND (status = 'NEW')

Обе строки условия попали в Index Cond, и на первый взгляд разницы нет. Но во втором случае база вынуждена пройти по индексу все записи за нужный период и у каждой проверить status — а это может быть миллион записей ради тысячи подходящих. В первом варианте она сразу входит в участок дерева, где лежат только заказы со статусом NEW, и читает оттуда ровно нужный кусок по дате. Увидеть разницу помогает EXPLAIN (ANALYZE, BUFFERS): на одних и тех же данных правильный порядок полей прочитал 8 страниц, неправильный — 27.

Порядок совпадает с 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) — по скорости это то же самое, что и прямой проход, отдельной сортировки не появится.

Важна другая оговорка: обратный проход выручает, только когда перевёрнуты все поля сразу. Запросу ORDER BY user_id, created_at DESC индекс (user_id, created_at) порядок уже не даёт — направления разные, ни прямой проход, ни обратный под такой порядок не подходят, и в плане появляется досортировка (Incremental Sort). Для смешанных направлений направление пишут прямо в индексе: (user_id, created_at DESC).

Все три правила сходятся на одном запросе, и его стоит разобрать целиком. Запрос ленты заказов продавца:

живой пример

SELECT id, customer_id, total_amount, created_at
FROM orders
WHERE seller_id = 'sel-01'
  AND status = 'PAID'
  AND created_at >= timestamptz '2026-08-01'
ORDER BY created_at DESC
LIMIT 20;

Запустить

Равенств два (seller_id, status), диапазон один (created_at), сортировка по нему же. Значит, порядок колонок такой: сначала оба равенства, потом колонка диапазона, она же колонка сортировки — (seller_id, status, created_at DESC). База найдёт по дереву участок, где продавец и статус нужные, пойдёт по нему в порядке убывания даты и остановится после двадцатой строки: ни сортировки, ни лишних чтений.

Поменяйте порядок на (created_at DESC, seller_id, status) — и от индекса останется только диапазон по дате: придётся прочитать все заказы за месяц, отфильтровать по продавцу и статусу, а потом ещё отсортировать. Тот же набор колонок, другой порядок, разница в работе — на порядки.

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

Если у вас уже есть индекс (a, b, c), отдельный индекс (a) чаще всего не нужен: любой запрос, который использовал бы (a), точно так же использует (a, b, c) по правилу левого префикса.

Дублирующие индексы:

занимают лишнее место на диске;
замедляют INSERT, UPDATE, DELETE — каждая операция обновляет все индексы по таблице;
сбивают с толку планировщик.

Оговорки всё-таки есть, и прежде чем сносить узкий индекс, стоит посмотреть, зачем он заведён. Индекс по одной колонке заметно меньше составного: там, где ответ берут прямо из индекса (Index Only Scan, подсчёт строк), читать придётся меньше страниц. А UNIQUE (a) составным индексом не заменить вовсе — уникальность тройки (a, b, c) разрешает сколько угодно повторов a.

Готового ответа «вот эти два индекса дублируют друг друга» база не даёт — список придётся просмотреть глазами. Запрос ниже выкладывает индексы таблица за таблицей, а внутри таблицы — по составу колонок, так что индексы с одинаковым началом окажутся рядом:

живой пример

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;

Запустить

Есть и вторая сторона счёта — сколько индексов на таблицу считать нормой. Каждая вставка пишет во все индексы, поэтому таблица с восемью индексами принимает INSERT заметно медленнее той же таблицы с двумя; UPDATE платит за каждый индекс, где участвует изменённая колонка. Ориентир для таблицы с активной записью — не больше пяти-шести, и каждый должен отвечать хотя бы на один реальный запрос. Проверяется это счётчиками: pg_stat_user_indexes с idx_scan = 0 за пару недель — кандидат на удаление.

И про то, как их вообще добавлять на живой базе: обычный CREATE INDEX держит блокировку, которая не пускает запись в таблицу на всё время построения. На боевой таблице это делают только через CREATE INDEX CONCURRENTLY и вне транзакции — со всеми оговорками про невалидный индекс после неудачи, которые разобраны в статье про типы индексов.

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  (actual rows=26000)
  Index Cond: (created_at > '2026-06-20')
  Heap Fetches: 0
  Buffers: shared hit=270

Здесь created_at — второе поле. Без условия по status войти в нужное место дерева нельзя, поэтому PostgreSQL проходит индекс целиком и проверяет условие на каждой записи. Что это был перебор, показывает Buffers: 270 страниц против восьми у того же запроса, где есть условие и по status. Поэтому EXPLAIN здесь запускают как EXPLAIN (ANALYZE, BUFFERS) — без BUFFERS разницу не увидеть.

Заодно обратите внимание: строки Rows Removed by Filter в таком плане нет. Когда колонка входит в индекс, PostgreSQL проверяет условие прямо по записям индекса и пишет его в Index Cond — даже если проверять приходится всё подряд. Строка Filter появляется для условий по колонкам, которых в индексе нет: тогда база сначала лезет за строкой в таблицу и только потом проверяет, а отброшенные строки считает в Rows Removed by Filter.

Для такого запроса нужен отдельный индекс (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_amount);

-- PostgreSQL может ответить на запрос только из индекса, без обращения к таблице
SELECT customer_id, status, created_at, total_amount
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-колонке нужно всегда.

DELETE — не единственный случай. Точно так же проверка нужна при UPDATE родительского ключа: база обязана убедиться, что на старое значение никто не ссылается. И проверка эта идёт не «когда-нибудь», а прямо в транзакции, удерживая блокировку на родительской строке всё время перебора дочерней таблицы, — то есть пока один процесс минуту читает миллионы строк в поисках ссылок, остальные ждут эту строку. Отсюда типичная картина: «удаление одного клиента кладёт сервис на минуту», а в pg_stat_activity очередь на блокировке.

Обратная сторона правила тоже есть: индекс под внешний ключ нужен не всегда. Если родительские строки не удаляют и ключ не меняют (справочник валют, статусов), проверять нечего, и лишний индекс только замедляет запись. Но это надо решить осознанно, а не по умолчанию.

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

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

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

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

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

Дополнительно: при первом чтении можно пропустить

Глубже: пагинация: OFFSET против keysetрасширенное

LIMIT 20 OFFSET 100000 выглядит как «пропустить сто тысяч строк», а работает как «прочитать сто тысяч и выбросить». Индекс тут не помогает: база всё равно проходит по индексу сто тысяч записей, чтобы найти начало страницы, и чем дальше страница, тем медленнее запрос. Плюс страницы плывут: пока пользователь листает, добавилась строка, и элемент с границы показан дважды.

Keyset-пагинация листает не по номеру, а по ключу: клиент запоминает последнюю строку страницы и просит следующие после неё.

SELECT id, created_at, total_amount
FROM orders
WHERE customer_id = $1
  AND (created_at, id) < ($2, $3)     -- значения последней строки прошлой страницы
ORDER BY created_at DESC, id DESC
LIMIT 20;

С составным индексом (customer_id, created_at DESC, id DESC) база сразу встаёт на нужное место и читает ровно двадцать строк, хоть на тысячной странице. Условие на пару (created_at, id) нужно, потому что даты повторяются, и без уникального «хвоста» строки с одинаковой датой могут потеряться на границе. UUID v7 из статьи про идентификаторы годится как такой ключ сам по себе: в нём есть время, и листать можно по одному полю.

Цена keyset: нет перехода на произвольную страницу и «страница 7 из 40». Обычно это и не нужно: ленты, списки заказов и логи листают «дальше», а сколько всего, считают отдельным запросом или не считают вовсе.

Коротко

Составной индекс (a, b, c) — телефонная книга по a → b → c: работает только левый префикс, поиск без a = листать всё подряд.
Порядок условий в WHERE не важен, оптимизатор их переставит; важен порядок полей в самом индексе.
Сначала поля с =, потом range (>, <, BETWEEN): после range дерево перестаёт сужать поиск по следующим полям.
Часто сортируете — согласуйте ORDER BY с порядком и направлением полей, иначе в плане появится отдельный Sort.
(a) при наличии (a, b, c) обычно лишний: занимает место и замедляет запись — но не когда он UNIQUE или когда важен его меньший размер. А Index Only Scan сам по себе ничего не доказывает — цену перебора показывает Buffers в EXPLAIN (ANALYZE, BUFFERS).
INCLUDE даёт покрывающий индекс; FK без индекса → перебор дочерней таблицы при удалении родителя; функциональный индекс нужен для lower() и подобных, и запрос должен использовать то же выражение.
OFFSET читает и выбрасывает все пропущенные строки; keyset листает по ключу (created_at, id) < (…) с тем же составным индексом и не замедляется на дальних страницах.
Три правила порядка работают вместе: сначала колонки равенства, потом диапазон, потом сортировка — и тогда один индекс закрывает фильтр, порядок и LIMIT.
Индексов на таблице с активной записью держат пять-шесть, лишние ищут по idx_scan = 0; на живой базе их создают только CONCURRENTLY.
Индекс под внешний ключ нужен и для UPDATE родительского ключа, а не только для DELETE: проверка идёт под блокировкой родительской строки.

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

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