Covering Index в PostgreSQL: зачем нужен INCLUDE

← назад к разделу

Индекс помогает найти нужные строки быстро. Но после того как PostgreSQL нашёл их в индексе, он обычно идёт ещё раз — в саму таблицу, чтобы прочитать значения колонок. Covering index позволяет этого избежать: все нужные данные лежат прямо в индексе, и в таблицу лезть не надо.

Почему PostgreSQL дважды ходит за данными

Стандартный индекс в PostgreSQL устроен как дерево (B-tree). В листьях дерева хранятся только ключевые колонки и адрес строки в таблице (ctid). Когда PostgreSQL находит строку по индексу, он читает этот адрес и идёт в таблицу за остальными значениями.

Это называется heap fetch — обращение к куче (heap — так называется хранилище строк в PostgreSQL). Если таблица большая и данные разбросаны по многим страницам, каждое такое обращение — отдельная операция ввода-вывода.

Пример сценария: витрина товаров. Запрос часто выглядит так:

SELECT name, price FROM product WHERE category_id = 1;

Есть индекс по category_id, PostgreSQL быстро находит нужные строки, но затем идёт в таблицу за name и price. На миллионах строк это ощутимо.

Что такое index-only scan

Index-only scan — режим, при котором PostgreSQL отвечает на запрос целиком из индекса, не обращаясь к таблице. Условие одно: все колонки, которые нужны запросу (и в WHERE, и в SELECT), должны лежать в индексе.

Проблема обычного подхода: если добавить name и price в ключ индекса, они попадут во все уровни дерева, включая внутренние узлы. Дерево станет физически шире, займёт больше места и будет медленнее при поиске.

INCLUDE решает это изящно: дополнительные колонки кладутся только в листья дерева, не в само дерево.

INCLUDE vs расширенный ключ

Посмотрим на два варианта одного и того же индекса:

-- Вариант 1: name и price попадают в ключ — во все уровни дерева
CREATE INDEX product_category_name_price_idx
  ON product (category_id, name, price);

-- Вариант 2: name и price только в листьях — дерево остаётся узким
CREATE INDEX product_category_idx
  ON product (category_id) INCLUDE (name, price);

Разница принципиальная:

Вывод: если name и price нужны только в SELECT, а не в WHERE и не в ORDER BY — берите INCLUDE. Если по ним тоже фильтруют или сортируют — добавляйте в ключ.

Как проверить, что index-only scan работает

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

Хороший результат выглядит так:

Index Only Scan using product_category_idx on product
  Index Cond: (category_id = 1)
  Heap Fetches: 0

Heap Fetches: 0 — PostgreSQL не ходил в таблицу. Это и есть цель.

Почему Heap Fetches может быть ненулевым

Здесь есть важная тонкость. Index-only scan работает только когда PostgreSQL уверен, что данные в индексе актуальны. Эту уверенность даёт специальная структура — visibility map: она помечает страницы таблицы, в которых все строки видны любой транзакции.

Проблема: visibility map обновляет VACUUM. На таблицах с частыми обновлениями и удалениями autovacuum может не успевать. Тогда PostgreSQL вынужден всё равно заглянуть в таблицу — проверить, видна ли строка текущей транзакции. В итоге Heap Fetches ≠ 0, и весь смысл covering index теряется.

Диагностика простая: смотрите на число в строке Heap Fetches. Если оно сравнимо с количеством возвращённых строк — autovacuum не справляется.

Решение — сделать autovacuum агрессивнее для конкретной таблицы:

ALTER TABLE product SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_vacuum_insert_scale_factor = 0.1
);

UNIQUE + INCLUDE — самый частый случай

Типичная задача: нужно, чтобы name был уникален в рамках категории, но при выборке всегда возвращать и price.

Без INCLUDE придётся выбирать:

-- Плохой вариант: расширяем уникальность — но тогда одно имя с разной ценой
-- становится двумя разными записями. Логика сломана.
CREATE UNIQUE INDEX ON product (category_id, name, price);

-- Неудобный вариант: два индекса вместо одного — двойная нагрузка на запись
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 поддерживают B-tree, GiST, SP-GiST. GIN не поддерживает — а GIN используется для полнотекстового поиска и JSONB.
• В INCLUDE нельзя класть выражения или функции: INCLUDE (lower(name)) — не сработает. Только обычные колонки таблицы.
• Если таблица маленькая и целиком помещается в оперативную память PostgreSQL — heap-страницы и так горячие, выигрыш от covering index минимален.
• SELECT * делает index-only scan бессмысленным: все колонки в индекс не положишь.

Коротко

• Обычный индекс находит строки, но за значениями колонок идёт в таблицу. INCLUDE позволяет положить нужные колонки в листья индекса и обойтись без этого похода.
• INCLUDE кладёт колонки только в листья B-tree — дерево остаётся узким, поиск быстрым.
• Ключевые колонки участвуют в сортировке, уникальности и фильтрации. Колонки в INCLUDE — только для возврата в SELECT.
• Цель — index-only scan (Heap Fetches: 0). Работает, когда visibility map помечает страницы как полностью видимые.
• Если Heap Fetches растёт — autovacuum не успевает; настраивайте autovacuum_vacuum_scale_factor для таблицы.
• UNIQUE (a, b) INCLUDE (c) — единственный способ вернуть c без расширения уникальности.
• GIN-индексы INCLUDE не поддерживают.

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

• ACID и уровни изоляции — про MVCC и visibility map, от которых зависит index-only scan.
• Полнотекстовый поиск в PostgreSQL — GIN-индексы, которые covering не поддерживают.
• Партиционирование в PostgreSQL — как индексы работают в партиционированных таблицах.