re:re:actable
Урок 113 минуты чтения

Что такое covering index и index only scan

На этой странице
Кратко

Покрывающий индекс (covering index) содержит все столбцы, которые нужны запросу: и для условий, и для SELECT. Тогда PostgreSQL выполняет Index Only Scan — берёт данные прямо из индекса, не обращаясь к таблице (heap). В PostgreSQL 11+ дополнительные столбцы можно добавить через INCLUDE: они хранятся только в листьях индекса, не участвуют в сортировке и поиске, но позволяют вернуть значение без чтения таблицы. Нюанс MVCC: индекс не знает, видна ли версия строки транзакции, поэтому Index Only Scan проверяет visibility map; если страница не помечена «всё видимо» (после изменений и до VACUUM), всё равно приходится заглядывать в таблицу — это видно в плане как Heap Fetches.

Вопрос

Что такое covering index и как работает index only scan?

Пример

-- частый запрос списка заказов пользователя
SELECT created_at, total, status
FROM orders
WHERE user_id = $1
ORDER BY created_at DESC
LIMIT 20;
 
-- ключ для поиска и сортировки + INCLUDE для возвращаемых столбцов
CREATE INDEX orders_user_created_cov_idx
ON orders (user_id, created_at DESC)
INCLUDE (total, status);
Limit
  -> Index Only Scan using orders_user_created_cov_idx on orders
       Index Cond: (user_id = 42)
       Heap Fetches: 0

INCLUDE или столбцы в ключе

В ключе (a, b, c)(a, b) INCLUDE (c)
Можно искать и сортировать по cДаНет
Размер внутренних узлов дереваБольшеМеньше (c только в листьях)
УникальностьУчитывает cUNIQUE (a) INCLUDE (c) — уникален только a
Типы без B-tree оператора (например, jsonb)НельзяМожно
-- уникальность по email + возврат имени без обращения к таблице
CREATE UNIQUE INDEX users_email_uniq ON users (email) INCLUDE (name);

Visibility map и Heap Fetches

Index Only Scan ...
  Heap Fetches: 18500     ← почти каждую строку пришлось проверять в таблице

Причина — много недавно изменённых страниц. Решение — регулярный (auto)vacuum; для таблиц «только вставка» в PG 13+ autovacuum запускается и по числу вставок.

VACUUM orders;   -- обновит visibility map

Когда покрывающий индекс оправдан

  • Очень частый запрос (список, лента, автодополнение), где каждая миллисекунда важна.
  • Возвращается несколько небольших столбцов.
  • Таблица «широкая» (много столбцов, большие text/jsonb), а нужна малая часть.

Не стоит включать большие столбцы и делать «индекс на всё» — индекс растёт, запись замедляется, HOT-обновления становятся невозможны для включённых столбцов.

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

  • SELECT * — покрывающий индекс бесполезен.
  • Ожидать Index Only Scan сразу после массового обновления — Heap Fetches будут высокими до VACUUM.
  • Включать в индекс часто меняющиеся столбцы.
  • Дублировать столбцы и в ключе, и в INCLUDE.

Что важно помнить

  • Covering index содержит все нужные запросу столбцы → Index Only Scan.
  • INCLUDE добавляет столбцы только для чтения, не для поиска.
  • Эффективность зависит от visibility map и VACUUM.

Мини-шпаргалка

  • CREATE INDEX ... ON t (search_cols) INCLUDE (output_cols).
  • План: Index Only Scan, Heap Fetches: 0.
  • VACUUM обновляет visibility map.
  • Без SELECT *.

Документация

PostgreSQL: Index-Only Scans and Covering Indexes.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам