Covering Index в PostgreSQL: INCLUDE vs ключевая колонка

Важно знать

• INCLUDE кладёт колонки только в листовые страницы B-tree, не в дерево. Дерево остаётся узким, поиск быстрый.
• Ключевая колонка участвует в сортировке и уникальности, INCLUDE — нет.
• Covering index нужен ради index-only scan — запрос отвечает целиком из индекса, не лезет в таблицу.
• Index-only scan работает, только когда visibility map говорит «страница вся видима». Bloat ломает оптимизацию.
• Для UNIQUE INCLUDE — единственный способ возвращать «лишнюю» колонку без расширения уникальности.
• INCLUDE уместен, когда колонка нужна только для возврата (SELECT-list), не для фильтрации, не для сортировки. Иначе — добавляй в ключ.

Индексы в PostgreSQL — B-tree. Дерево, у которого внутренние узлы хранят границы диапазонов, а листья — указатели на строки в куче (tuple identifier, ctid). Поиск — O(log N) по дереву, потом чтение листа, потом — почти всегда — чтение страницы из таблицы за фактическими данными.

INCLUDE — это синтаксис, появившийся в PostgreSQL 11, который кладёт дополнительные колонки только в листья, минуя дерево. Это поведение и порождает разницу с обычным composite index.

Базовые таблицы — те же, что и в статье про ACID:

CREATE TABLE category (
    id   BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE product (
    id          BIGSERIAL PRIMARY KEY,
    category_id BIGINT REFERENCES category(id),
    price       NUMERIC(10, 2) NOT NULL,
    name        TEXT NOT NULL
);

Сценарий: на витрине часто грузят список товаров категории — SELECT name, price FROM product WHERE category_id = ?. На миллионах строк хочется, чтобы запрос отвечал из индекса, не дёргая heap.

Два способа сделать «лишние» поля доступными в индексе

Вариант 1 — расширенный composite

CREATE INDEX product_category_name_price_idx
  ON product (category_id, name, price);

name и price стали ключевыми. Это значит:

• Они попадают во все уровни B-tree, включая внутренние узлы.
• Дерево физически шире — внутренние узлы хранят больше байт на запись, fan-out меньше, дерево выше.
• Если в WHERE есть только category_id, planner всё равно может пользоваться индексом (левый префикс), но узлы дерева раздуты — больше I/O.
• ORDER BY category_id, name теперь идёт без сортировки — индекс уже отсортирован по этой паре.
• Любой UPDATE на name или price пересоздаёт запись в индексе.

Вариант 2 — covering через INCLUDE

CREATE INDEX product_category_idx
  ON product (category_id) INCLUDE (name, price);

Здесь category_id — единственная ключевая колонка, дерево узкое. name и price лежат в листьях: planner может прочитать их при index scan, но в дереве их нет.

Сравнение в один взгляд:

Композит выгоднее, когда колонки реально участвуют в фильтрации или сортировке. Covering — когда они нужны только в SELECT-list, и хочется маленькое дерево + index-only scan.

Index-only scan — ради чего всё затевается

Обычный index scan: дерево → лист → таблица (heap) за фактическими значениями. Heap-страница может быть холодной (не в shared_buffers), это лишний I/O.

Index-only scan: дерево → лист → возврат из листа, без обращения к heap. Условие — все колонки запроса лежат в индексе. С INCLUDE это достигается прицельно, без расширения ключа.

EXPLAIN (ANALYZE, BUFFERS)
SELECT name, price FROM product WHERE category_id = 1;

-- С INCLUDE-индексом:
-- Index Only Scan using product_category_idx on product
--   Index Cond: (category_id = 1)
--   Heap Fetches: 0       ← вот это и есть выигрыш

Ловушка с Heap Fetches. Index-only scan работает только если visibility map говорит, что страница «всецело видима» (all-visible). Visibility map обновляется VACUUM-ом. На write-heavy таблице с отстающим autovacuum visibility map устаревает — и planner вынужден лезть в heap проверять видимость по xmin/xmax (см. MVCC в статье про ACID). Тогда Heap Fetches ≠ 0, и весь смысл covering index теряется.

Диагностика:

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
-- смотрим строку "Heap Fetches: N"
-- если N > 0 и сравним с числом возвращённых строк — VACUUM не успевает

Лечение — поднять агрессивность autovacuum на конкретной таблице:

ALTER TABLE product SET (
    autovacuum_vacuum_scale_factor = 0.05,   -- vacuum при 5% мёртвых строк
    autovacuum_vacuum_insert_scale_factor = 0.1  -- vacuum после вставки 10% строк
);

INCLUDE + UNIQUE — единственный честный способ

Допустим, нужно: «name уникален в рамках категории, но при поиске часто возвращаем price за компанию».

Без INCLUDE пришлось бы либо:

-- А: расширить уникальность — но тогда (cat=1, name='Чай', price=50)
--    и (cat=1, name='Чай', price=60) считаются разными. Контракт сломан.
CREATE UNIQUE INDEX ... ON product (category_id, name, price);

-- Б: оставить уникальность узкой и завести ВТОРОЙ индекс ради covering —
--    дисковое и write-overhead удвоились.
CREATE UNIQUE INDEX ... ON product (category_id, name);
CREATE INDEX        ... ON product (category_id, name, price);

С INCLUDE это одна структура, делающая обе работы:

CREATE UNIQUE INDEX product_category_name_uq
  ON product (category_id, name) INCLUDE (price);

Уникальность проверяется по (category_id, name). price доступен для index-only scan и не участвует в проверке коллизий. Это самый частый и наименее спорный случай применения INCLUDE.

Что НЕЛЬЗЯ положить в INCLUDE

• Не любую колонку. INCLUDE поддерживают B-tree, GiST, SP-GiST. GIN не поддерживает — а GIN мы как раз используем для FTS и JSONB.
• Не выражения / не функции. INCLUDE (lower(name)) — нельзя. Только базовые колонки таблицы.
• Не имеет смысла класть в INCLUDE то, что и так в ключе — это no-op.

Когда не нужен covering index

• Если запрос возвращает SELECT * — все колонки в индекс класть бессмысленно. Index-only scan не получится, ширина индекса перевесит выигрыш.
• Если колонки в WHERE совпадают с теми, что нужны в SELECT, и они уже в ключе — covering не нужен.
• Если таблица узкая (3–4 коротких колонки) и помещается в shared_buffers целиком — heap-страницы и так горячие, выигрыш от index-only scan копеечный, write-overhead индекса с лишними колонками может перевесить.

Краткая шпаргалка по выбору

Что почитать дальше

• PostgreSQL: CREATE INDEX — синтаксис, в т.ч. INCLUDE.
• PostgreSQL: Index-Only Scans — как работает оптимизация и при чём тут visibility map.
• ACID и уровни изоляции — про MVCC и xmin/xmax, на которые опирается visibility map.
• PG Style Guide — правила работы с индексами в наших Java/Spring-сервисах.