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

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

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

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

Составной 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 немного.

Порядок в 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 > ...)

-- плохо: диапазон первым, точка входа теряется
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, и читает оттуда ровно нужный кусок по дате. Увидеть разницу в плане можно по строке Rows Removed by Filter: чем она больше, тем больше лишней работы.

Порядок совпадает с 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
  Index Cond: (created_at > '2026-06-20')
  Rows Removed by Filter: 8420134
  Heap Fetches: 0

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

Заодно обратите внимание на Index Cond вместо Filter. Когда колонка входит в индекс, PostgreSQL проверяет условие прямо по записям индекса и пишет его в Index Cond — даже если проверять приходится всё подряд. Строка Filter появляется для условий по колонкам, которых в индексе нет: тогда база сначала лезет за строкой в таблицу и только потом проверяет. Разница заметна в цене: 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);

-- 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() и т.п. — запрос должен использовать то же выражение.

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