В предыдущих уроках мы рассмотрели B+tree как структуру для поиска: ключи в листьях указывают на heap-кортежи, и Index Scan по индексу = поход в индекс + поход в heap за данными. Но что, если бы Index Scan мог отдать данные прямо из листа, не возвращаясь в heap? Это и есть Index Only Scan, и INCLUDE — главный инструмент его настройки.

Что такое covering index

До Postgres 11 единственный способ сделать индекс покрывающим — добавить колонки в ключ:

CREATE INDEX orders_customer_idx ON orders (customer_id, status, total_cents);

Проблема: status и total_cents участвуют в сортировке, в split-логике, в suffix truncation — хотя по ним мы не ищем. Эти колонки раздувают internal-узлы и ухудшают fan-out.

С Postgres 11 появилась более гранулярная альтернатива:

CREATE INDEX orders_customer_idx ON orders (customer_id) INCLUDE (status, total_cents);

Теперь customer_id — единственный ключ, по которому работает B+tree (сортируется, делит, ищется). А status и total_cents лежат только в листьях как payload — не участвуют в поиске, не влияют на internal-узлы.

Index Only Scan и visibility map

Однако даже с covering-индексом Postgres не может просто так взять данные из индекса. Причина — MVCC. Каждая строка в Postgres имеет xmin/xmax (см. модуль 4), которые определяют, видна ли она текущей транзакции. Эта информация в индексе не хранится. Поэтому даже с покрывающим индексом Postgres должен либо:

• сходить в heap и проверить видимость (обычный Index Scan), либо
• доказать, что все строки на конкретной heap-странице видны всем — и тогда heap читать не надо.

Второй вариант — это и есть Index Only Scan, и он работает благодаря

VM хранит 2 бита на каждую heap-страницу:

• all-visible — все строки на этой странице видны всем (нет открытых транзакций, которые их не видят).
• all-frozen — на странице нет dead tuples (все «замороженные»).

Когда Postgres делает Index Only Scan и для конкретной строки лист индекса показывает TID = (block, offset), он смотрит в VM по этому block. Если бит all-visible стоит — данные из листа индекса достоверны, в heap не идём. Если нет — идём в heap проверять видимость.

Важно: VM обновляется только VACUUM. Свежевставленные строки не помечены all-visible, пока не прошёл VACUUM. Поэтому свежий append после INSERT INTO ... SELECT generate_series(...) всегда даёт Index Scan, а не Index Only — пока не отработал autovacuum.

Демонстрация: EXPLAIN на Index Only Scan

Если всё прошло хорошо — увидишь Index Only Scan using orders_customer_total_idx. В строке Buffers: будет небольшое число — только страницы индекса, без heap. Также увидишь Heap Fetches: 0 — это маркер, что VM сработала и в heap не пришлось ходить.

Здесь будет обычный Index Scan + heap fetches. Чтобы получить Index Only Scan, нужно либо добавить placed_at в INCLUDE, либо не SELECT’ить её.

Bitmap Index Scan: ещё одна альтернатива

Прежде чем углубиться в INCLUDE, ещё одно полезное наблюдение. Постгрес умеет делать Bitmap Index Scan, при котором он:

1. Сканирует индекс целиком (или его часть, удовлетворяющую WHERE).
2. Строит bitmap всех найденных TID (block × offset).
3. Сортирует bitmap по block_number.
4. Идёт в heap последовательно (в порядке страниц), читает строки.

Bitmap-стратегия проигрывает по latency (нужно собрать весь bitmap, прежде чем что-то отдать), но выигрывает по throughput на широких выборках (sequential I/O в heap). Для больших отчётов Bitmap Scan часто оптимален.

INCLUDE-индексы Bitmap-плану не помогают (всё равно идём в heap), но они помогают Index-Only-плану, и planner выбирает между ними на основе costing. Если хочешь принудительно — SET enable_bitmapscan = off для теста (не делай это на проде).

История: до и после PG 11

До Postgres 11 трюк «covering-индекс» делали через композитный индекс по всем нужным колонкам. Это работало, но имело три явные проблемы:

1. Все колонки участвовали в сортировке. Если в композитный (a, b, c) колонка c — это text шириной 200 байт, internal-узлы раздувались огромными разделителями (даже с suffix truncation). Fan-out падал.
2. Все колонки участвовали в split-логике. Каждый INSERT с новым c мог вызвать pere-balance — даже если c не использовалась в поиске.
3. UNIQUE распространялся на всю комбинацию. CREATE UNIQUE INDEX ON t (email, name) накладывал ограничение «уникальная пара email+name» — а это часто не то, что нужно.

PG 11 решил эти три проблемы одним механизмом: INCLUDE-колонки не участвуют ни в сортировке, ни в split-логике, ни в UNIQUE. Это в принципе другой механизм, который выглядит синтаксически как «ещё одна колонка», но семантически — это payload в листьях.

Когда INCLUDE выгоден

1. Heavy SELECT-запросы с одним predicate-condition. Типичный пример — отчётный запрос вида SELECT customer_id, SUM(total_cents) FROM orders WHERE placed_at >= '2024-01-01' GROUP BY customer_id. Если есть индекс (placed_at) INCLUDE (customer_id, total_cents), весь запрос отрабатывает через Index Only Scan без обращения к heap.

2. Узкие SELECT’ы из широких таблиц. Если таблица — 100 колонок, а запрос трогает 3 из них, covering-индекс по этим 3 может быть в 30x меньше heap и весь поместиться в RAM. Buffer cache hit rate приближается к 100%.

3. READ-only нагрузка (или близкая к ней). Когда VACUUM успевает поддерживать VM в актуальном состоянии, Index Only Scan стабильно работает. На активно изменяющихся таблицах часть страниц всегда «грязная», и Heap Fetches > 0.

INCLUDE и WAL

Каждое изменение INCLUDE-колонки порождает изменение в индексе. Если ты UPDATE’ишь только INCLUDE-колонку (не ключ), Postgres всё равно должен обновить запись в индексе — потому что INCLUDE-payload теперь другой. Это значит:

• HOT update не сработает, если изменена колонка из INCLUDE. HOT (Heap-Only Tuple) — оптимизация, при которой UPDATE не трогает индексы, если ни одна индексируемая колонка не изменилась. INCLUDE-колонка считается «индексируемой» — она участвует в индексе.
• WAL вырастает: на каждый UPDATE INCLUDE-колонки пишутся изменения в ВСЕ индексы, где эта колонка есть в INCLUDE.

Это серьёзный аргумент против необдуманного добавления широких или часто-меняющихся колонок в INCLUDE. Если у тебя колонка last_seen_at обновляется на каждый клик пользователя — добавить её в INCLUDE = убить HOT и удвоить I/O.

Правило: в INCLUDE — только редко меняющиеся колонки, которые при этом часто читаются.

Visibility map: устройство

VM — это отдельный файл рядом с heap: $PGDATA/base/<dbid>/<relfilenode>_vm. Размер крошечный: 2 бита на каждую heap-страницу, то есть 1 бит-pair на 8 KiB heap. На таблице в 100 GiB (≈13M страниц) VM занимает всего 3.2 MiB.

VM делится на страницы по 8 KiB, в каждой по 32768 bit-pairs — покрытие 256 MiB heap. Чтение VM — это всегда 1 page read, кэшируется отлично.

Когда executor выполняет Index Only Scan, для каждого TID он:

1. Берёт block_number из TID.
2. Вычисляет смещение в VM: vm_page = block / 32768, vm_offset = block % 32768.
3. Читает бит all-visible. Если 1 — отдаёт строку прямо из индекса. Если 0 — heap fetch.

Это и есть та «магия», которая делает Index Only Scan дешевле в разы. На read-only нагрузке VM почти всегда покрывает 100% страниц, и heap fetches = 0.

VM обновляется в нескольких местах:

• VACUUM устанавливает биты после полной верификации страницы.
• INSERT/UPDATE/DELETE сбрасывают all-visible, если меняют страницу (нужно перепроверить видимость).
• На очень warm-update workload биты постоянно мигают, и Index Only Scan не стабильно работает.

Когда INCLUDE лишний

1. Если ты добавляешь широкие колонки. INCLUDE-колонки полностью копируются в лист индекса. Если ты включишь description TEXT шириной 2 KiB, лист сможет содержать всего 3-4 entries вместо 350-400. Дерево превратится в тонкое и высокое. Часто хуже, чем обычный Index Scan + heap.

2. Если запрос всё равно идёт в heap. Если ты SELECT’ишь часть колонок из INCLUDE, а часть — нет, оптимизатор всё равно сделает Index Scan (с heap fetch). В этом случае INCLUDE — мёртвый груз, увеличивающий размер индекса без выигрыша.

3. На активно изменяющихся таблицах с редким VACUUM. VM не успевает помечать страницы как all-visible, Index Only Scan «сваливается» в обычный Index Scan + heap fetches. INCLUDE-колонки увеличивают размер индекса, но не дают выигрыша.

4. На UNIQUE-индексах. Тонкость: с PG 11 в CREATE UNIQUE INDEX ... INCLUDE уникальность проверяется только по ключу — INCLUDE-колонки не участвуют. Это удобно для constraint’ов вида «email уникален, но в индексе нужны имя+фамилия для покрывающего scan». Но если ты ожидаешь, что INCLUDE-колонки участвуют в UNIQUE — будет сюрприз.

Visibility map: смотрим напрямую

Замечание: pg_stat_user_tables не показывает напрямую число all-visible страниц, но n_dead_tup даёт грубую оценку. Большие n_dead_tup → VM устаревает → Index Only Scan хуже работает.

Index Scan vs Index Only Scan vs Bitmap Heap Scan

В EXPLAIN ты увидишь разные варианты «походов» в индекс. Разберём, чем они отличаются.

• Index Scan — классический. Идём в индекс, для каждой найденной записи прыгаем в heap, отдаём строки в порядке индекса.
• Index Only Scan — обсуждали выше. Heap не задействован (или почти не задействован, если VM подтверждает all-visible).
• Bitmap Index Scan + Bitmap Heap Scan — двухфазная схема. Сначала идём в индекс, собираем bitmap всех TID, которые матчат. Потом сортируем bitmap по block_number и идём в heap последовательно (в порядке страниц, а не в порядке индекса). Это даёт sequential I/O вместо random — выигрыш на больших выборках. Цена — теряется порядок строк (нужен ORDER BY).

Index Only Scan побеждает Bitmap, когда:

• Запрос узкий (мало строк).
• VM полностью покрывает запрашиваемые страницы.
• Все колонки в индексе.

Bitmap побеждает, когда:

• Запрос широкий (много строк).
• Heap fetches неизбежны (нет covering-индекса).
• Порядок строк не важен.

Тонкость: order matters

Если у тебя индекс (a, b) INCLUDE (c), и запрос SELECT c FROM t WHERE b = 5 — Index Only Scan не сработает, потому что для использования индекса нужно искать по префиксу ключа. То есть WHERE a = ... или WHERE a = ... AND b = ..., но не «только по b».

INCLUDE не меняет правил composite-индекса по ключевой части. Это типичная ошибка: «у меня есть INCLUDE, почему не работает». Ответ — потому что префикс ключа не использован.

EXPLAIN-маркеры: что искать

При анализе плана с covering-индексом смотри на три маркера:

1. Node type: Index Only Scan — есть, значит план нацелен на чтение из индекса.
2. Heap Fetches: 0 — нулевое число обращений в heap. Если число ненулевое — VM устарела или часть страниц «грязная»; Index Only Scan сваливается в Index Scan для этих записей.
3. Buffers: shared hit=N read=M — N+M равно числу прочитанных страниц. Для Index Only Scan на маленькую выборку это N = высота_дерева + страницы листьев (мало). Для Index Scan + heap fetches число будет в разы выше.

Если видишь Heap Fetches: N > 0 после VACUUM — попробуй VACUUM (FREEZE) <table>. FREEZE форсит обновление VM даже для не очень старых страниц.

Antipattern: индексы-«молотки»

Среди начинающих DBA популярна стратегия «когда что-то медленное — добавить INCLUDE». В результате на одной таблице оказывается 8-12 индексов, каждый со своим набором INCLUDE-колонок. Что плохо:

1. Каждый INSERT/UPDATE/DELETE пишет в ВСЕ индексы. Write amplification растёт линейно от числа индексов.
2. Суммарный размер индексов превышает размер таблицы в разы. На дисках это терпимо, в RAM — катастрофа. Buffer cache забивается, hit rate падает.
3. Autovacuum работает на каждый индекс отдельно. На большой таблице с 10 индексами VACUUM растягивается на часы.
4. Конкурентные UPDATE становятся слабее. Чем больше индексов трогает UPDATE, тем меньше шансов на HOT-update (см. модуль 4).

Правило: меньше = лучше. Для каждой таблицы — primary key + 1-3 индекса под самые горячие запросы. INCLUDE-колонки — только если они реально дают Index Only Scan и реально это нужно. Не добавляй INCLUDE «на всякий случай».

Если сомневаешься — построй индекс, посмотри на pg_stat_user_indexes.idx_scan через неделю на проде. Если idx_scan = 0 — DROP. Это и есть data-driven подход к индексам.

Чек-лист

• INCLUDE (col1, col2) (PG 11+) добавляет колонки в лист B+tree, не делая их частью ключа.
• INCLUDE-колонки не участвуют в поиске, не раздувают internal-узлы, не учитываются в UNIQUE.
• Index Only Scan — план, при котором данные читаются прямо из индекса. Требует covering-индекс и all-visible бит в visibility map.
• Visibility map обновляется только VACUUM. На свежезаписанных данных Index Only Scan не работает — нужен VACUUM (ANALYZE).
• Heap Fetches: 0 в EXPLAIN — маркер, что Index Only Scan «настоящий», VM работает.
• Широкие INCLUDE-колонки (длинный TEXT, JSONB) могут уничтожить fan-out — взвешивай размер vs выигрыш.
• INCLUDE не меняет правил composite-индекса по ключу: префикс должен использоваться в WHERE.