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

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

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

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

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

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

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

живой пример

SELECT title, price FROM products WHERE seller_id = 'sel-01';

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

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

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

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

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

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

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

-- Вариант 1: title и price попадают в ключ — во все уровни дерева
CREATE INDEX products_seller_title_price_idx
  ON products (seller_id, title, price);

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

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

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

Сразу уберём частое недопонимание: покрывающим бывает и обычный составной индекс, INCLUDE для этого не обязателен. Index-only scan включается тогда, когда все колонки запроса есть в индексе — неважно, в ключе они или в довеске. Индекс (seller_id, title, price) покрывает запрос SELECT title, price … WHERE seller_id = ? точно так же. INCLUDE нужен ровно в двух случаях: когда лишние колонки не должны раздувать дерево и когда они не должны попадать в проверку уникальности.

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

живой пример

EXPLAIN (ANALYZE, BUFFERS)
SELECT title, price FROM products WHERE seller_id = 'sel-01';

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

Index Only Scan using products_seller_idx on products
  Index Cond: (seller_id = 'sel-01'::text)
  Heap Fetches: 0

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

Измерить выигрыш можно прямо, по числу прочитанных блоков. Сравните два прогона одного запроса — с покрывающим индексом и без него:

живой пример

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, total_amount FROM orders WHERE seller_id = 'sel-01';

Смотреть надо на строку Buffers: shared hit=…. Без покрытия план читает страницы индекса и страницы таблицы: к каждой найденной строке добавляется поход в кучу. С покрытием остаются только страницы индекса, а Heap Fetches: 0 подтверждает, что в таблицу не ходили вовсе. На выдаче в сотню строк разница обычно двух-трёхкратная по блокам; чем шире таблица и чем разбросаннее строки по страницам, тем больше выигрыш — именно поэтому приём работает на широких таблицах и почти не даёт эффекта на узких.

Полезный приём для такого сравнения: временно запретить базе брать индекс (SET enable_indexonlyscan = off;) и прогнать тот же запрос — разница в Buffers и будет ценой походов в таблицу.

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

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

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

Сразу важная оговорка: ноль — не то состояние, в которое живую таблицу можно привести раз и навсегда. Страницу нельзя пометить «видна всем», пока её только что меняли или пока живы старые снимки данных — открытая транзакция, длинный отчёт, реплика с hot_standby_feedback. На таблице, в которую постоянно пишут, часть страниц всегда свежая, и небольшой Heap Fetches — это норма, а не поломка, и никакими настройками уборки его не убрать.

Тревожный признак другой: число, сравнимое с количеством возвращённых строк, да ещё и растущее от прогона к прогону. Тогда уборка действительно не поспевает — и первым делом стоит поискать не настройки, а долгоживущую транзакцию: пока она открыта, VACUUM не может пометить страницы видимыми, сколько его ни запускай.

Если дело всё-таки в частоте уборки, её задают для конкретной таблицы:

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

Эти два параметра отвечают за разные события, и путать их не стоит. Первый считает изменённые и удалённые строки — это про таблицу, которую правят. Второй появился в PostgreSQL 13 и считает вставленные: до него таблицу, в которую только добавляют, autovacuum не трогал вовсе, а раз так — карту видимости никто не заполнял, и index-only scan на журнале всегда ходил в таблицу. Если у вас именно журнал, где только пишут, нужен второй; если справочник, который постоянно правят, — первый.

Чем платит INCLUDE: обновления перестают быть лёгкими

У приёма есть цена, о которой почти не пишут, и она решающая при выборе колонок.

PostgreSQL умеет обновлять строку «облегчённо» (HOT): если изменённые колонки не участвуют ни в одном индексе и на странице есть место, новая версия строки кладётся на ту же страницу, а индексы не трогаются вовсе. Это главный механизм, который держит таблицу и индексы от раздувания при частых обновлениях.

Колонка в INCLUDE считается проиндексированной. Значит, её обновление лёгким быть не может: придётся писать новую версию строки и обновлять индекс. Проверяется это за минуту на PostgreSQL 17:

таблица с fillfactor 50, индекс (a) INCLUDE (b)
1000 обновлений колонки c (нет ни в одном индексе): n_tup_hot_upd = 1000
1000 обновлений колонки b (включена в индекс):      n_tup_hot_upd = 0

Отсюда правило выбора, которого не хватает в большинстве советов: в INCLUDE кладут колонки, которые редко меняются. Рекомендация «(seller_id) INCLUDE (title, price)» отлично выглядит на витрине и плохо — на товарах с плавающей ценой: каждая переоценка перестаёт быть лёгким обновлением, раздувает и таблицу, и индекс, и создаёт ту самую работу для уборки, о которой следующий раздел.

И вторая граница, техническая: включённые колонки лежат в записи индекса целиком, а запись обязана поместиться на страницу. Длинный текст в INCLUDE роняет вставку:

ERROR: index row requires 9616 bytes, maximum size is 8191

Для «витрины с названием» это не теоретический риск: одно аномально длинное название — и запись в таблицу останавливается.

Когда не брать

Складывая эти две цены с выигрышем, получаем короткий критерий. INCLUDE окупается на таблице, которую читают намного чаще, чем пишут, с колонками, которые редко меняются и не бывают длинными. На таблице с активной записью индекс растёт на ширину включённых колонок, обновления становятся тяжёлыми, а выигрыш от index-only scan съедается ростом Heap Fetches, потому что уборка не поспевает помечать страницы видимыми.

UNIQUE + INCLUDE — самый показательный случай

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

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

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

-- Неудобный вариант: два индекса вместо одного — двойная нагрузка на запись
CREATE UNIQUE INDEX ON products (category_id, name);
CREATE INDEX        ON products (category_id, name, price);

С INCLUDE — один индекс делает обе работы:

CREATE UNIQUE INDEX products_seller_title_uq
  ON products (seller_id, title) INCLUDE (price);

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

Ограничения

Границы у приёма такие. INCLUDE появился в PostgreSQL 11 и сначала работал только с B-tree; GiST научился ему в 12-й версии, SP-GiST в 14-й, а GIN не поддерживает до сих пор, и это важно: GIN стоит на полнотекстовом поиске и JSONB, там покрывающего индекса не будет. В INCLUDE нельзя класть выражения и функции, INCLUDE (lower(name)) не сработает, только обычные колонки таблицы. Если таблица маленькая и целиком помещается в оперативную память, heap-страницы и так горячие, и выигрыш минимален. И SELECT * делает index-only scan бессмысленным: все колонки в индекс не положишь.

Коротко

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

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

• ACID и уровни изоляции — про MVCC и visibility map, от которых зависит index-only scan.
• Полнотекстовый поиск в PostgreSQL — GIN-индексы, которые covering не поддерживают.
• Партиционирование в PostgreSQL — как индексы работают в партиционированных таблицах.
• Составные индексы PostgreSQL - порядок колонок в ключе и правило левого префикса, на котором держится покрытие запроса.
• VACUUM, autovacuum и bloat - почему карта видимости отстаёт и Heap Fetches перестаёт быть нулевым.