Buffers: shared hit, read, dirtied, written in PostgreSQL EXPLAIN

What it does

PostgreSQL reads and writes tables and indexes in 8 kB pages (blocks), through a cache in shared memory called shared buffers. The Buffers: line under a plan step counts the pages that step and the steps below it touched:

Buffers: shared hit=2048 read=1061

Multiply by 8 kB for bytes: shared hit=10473 read=121 is about 83 MB.

When you see it

In PostgreSQL 18, EXPLAIN ANALYZE includes buffers by default. In 17 and earlier, ask for them: EXPLAIN (ANALYZE, BUFFERS). A Planning: section has its own Buffers: line for the catalogue pages the planner read.

With track_io_timing on, an I/O Timings: line follows, with the time spent on those reads and writes. It’s off by default; a superuser can turn it on for a session with SET track_io_timing = on.

Reading its numbers

• Each step includes its children. The top line of the plan is the whole query’s total. To find which step did the reading, look for where the number drops between a parent and its child.
• Totals, not per loop. Unlike rows and actual time, buffer counts aren’t divided by loops. A step with loops=1000 and shared hit=53000 touched 53,000 pages in all.
• The same page counts each time it’s touched. A nested loop that visits the same index page 1,000 times reports 1,000 hits.
• read isn’t necessarily disk. PostgreSQL can’t see whether the OS served the page from its own cache. I/O Timings can: in the example below, 1,061 reads took 4.086 ms, about 4 µs each, which is memory speed. Reads that reach an SSD usually take tens of microseconds or more each.
• Run it twice. The first run warms the cache. Compare the second run’s hit and read with the first to see what a cold cache costs.

When it’s a problem

• Lots of pages for few rows. The page count is the work. A step that touches 10,000 pages to return 28 rows needs a better index (see Rows Removed by Filter), even if every page was a hit.
• read stays high on every run. The data you query doesn’t fit in shared buffers, or other queries keep pushing it out. Reduce the pages the query needs before you add memory.
• temp read and temp written. A sort or hash spilled to disk. Raise work_mem for that query or session and check that the temp counters disappear.
• written on a query. The backend had to write dirty pages itself before it could reuse their buffers; normally the background writer and checkpointer do that. Occasional writes are normal under load; constant ones suggest shared buffers are small for the write volume.
• dirtied on a read-only query. The first time a query reads rows after they were written, it records on the page that the inserting transaction committed (hint bits), which changes the page. It’s harmless and goes away once the pages have been read or vacuumed.

Example

PostgreSQL 18.6 with track_io_timing on for the session. A 24 MB table written moments before:

SET search_path = seo_terms;
CREATE TABLE page_views AS
SELECT i AS id, i % 5000 AS page_id,
       timestamptz '2026-01-01' + i * interval '1 second' AS viewed_at,
       md5(i::text) AS session
FROM generate_series(1, 300000) AS i;
SET max_parallel_workers_per_gather = 0;
SET track_io_timing = on;

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM page_views WHERE page_id = 42;

First run: 2,048 of the 3,109 pages were still in shared buffers from when the table was written; the other 1,061 were read:

Aggregate  (cost=7271.45..7271.46 rows=1 width=8) (actual time=25.261..25.262 rows=1.00 loops=1)
  Buffers: shared hit=2048 read=1061
  I/O Timings: shared read=4.086
  ->  Seq Scan on page_views  (cost=0.00..7267.29 rows=1663 width=0) (actual time=0.087..25.236 rows=60.00 loops=1)
        Filter: (page_id = 42)
        Rows Removed by Filter: 299940
        Buffers: shared hit=2048 read=1061
        I/O Timings: shared read=4.086
Planning:
  Buffers: shared hit=17
Planning Time: 0.048 ms
Execution Time: 25.280 ms

Second run, everything cached:

Aggregate  (cost=7271.45..7271.46 rows=1 width=8) (actual time=16.045..16.045 rows=1.00 loops=1)
  Buffers: shared hit=3109
  ->  Seq Scan on page_views  (cost=0.00..7267.29 rows=1663 width=0) (actual time=0.012..16.029 rows=60.00 loops=1)
        Filter: (page_id = 42)
        Rows Removed by Filter: 299940
        Buffers: shared hit=3109
Planning Time: 0.044 ms
Execution Time: 16.067 ms

An UPDATE (rolled back afterwards) shows dirtied and written:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE page_views SET page_id = page_id + 1 WHERE id <= 20000;
ROLLBACK;

Update on page_views  (cost=0.00..6910.62 rows=0 width=0) (actual time=41.372..41.375 rows=0.00 loops=1)
  Buffers: shared hit=61426 read=2105 dirtied=2568 written=191
  ->  Seq Scan on page_views  (cost=0.00..6910.62 rows=20649 width=10) (actual time=0.100..22.197 rows=20000.00 loops=1)
        Filter: (id <= 20000)
        Rows Removed by Filter: 280000
        Buffers: shared hit=1004 read=2105 dirtied=2359 written=1

The scan only reads, yet it dirtied 2,359 pages: it was the first to read those rows since they were written, and set their hint bits. Other sessions had pushed 2,105 pages out of the cache in the meantime. The Update step adds the pages holding the new row versions.

A sort that doesn’t fit in work_mem uses temp blocks:

SET work_mem = '1MB';
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM page_views ORDER BY session LIMIT 100000;

Limit  (cost=57341.36..57591.36 rows=100000 width=49) (actual time=456.145..476.543 rows=100000.00 loops=1)
  Buffers: shared hit=3302, temp read=5117 written=6552
  ->  Sort  (cost=57341.36..58137.19 rows=318334 width=49) (actual time=456.143..471.205 rows=100000.00 loops=1)
        Sort Key: session
        Sort Method: external merge  Disk: 17360kB
        Buffers: shared hit=3302, temp read=5117 written=6552
        ->  Seq Scan on page_views  (cost=0.00..6482.34 rows=318334 width=49) (actual time=0.010..14.634 rows=300000.00 loops=1)
              Buffers: shared hit=3299

With work_mem = '64MB' the same sort reported Sort Method: top-N heapsort Memory: 28627kB and no temp blocks. A temporary table shows local instead of shared:

CREATE TEMP TABLE recent AS SELECT * FROM page_views WHERE id <= 50000;
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM recent;

Aggregate  (cost=1346.40..1346.41 rows=1 width=8) (actual time=4.895..4.896 rows=1.00 loops=1)
  Buffers: local hit=576
  ->  Seq Scan on recent  (cost=0.00..1192.32 rows=61632 width=0) (actual time=0.005..2.514 rows=50000.00 loops=1)
        Buffers: local hit=576

In Inlet

Inlet draws EXPLAIN ANALYZE as a tree and highlights the slowest step. You can also paste a plan into the free plan visualizer.