В чем разница между Index Scan и Index Only Scan?
Для работы понадобилось ХОТЯ БЫ ПОВЕРХНОСТНО (ключевое слово) разобраться в оптимизации SQL-запросов. Не понимаю разницу между Index Scan и Index Only Scan, хотелось бы понять хотя бы поверхностно, без сильного углубления в теорию (если это возможно конечно). Как я понимаю, индексы в принципе структура, которая создается как некий "справочник" с ключом (например, значением заиндексированного столбика) и указателем на строки, где хранятся эти значения. Но в чем тогда разница между сканированием по индексам и сканированиям ТОЛЬКО индексов? В том, что сканирование только индексов будет происходить сразу из этого "справочника" и будет происходить фильтрация только по индексированному столбику?
Ответы (2 шт):
В PostgreSQL index only scan появился, начиная с 12-ой версии.
Если поверхностно и на пальцах, то при выборке данных стандартно используется основная "таблица". Если же делается выборка с условиям по полям, которые проиндексированы, то для поиска так же используется индекс. Это index scan. Т.е. по индексу определяется какие записи нужны, а потом из таблицы уже извлекаются данные.
Если же в выборке используются только те поля, для которых есть индекс, то возможен вариант index only scan. Когда поиск и выборка делается только по индексу и не затрагивает основную таблицу.
Если на простом, бытовом примере, то предположим есть обычная таблица из двух столбцов key и val
И нам нужно получить записи у которых значения поля key находятся в диапазоне от 3 до 10. Тогда запрос будет
SELECT * FROM table WHERE key BETWEEN 3 AND 10;
И это будет index scan. По индексу определили какие записи соответствуют условию, а потом получили их из базы.
Если же запрос будет
SELECT key FROM table WHERE key BETWEEN 3 AND 10;
Тут возможен случай index only scan. Точно так же по индексу определили какие записи соответствуют указанному условию. И так как нужно вернуть только значение поля key (а оно уже есть в индексе), то для формирование результата не нужно обращаться к основной таблице.
Таким образом index only scan может работать быстрее.
Одним из любопытных нововведений postgresql 9.2 было появление техники index only scan. (именно 9.2, а не 12, которую по непонятно причине некоторые пользователи считают родоначальником index only scan)
Обычный index scan при поиске по индексу возвращает TID (tuple ID) строк, подходящих по критерию поиска. По этому TID затем postgresql идёт в файлы таблицы и читает данные самой строки (tuple).
Index only scan же даёт возможность пропустить чтение данных из самой таблицы, если соблюдаются важные условия:
- в индексе есть все необходимые данные, которые запрашиваются из этой таблицы
- visibility map разрешает пропустить проверку видимости MVCC снэпшота, т.к. этот tuple виден всем
Таким образом, если у вас есть запрос
select b from foo where a = ?
то индекс
create index on foo using btree(a);
ускорит запрос, но поскольку b можно достать только из таблицы, здесь применим только index scan (или bitmap index scan)
А вот если есть индекс
create index on foo using btree(a, b);
либо же (к этому вернусь чуть позже)
create index on foo using btree(a) include (b);
то у нас и колонка a для поиска и колонка b с искомым значением есть сразу в индексе. Если visibility map позволит, то мы можем не читать данные из таблицы, а вернуть результат сразу из индекса. Это может быть производительнее так как меньше обращений IO.
В худшем случае index only scan работает как index scan, запрашивая данные всех tuple из таблицы.
visibility map позволит?
что такое visibility map и что он должен позволять? Postgresql - это MVCC база. В таблице одновременно может быть много версий одной и той же строки. Например, после update у нас будет две версии этой строки - одна с данными до обновления и одна с данными после обновления. Базе необходимо как-то разделять, какой транзакции какие версии строк видны. И вот все данные о транзакционной видимости хранятся рядом с tuple в само таблице. Поиск по индексу не может знать, видна ли эта строка текущей транзакции и просто возвращает TID всех, а уже потом по данным таблицы проверяется, видна ли эта версия строки текущей транзакции или нет.
visibility map - это стоящая сбоку таблицы структура, которая может быстро сказать базе, что все строки на заданной странице данных в данный момент видны всем транзакциям. Изначально оно делалось для ускорения автовакуума (нет нужды читать и обрабатывать страницы, в которых все строки видны всем - то есть заведомо нет мёртвых строк), потом пригодилось для index only scan. Обновляет visibility map автовакуум или же запущенный вручную vacuum.
И это важный момент: рассчитывать на пропуск чтения таблицы при index only scan для чтения часто обновляемых данных не приходится совершенно.
Позже, в postgresql 12 появился синтаксис include
create index on foo using btree(a) include (b);
Тут простая идея, если данные колонки нам в индексе нужны только для index only scan - то нам нет необходимости тратить ресурсы на поддержку отсортированного представления (a,b), а нужно только сортировать (a) плюс рядом в листе дерева сохранить b для удобного доступа. Это всё.
IRL
Вам не нужен index only scan "на всякий случай". Создавайте индекс необходимый для index only scan только если можете подтвердить тестами, что он приносит пользу вашему запросу и вашему проекту. Это иногда бывает полезным инструментом, но отнюдь не повсеместным.