Табличные и индексные методы доступа

Все четыре дают один результат - разница только в стоимости при разной селективности. Seq Scan платит за чтение всех страниц, но читает их последовательно (дёшево за страницу). Index Scan читает мало, но платит за случайные обращения к таблице. Между ними - Bitmap, а Index-Only обходит таблицу вовсе.

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

Index Scan идёт по индексу к совпадающим записям и за каждой строкой отдельно ходит в таблицу (heap). Каждое такое обращение - случайное чтение страницы (random_page_cost = 4). Если строк мало - выгодно: прочитали пару записей индекса, сходили за двумя строками. Если много - разорительно: тысячи случайных обращений к таблице обойдутся дороже, чем прочитать её целиком последовательно.

EXPLAIN SELECT * FROM big WHERE id = 42;        -- Index Scan: одна строка

EXPLAIN SELECT * FROM big WHERE id > 0;         -- Seq Scan: почти всё

Когда строк не «мало» и не «почти всё», а посередине, оба полюса плохи: Index Scan утонет в случайных обращениях, Seq Scan прочитает лишнее. Решение - Bitmap Scan в два узла.

Сначала Bitmap Index Scan проходит индекс и строит битовую карту: какие страницы таблицы содержат совпадения. Потом Bitmap Heap Scan читает эти страницы - но не в порядке индекса, а в физическом порядке таблицы, по одной странице за раз. Так случайные обращения превращаются в почти последовательные, и каждая нужная страница читается ровно один раз, даже если на ней несколько совпадений.

Bitmap Heap Scan on tickets  (cost=... rows=...)

  Recheck Cond: (flight_id = ANY ('{1,2,3}'))

  ->  Bitmap Index Scan on tickets_flight_idx

        Index Cond: (flight_id = ANY ('{1,2,3}'))

Сверх того: две битовые карты можно пересечь (BitmapAnd) или объединить (BitmapOr) - так PostgreSQL использует сразу два индекса для условия a = 1 AND b = 2.

Index Scan ходит в таблицу за строкой, потому что в индексе лежат только проиндексированные колонки плюс ссылка (ctid). Но если запросу нужны ровно те колонки, что есть в индексе, в таблицу можно не ходить - всё уже в индексе. Это Index-Only Scan, самый дешёвый доступ.

-- индекс по (flight_id), запрос просит только flight_id

EXPLAIN SELECT flight_id FROM tickets WHERE flight_id = 7;

-- Index Only Scan using tickets_flight_idx

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

CREATE INDEX ON tickets (flight_id) INCLUDE (passenger);

Теперь SELECT flight_id, passenger WHERE flight_id = 7 берёт обе колонки из индекса. Но есть условие, без которого Index-Only Scan не сработает, - и это главный подводный камень.

Индекс не хранит информацию о видимости версий строк - её знает только сам кортеж в таблице. Поэтому, чтобы вернуть строку из индекса, не сходив в таблицу, PostgreSQL должен быть уверен, что она видна всем. Эту уверенность даёт visibility map: бит «вся страница видна всем» (см. visibility-map).

Если страница в visibility map помечена как all-visible, индекс отдаёт строку сам. Если нет - приходится всё-таки сходить в таблицу проверить видимость. В EXPLAIN ANALYZE это видно как Heap Fetches:

Index Only Scan using ... (actual ...)

  Heap Fetches: 4310      ← пришлось сходить в таблицу 4310 раз

Большой Heap Fetches означает, что visibility map не выставлена - обычно после массовых изменений без VACUUM. Лекарство: VACUUM выставляет биты, и при следующем запуске Heap Fetches: 0 - Index-Only Scan работает в полную силу.

Почему планировщик переключается между методами? Из-за стоимости, которая по-разному растёт с селективностью.

стоимость

  │            Seq Scan (горизонталь: не зависит от доли строк)

  │ ───────────────────────────

  │        ╱  Index Scan (круто растёт: random I/O на строку)

  │      ╱

  │   ╱

  └──┴───────────────────── доля строк

     ^ точка перелома (~2-5%)

Index Scan дёшев при малой доле и дорожает быстро (каждая строка - случайное обращение). Seq Scan стоит одинаково при любой доле. Линии пересекаются где-то на единицах процентов: левее выгоден индекс, правее - последовательное чтение, в зоне перехода - Bitmap. Точное место перелома зависит от random_page_cost: снизишь его - индекс станет выгоден на большей доле. Разбор - в selectivity-crossover.

На большой таблице один процесс читает её долго. PostgreSQL умеет распараллелить: ведущий процесс запускает рабочие (workers), и они делят страницы между собой. В плане это видно как Parallel Seq Scan под узлом Gather.

Делёж устроен так, что воркеры не топчутся: каждый берёт следующий блок страниц из общего «прилавка», обрабатывает и возвращается за новым. Параллельный план включается, когда таблица крупная и есть свободные слоты воркеров (max_parallel_workers_per_gather). Это не новый метод доступа, а параллельная форма уже знакомых - Seq Scan и Index Scan тоже бывают параллельными.