Типы сканирования: Seq, Index, Bitmap, Index-Only

EXPLAIN показывает Seq Scan на таблице из 10M строк, хотя индекс на колонке фильтрации существует. Планировщик не сломан — он подсчитал, что последовательное сканирование дешевле для данной селективности. Понимать, когда и почему планировщик выбирает тот или иной тип сканирования — вот что отличает угадывание от диагностики.

Чтение строки стоимости

Каждый узел плана печатает:

(cost=startup..total rows=N width=B)

• startup — стоимость до выдачи первой строки. Ноль для Seq Scan; ненулевая для Sort (нужно прочитать всё первым делом).
• total — стоимость полного выполнения узла.
• rows — оценка строк, которые выдаст этот узел.
• width — средняя ширина строки в байтах.

Стоимость измеряется в произвольных единицах, где 1.0 ≈ одно последовательное чтение страницы. Важны только соотношения; абсолютные числа без контекста бессмысленны. С ANALYZE каждый узел также получает (actual time=startup..total rows=N loops=L) — реальное wall time в миллисекундах.

Правило интерпретации: если узел находится на внутренней стороне Nested Loop, делите actual time на loops, чтобы получить стоимость одного выполнения.

Четыре типа сканирования

Seq Scan

Формула стоимости: relpages × seq_page_cost + reltuples × cpu_tuple_cost. По умолчанию seq_page_cost = 1.0, cpu_tuple_cost = 0.01. Для таблицы в 5000 страниц: 5000 × 1.0 + 1M × 0.01 = 15,000 единиц. Оптимален, когда предикат неселективен (совпадает большинство строк) — читать всю кучу последовательно быстрее, чем случайно переходить к каждой совпадающей строке.

Index Scan

Стоимость растёт с random_page_cost × matching_rows. По умолчанию random_page_cost = 4.0 (эпоха HDD). Для 100 совпадающих строк: 100 × 4.0 = 400 — намного дешевле, чем Seq Scan за 15,000. Для 100,000 совпадающих строк: 100,000 × 4.0 = 400,000 — Seq Scan выигрывает. Точка пересечения примерно там, где предикат совпадает с 5–15% таблицы.

Замечание по настройке для SSD: на NVMe SSD случайные чтения лишь в 1.5–2× медленнее последовательных — не в 4×. Установите random_page_cost = 1.1 в postgresql.conf для SSD-систем. Планировщик будет предпочитать Index Scan для значительно большей доли запросов.

Bitmap Heap Scan

Двухэтапный процесс: сначала Bitmap Index Scan строит в памяти битовую карту всех совпадающих TID из индекса; затем Bitmap Heap Scan сортирует TID по физическому адресу страницы и читает кучу по порядку. Это преобразует разрозненные случайные чтения в почти последовательный обход кучи. Побеждает, когда совпадает много строк, но они разбросаны по куче — промежуток между Index Scan и Seq Scan.

Битовую карту можно объединять через OR для нескольких индексов (узел BitmapOr) — полезно, когда предикат OR имеет индекс на каждую колонку.

Index Only Scan

Читает только листья индекса — без чтения кучи — при двух условиях: (1) все нужные запросу колонки хранятся в индексе (как ключевые или через INCLUDE), и (2) Visibility Map подтверждает, что страница «all-visible» (каждый кортеж виден всем транзакциям). Когда бит VM не установлен, Postgres должен обратиться к куче для этой страницы. В выводе EXPLAIN ANALYZE значение Heap Fetches: N показывает, как часто это происходило — высокое N означает, что VM устарела и VACUUM давно не запускался.

BUFFERS: картина I/O

EXPLAIN (ANALYZE, BUFFERS) добавляет учёт буферов на каждом узле:

Seq Scan on orders  (actual time=0.1..450 rows=1000000 loops=1)
  Buffers: shared hit=4800 read=200

• shared hit — страница найдена в кеше shared_buffers Postgres (без дискового I/O)
• read — страница получена с диска или из ОС
• dirtied — страница изменена этим узлом
• written — страница сброшена на диск

Высокий read при низком hit означает, что рабочий набор больше shared_buffers или запрос обращается к холодным данным. Это диагностирует «медленно из-за CPU или I/O»: высокие reads = ограничен I/O; мало reads, но высокое actual time = ограничен CPU (обычно плохое соединение или тяжёлая сортировка).