Воркер Brew раз в секунду спрашивал «дай мне следующие необработанные заказы»: WHERE status = 'pending' ORDER BY id LIMIT 100. Заказов в статусе pending всегда мало — десятки из миллиона, остальное давно done. Но обычный индекс по status индексировал все строки, включая бесполезный миллион done: он был огромным, занимал память и медленно обновлялся на каждой смене статуса. А рядом дашборд клиента тянул SELECT customer_id, total ... WHERE customer_id = ? — и на каждую строку индекс отправлял базу ещё и в саму таблицу за полем total, хотя по сути нужно было лишь два числа.

Оба сценария — про то, что обычный индекс берёт избыточно: лишние строки или лишние походы в таблицу. Цель юнита — три индекса, которые берут ровно столько, сколько надо: частичный (только нужные строки), покрывающий (несёт лишние столбцы в себе → Index-Only Scan, без похода в таблицу) и уникальный (заодно гарантирует уникальность).

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

CREATE INDEX ... WHERE <условие> строит индекс только по строкам, удовлетворяющим условию. Для «очереди pending» это идеально: индекс по (id) WHERE status = 'pending' содержит лишь pending-строки — он в десятки раз меньше полного, быстрее обновляется (строки done его вообще не трогают) и обслуживает горячий запрос напрямую.

Запрос пользуется частичным индексом, если его WHERE влечёт условие индекса. WHERE status = 'pending' ORDER BY id идёт по нашему индексу; WHERE status = 'done' — нет (другие строки, их в индексе просто нет). Частичные индексы хороши именно там, где запрос всегда смотрит на узкое подмножество: активные записи, неопубликованное, неоплаченное.

Покрывающий индекс: Index-Only Scan без похода в таблицу

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

INCLUDE добавляет в индекс «полезную нагрузку» — столбцы, по которым не ищут, но которые хотят прочитать:

CREATE INDEX orders_lab_cust_cover_idx ON orders_lab (customer_id) INCLUDE (total);

Ключ — customer_id (по нему ищем и сортируем), total лежит рядом «прицепом». Запрос SELECT customer_id, total WHERE customer_id = ? полностью покрывается индексом → Index-Only Scan, Heap Fetches: 0 (ни одного похода в таблицу). Heap Fetches — это и есть счётчик «сколько раз всё-таки пришлось идти в heap».

⚠️ Heap Fetches: 0 достигается не магией индекса, а картой видимости (visibility map): Index-Only Scan может пропустить heap только для страниц, помеченных «all-visible», а их помечает VACUUM. На свежезаписанной таблице до VACUUM тот же план покажет Heap Fetches > 0. Поэтому в демо мы зовём VACUUM перед замером — в проде это делает автовакуум.

Уникальный индекс

Уникальный индекс (CREATE UNIQUE INDEX, или то, что Postgres строит под UNIQUE-констрейнтом и PRIMARY KEY) служит сразу двум целям: гарантирует уникальность (вставка дубликата падает с 23505) и одновременно ускоряет поиск по равенству — это обычный B-tree, по которому идёт Index Scan. То есть UNIQUE (email) — это не только правило целостности из модуля 02, но и готовый индекс под WHERE email = ?; отдельный индекс по email рядом был бы дубликатом.

Один шаг или два: куда ходит индекс

Обычный Index Scan работает в два шага; покрывающий индекс убирает второй:

Обычный Index Scan — два шага:
  [ индекс (ключ) ] —найти строки→ [ таблица (heap) ] —дочитать остальные столбцы→ ответ
 
Index-Only Scan — все нужные столбцы уже в индексе, второй шаг отпадает:
  [ индекс: ключ + INCLUDE ] —ответ собран прямо здесь→ ✓   Heap Fetches: 0
  в heap НЕ ходим, если страница all-visible по карте видимости (её ставит VACUUM)

Три формы индекса — каждая берёт ровно столько, сколько надо:

Что показывает наш код

demo.sql строит orders_lab (200 000 заказов, 1% в статусе pending) и показывает:

-- A) частичный индекс по pending — крошечный и обслуживает очередь
CREATE INDEX orders_lab_pending_idx ON orders_lab (id) WHERE status = 'pending';
-- размер: 64 kB против 4408 kB у полного индекса (PK по всем строкам)
SELECT id, total FROM orders_lab WHERE status = 'pending' ORDER BY id LIMIT 5;   -- Index Scan
 
-- B) покрывающий индекс → Index-Only Scan
CREATE INDEX orders_lab_cust_cover_idx ON orders_lab (customer_id) INCLUDE (total);
VACUUM (ANALYZE) orders_lab;
SELECT customer_id, total FROM orders_lab WHERE customer_id = 777;   -- Index Only Scan, Heap Fetches: 0

Запуск

docker compose up -d
make lecture L=06-indexing-and-explain/06-04-partial-covering-and-unique

Вывод:

== A1) частичный индекс много меньше полного (PK по всем строкам) ==
 full_pk_idx | partial_idx | partial_is_smaller 
-------------+-------------+--------------------
 4408 kB     | 64 kB       | t
 
 
== A2) частичный индекс обслуживает "разгрести pending по порядку id" ==
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Limit (actual rows=5.00 loops=1)
   ->  Index Scan using orders_lab_pending_idx on orders_lab (actual rows=5.00 loops=1)
         Index Searches: 1
 
 
== B) покрывающий индекс INCLUDE → Index Only Scan, Heap Fetches: 0 ==
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Index Only Scan using orders_lab_cust_cover_idx on orders_lab (actual rows=200.00 loops=1)
   Index Cond: (customer_id = 777)
   Heap Fetches: 0
   Index Searches: 1

Частичный индекс по pending — 64 kB против 4408 kB у полного индекса по id: он держит только 1% строк (partial_is_smaller = t) и обслуживает запрос очереди обычным Index Scan. Покрывающий индекс с INCLUDE (total) отдаёт customer_id и total прямо из индекса: Index Only Scan, Heap Fetches: 0 — в таблицу не ходили ни разу.

Заборчик

Что мы упростили:

• Heap Fetches: 0 держится на свежем VACUUM. В живой таблице между вакуумами часть страниц «грязные», и Index-Only Scan всё равно подныривает в heap — выигрыш реальный, но не абсолютный, и зависит от частоты автовакуума, которую настраивает твой DBA.
• INCLUDE раздувает индекс (несёт лишние столбцы) — это разменивает диск и скорость записи на скорость одного запроса; класть в INCLUDE всё подряд не стоит.
• Частичный индекс выгоден, только если запросы реально смотрят в его подмножество. Разойдись условие индекса и условие запроса хоть немного — индекс не возьмётся; это надо проверять в EXPLAIN.
• «Какой индекс окупается под нагрузкой», ловля неиспользуемых (pg_stat_user_indexes), баланс «лишний индекс vs запись» в большой системе — это сопровождение, которое ведёт DBA.

Твоя задача — подобрать форму индекса под форму запроса: узкое подмножество → частичный, «нужно только пару столбцов» → покрывающий, «должно быть уникально» → уникальный.

Что забрать с собой

• Частичный индекс (... WHERE условие) индексирует только нужные строки — он мал, быстро обновляется и идеален для «активных»/«очереди»; запрос должен влечь условие индекса.
• Покрывающий индекс (... INCLUDE (cols)) несёт читаемые столбцы в себе → Index-Only Scan без похода в таблицу (Heap Fetches: 0).
• Heap Fetches: 0 обеспечивает карта видимости, а её выставляет VACUUM — до вакуума будут походы в heap.
• Уникальный индекс (UNIQUE/PK) одновременно гарантирует уникальность и ускоряет поиск по равенству — отдельный индекс рядом был бы дубликатом.
• INCLUDE раздувает индекс, частичный — экономит: и то и другое разменивает место/запись на скорость запроса.

Дальше — 06-05 «GIN для jsonb и массивов»: чем B-tree не годится для @>/поиска внутри jsonb и массивов и как это берёт на себя индекс GIN.