Covering Index и Index-Only Scan

Дашборд показывает заказы пользователя за последние 30 дней - запрос на 500 строк. EXPLAIN: Index Scan, 800 buffers, 12 мс. Добавление одной строки `INCLUDE (amount, status)` к существующему индексу: Index Only Scan, 6 buffers, 0.5 мс. Тот же запрос, тот же сервер, без переписывания SQL - в 24 раза быстрее. Это и есть covering index в одной строчке DDL.

• **Stripe**: дашборды активности мерчантов используют covering-индексы на (merchant_id, created_at DESC) INCLUDE (amount, currency, status) - сокращение p99 latency с 200мс до 15мс
• **GitHub**: страница 'мои PR' опирается на covering index на pulls с включёнными title/state/updated_at - выдерживает миллионы запросов в час без чтения heap
• **Notion**: workspace-индекс на page-table с INCLUDE для title/icon/updated_at позволяет рендерить сайдбар одним Index Only Scan, без N+1 fetches

Что такое covering index

Обычный B-Tree индекс хранит ключ и ctid (указатель на строку в heap). При выполнении `SELECT amount FROM orders WHERE user_id = 42` PostgreSQL находит листовой узел индекса за 3-4 обращения к диску, затем читает heap-страницу, чтобы достать `amount`. Это **Index Scan**: индекс отвечает на условие WHERE, но heap нужен для возврата дополнительных столбцов. **Covering index** включает в индекс все необходимые столбцы запроса - тогда heap читать не нужно. Запрос обслуживается одним проходом по индексу, без random I/O в основную таблицу.

Термин 'covering' означает, что индекс **покрывает** запрос: все столбцы, которые SELECT хочет вернуть, физически содержатся в листовых узлах индекса. До PostgreSQL 11 covering достигался только через включение столбца в ключ (`CREATE INDEX ... (user_id, amount)`), что увеличивало размер индекса и заставляло сортировать по второму столбцу. С 11-й версии появилось ключевое слово `INCLUDE` - столбцы хранятся в листьях без участия в навигации и сортировке.

Синтаксис INCLUDE и стратегия колонок

При проектировании covering index стратегия проста: ключевые столбцы - те, по которым идёт фильтрация и сортировка; INCLUDE-столбцы - те, что только возвращаются. Если запрос делает `WHERE user_id = ? AND created_at > ?` и возвращает `(id, amount, status)`, то ключ - `(user_id, created_at)`, INCLUDE - `(id, amount, status)`. Преимущество перед составным ключом из всех пяти столбцов: меньше байт на ключ, выше branching factor, быстрее навигация. INCLUDE-столбцы хранятся только в листьях, а не на каждом уровне дерева.

Не каждый запрос стоит покрывать. Стоимость covering index: запись становится дороже (нужно обновлять INCLUDE-данные при UPDATE), индекс занимает больше места на диске и в shared_buffers. Покрытие имеет смысл для горячих SELECT-запросов с предсказуемым набором возвращаемых столбцов. Покрытие SELECT * почти всегда неверный выбор - тащит все TOAST-данные в индекс.

Index Only Scan: механика без обращения в heap

**Index Only Scan** - план выполнения, при котором PostgreSQL возвращает данные прямо из индекса, не читая heap. Это сильно дешевле обычного Index Scan: одна страница индекса вместо двух обращений (индекс + heap). Для запроса по 100 строкам с random I/O в heap разница может составлять 10-20x. Но есть тонкость: PostgreSQL хранит видимость версий строк (MVCC visibility) только в heap, не в индексе. Чтобы решить, видима ли строка текущей транзакции, обычно нужно сверяться с heap-страницей. Index Only Scan возможен только если эта проверка не нужна.

PostgreSQL хранит каждую версию строки в heap отдельно (MVCC). Индекс может ссылаться на устаревшую версию или на ту, что ещё не закоммитчена. Чтобы определить, валидна ли версия для текущей транзакции, в общем случае нужно читать heap-страницу и смотреть xmin/xmax. Это сводит на нет выгоду covering-индекса, если каждая строка всё равно требует heap fetch.

Visibility Map и роль VACUUM

**Visibility Map** (VM) - битовая карта, по одному биту на каждую heap-страницу таблицы (по факту два бита: all-visible и all-frozen). Бит all-visible=1 означает, что каждая версия каждой строки на этой странице видна всем существующим и будущим транзакциям - то есть проверка xmin/xmax не нужна. Index Only Scan смотрит в VM перед обращением в heap: если бит установлен, heap пропускается. Бит сбрасывается на 0 при любой записи на страницу (INSERT/UPDATE/DELETE) и устанавливается обратно только VACUUM-ом.

VM хранится в отдельном forke файла таблицы (`relname_vm`). Размер VM крошечный: 1 бит на 8KB страницы = 1/65536 размера таблицы. Поэтому VM почти всегда помещается в shared_buffers целиком. Эффективность Index Only Scan напрямую зависит от свежести VM, которую поддерживает autovacuum. Высокая скорость записи без VACUUM = постоянно сброшенные биты = плохая работа covering index.

Trade-offs и измерение выигрыша

Covering index - инструмент с измеримой ценой. Выгода: чтение, которое раньше делало два I/O (индекс + heap), теперь делает один. Для запросов с малой селективностью (тысячи строк) выигрыш достигает 10x по latency. Цена: каждая запись в таблицу триггерит обновление дополнительных INCLUDE-столбцов в индексе; HOT updates становятся невозможны, если INCLUDE-столбец входит в обновляемое поле. На write-heavy системах с 80% INSERT/UPDATE добавление широкого covering index может ухудшить throughput на 15-30%. Правило: covering имеет смысл только для горячих SELECT-путей, проверенных по `pg_stat_statements`.

Метрики для решения 'добавлять ли covering': (1) частота запроса - из pg_stat_statements; (2) текущая стоимость - EXPLAIN ANALYZE с BUFFERS; (3) profile записи на таблицу - pg_stat_user_tables.n_tup_upd; (4) размер индекса - pg_size_pretty(pg_relation_size). Если запрос вызывается миллион раз в день, экономит 5мс каждый раз, а индекс добавляет 50мс к одному UPDATE из тысячи - выгода очевидна. Если запрос вызывается раз в час - индекс не окупится.

Ключевые идеи

• **Covering index**: содержит все столбцы, которые нужны запросу - SELECT обслуживается одним проходом по индексу без обращения в heap
• **INCLUDE-столбцы** (с PostgreSQL 11): хранятся только в листьях, не участвуют в сортировке и навигации - дают covering без раздувания дерева
• **Index Only Scan** работает только если страница помечена all-visible в visibility map; VM поддерживается VACUUM-ом, при отставании VACUUM падает обратно к Index Scan + heap fetch
• **Trade-off**: covering ускоряет read-пути в 5-10x, но добавляет write amplification - применять прицельно для горячих запросов из pg_stat_statements

Связанные темы

Covering index стоит на стыке нескольких механизмов PostgreSQL:

• B-Tree индексы — Covering - расширение обычного B-Tree через INCLUDE; принципы branching factor и составных ключей напрямую применимы к проектированию
• MVCC и VACUUM — Эффективность Index Only Scan зависит от visibility map; настройка autovacuum критична для covering на write-active таблицах
• Планировщик запросов — Выбор Index Only Scan vs Index Scan vs Bitmap Heap Scan делается на основе статистики; EXPLAIN ANALYZE - единственный объективный инструмент проверки

Вопросы для размышления

• Почему PostgreSQL не использует Index Only Scan по умолчанию для каждого индекса, ведь это всегда дешевле?
• В каких ситуациях даже корректно спроектированный covering index может оказаться медленнее обычного Index Scan + heap fetch?
• Visibility map - крошечная структура, но критична для covering. Какие ещё подобные 'derived' структуры PostgreSQL могли бы появиться для других оптимизаций?

Связанные уроки

• pg-12-btree — Covering index строится поверх B-tree структуры
• pg-14-partial-expr — Partial и expression indexes дополняют covering стратегию
• pg-18-explain — EXPLAIN ANALYZE показывает Index Only Scan для covering индексов
• ds-12-balanced-trees — B-tree - балансированное дерево, covering - надстройка над ним
• db-09-indexes-btree