What does EXPLAIN (ANALYZE, BUFFERS) show that timing alone does not?

EXPLAIN ANALYZE executes the query and reports, per plan node, how long it took and how many rows it produced. Adding BUFFERS reports how many pages each node touched and where those pages came from. Timing tells you where the query spent its time. Buffer counts tell you why, measured as page-level work. A buffer is one page, 8 kB by default, so the counts convert directly into data volume.

Counter What it counts How to read it
shared hit Pages found already in shared_buffers Cheap memory access, but a large count still means a lot of work
shared read Pages not in shared_buffers, requested from the OS The OS page cache may serve these, so they do not prove disk I/O
shared dirtied Pages this query modified Common after writes, or when reads set hint bits
shared written Dirty pages this backend wrote out during the query The backend had to evict pages to make room, so the cache is under pressure
local Buffers for temporary tables, private to the session Same meaning as shared, but for temp-table pages
temp Pages in spill files for sorts, hashes, and similar operations The operation did not fit in work_mem

Buffer counts are also more reliable than timing. Per-node timing reads the clock on every execution of a node. That overhead inflates nodes that run many loops, such as the inner side of a nested loop. Buffer counters only increment, so the instrumentation does not distort them.

Backend and platform engineers should use this view when they need to decide whether a slow query has a bad plan, a cold cache, or too much data.

How does EXPLAIN (ANALYZE, BUFFERS) work?

Postgres plans the statement exactly as it would for a normal execution. It then runs that plan with instrumentation attached to every plan node. The rows the query produces are discarded instead of being sent to the client. As a result, the reported time excludes network transfer and client-side processing.

The buffer counters come from the buffer manager. Every time the executor needs a page of a table or index, it requests that page from the buffer manager:

  1. If the page is already in shared_buffers, the request counts as a hit.
  2. If it is not, the backend finds a free buffer slot and asks the operating system to read the page into it. The request counts as a read. The OS may answer from its own page cache or from storage. Postgres cannot tell which one served it.
  3. If the slot the backend picks holds a modified page that has not been flushed yet, the backend must write that page out first. That write counts as written.
  4. If the query changes a page, including setting hint bits during a read, the page counts as dirtied.

Temporary tables use session-local buffers and are counted as local. Sorts and hashes that exceed work_mem write spill files, and those are counted as temp.

Each counter is charged to the plan node that is executing when the page is requested. A parent node's instrumentation covers the time its children spend producing rows for it, so the reported numbers are inclusive of the subtree. The counters accumulate over every execution of the node. When the statement finishes, Postgres prints the plan with the collected counts next to each node. Depending on the server version, the output can also include a separate buffers line for the planning phase. Planning can read catalog pages, especially in a fresh session.

Because the statement really runs, everything the statement normally does also happens. Writes are applied, locks are taken, and triggers fire. The next section covers how to run it safely.

Who should use EXPLAIN (ANALYZE, BUFFERS)?

  • Backend developers reviewing a new or changed query. This includes ORM-generated SQL. Buffer counts show whether the query does work proportional to the rows it returns before it reaches production data volumes.
  • DBAs and platform engineers handling a slow-query incident. They need to decide quickly between adding an index, refreshing statistics, tuning memory, or changing the query.
  • SREs and capacity planners. They need to know whether latency comes from a working set that no longer fits in memory, a problem that needs more RAM or better access patterns, or from plans that would be slow on any hardware.
  • Engineers validating a fix. They need a before-and-after comparison that does not change just because the cache happened to be warmer on the second run.

It is the wrong first tool when you do not yet know which query is slow, or when the latency may sit outside the database, such as in connection pool waits, network, or application code. In those cases, start with workload-level data like pg_stat_statements (covered below) or application traces. Once you have identified a query, come back to EXPLAIN.

How do you run EXPLAIN ANALYZE BUFFERS safely in Postgres?

Because ANALYZE executes the statement for real, an EXPLAIN (ANALYZE, BUFFERS) on an UPDATE changes rows. For any INSERT, UPDATE or DELETE, open a transaction with BEGIN, run the EXPLAIN, then issue ROLLBACK. The rollback discards the data changes but not the work done to produce them. The statement still writes WAL, takes row and table locks for as long as the transaction stays open, fires triggers (including ones with external side effects), and can advance sequences, which a rollback does not reset. On a busy primary, that means extra WAL volume, replication traffic, and lock contention with live sessions. Run it on a replica or a restored copy when the statement allows, and keep the transaction short.

Use data that matches production in row count, value distribution and table bloat. The planner chooses plans from table statistics, so a dev database with a few thousand rows often gets a sequential scan where production would use an index, or the reverse. Its buffer counts are also small enough that the whole working set fits in cache, which hides the read patterns you are trying to find.

To see how much of the runtime went to waiting on storage, run SET track_io_timing = on in your session before the EXPLAIN. Nodes that performed I/O then report read and write timings next to their buffer counts. Changing this setting may require elevated privileges, depending on your configuration. The overhead comes from reading the clock on every I/O. It is measurable but usually modest, and it depends on the platform's clock source. Running pg_test_timing on the host shows whether clock reads there are cheap.

-- Enable I/O timing for this session only (superuser by default,
-- or a role granted SET on this parameter)
SET track_io_timing = on;

BEGIN;

-- Fail fast instead of queueing behind live sessions' locks,
-- and cap how long the real execution can run
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '30s';

-- ANALYZE really executes the UPDATE: rows change, WAL is written,
-- triggers fire and row locks are held until the transaction ends
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders
SET    status = 'archived'
WHERE  created_at < now() - interval '90 days'
  AND  status = 'shipped';

-- Discard the data changes and release locks immediately.
-- WAL already generated, trigger side effects and sequence
-- advances are NOT undone.
ROLLBACK;

-- Restore the default for the rest of the session
RESET track_io_timing;

Runs EXPLAIN (ANALYZE, BUFFERS) on an UPDATE inside a short, timeout-guarded transaction that is rolled back, with track_io_timing enabled for the session so nodes report I/O time next to their buffer counts.

How do buffer counts aggregate across a query plan?

A node's Buffers: line is inclusive: it counts every page touched by that node and everything beneath it. The top node's numbers are the total for the whole query. To find the node that actually did the I/O, subtract each child's counts from its parent's. The same applies to read, dirtied, written and temp blocks.

A second rule causes more mistakes. Buffer counts are summed across all loops of a node, but actual time and rows are averages per loop. Here is an illustrative plan for a nested loop that joins 1,000 orders to customers:

Node loops rows (per loop) shared hit (inclusive) shared hit (exclusive)
Nested Loop 1 1,000 4,120 0
→ Seq Scan on orders 1 1,000 120 120
→ Index Scan on customers_pkey 1,000 1 4,000 4,000

Reading the index scan line on its own invites two wrong conclusions:

  • "4,000 shared hits to return one row means the index is broken." It is 4,000 hits over 1,000 loops, or 4 pages per lookup. That is a few index levels plus one heap page, which is normal for a B-tree probe.
  • "0.02 ms per row, so this node is cheap." Multiplied by 1,000 loops, that is about 20 ms. The node may be the most expensive part of the query.

Before you judge any node, multiply its time and rows by loops, and divide its buffers by loops. The real question here is whether 1,000 probes is the right strategy at all. Possible fixes include a hash join or a smaller outer set, not a different index.

-- EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
-- SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id;
-- Illustrative output, annotated. Not a measured result.

Nested Loop (actual time=0.031..21.400 rows=1000 loops=1)
  Buffers: shared hit=4120          -- inclusive: whole query
  ->  Seq Scan on orders o (actual time=0.010..0.850 rows=1000 loops=1)
        Buffers: shared hit=120     -- leaf: inclusive = exclusive
  ->  Index Scan using customers_pkey on customers c
        (actual time=0.019..0.020 rows=1 loops=1000)
        Index Cond: (id = o.customer_id)
        Buffers: shared hit=4000    -- summed over all 1000 loops

-- Plan tree, inclusive / exclusive shared hits:
--
--   Nested Loop            incl 4120   excl 4120 - 120 - 4000 = 0
--   |-- Seq Scan orders    incl  120   excl  120
--   `-- Index Scan cust    incl 4000   excl 4000
--
-- Per-loop vs total for the index scan (loops = 1000):
--   buffers: 4000 total     / 1000 loops = 4 pages per probe (normal)
--   time:    0.020 ms/loop  * 1000 loops = ~20 ms total (most of 21.4 ms)
--   rows:    1 row/loop     * 1000 loops = 1000 rows total

An illustrative annotated EXPLAIN (ANALYZE, BUFFERS) nested loop plan, with a tree showing how to subtract child buffer counts to get exclusive I/O. It also shows how to convert per-loop time and rows, and the all-loop buffer totals, before judging a node.

Is it a bad plan, a cold cache, or too much data?

Whichever strategy you settle on, check whether the slowness comes from the plan, the cache state, or the volume of data. Start with total buffers touched (shared hit + shared read) divided by rows returned. A page counts once whether it came from shared buffers or from the OS, so this ratio does not depend on how warm the cache was. It measures how much work the plan does per useful row.

Illustrative estimate: a query returns 10 rows and touches 50,000 pages. At 8 kB per page, that is about 390 MB of page access, or roughly 39 MB per row. No cache state makes that efficient.

Next, run the query again and compare. If most read counts become hit counts and the latency drops, the first run was paying for a cold cache. If the total count stays high, the cause is the plan or the data layout, such as a missing index, a filter applied after a wide scan, or bloated or poorly correlated tables.

Signal Bad plan / layout Cold cache Genuinely large result
Buffers per row High Low to moderate Low to moderate
Second run Total unchanged, still slow Reads become hits, faster Total unchanged, scales with rows
Fix Indexes, rewrite, statistics Memory sizing, access patterns Pagination, aggregation, less data

These checks have limits:

  • A single run can mislead in both directions. It can show a cold-cache outlier, or a warm cache that real traffic rarely gets.
  • Warming the cache hides the cold-start latency that users actually experience.
  • read only means the page did not come from shared buffers. It may have been served from the OS page cache, which is just a memory copy. Without track_io_timing or OS-level I/O metrics, you can end up blaming storage for something that never reached the disk.
-- Record time spent in read calls. Changing this setting needs superuser or
-- explicitly granted SET privilege; it can also be enabled in postgresql.conf.
SET track_io_timing = on;

-- Run 1: the cache may be cold.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, total
FROM orders
WHERE customer_id = 4821
  AND created_at >= now() - interval '30 days';

-- On the top plan node, note:
--   actual ... rows=N        rows returned
--   Buffers: shared hit=H read=R
--   I/O Timings: ...         time spent in read calls
-- Plan-quality signal: (H + R) / N, the pages touched per row returned.
-- Pages are 8 kB by default, so (H + R) * 8 kB is the page access volume.

-- Run 2: the same statement, right after run 1.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, total
FROM orders
WHERE customer_id = 4821
  AND created_at >= now() - interval '30 days';

-- Interpreting run 2:
--   R falls, H rises, total H + R roughly unchanged, latency drops
--     -> run 1 was paying for a cold cache.
--   H + R still far larger than N, still slow
--     -> plan or layout problem: missing index, filter after a wide scan,
--        or a bloated or poorly correlated table.
--   H + R low per row, but N is large
--     -> the query really does return a lot of data.
--
-- If run 1 shows a high R but low I/O timings, the reads were probably
-- served from the OS page cache (a memory copy) and never reached the disk.
-- Check OS-level I/O metrics before blaming storage.

-- For scripting, FORMAT JSON exposes the same counters as fields:
-- "Shared Hit Blocks", "Shared Read Blocks", "Actual Rows".

Running the same query twice with EXPLAIN (ANALYZE, BUFFERS) and track_io_timing enabled shows whether shared reads turn into hits (a cold cache) or the total pages touched per row stays high (a plan or layout problem).

What buffer patterns point to common Postgres performance problems?

Once you know what the counts mean, a handful of patterns cover most slow plans.

Pattern in the plan Likely cause Fix
Seq Scan with very large shared read where you expected an index No usable index, a predicate that wraps the column in a function, or a bad selectivity estimate Add or adjust the index, rewrite the predicate, refresh statistics
Index Scan with heap hits far above rows returned Poor physical correlation, so each row lands on a different page, or a non-covering index Add INCLUDE columns, or consider CLUSTER for read-heavy tables
Index Only Scan with high Heap Fetches Visibility map is not current Check vacuum state and tune autovacuum for that table
temp read/written on Sort or Hash Operation exceeded work_mem and spilled to disk Raise work_mem for that workload
High dirtied on a plain SELECT Hint bits being set after a bulk write Vacuum after bulk loads

Buffer counts multiply when row estimates are wrong. If a node expects 10 rows and gets 100,000, the planner may have picked a nested loop that repeats an inner index probe 100,000 times. Each probe touches pages, so the total climbs quickly. Start by running ANALYZE on the table. If the columns are correlated, such as city and postal code, add extended statistics. If the estimate is still off, rewrite the query so the planner can reason about it.

work_mem applies to each sort or hash operation, not to each query. One query can use several multiples of it, and many concurrent connections multiply that again. Raising it globally can exhaust memory under load. Setting it per session or per role is safer.

-- 1. Row-estimate error on correlated columns: compare estimated vs actual rows
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM addresses
WHERE city = 'Springfield' AND postal_code = '62701';

-- Refresh statistics first, then teach the planner the columns are related
ANALYZE addresses;
CREATE STATISTICS addresses_city_postal (dependencies, ndistinct)
  ON city, postal_code FROM addresses;
ANALYZE addresses;  -- extended stats are only populated by ANALYZE

-- 2. Index Only Scan with high Heap Fetches: check vacuum state for the table
SELECT relname, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';
VACUUM (ANALYZE) orders;  -- updates the visibility map and sets hint bits

-- 3. Sort/Hash spilling (temp read/written): raise work_mem narrowly
BEGIN;
SET LOCAL work_mem = '256MB';  -- applies only until COMMIT/ROLLBACK
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, sum(total)
FROM orders
GROUP BY customer_id
ORDER BY 2 DESC;
COMMIT;

-- Or scope it to a role used by reporting jobs instead of the whole server
ALTER ROLE reporting SET work_mem = '128MB';

Targeted fixes for three buffer patterns: extended statistics for correlated-column misestimates, VACUUM for Index Only Scans that hit the heap, and per-transaction or per-role work_mem for spilling sorts and hashes.

How do you use pg_stat_statements to choose which query to EXPLAIN?

EXPLAIN tells you why one statement is slow. It does not tell you which statement deserves your attention. pg_stat_statements answers that question across the whole workload by aggregating execution counts, timing, and buffer counters for each normalized statement.

Ranking candidates

  • Total execution time. This is usually the most useful first sort. A 5 ms query called a million times costs more than a 10-second report that runs once a day.
  • shared_blks_hit + shared_blks_read. This ranks statements by how many pages they touch. A statement can look fast because everything is cached and still be the heaviest consumer of shared buffers and CPU.
  • The ratio of shared_blks_read to shared_blks_hit. A high share of reads marks I/O-heavy statements. These are the ones most likely to get worse when the working set outgrows memory.

Once you have chosen a statement, reproduce it with representative parameters before running EXPLAIN (ANALYZE, BUFFERS). The view stores WHERE status = $1, not the literal value. The planner may choose an index scan for a rare status and a sequential scan for a common one. Pull real values from application logs or traces.

Costs and limits

  • The module must be listed in shared_preload_libraries, and changing that requires a server restart. Plan for it before you need it.
  • It tracks a bounded number of statements. When that limit is reached, the least-executed entries are evicted, so rare but expensive queries can disappear from the view.
  • Constant normalization merges queries that run with very different plans into one entry.
  • Counters accumulate until they are reset. To see recent behavior, take periodic snapshots and compare the deltas between them, rather than reading the lifetime totals.

How can buffer analysis save engineering time?

Most time lost on slow queries goes to fixes that target the wrong cause. Typical examples are adding an index when the real problem is a cold cache, provisioning faster storage when the plan reads far too many pages, or raising memory settings when the row estimate is wrong. Buffer counts shorten that loop in several ways:

  • They separate causes in one or two runs. The buffers-per-row ratio and the cold-versus-warm comparison distinguish a bad plan from a cold cache from a large result. You do not need to experiment with each kind of fix in turn.
  • They point to the node that did the work. Exclusive buffer counts and per-loop arithmetic identify the expensive node directly. You do not have to reason about the whole plan at once.
  • They give a stable before-and-after metric. Timing depends on cache state, concurrent load and host noise, so validating a fix by timing alone often takes repeated runs. Total pages touched for the same query and parameters stays much more consistent between runs. A drop in that number is evidence that the fix changed the work, not just the conditions.
  • They catch regressions before production. In code review, a query whose pages touched grows with table size while its returned rows stay constant can be flagged before it ships, even on a smaller dataset. The planner may still choose differently at production scale, so confirm on representative data.
  • They prevent misdirected infrastructure work. Combined with track_io_timing, buffer counts show whether reads actually waited on storage. That keeps teams from treating a query problem as a hardware problem.

Illustrative estimate, with assumed inputs: assume each diagnose-change-retest cycle takes 45 minutes. Assume that without buffer data an engineer tries three candidate fixes before finding the right one, and that cache noise forces each result to be re-run once to be trusted. That totals about six cycles, or 4.5 hours. Now assume buffer counts identify the cause on the first attempt, and that one confirming re-run is still made. That is two cycles, or 1.5 hours. Real savings depend entirely on the query, the team and the environment. These figures only show where the time goes, not what any team will measure.

When should you avoid relying on EXPLAIN ANALYZE, and what are the alternatives?

EXPLAIN ANALYZE executes the query once, on one backend, with one set of parameter values. Lock waits and contention appear only under concurrent load, so a single isolated run does not show them. It also shows only the plan chosen for the values you typed. A prepared statement can switch to a generic plan after repeated executions, and that plan may differ from the custom plan you get in a manual test.

Tool What it shows What it misses
Plain EXPLAIN Estimated plan without executing; safe for writes and very slow queries Actual rows, timing, buffers
EXPLAIN (ANALYZE, BUFFERS) Actual rows, time and pages per node Concurrency, other parameter values
pg_stat_statements Calls, total time and buffers per normalized query Per-node detail, the plan itself
Cumulative statistics views Scan types, dead tuples, wait events Which query caused them
OS I/O metrics Device latency, throughput, page cache pressure Anything at the query level

A repeatable workflow

  1. Pick the query with the highest total time or shared reads in pg_stat_statements.
  2. Run EXPLAIN (ANALYZE, BUFFERS) twice and compare the cold run with the warm run.
  3. Find the node with the highest exclusive buffer count.
  4. Compare its estimated rows with its actual rows.
  5. Form one hypothesis: an index, statistics, work_mem, vacuum or a query rewrite.
  6. Apply the change and confirm that buffers dropped, not just timing. Timing alone can improve only because the cache is warmer.

A lower buffer count is a proxy for improvement, not the goal. A new index can cut reads for one query while adding write amplification, vacuum work and memory pressure for every other query. After the change, recheck the workload-wide view.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to explain analyze buffers postgres, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.