In PostgreSQL 18 EXPLAIN ANALYZE, what do the Buffers counters shared hit, read, dirtied, written and temp mean, and how do you use them?
answer
- pages, not milliseconds
- 8 kB blocks and shared_buffers
- read is not necessarily disk
- temp means a spill
basics
~20 sBuffers counts pages per node: shared hit was in shared_buffers, read was fetched from the OS or storage, dirtied was modified, written was evicted by this backend, temp is spilled work files. PostgreSQL 18 shows them with ANALYZE by default.
solid answer
~40 sSince PostgreSQL 18, `EXPLAIN ANALYZE` includes `BUFFERS` by default; on 17 you write `EXPLAIN (ANALYZE, BUFFERS)`. The counts are blocks, 8 kB with the default `block_size`, totalled over the node's loops and including its children. `shared hit` was already in `shared_buffers`; `shared read` was not, and came from the kernel page cache or from storage, which the counts alone cannot tell apart. Turning on `track_io_timing` adds an `I/O Timings` line that can. `dirtied` counts pages this query modified, which a `SELECT` can do by setting hint bits, and `written` counts dirty pages this backend had to evict itself. `local` covers temporary tables; `temp` covers sort, hash and materialize spill files. I treat hit plus read as the query's real work: unlike time, it does not change with cache warmth, so it compares rewrites fairly.
code
console · 12 linesSort (cost=231850.42..234287.86 rows=974976 width=20) (actual time=1398.512..1471.906 rows=1012384.00 loops=1)
Sort Key: w.started_at
Sort Method: external merge Disk: 40960kB
Buffers: shared hit=1204 read=40381, temp read=5120 written=5120
I/O Timings: shared read=982.114, temp read=21.402 write=30.877
-> Seq Scan on workouts w (cost=0.00..89741.10 rows=974976 width=20) (actual time=0.019..1102.337 rows=1012384.00 loops=1)
Filter: (started_at >= '2026-01-01 00:00:00+00'::timestamp with time zone)
Rows Removed by Filter: 2840115
Buffers: shared hit=1204 read=40381
I/O Timings: shared read=982.114
Planning Time: 0.233 ms
Execution Time: 1503.674 msgo deeper
Recall that hit means the page was in PostgreSQL's cache and read means it was not, and that PostgreSQL 18 prints these with EXPLAIN ANALYZE automatically.
Explain each counter, the 8 kB block unit, that counts include children and are summed over loops, and what temp blocks reveal about spills.
Compare plans by pages touched rather than milliseconds, separate cold-cache effects from plan problems, and explain dirtied and written on a read-only query.
Make buffer counts and track_io_timing part of the team's performance-review routine, weighing the timing overhead on your hardware.
## Buffers is on by default from PostgreSQL 18 `BUFFERS` reports, for each plan node, how many **blocks** the node touched and where they came from. From **PostgreSQL 18**, `EXPLAIN ANALYZE` includes it automatically; on 17 and earlier you must write `EXPLAIN (ANALYZE, BUFFERS)`, and on 18 you can still switch it off with `BUFFERS OFF`. Without `ANALYZE` nothing executes, so `EXPLAIN (BUFFERS)` can only show the `Planning:` section's buffers, the catalog pages read while planning. A block is one page, **8 kB** with the default `block_size`. Multiply by 8 to get kilobytes: `read=40381` is about 315 MB. ## The counters | Counter | Meaning | |---|---| | `shared hit` | The page was already in `shared_buffers`; no read was needed | | `shared read` | The page was not in `shared_buffers` and was read in, from the kernel page cache or from storage | | `shared dirtied` | A previously clean page that this query modified | | `shared written` | A dirty page this backend evicted from the cache and wrote out itself | | `local ...` | The same four counters for temporary tables and their indexes | | `temp read` / `temp written` | Blocks of short-lived work files used by sorts, hashes and `Materialize` nodes that did not fit in memory | Three reading rules apply everywhere: - A node's numbers **include its children**, so the top node carries the whole statement. - They are **totals across all loops**, not per-loop averages like `actual rows`. - In text format zero counters are omitted, so a missing `read` means none. ## Hit versus read, and what read does not tell you PostgreSQL reads data files through the operating system, so `shared read` means "not in PostgreSQL's own cache", not "came from disk". A node can show thousands of reads and still be fast because the kernel cache served them. To know how long the reads took, turn on `track_io_timing`, which is off by default and can be changed in a session only by superusers or roles granted that right. `EXPLAIN` then adds a line such as `I/O Timings: shared read=982.114` in milliseconds. Its overhead depends on how fast the platform's clock is, which the `pg_test_timing` tool measures. ## Why a SELECT can dirty or write pages Two things surprise people on read-only queries: - **dirtied on a SELECT**: the first reader after a bulk load or a burst of writes sets hint bits on tuples whose transaction outcome it had to look up, and may prune dead tuples from a page. Both modify the page. With data checksums enabled, which `initdb` does by default from PostgreSQL 18, hint-bit changes are WAL-logged too, so the first such change to a page after a checkpoint writes a full-page image that `EXPLAIN (ANALYZE, WAL)` shows even for a `SELECT`. - **written on a SELECT**: the backend needed a free buffer, found only dirty ones to evict, and wrote one out itself. A steady stream of `written` points at cache pressure; sizing `shared_buffers` and tuning the background writer are separate tuning topics. ## Using buffers in an investigation Timing changes with cache warmth, so one query can take 1.5 s cold and 150 ms warm. Buffer counts do not. A practical routine for a slow dashboard query on a fitness app's `workouts` table: 1. Read the top node's `hit + read`: the total pages touched is the query's real I/O work. 2. Walk down to the node that owns most of those pages; a scan touching 41,000 pages to feed a chart of 1,000 points is the target. 3. Compare `read` between a first and a second run to separate cold-cache effects from plan problems. 4. Look for `temp read` and `temp written`: a node spilled to disk, and its own line, such as `Sort Method: external merge Disk: 40960kB`, says which and how much. 5. After a rewrite or a new index, compare total pages touched, not only milliseconds. A quick sanity check is the ratio of pages to useful rows: 41,585 pages for a million rows is normal for a full scan, while the same page count for 1,000 rows says the access path, not the cache, is the problem. The `Planning:` block works the same way for the planner: a large `shared read` there means the catalogs themselves were cold, which can make the first query on a fresh connection look slow for reasons the plan tree cannot show.
- Why does a plain SELECT sometimes show dirtied pages and even WAL records?The first reader after rows were written sets hint bits recording each inserting transaction's outcome, and may prune dead tuples; both modify the page. With data checksums, which `initdb` enables by default from PostgreSQL 18, hint-bit changes are WAL-logged, so the first one on a page after a checkpoint writes a full-page image that `EXPLAIN (ANALYZE, WAL)` reports.
- How do you tell whether shared read=40381 cost real disk time?Enable `track_io_timing`, off by default, and re-run: `EXPLAIN` adds `I/O Timings: shared read=...` in milliseconds. A high read count with a small read time means the kernel page cache served most of it. Check the host's clock overhead with `pg_test_timing` before turning the setting on server-wide.
- What does temp read=5120 written=5120 tell you, and where do you look next?A node wrote about 40 MB (5,120 × 8 kB) of temporary files and read them back: a sort, hash or materialize step did not fit in memory. The node's own lines name it, for example `Sort Method: external merge Disk: 40960kB`. Whether to raise `work_mem` for it is a memory-sizing decision the plan alone does not settle.
- Shared read means the page was read from physical disk.
- Buffers counts on a looped node are per-loop averages.
- A read-only SELECT can never dirty or write a page.
- On PostgreSQL 17, EXPLAIN ANALYZE already shows Buffers without asking for it.
- Temp blocks are reads of temporary tables created by the session.
Assumes PostgreSQL 18; from 18 EXPLAIN ANALYZE includes BUFFERS by default and initdb enables data checksums by default, while on 17 BUFFERS must be requested.