Запросы, отвечающие из одного индекса без единого обращения к таблице

Индекс ускоряет поиск нужных строк, но потом PostgreSQL всё равно идёт в саму таблицу за их содержимым. Эти дополнительные обращения к таблице и есть скрытая цена многих запросов, особенно когда строк находится тысячи. Между тем у PostgreSQL есть режим, при котором запрос целиком отвечается из индекса, не касаясь таблицы вообще. Это сканирование только по индексу, и при грамотной настройке оно экономит тысячи операций ввода-вывода. Разберём, как оно устроено, как переписать запросы и индексы под него и почему оно иногда тихо не срабатывает, хотя план уверяет в обратном.

Почему обычный поиск по индексу всё равно лезет в таблицу

В PostgreSQL данные строк физически лежат в так называемой куче, а индекс хранит лишь значения ключа и указатели на строки. При обычном сканировании по индексу база сначала находит в индексе подходящие записи, а потом по указателям идёт в кучу за остальными полями строки. Если запрос находит одну строку, это одно обращение. Но если запрос возвращает тысячи строк, это тысячи разрозненных обращений к куче, разбросанных по диску. Именно они и составляют основную стоимость.

Сканирование только по индексу решает проблему в корне: оно отвечает на запрос из одного индекса без всякого обращения к куче. Чтобы это стало возможным, должны выполниться два фундаментальных требования. Первое: индекс должен содержать все колонки, которые нужны запросу, и в условии, и в выборке. Второе: тип индекса должен поддерживать такое сканирование, и обычное сбалансированное дерево его поддерживает. Если оба условия соблюдены, все нужные значения берутся из индекса, и обращение к таблице физически не требуется.

Зачем индексу проверять видимость и при чём тут карта

Но есть тонкость, из-за которой всё не так просто. У PostgreSQL многоверсионное управление конкурентным доступом, и любое чтение обязано убедиться, что строка видима текущему снимку транзакции. Информация о видимости хранится не в индексе, а только в куче. На первый взгляд это значит, что обращаться к куче придётся всё равно ради проверки видимости, и тогда весь смысл теряется.

Для редко меняющихся данных есть обходной путь. PostgreSQL ведёт для каждой страницы кучи отметку о том, все ли строки на ней достаточно стары, чтобы быть видимыми всем текущим и будущим транзакциям. Эта информация лежит в карте видимости. Найдя в индексе подходящую запись, сканирование проверяет бит карты видимости для соответствующей страницы кучи. Если бит установлен, строка точно видима, и данные возвращаются из индекса без дальнейшей работы. Если не установлен, приходится идти в кучу за проверкой, и тогда выигрыша перед обычным сканированием нет.

Хитрость в размерах. Карта видимости на четыре порядка меньше описываемой ею кучи, поэтому даже в успешном случае мы меняем обращения к огромной куче на обращения к крошечной карте, и физического ввода-вывода требуется несоизмеримо меньше. В большинстве ситуаций карта видимости постоянно держится в памяти. Вывод важный: сканирование только по индексу возможно при соблюдении двух требований, но выигрышным оно станет, лишь когда значительная доля страниц кучи помечена как полностью видимая.

Что такое покрывающий индекс и как его построить

Чтобы эффективно пользоваться этим режимом, создают покрывающий индекс, специально спроектированный так, чтобы содержать все колонки, нужные определённому частому запросу. Поскольку запросы обычно тянут больше колонок, чем те, по которым ищут, PostgreSQL позволяет добавить в индекс колонки, которые не участвуют в поиске, а служат лишь полезной нагрузкой. Делается это через специальное включение списка дополнительных колонок.

-- Запрос, который хотим ускорить
SELECT customer_name, customer_email
FROM customers
WHERE customer_id BETWEEN 1000 AND 2000;

-- Покрывающий индекс: ключ для поиска, остальное как нагрузка
CREATE INDEX idx_customers_cover
  ON customers (customer_id)
  INCLUDE (customer_name, customer_email);

Включённые колонки хранятся в индексе рядом, но не сортируются по ним: они существуют исключительно ради того, чтобы запрос отвечался из одного индекса. Это принципиальное отличие от простого составного индекса, где все колонки идут в ключ. Включать колонки нагрузкой выгоднее, потому что индекс остаётся компактнее и эффективнее для поиска по ключу.

Альтернативный подход это составной индекс, когда дополнительная колонка тоже идёт в ключ. Он годится, если по этой колонке тоже фильтруют или сортируют, но если она нужна только для возврата, включение нагрузкой предпочтительнее.

Как убедиться по плану, что обращений к таблице нет

Проверить, реально ли запрос отвечается из индекса, помогает разбор плана с замером буферов. Ключевая строка в выводе это число обращений к куче.

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_name, customer_email
FROM customers
WHERE customer_id BETWEEN 1000 AND 2000;

-- Index Only Scan using idx_customers_cover on customers
--   Index Cond: ((customer_id >= 1000) AND (customer_id <= 2000))
--   Heap Fetches: 0

Две вещи здесь говорят об успехе. Пометка о сканировании только по индексу означает, что PostgreSQL использовал индекс, не трогая кучу. А нулевое число обращений к куче означает, что все строки пришли из индекса благодаря карте видимости. Время выполнения при этом несоизмеримо мало по сравнению со сканированием таблицы. Если же число обращений к куче ненулевое, значит часть страниц не помечена как полностью видимая, и режим работает лишь наполовину.

Почему план говорит одно, а обращения к куче всё равно есть

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

Покажу на наглядном примере. Свежая таблица, по которой ещё не проходила очистка, не имеет карты видимости вовсе, и тогда база вынуждена обращаться к куче для каждой строки. Стоит выполнить очистку, и карта видимости появляется.

-- Сразу после массовой загрузки карты видимости ещё нет
VACUUM customers;
-- Теперь страницы помечаются полностью видимыми, обращения к куче исчезают

Ещё показательнее ситуация с изменением. Любое обновление строки на странице сбрасывает её бит в карте видимости, и сканирование снова вынуждено идти в кучу. Причём изменение одной строки создаёт новую версию строки, и обращений к куче может оказаться даже несколько. Поэтому таблицы с частыми изменениями плохо подходят под этот режим: обновлённые и удалённые строки задерживают возврат страниц в полностью видимое состояние и вынуждают обращаться к куче.

Почему очистка это условие, а не опция

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

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

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_vacuum
FROM pg_stat_user_tables
WHERE relname = 'customers';

Если время последней автоматической очистки устарело, а мёртвые строки накапливаются, стоит настроить автоматическую очистку так, чтобы она запускалась чаще именно на этой таблице. Здесь же кроется любопытный нюанс работы базы: при сканировании индекс умеет помечать заведомо невидимые записи как мёртвые, и иногда повторный разбор того же запроса показывает на одно обращение к куче меньше просто потому, что предыдущий разбор пометил устаревшую запись.

Где режим уместен и где от него мало толку

Сканирование только по индексу блистает в нескольких сценариях. На больших таблицах с избирательными запросами, где отказ от обращений к куче экономит тысячи или миллионы операций ввода-вывода. На дашбордах отчётов и программных интерфейсах, где часто запрашивают небольшое число колонок. На запросах с сортировкой и ограничением выборки, где результат быстро отдаётся прямо из индекса. И, разумеется, на покрывающих индексах, спроектированных под частые обращения к конкретному набору колонок.

А вот где толку мало. На маленьких таблицах последовательное сканирование может оказаться не медленнее. На таблицах с частыми изменениями, где обновлённые строки постоянно сбрасывают карту видимости. В этих случаях овчинка не стоит выделки, и проще не городить покрывающий индекс.

Как не дать оптимизации тихо сломаться со временем

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

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

Какой стратегии придерживаться

Сканирование только по индексу это один из самых действенных приёмов ускорения чтения, но он требует понимания и дисциплины. Разумный подход складывается из нескольких решений. Строить покрывающие индексы под частые запросы, вынося возвращаемые, но не фильтруемые колонки в нагрузку. Проверять результат через разбор плана и добиваться нулевого числа обращений к куче. Держать очистку в форме, потому что без актуальной карты видимости весь приём бесполезен. Не применять его на маленьких или часто меняющихся таблицах, где он не окупается. И следить за тем, чтобы изменения в коде не добавляли в выборку колонки, выпадающие из индекса.

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