Реальная скорость запроса определяется почти полностью одной метрикой: сколько страниц нужно прочитать с диска. Все остальные cost-оценки — это уже надстройки. И именно эту метрику показывает EXPLAIN (ANALYZE, BUFFERS).

В EXPLAIN без BUFFERS ты видишь cost, который сам по себе сделан на предположении, что страницы могут быть в кэше или на диске — но конкретики нет. С BUFFERS ты видишь реальное число hit (нашли в shared_buffers) и read (прочитали с диска или из OS page cache) для каждого узла. Это сильнейший диагностический инструмент после actual rows.

Что такое buffer и зачем считать

В

1. Executor нуждается в странице <relfilenode, blockno>.
2. Сначала смотрит в shared_buffers (hashtable по этому ключу).
3. Если есть — hit, страница уже в RAM, чтение бесплатное (микросекунды).
4. Если нет — read, идёт в OS, OS либо отдаёт из своего page cache, либо реально читает с SSD/HDD.

Postgres не различает «OS page cache hit» и «real disk I/O» — для него и то, и другое одинаковый read. Чтобы посмотреть реальный disk I/O, нужны системные инструменты (iostat) или extension типа pg_stat_io.

Синтаксис

EXPLAIN (ANALYZE, BUFFERS) — старый синтаксис со скобками, в которые можно положить ещё VERBOSE, COSTS, SETTINGS, WAL, FORMAT JSON. С Postgres 13+ BUFFERS работает даже без ANALYZE, но в этом случае выдаёт только buffer accounting во время планирования (полезно, но не главное). Реальные buffers сборщика собираются под ANALYZE.

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

В каждом узле плана появятся строки Buffers: .... Они НЕ читаются автоматически в актуальные cost — это диагностика для нас.

Что считается

Полная номенклатура (Postgres 17):

• shared hit=N — N страниц найдено в shared_buffers (cache hit).
• shared read=N — N страниц пришлось читать (с диска или OS cache; Postgres не различает).
• shared dirtied=N — N страниц помечены как dirty (изменены этим узлом). Будут записаны в WAL и потом checkpointer’ом или background writer’ом на диск.
• shared written=N — N страниц этот процесс сам вытеснил из shared_buffers (потому что нужны были новые страницы, а буфер заполнен). Это «грязный» процесс evict — нежелательная ситуация, означает, что shared_buffers мал.
• local hit/read/dirtied/written — то же самое, но для локальных буферов: они используются для temp tables (созданных текущей сессией). У temp таблиц своя политика буферов, изолированных от shared_buffers.
• temp read/written — для temp files: когда work_mem не хватило для Sort или Hash, executor сливает на диск в $PGDATA/base/pgsql_tmp/. Это «настоящий» дисковый I/O, его нельзя избежать тюнингом shared_buffers — только увеличением work_mem.

В выводе строка выглядит так:

Seq Scan on orders  (cost=0.00..2350 rows=16667 width=33)
                    (actual time=0.012..18.5 rows=16548 loops=1)
  Buffers: shared hit=180 read=200

Здесь 380 страниц прочитано суммарно, из них 180 уже были в кэше, 200 пришлось подгрузить. Если ты выполнишь тот же запрос второй раз — большая часть read уйдёт в hit, потому что страницы уже подгружены в shared_buffers.

Cache hit rate как метрика

В одной формуле: hit_rate = hit / (hit + read). Это процент cache-hits для этого запроса.

Что считается «хорошо»:

• > 99% — отлично; рабочее множество (working set) запроса помещается в shared_buffers.
• 90-99% — приемлемо для OLTP с большим объёмом, где работают много разных таблиц.
• < 90% — плохо для OLTP, нормально для аналитических single-shot запросов на больших датасетах.

Если ты видишь у production-запроса hit_rate=70%, и запрос медленный — у тебя один из:

1. shared_buffers слишком мал — рабочее множество не помещается. Решение: увеличить.
2. Запрос делает Seq Scan гигантской таблицы, мимо кэша. Решение: индекс.
3. Random access pattern (Nested Loop с большим outer на разных страницах). Решение: переписать join.

Прогон в песочнице

В pglite shared_buffers по умолчанию маленький, но эффект всё равно виден: cache warm-up — это реальное явление. На проде после деплоя первые запросы всегда медленнее, потом «прогреваются».

Index Scan и BUFFERS

На Index Scan строка Buffers покажет страницы и индекса, и heap (если узел сам читает heap). Для Index Only Scan, если visibility map говорит «all visible», heap не читается — Buffers будет только по индексу. Это можно увидеть прямо.

После VACUUM visibility map обновляется, и план может перейти в Index Only Scan — тогда Buffers по heap не считаются. Это самый дешёвый scan-узел: только индексные страницы.

Dirtied и written — для DML и parallel

dirtied появляется у DML-узлов (Insert, Update, Delete), а также у HOT updates на промежуточных узлах. Это страницы, которые этот запрос изменил.

written означает, что этот процесс сам вытеснил dirty-страницу из shared_buffers, чтобы освободить место. Это плохо: значит, background writer и checkpointer не успевают, и obычный backend-процесс берёт на себя их работу, тормозя запрос. Если ты часто видишь written в проде — пора увеличить bgwriter_delay или shared_buffers.

Temp blocks — отдельная сущность

Когда executor не помещает Sort или Hash в work_mem, он сливает данные на временные файлы в pg_tmp. Это видно в плане как:

Sort  (cost=... rows=1000000)
   Sort Method: external merge  Disk: 25MB
   Buffers: shared hit=100, temp read=3200 written=3200

temp read/written — это реальный disk I/O. Никакой shared_buffers не поможет. Единственный способ убрать: увеличить work_mem на сессию или запрос (SET work_mem = '256MB'; или SET LOCAL work_mem ... внутри транзакции).

Внимание: pglite может иначе обрабатывать work_mem; на real Postgres эффект гарантирован.

Index Only Scan и heap fetches

Особый случай для BUFFERS — Index Only Scan. Если visibility map говорит «вся страница visible» для всех matching строк, executor не идёт в heap вообще; план показывает только index pages в Buffers.

Но если хотя бы для части строк visibility map устарела (страница изменялась, autovacuum не дошёл) — executor вынужден заглянуть в heap, чтобы проверить актуальность. Это видно отдельной строкой Heap Fetches: N.

Index Only Scan using idx_orders_status on orders
  Index Cond: (status = 'paid')
  Heap Fetches: 0           <- идеально, всё из индекса
  Buffers: shared hit=15

vs

Index Only Scan using idx_orders_status on orders
  Index Cond: (status = 'paid')
  Heap Fetches: 1200        <- visibility map устарела
  Buffers: shared hit=2400

Heap Fetches: 1200 означает 1200 раз пришлось идти в heap. Лечение: VACUUM table; — обновит visibility map, и следующий запрос будет идти только в индекс.

Buffers — это per-node, не глобально

Главное непонимание новичков: «у узла X в BUFFERS=200, у Y=300, итого 500». Это неверно. Buffers суммируются по поддереву узла, но если две ветки читают одну и ту же страницу, она засчитывается обеим. Один и тот же физический read может быть отражён в нескольких ветках плана.

Корректная глобальная метрика — это итог в корне плана. Внутренние числа полезны для поиска узкого места: какой узел доминирует по чтениям.

Также важно: loops > 1 влияет на Buffers. В Nested Loop inner узел запускается заново для каждой outer-строки, и Buffers показывает СУММУ по всем итерациям, не среднее. Это исключение из общего правила (actual time/rows = среднее) — buffers суммируются.

Анализ через BUFFERS: типичные сценарии

Несколько паттернов BUFFERS-вывода и что они значат.

Сценарий 1: hit dominates, нет read.

Buffers: shared hit=12000 read=0

Запрос полностью попадает в shared_buffers. Если он всё равно медленный — узкое место не в I/O, а в CPU (тяжёлые expressions, JIT отсутствует, неоптимальный алгоритм join). EXPLAIN ANALYZE с TIMING покажет, где именно тратится время.

Сценарий 2: read dominates на cold cache.

Buffers: shared hit=50 read=12000

Запрос с нуля прочитал почти всё с диска. Если повторный запуск даст hit=12000 read=0 — рабочее множество в кэш помещается, просто холодный старт. Если повторный запуск даёт примерно те же числа — рабочее множество не помещается в shared_buffers, нужно либо увеличить буфер, либо уменьшить чтение (индексы, partitioning).

Сценарий 3: temp read/written большой.

Buffers: shared hit=200 temp read=5000 written=5000

Sort или Hash не уместился в work_mem. Каждое чтение temp = реальный disk I/O. shared_buffers тут не поможет — нужно SET work_mem = '256MB'; или индекс, убирающий Sort.

Сценарий 4: дисбаланс read между ветвями join.

Hash Join
  Buffers: shared hit=5050 read=14950
  ->  Seq Scan on big_table
        Buffers: shared hit=5000 read=15000   <- основной источник read
  ->  Hash
        ->  Seq Scan on small_table
              Buffers: shared hit=50 read=0

В корне план показывает суммарно по поддереву. Чтобы понять, откуда идут read, спускайся вниз. Здесь видно: основная стоимость — на big_table. Решение — индекс или фильтрация раньше, чтобы read’ы не делать.

Чек-лист

• EXPLAIN (ANALYZE, BUFFERS) — главный диагностический режим для performance.
• shared hit — нашли в shared_buffers (быстро). shared read — пришлось читать (медленно).
• hit_rate = hit / (hit + read). На OLTP должно быть > 95%.
• dirtied — страницы, изменённые DML. written — страницы, вытесненные этим процессом (плохой знак).
• temp read/written — work_mem не хватил, ушло на диск. Решение: повысить work_mem.
• Buffers per-узел суммируются по поддереву; одну страницу могут видеть несколько ветвей.
• В Nested Loop buffers суммируются по всем итерациям (в отличие от actual time/rows).
• На pglite: первый прогон холодный (много read), второй прогретый (много hit).