Index-only scan, Visibility Map и INCLUDE

EXPLAIN ANALYZE показывает “Index Only Scan” на одном запросе и “Index Scan” на другом — одинаковый индекс, разные таблицы. Разница не в индексе. Она в Visibility Map. Одна таблица была VACUUMed вчера; другая имеет 12% мёртвых tuple. Понимание причины — это разница между 1 мс и 10 мс запросом на тех же данных.

Что стоит index scan vs index-only scan

Обычный index scan для запроса SELECT id, status FROM orders WHERE user_id = 42:

1. Проход B-tree до matching leaf-записей — O(log n), несколько page reads.
2. Для каждого matching TID — fetch heap-строки для получения id и status — один heap page read на строку, если строки разбросаны.

Шаг 2 — узкое место при масштабе. Для 10 000 matching строк на 10 000 разбросанных heap-страницах это 10 000 случайных I/O операций.

Index-only scan полностью устраняет шаг 2. Если индекс содержит все нужные запросу столбцы, Postgres читает только leaf-страницы индекса. Для плотных диапазонов (1000 строк на 10 leaf-страницах) I/O падает с тысяч до десятков.

Visibility Map: почему index-only scan требует её

Postgres использует MVCC — каждая строка может иметь несколько версий (старые и новые tuple от параллельных обновлений). Чтобы определить видимость строки текущей транзакции, Postgres обычно читает системные столбцы строки (xmin, xmax, биты статуса транзакции). Эти столбцы находятся в heap, не в индексе.

Index-only scan не читает heap. Как тогда определить видимость?

Visibility Map (VM) — per-table bitmap с одним битом на heap-страницу. Бит устанавливается в 1 («all visible»), когда каждый tuple на странице виден каждой текущей и будущей транзакции — то есть нет pending параллельных обновлений или удалений. VACUUM устанавливает эти биты после очистки мёртвых tuple. Любая запись, затрагивающая страницу, сбрасывает бит.

При index-only scan для matching TID:

• Если VM-бит для этой страницы установлен: строка гарантированно видима без чтения heap. Нет heap fetch.
• Если VM-бит сброшен: Postgres должен получить heap-страницу для проверки видимости. Это heap fetch, считаемый в EXPLAIN ANALYZE как «Heap Fetches: N».

Высокий счётчик «Heap Fetches» в index-only scan означает, что VM не актуальна — «index-only» scan фактически делает heap fetch для большинства строк.

VACUUM поддерживает VM актуальной

VACUUM освобождает мёртвые tuple и устанавливает VM-биты. Autovacuum запускает его автоматически (когда мёртвые tuple превышают autovacuum_vacuum_scale_factor × размер таблицы, дефолт 20%). Для таблиц, зависящих от index-only scan:

• 20% мёртвых tuple — слишком мягкий порог. Установи autovacuum_vacuum_scale_factor = 0.05 и autovacuum_analyze_scale_factor = 0.02.
• Long-running транзакции блокируют VACUUM от освобождения tuple — одна транзакция, открытая часами, может блокировать VM-обновления на это время.
• Мониторь pg_stat_user_tables.n_dead_tup как leading indicator свежести VM.

-- Настройка autovacuum per-table на горячих OLTP-таблицах
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02
);

INCLUDE-clause: covering-индексы

Postgres 11+ поддерживает CREATE INDEX ... INCLUDE (cols) — INCLUDE-столбцы хранятся в leaf-страницах индекса, но не являются частью sort key. Результат: индекс становится «covering» для запросов, которым нужны эти дополнительные столбцы в SELECT-projection.

-- Ключевые столбцы: workspace_id, created_at DESC (sort key)
-- INCLUDE: id, total_cents (payload projection — не в sort key)
CREATE INDEX CONCURRENTLY idx_orders_ws_recent_covering
  ON orders (workspace_id, created_at DESC)
  INCLUDE (id, total_cents);

Запрос SELECT id, total_cents FROM orders WHERE workspace_id = $1 ORDER BY created_at DESC LIMIT 50 теперь обслуживается только из индекса. Нет heap fetch. Читается 50 leaf-записей в порядке.

Цена: INCLUDE-столбцы увеличивают размер индекса (хранятся в каждой leaf-записи). Большие INCLUDE payload могут приближать размер индекса к размеру таблицы. Включай только столбцы projection реально нужные горячему запросу.

Concurrency и timing построения индекса

CREATE INDEX CONCURRENTLY на 100M-строк таблице занимает 10-30 минут. Во время построения:

1. Postgres сканирует таблицу дважды.
2. Между фазами ждёт завершения всех текущих транзакций.
3. Индекс реплицируется на standby через WAL.

Последствия:

• Long-running аналитические запросы на той же таблице могут застопорить build часами.
• Несколько concurrent builds на одной БД конкурируют за I/O — планируй большие builds в low-traffic окна.
• После build проверяй pg_indexes.indisvalid — упавший build оставляет INVALID-индекс.

-- Проверка INVALID-индексов после CONCURRENTLY-build
SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE NOT indisvalid;

Recovery из INVALID: DROP INDEX CONCURRENTLY idx_name; затем повторить.