В PostgreSQL план показывает индексное сканирование только по индексу, но запрос всё равно читает таблицу. Какой механизм MVCC это объясняет?
Индексное сканирование без обращения к таблице не гарантирует отсутствие чтения таблицы в PostgreSQL. При index-only scan сервер может взять значения из индекса, но должен проверить видимость версии строки для текущей транзакции. Если соответствующая страница таблицы не отмечена в карте видимости как полностью видимая, PostgreSQL обращается к таблице для проверки MVCC.
Обычный индекс хранит ключи и ссылки на строки, но не всегда содержит информацию о том, видна ли конкретная версия строки текущей транзакции. Это связано с MVCC: после обновлений и удалений в таблице могут существовать версии строк, видимые разным транзакциям.
Механизм index-only scan появился как способ обслуживать запрос из одного индекса, когда индекс содержит все необходимые столбцы. Он решает проблему лишних обращений к таблице, но не отменяет необходимость проверять видимость строк.
Планировщик может выбрать index-only scan, потому что индекс содержит столбцы из выборки и условий. Однако фактическое выполнение может сопровождаться большим числом обращений к таблице, если страницы таблицы недавно изменялись или не успели попасть в карту видимости.
В результате план выглядит дешевым, но запрос работает медленно: чтение индекса дополняется случайными обращениями к таблице. Особенно заметно это на больших таблицах и при возврате множества строк.
PostgreSQL использует карту видимости — служебную структуру, показывающую, что все строки конкретной страницы таблицы видимы всем текущим и будущим транзакциям. Если такая отметка есть, серверу не нужно читать страницу таблицы для проверки видимости, и index-only scan действительно может работать только с индексом.
Если страница не отмечена, PostgreSQL выполняет проверку в таблице. Поэтому важен не только набор столбцов индекса, но и состояние таблицы после вставок, обновлений и удалений. Обновления могут создавать новые версии строк и снимать отметки видимости с затронутых страниц.
Обслуживание таблицы, включая VACUUM, позволяет восстановить информацию о страницах, где старые версии строк больше не нужны транзакциям. Но VACUUM не превращает любой index-only scan в полностью индексный: страницы с ещё активными версиями или незавершёнными изменениями могут оставаться невидимыми для такого чтения.
При оценке производительности нужно смотреть не только тип операции в плане, но и фактическое число обращений к таблице, например показатель heap fetches в фактическом плане. Покрывающий индекс уменьшает объём данных для чтения, но увеличивает стоимость вставок и обновлений и занимает дополнительное место.
Отчёт выбирал идентификаторы и даты последних заказов из большого индекса. План показывал index-only scan, но после массового обновления заказов время выполнения выросло в несколько раз, а число обращений к таблице стало большим.
Рассматривались три варианта. Увеличение индекса могло покрыть дополнительные столбцы, но не устраняло проблему проверки видимости и увеличивало стоимость записи. Полное сканирование таблицы было предсказуемым, но для выборки небольшой доли строк оказалось дороже. Принудительный выбор index-only scan не решал причину и мог ухудшить ситуацию.
Выбрали регулярное обслуживание таблицы и проверку фактического плана после завершения массовых изменений. После того как страницы стали отмечаться как полностью видимые, число обращений к таблице уменьшилось, и индексное чтение приблизилось к ожидаемой стоимости.
Достаточно ли, чтобы индекс содержал все столбцы запроса, для настоящего index-only scan?
Нет. Индекс должен содержать необходимые значения, но PostgreSQL также должен иметь возможность подтвердить видимость строк без чтения таблицы. Поэтому покрывающий индекс — необходимое условие для многих случаев, но не гарантия отсутствия heap fetches.
Почему после обновления небольшого числа строк может замедлиться чтение большого диапазона?
Изменение одной строки может снять отметку видимости со всей страницы таблицы, на которой она находится. Если запрос читает много строк с таких страниц, серверу приходится обращаться к таблице для проверки видимости каждой подходящей индексной ссылки. Поэтому влияние определяется не только числом изменённых строк, но и распределением изменений по страницам.
Почему принудительное использование index-only scan не является надёжной оптимизацией?
Хинт или иное принуждение плана не устраняет MVCC-проверки. Если карта видимости не помогает, индексное чтение всё равно будет выполнять обращения к таблице, а принудительный выбор может помешать оптимизатору выбрать более подходящий план, например последовательное сканирование. Сначала нужно проверить фактические heap fetches, состояние таблицы и характер нагрузки.