Reading BUFFERS Output in EXPLAIN for PostgreSQL Queries

Two runs of the same query take 40 ms and 4 seconds. The plan is identical. The difference is where the data came from: PostgreSQL's cache, the operating system's cache or the disk. Plain EXPLAIN ANALYZE shows time and rows and says nothing about that. Adding BUFFERS shows how many blocks each node touched and from where, which turns "it was slow" into "it read 400,000 blocks it did not have". This article explains each counter, how to read them across a plan and what to do about large numbers. It belongs to the series that reading EXPLAIN ANALYZE output in PostgreSQL step by step opens.

Turning it on

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM app_order WHERE customer_id = 42;

Each node gains a Buffers: line. A block is one 8 kB page, PostgreSQL's unit of I/O. The line may carry several groups:

Buffers: shared hit=1450 read=210 dirtied=3 written=0, temp read=0 written=0

The top node's line is the total for the statement, including all children. Child lines show their share. Planning has its own line, Planning: Buffers: shared hit=..., which counts catalog pages read while planning.

The counters

shared hit. Blocks found in shared_buffers, PostgreSQL's cache. No system call was needed. These are the cheapest reads.

shared read. Blocks not in shared_buffers, requested from the operating system. They may be served from the OS page cache (fast, a memory copy) or from the storage device (slow). PostgreSQL cannot tell which; the counter says "asked the OS".

shared dirtied. Blocks this statement changed from clean to dirty. Writes dirty blocks by design. A SELECT can too: the first read after a bulk load sets hint bits on rows (marking the transaction as committed), which dirties pages. A query that dirties blocks while only reading is the signature of reading freshly loaded data, and VACUUM or a first scan settles it.

shared written. Blocks the backend itself wrote out to make room in the cache. Normally the background writer and checkpointer write dirty pages, so a nonzero value in a query means the cache was under pressure.

local hit/read/dirtied/written. The same counters for temporary tables, which use backend-local buffers.

temp read/written. Blocks read from or written to temporary files, for sorts, hashes, materialization and large CTEs that did not fit in work_mem. Nonzero values mean a spill. See spotting sorts that spill to disk.

Turning blocks into bytes and ratios

Multiply by 8 kB: 1,450 hits is 11.3 MB, 210 reads is 1.6 MB. The hit ratio of a node is hit / (hit + read). In the example, 87 percent. A query that is cold shows mostly reads, a query on a hot working set mostly hits. A ratio alone does not say good or bad; a query that touches 5 blocks is fast either way, and one that touches 500,000 blocks is slow even at 99 percent hits, because 500,000 cache lookups cost real time (a hit is cheap, not free: roughly 0.1 to 1 microsecond each, so 500,000 hits is tens to hundreds of milliseconds).

The more useful number is the total: how many blocks does the query need? A lookup by primary key needs 3 to 5. A query that returns 10 rows and touches 100,000 blocks is doing a lot of work to discard data, which the node's Rows Removed by Filter will confirm.

Counting under loops

A node inside a nested loop reports buffers accumulated over all its loops. An index scan with loops=50000 and shared hit=150000 touched three blocks per probe. That is correct for a primary key lookup and still 150,000 block accesses in total; the product explains the time. Compare buffers with loops to see per-execution cost.

->  Index Scan using customer_pkey on customer c  (actual time=0.01..0.01 rows=1 loops=50000)
      Buffers: shared hit=150000

Parent nodes include their children's buffers. To get a node's own buffers, subtract its children's, as you do for time.

Cold versus warm

Run the query twice and compare. The first run shows read for blocks not yet cached; the second, if everything now fits, shows hit. A big difference between runs means the query depends on the cache state, and a production system that restarts, or whose working set exceeds memory, will see the slow case. Clearing caches for a test is possible by restarting PostgreSQL and dropping the OS cache (not on shared systems), and is usually not needed: the first-run read count tells you what a cold run needs.

When a query is slow once and fast afterwards, the question becomes whether the data will be there next time. That depends on shared_buffers, the OS cache and the competition of other queries. The settings in setting shared_buffers on a small server and choosing effective_cache_size govern it.

I/O timing

By default, PostgreSQL does not time individual reads, because calling the clock for every block is costly on some systems. With track_io_timing = on (check cost first with pg_test_timing), EXPLAIN (ANALYZE, BUFFERS) adds I/O time lines:

Buffers: shared hit=1450 read=210
I/O Timings: shared read=38.2

Here the 210 block reads took 38 ms in total, about 0.18 ms each, which suggests the OS cache and a fast SSD. If the same 210 reads took 2,100 ms, that is 10 ms per read, a spinning disk or a congested network volume. The timing tells you whether the cost is a number of blocks or a slow device. Set it per session for diagnosis: SET track_io_timing = on; (it needs sufficient privilege), or enable it server-wide on systems where the clock call is cheap, which is most modern Linux hosts.

WAL, for writes

EXPLAIN (ANALYZE, WAL) adds WAL: records=..., fpi=..., bytes=... for statements that write. fpi counts full-page images, written to the WAL for the first change to a page after a checkpoint. A bulk update with a large fpi right after a checkpoint explains a burst of I/O unrelated to the query's logic. The settings that shape it are in checkpoint settings and write spikes.

What to do about large numbers

Many blocks, few rows. The query reads far more than it returns. Add or fix an index, narrow a filter or select fewer rows. A covering index can cut heap reads, as in covering indexes with INCLUDE.

**Mostly read, slow I/O.** The working set does not fit in cache or the device is slow. Increase memory, reduce the data touched (partitioning, narrower rows) or improve storage.

Temp blocks. Fix the spill: more work_mem for that query, a better plan, an index that gives the order.

Dirtied blocks in a read. Hint bits after a load. Run VACUUM after bulk loads so the first user queries do not pay.

High hits with a high loops count. The per-probe cost is small and the number of probes is large; reduce the loops with a better join.

Index blocks dominating. A bloated or very wide index; check size with pg_relation_size and consider rebuilding or narrowing it.

When block counts mislead

Buffer counts describe I/O, not CPU. A query that spends its time in an expensive function, a regular expression or JSON processing shows few blocks and long time; the clue is time without buffers. Counts do not include time waiting for locks or for other sessions. A high shared hit figure for a small table scanned many times in a loop is cheap per hit, so judge by time as well. Cache effects across sessions mean that a query's read count depends on what ran before it. And parallel queries report buffers per process, with the leader's line including workers in the totals, which makes per-node reading subtle; see making sense of parallel query plans.

What to do next

Rerun your slowest query with BUFFERS, note the total blocks and the hit ratio, then compare with the rows returned. If the ratio of blocks to rows is large, you have found the work to remove. The manual describes the option in EXPLAIN and the counters in monitoring database activity. For the next specific symptom, see spotting sorts that spill to disk.