Стратегия индексирования: составные, частичные, по выражению

Один столбец — один индекс часто недостаточно: грамотно собранный составной, частичный или покрывающий индекс ускоряет запрос в разы и при этом экономит место.

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

В первых уроках мы выбирали тип индекса и учились читать план. Теперь — как строить индексы умнее, чем «по одному столбцу на каждый WHERE». Реальные запросы фильтруют по нескольким полям сразу, часто работают лишь с подмножеством строк и нередко могут получить весь результат, вообще не заглядывая в таблицу. Под каждый из этих случаев у PostgreSQL есть приём.

Зачем это на практике

Правильно спроектированный индекс — это разница между запросом, который под нагрузкой укладывается в миллисекунду, и тем, что тормозит интерфейс. Составной индекс закрывает фильтр сразу по нескольким колонкам. Частичный индекс не тратит место на строки, по которым никогда не ищут. Покрывающий индекс позволяет ответить, не читая саму таблицу, — это часто кратное ускорение. Эти приёмы превращают индексирование из «повесить индекс на столбец» в инженерную работу под конкретную нагрузку.

Составной индекс и порядок столбцов

Составной (многоколоночный) индекс строится по нескольким столбцам сразу. Ключевой момент — порядок столбцов: он определяет, какие запросы индекс ускорит. Работает правило «слева направо» (правило префикса): индекс по (a, b) помогает фильтрам по a и по a + b, но не помогает фильтру только по b.

CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);

SELECT * FROM orders WHERE user_id = 42;                         -- индекс работает (префикс)
SELECT * FROM orders WHERE user_id = 42 AND created_at > '2026-06-01'; -- работает целиком
SELECT * FROM orders WHERE created_at > '2026-06-01';            -- индекс НЕ помогает

Аналогия — телефонный справочник, отсортированный сначала по фамилии, потом по имени. Найти всех «Ивановых» легко; найти всех «Сергеев» с любой фамилией — нет, придётся читать весь справочник. Отсюда практическое правило порядка: сначала столбцы, по которым идёт равенство (=), потом столбец диапазона (>, <, BETWEEN). Покажем идею на исполнимом примере — SQLite тоже соблюдает правило префикса.

CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, amount INTEGER, status TEXT);
INSERT INTO orders (user_id, amount, status) VALUES
  (42, 100, 'paid'), (42, 250, 'new'), (7, 80, 'paid'),
  (42, 60, 'paid'), (7, 300, 'new'), (42, 90, 'cancelled');

CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- фильтр по префиксу user_id + второму столбцу status — индекс применим
SELECT id, amount FROM orders WHERE user_id = 42 AND status = 'paid' ORDER BY id;

Вывод:

1|100
4|60

Если бы запросы фильтровали в основном по status без user_id, порядок столбцов в индексе стоило бы поменять местами.

Частичный индекс: индексируем только нужные строки

Частичный индекс (partial index) строится не по всей таблице, а только по строкам, удовлетворяющим условию WHERE в его определении. Он меньше, быстрее обновляется и применяется, когда запросы всегда касаются одного и того же подмножества.

-- 95% заказов давно завершены, а ищем мы почти всегда активные
CREATE INDEX idx_orders_active ON orders (created_at)
WHERE status IN ('new', 'processing');

-- индекс применится: его условие покрывает условие запроса
SELECT id FROM orders
WHERE status = 'new' AND created_at > '2026-06-01';

Если активных заказов 5% от таблицы, такой индекс в двадцать раз компактнее полного и не тратит ресурсы на индексирование «мёртвых» строк, которые всё равно никогда не попадают в выборку. Частичный индекс хорош и для уникальности с условием: например, «email уникален только среди не удалённых пользователей» — CREATE UNIQUE INDEX ... WHERE deleted_at IS NULL.

Индекс по выражению

Обычный индекс хранит значения столбца как есть. Но запросы часто ищут по результату функции: lower(email), date(created_at). Индекс по столбцу email для условия WHERE lower(email) = '...' бесполезен — в нём нет значений lower(email). Решение — индекс по выражению: он хранит вычисленный результат.

-- регистронезависимый поиск по email
CREATE INDEX idx_users_lower_email ON users (lower(email));

-- условие точно совпадает с выражением индекса — он сработает
SELECT id FROM users WHERE lower(email) = '[email protected]';

Главное условие: выражение в запросе должно дословно совпадать с выражением в индексе. WHERE lower(email) = ... попадёт в индекс lower(email), а WHERE email = ... — нет, это разные выражения. Эта тема напрямую связана со следующим уроком: именно функция на столбце чаще всего и ломает обычный индекс, а индекс по выражению — штатное лекарство.

Покрывающий индекс и Index Only Scan

Обычно Index Scan работает в два шага: найти строки в индексе, затем сходить в таблицу за остальными столбцами. Второй шаг — обращение к таблице — стоит времени. Если все нужные запросу столбцы есть в самом индексе, второй шаг не нужен: PostgreSQL отвечает прямо из индекса. Такой план называется Index Only Scan, а индекс, которому это под силу, — покрывающим (covering) запрос.

Чтобы добавить в индекс столбцы, которые нужны лишь «на выдачу», но не для поиска, есть INCLUDE: эти столбцы хранятся в индексе, но не участвуют в его сортировке.

-- ищем по user_id, а вернуть нужно amount и status
CREATE INDEX idx_orders_cover ON orders (user_id) INCLUDE (amount, status);

EXPLAIN SELECT amount, status FROM orders WHERE user_id = 42;

                              QUERY PLAN
----------------------------------------------------------------------
 Index Only Scan using idx_orders_cover on orders
     (cost=0.29..4.31 rows=4 width=12)
   Index Cond: (user_id = 42)

Узел Index Only Scan в плане — признак, что запрос покрыт: таблица не читается вообще. Это особенно ценно для частых лёгких запросов, где обращения к таблице составляют львиную долю стоимости.

Как это работает под капотом

Все эти приёмы опираются на устройство B-tree. Составной индекс хранит ключи, отсортированные сначала по первому столбцу, при равенстве — по второму, и так далее; поэтому по первому столбцу искать можно, а «перепрыгнуть» его и искать только по второму — нет, отсюда правило префикса. Частичный индекс физически содержит лишь строки, прошедшие его WHERE, — оттого он меньше. Индекс по выражению при каждой записи вычисляет выражение и кладёт в дерево уже результат, поэтому запрос обязан использовать ровно то же выражение. Index Only Scan возможен потому, что в индексе лежат все запрошенные столбцы, и PostgreSQL не нужно идти в таблицу за остальными; единственная тонкость — индексу приходится свериться с «картой видимости» (visibility map), чтобы убедиться, что версия строки актуальна, но это всё равно дешевле полноценного обращения к таблице.

Частые ошибки

• Неверный порядок столбцов в составном индексе. Индекс (a, b) не ускорит запрос только по b. Ставьте первыми столбцы равенства, диапазонный — последним.
• Несколько одноколоночных индексов вместо одного составного. Для условия a = ? AND b = ? один индекс (a, b) почти всегда лучше, чем два отдельных по a и по b.
• Условие запроса не совпадает с выражением индекса. Индекс lower(email) бесполезен для WHERE email = ... и наоборот — нужно дословное совпадение.
• Лишние INCLUDE-столбцы. Добавив в INCLUDE половину таблицы, вы раздуете индекс и потеряете смысл покрытия. Включайте только то, что реально нужно частому запросу на выдаче.

Итоги

• В составном индексе важен порядок: правило префикса (слева направо), сначала столбцы равенства, диапазонный — последним.
• Частичный индекс (... WHERE условие) индексирует только нужное подмножество строк — меньше размер, быстрее запись.
• Индекс по выражению (lower(email)) ускоряет поиск по результату функции; условие запроса должно дословно совпадать с выражением.
• Покрывающий индекс с INCLUDE даёт Index Only Scan — ответ прямо из индекса, без чтения таблицы.
• Проектируйте индексы под реальные запросы приложения, а не «по столбцу на каждый WHERE».