Index Only Scan и Heap Fetches

Утро. Лена зашла в кабинет Макса с блокнотом:

— Макс, привет! А что такое «сканирование только индекса»? Я видела в плане узел Index Only Scan. Расскажи, как он работает?

Макс повернулся к Лене:

— Доброе утро! Твое упорство и интерес меня восхищают.

— Ну мне правда интересно. — смутилась Лена. — Ну а все же, что это?

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

Он набросал простой пример:

EXPLAIN ANALYZE
SELECT login FROM users
WHERE login LIKE 'z%';

План запроса:

Index Only Scan using users_login_uindex on users  (cost=0.28..156.60 rows=1 width=10) (actual time=2.690..2.709 rows=17 loops=1)
  Filter: ((login)::text ~~ 'z%'::text)
  Rows Removed by Filter: 5961
  Heap Fetches: 469
Planning Time: 0.144 ms
Execution Time: 2.745 ms

— Кажется классно, — кивнула Лена. — Получается таблица в таком случае вообще не нужна, достаточно только индекса?

— А вот это, Лена, самый коварный миф про Index Only Scan. Индекс хранит не всё. Обрати внимание на строку Heap Fetches.

— Вижу там цифры, а что они значат?

— Дело в том, что сам индекс не хранит информацию о видимости строк. Есть специальная карта видимости, куда VACUUM помечает «чистые» страницы — те, где все версии строк таблицы видны всем транзакциям. Если страница помечена, Postgres пропускает проверку в heap и сразу отдаёт данные из индекса. Если же пометка не стоит — он идёт в таблицу, проверяет видимость и увеличивает счётчик Heap Fetches.

— Ничего не понятно. — Честно призналась Лена.

— Смотри, таблица - это книга, индекс - глоссарий. Но в этой книге постоянно ведутся правки - кто-то стирает строки, кто-то дописывает новые и оставляет об этом отметки. Сам глоссарий содержит только слово для поиска и номера страниц, где оно встречалось. Но он сам не знает, зачистили ли эти страницы от пометок «старое» или «удалено». — Макс выжидающе смотрел на Лену.

— Продолжай.

— Чтобы не перелистывать каждую страницу (то есть не заглядывать в основную таблицу), Postgres ведёт отдельный список — «карта видимости». В этой карте отмечены страницы, которые точно чистые и в порядке.

• Если страница в карте помечена как «чистая», то сразу берём данные из индекса и не ходим в таблицу.

• Если страницы там нет, значит нужно проверить: заглянуть в таблицу и убедиться, что строка «видна» (это и считается одной «вынужденной проверкой» — Heap Fetch).

Чем чаще делается VACUUM, тем больше страниц помечено чистыми, и тем реже Postgres будет лишний раз заглядывать в таблицу. — подвел итог Макс.

— То есть, чтобы Heap Fetches ушли в ноль, нужен покрывающий индекс и актуальная карта видимости, правильно?

— Именно. В Postgres есть статистика, по которой это можно отслеживать - это поля relpages (всего страниц) и relallvisible (сколько видимых) в pg_class. Можешь потренироваться (строго на тесте) - засечь эти значения для какой-нибудь таблицы, затем выполнить VACUUM и посмотреть как они изменятся.