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

Мы знаем виды индексов и умеем читать план. Остался главный практический вопрос: какие индексы строить. Индекс на каждую колонку не ответ: он тормозит запись и съедает место. Ответ в том, чтобы строить индекс под запрос. Сегодня четыре приёма, которые закрывают большинство запросов в обычном приложении.

Телефонный справочник

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

Составной индекс устроен точно так же. Индекс по (customer_id, sold_on) отсортирован по покупателю, а внутри покупателя по дате.

Таблица продаж

Сгенерируем продажи с 500 покупателями, статусом заказа и почтой покупателя:

CREATE TABLE sales AS
SELECT g AS id,
       (g % 500) + 1 AS customer_id,
       (g % 9) + 1 AS book_id,
       date '2026-01-01' + (g % 280) AS sold_on,
       CASE WHEN g % 50 = 0 THEN 'new' ELSE 'delivered' END AS status,
       'user' || (g % 500) || '@Example.com' AS email
FROM generate_series(1, 100000) AS g;

ALTER TABLE sales ADD PRIMARY KEY (id);
ANALYZE sales;

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

Приём первый: составной индекс

Личный кабинет показывает покупки одного покупателя, свежие сверху. Построим индекс под этот запрос:

CREATE INDEX sales_customer_day ON sales (customer_id, sold_on);

EXPLAIN SELECT * FROM sales
WHERE customer_id = 42
ORDER BY sold_on DESC
LIMIT 5;

                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Limit  (cost=0.29..18.67 rows=5 width=44)
   ->  Index Scan Backward using sales_customer_day on sales  (cost=0.29..731.77 rows=199 width=44)
         Index Cond: (customer_id = 42)
(3 rows)

Один индекс сделал сразу две работы. Нашёл покупателя 42, и его покупки в индексе уже лежат по дате. Backward значит, что сервер прошёл их от конца к началу, ради DESC, и остановился на пятой строке. Сортировки в плане нет вовсе.

Тот же индекс помогает, когда к покупателю добавлен диапазон дат: WHERE customer_id = 42 AND sold_on >= date '2026-06-01'. А вот запрос только по дате, как поиск Петров в справочнике, работает плохо:

EXPLAIN (ANALYZE, BUFFERS OFF) SELECT * FROM sales WHERE sold_on = date '2026-03-01';

                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on sales  (cost=1178.38..1881.20 rows=356 width=44) (actual time=2.839..4.107 rows=357.00 loops=1)
   Recheck Cond: (sold_on = '2026-03-01'::date)
   Heap Blocks: exact=357
   ->  Bitmap Index Scan on sales_customer_day  (cost=0.00..1178.29 rows=356 width=0) (actual time=2.615..2.616 rows=357.00 loops=1)
         Index Cond: (sold_on = '2026-03-01'::date)
         Index Searches: 1
 Planning Time: 0.779 ms
 Execution Time: 4.952 ms
(8 rows)

Индекс вроде бы использован, но посмотри на стоимость нижнего узла: 1178 против 5.79 у поиска по покупателю. Сервер прочитал индекс целиком, от первой записи до последней, потому что индекс меньше таблицы. Это перелистывание всего справочника, только тонкого. В 18-й версии PostgreSQL научился ещё и перепрыгивать между значениями первой колонки, когда их немного, но рассчитывать на это не стоит.

Отсюда правило порядка колонок: первой ставь колонку, по которой ищут на равенство чаще всего. Колонку для диапазона или сортировки ставь после неё. Если по дате тоже часто ищут отдельно, ей нужен свой индекс.

Приём второй: частичный индекс

Склад каждые пять минут спрашивает, какие новые заказы появились за последний месяц. Индексировать статус всех ста тысяч строк ради двух процентов незачем. Частичный индекс берёт в себя только нужные строки:

CREATE INDEX sales_new ON sales (sold_on) WHERE status = 'new';

EXPLAIN SELECT * FROM sales WHERE status = 'new' AND sold_on >= date '2026-09-01';

                                  QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on sales  (cost=6.28..588.81 rows=258 width=44)
   Recheck Cond: ((sold_on >= '2026-09-01'::date) AND (status = 'new'::text))
   ->  Bitmap Index Scan on sales_new  (cost=0.00..6.21 rows=258 width=0)
         Index Cond: (sold_on >= '2026-09-01'::date)
(4 rows)

Частичный индекс занял 32 kB. Обычный индекс по status на той же таблице весит 704 kB, в 22 раза больше. И частичный индекс не замедляет запись доставленных заказов, которых большинство: их в индексе просто нет.

Условие нужно повторять в запросе. Без status = 'new' сервер не может пользоваться этим индексом, ведь в нём лежит не вся таблица.