Using BUFFERS to Find I/O Hotspots #

Execution time tells you a query was slow. Buffer counts tell you why, and they do it in a unit that does not change between a cold cache and a warm one. EXPLAIN (ANALYZE, BUFFERS) reports, for every node, how many 8 KB pages were found in cache, fetched from the operating system, modified, and written — plus how many went through temporary files.

This page is about reading those numbers well. The broader bottleneck-hunting method is in identifying plan bottlenecks.

What Each Counter Means #

Buffer statistics come in three families:

Two subtleties matter in practice. First, counts are cumulative up the tree: a node’s figures include everything its children did, so a node’s own contribution is its total minus its children’s. Second, a SELECT can legitimately show dirtied pages, because reading a row for the first time after a write may set hint bits — which is why a read-only report can generate write I/O.

Where each buffer counter comes from Three layers: the shared buffer cache serving hits, the operating system and disk serving reads and receiving writes, and the temporary file area serving temp reads and writes for spills. shared buffer cache shared hit — free, already in memory miss OS cache / storage shared read · dirtied · written temporary file area temp read · temp written sort spills · hash batches · tuplestores spill counts are cumulative up the tree — subtract children to get a node's own I/O

Annotated Evidence #

A plan where timings mislead and buffers do not:

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.name, sum(o.total_cents)
  FROM customers c JOIN orders o ON o.customer_id = c.id
 WHERE o.created_at > '2026-01-01'
 GROUP BY c.name;

HashAggregate  (actual time=8412.6..8480.2 rows=118422 loops=1)
  Buffers: shared hit=41208 read=182400, temp read=61240 written=61240
  ->  Hash Join  (actual time=4102.1..7204.8 rows=4120334 loops=1)
        Buffers: shared hit=41208 read=182400
        ->  Seq Scan on orders o  (actual time=0.02..2841.60 rows=4120334 loops=1)
              Filter: (created_at > '2026-01-01')
              Buffers: shared hit=1204 read=178402         -- 178k pages from storage
        ->  Hash  (actual time=1204.4..1204.4 rows=200000 loops=1)
              Buffers: shared hit=40004 read=3998
Execution Time: 8620.4 ms

Work out each node’s own I/O by subtraction:

Now compare with the timings. Hash Join shows the largest actual time span, which invites the conclusion that the join is the problem. The buffers say otherwise: the join inherits its time from the scan beneath it, and the genuinely independent cost is the aggregate’s spill.

Step-by-Step Method #

  1. Always ask for buffers. Make EXPLAIN (ANALYZE, BUFFERS) the default form; the extra output costs nothing.

  2. Subtract children. A node’s own pages are its total minus the sum of its children’s totals. This single habit prevents most misdiagnoses.

  3. Rank nodes by own-pages, not by time. Pages are the currency; time is what your particular cache state charged for them today.

  4. Read hit against read as a cache verdict. A node reading 178,402 pages from storage on every execution is either scanning too much or working on a table that does not fit — both actionable, differently.

  5. Treat any temp figure as a spill to be removed:

    BEGIN;
SET LOCAL work_mem = '256MB';
EXPLAIN (ANALYZE, BUFFERS) SELECT … ;   -- expect the temp counters to disappear
COMMIT;

    The mechanics are covered in tuning work_mem for hash aggregates.

  6. Watch for dirtied on read-only statements. It is usually hint-bit setting after a bulk load, and it settles once the pages have been read a first time.

  7. Compare page counts before and after any fix. A change that lowers time but not pages probably just warmed the cache.

Own pages per node after subtracting children Three bars showing each node's own page count: the sequential scan on orders dominates with about one hundred and eighty thousand pages, the hash on customers is much smaller, and the hash aggregate contributes temporary file pages instead of shared pages. Seq Scan orders 179,606 pages (99% read) Hash on customers 44,002 pages (91% hit) HashAggregate 122,480 temp pages (spill) shared pages temporary file pages

Before and After #

-- BEFORE
Seq Scan on orders   Buffers: shared hit=1204 read=178402
HashAggregate        temp read=61240 written=61240
Execution Time: 8620.4 ms

-- AFTER: index on orders(created_at) + work_mem raised for the statement
Index Scan using orders_created_idx   Buffers: shared hit=18402 read=6120
HashAggregate                          (no temp counters)
Execution Time: 1204.8 ms

Pages touched fell from roughly 240,000 to 24,500. That ratio holds regardless of cache warmth, which is exactly why it is the number to quote.

Common Pitfalls #

Reading cumulative counts as per-node. A parent always looks like the biggest consumer. Diagnostic signal: the top node showing the largest figures in every plan you look at. Fix: subtract children.

Comparing timings across cache states. A cold first run and a warm second run differ enormously with an identical plan. Diagnostic signal: a “fix” that only works the second time you run it. Fix: compare pages.

Ignoring temp counters. They are the clearest signal of a spill and are easy to overlook beside the shared figures. Diagnostic signal: temp written on a sort, hash, or CTE scan. Fix: raise the allowance or reduce the working set.

Assuming shared read means physical disk I/O. The OS page cache may have served it. Diagnostic signal: high read counts with low system I/O wait. Fix: use track_io_timing and the I/O Timings output for actual device latency.

Overlooking loops when reading a node’s buffers. On the inner side of a nested loop the reported figures are already totals across all loops, but the per-loop timings are averages — mixing the two leads to conclusions that are off by the loop count. Diagnostic signal: buffer counts that seem impossibly large for the reported per-loop time. Fix: read loops first and decide which columns it applies to.

Treating a high hit ratio as proof of health. A plan can read a hundred thousand pages entirely from cache and still be wasteful; the pages were touched, they evicted something else, and the CPU cost of examining them is real. Diagnostic signal: shared hit in the hundreds of thousands with a small result set. Fix: reduce pages touched, not merely pages read from storage.

Comparing buffers across different data volumes. A plan that reads fewer pages because the table is smaller today has not improved. Diagnostic signal: an improvement measured after a retention job ran. Fix: compare on the same data, or normalise pages against the rows returned.

Subtract children to get a node's own pages The join node reports two hundred and twenty three thousand pages, its two children report one hundred and seventy nine thousand and forty four thousand, leaving the join itself with almost none of its own. Hash Join — reports 223,608 Seq Scan orders — 179,606 Hash customers — 44,002 223,608 − 179,606 − 44,002 = 0 own pages for the join itself

Frequently Asked Questions #

What is the difference between shared hit and shared read? A hit was found in PostgreSQL’s own buffer cache and costs a memory access. A read was requested from the operating system, which may still serve it from the OS page cache. Both are counted in 8 KB pages.

Why are buffer counts better than timings? Because they measure what the plan had to touch rather than what your cache happened to hold. The same plan can differ tenfold in time between a cold and a warm run, while its page counts stay essentially constant.

What does dirtied mean? Pages the statement modified and that must eventually be written back. A SELECT can dirty pages by setting hint bits on rows it is first to read after a write, which is why read-only queries sometimes generate write I/O.