Среди применяемых в PostgreSQL методов доступа к данным Index Only Scan стоит особняком, считаясь у многих разработчиков "волшебной пилюлей" для ускорения работы запроса - мол, "Index Scan - плохо, Index Only Scan - хорошо, как только получим его в плане - все станет замечательно".

Как минимум, это утверждение неверно. Как максимум, при определенных условиях может вызвать проблемы чуть ли не на ровном месте.

Как PostgreSQL хранит данные

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

• каждая таблица или индекс - отдельный файл данных (pg_class.relfilenode)

• каждый файл делится на физические сегменты, не превышающие 1GB

• каждый сегмент состоит из последовательности страниц данных по (обычно) 8KB

• страница данных содержит непосредственно набор записей

• дополнительно к файлу данных используются два файла с картами: Free Space Map (FSM) и Visibility Map (VM)

Вот как раз с последней и могут возникать проблемы:

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

Как работает Index Scan

Для поиска необходимой записи Index Scan:

• переходит по записям структуры btree-дерева в файле индекса, пока не дойдет до "листа", указывающего на файл таблицы

• извлекает из файла таблицы необходимую запись, удостоверяясь в ее "видимости" для текущей транзакции (по значениям xmin/xmax)

Как работает Index Only Scan

Index Only Scan может быть выбран в качестве способа извлечения записей в случае чтения запросом только полей, которые присутствуют в самом индексе - его ключах или INCLUDE-списке.

По этой причине использование SELECT * FROM ... почти всегда "ломает" возможность использования Index Only Scan.

Для поиска необходимой записи Index Only Scan:

• переходит по записям структуры btree-дерева в файле индекса, пока не дойдет до "листа", указывающего на файл таблицы

• проверяет бит "видимости всех записей" нужной страницы с помощью Visibility Map

• если бит не взведен, ровно как "обычный" Index Scan, извлекает из файла таблицы необходимую запись, удостоверяясь в ее "видимости" для текущей транзакции (по значениям xmin/xmax)

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

Проблемы Index Only Scan

Однако, в силу алгоритма заполнения VM, существуют ситуации, когда Index Only Scan всегда придется извлекать запись из таблицы, постоянно проигрывая "обычному" Index Scan на необходимости проверки данных VM.

Процитирую документацию еще раз:

Биты карты видимости устанавливаются только при очистке, а сбрасываются при любых операциях, изменяющих данные на странице.

Из этого предложения следует ровно два паттерна, когда Index Only Scan будет заведомо неэффективен:

• извлечение "свежевставленных" записей, которые еще не успел пройти [auto]VACUUM

• наличие даже редких UPDATE/DELETE по произвольно разбросанным по страницам записям

Рассмотрим на простом примере "типа-мониторинга", когда у нас есть метрика (m) и метка времени (ts):

-- создаем исходную таблицу с 1M случайных записей
CREATE TABLE ios_orig AS
SELECT
  (random() * 1e3)::integer m                -- метрика
, now() - (random() * '1 msec'::interval) ts -- метка времени
FROM
  generate_series(1, 1e6);

-- копируем данные в тестовую таблицу
CREATE TABLE ios_test AS
  TABLE ios_orig;

-- создаем индекс, по которому будем искать
CREATE INDEX ON ios_test(m, ts);

Попробуем отобрать часть записей по условию, четко ложащемуся на индекс:

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT
  m
, ts
FROM
  ios_test
WHERE
  m > 900
ORDER BY m;

Heap Fetches как признак беды

... и получаем время выполнения дольше 80ms:

Index Only Scan using ios_test_m_ts_idx on ios_test (actual time=0.032..75.224 rows=99094 loops=1)
  Index Cond: (m > 900)
  Heap Fetches: 99094
  Buffers: shared hit=99478
Planning Time: 0.084 ms
Execution Time: 80.838 ms

Обратите внимание на строку Heap Fetches - ровно она показывает нам количество записей, которые пришлось "достать" из файла таблицы.

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

Index Only Scan vs Index Scan

Давайте попробуем заставить PostgreSQL воспользоваться "обычным" Index Scan:

SET enable_indexonlyscan = FALSE;

Index Scan using ios_test_m_ts_idx on ios_test (actual time=0.028..64.810 rows=99094 loops=1)
  Index Cond: (m > 900)
  Buffers: shared hit=99478
Planning Time: 0.091 ms
Execution Time: 70.010 ms

Мы вычитали ровно столько же данных, но сделали это почти на 15% быстрее! А ведь вся разница - лишь в необходимости проверки VM.

Когда Index Only Scan все-таки выигрывает

Давайте все же приведем Visibility Map в порядок (для для этого нам необходимо прогнать VACUUM) и сбросим запрет использования Index Only Scan:

VACUUM ios_test;
RESET enable_indexonlyscan;

Index Only Scan using ios_test_m_ts_idx on ios_test (actual time=0.026..15.500 rows=99094 loops=1)
  Index Cond: (m > 900)
  Heap Fetches: 0
  Buffers: shared hit=400
Planning Time: 0.080 ms
Execution Time: 20.831 ms

Вот теперь все отлично Heap Fetches: 0, а время выполнения запроса сократилось в 4 раза!

Подведем промежуточные итоги:

То есть при актуальном состоянии VM мы получаем кратный прирост производительности запроса, а ее неактуальное состояние мы можем контролировать с помощью метрики Heap Fetches.

А чтобы вы могли быстро понять причину подобной проблемы в своем запросе, мы добавили в наш сервис анализа планов explain.tensor.ru в набор автоматических рекомендаций еще и эту проверку:

А в завершение еще несколько слов о том, как можно себе все сломать и снова починить.

Рулим VACUUM'ом

Итак, мы выяснили, что пока по данным еще не успел пройти процесс autovacuum (или пока мы не выполним VACUUM вручную), они очень "дорого" обходятся Index Only Scan.

В этом достаточно просто убедиться, докинув в нашу таблицу еще столько же данных:

INSERT INTO ios_test
  TABLE ios_orig;

Index Only Scan using ios_test_m_ts_idx on ios_test (actual time=0.036..86.064 rows=198188 loops=1)
  Index Cond: (m > 900)
  Heap Fetches: 99102
  Buffers: shared hit=99660
Planning Time: 0.133 ms
Execution Time: 96.159 ms

Записей теперь отбирается вдвое больше, но в Heap Fetches попала только "новая" часть - поэтому в наших интересах заставить VACUUM выполняться как можно чаще на нашей таблице.

Для этого существует несколько способов, игнорирование которых может вызвать проблемы:

• autovacuum_naptime - этот параметр определяет как часто должен стартовать autovacuum-процесс

  Если вдруг вы решили сэкономить на "паразитной" активности PostgreSQL и выкрутили значение до '1d' (раз в день), а потом массово что-то записали в таблицу, то будете получать Heap Fetches все это время.

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

• autovacuum_vacuum_threshold и autovacuum_vacuum_insert_threshold (плюс autovacuum_vacuum_scale_factor и autovacuum_vacuum_insert_scale_factor) отвечают за автоматический запуск VACUUM на таблице после изменения/вставки определенного количества (или процента) записей

  Они могут быть определены как для всего сервера в целом, так и заданы для определенных, наиболее "горячих", таблиц с помощью команды

  ALTER TABLE ... SET (
    autovacuum_vacuum_threshold = ...
  );