What does EXPLAIN (ANALYZE, BUFFERS) show, and why use it over timing alone?
EXPLAIN ANALYZE executes the query and reports, for each plan node, the actual row counts and elapsed time. Adding BUFFERS also reports how many data pages each node touched and where those pages came from. Timing tells you a query took 4 seconds. Buffer counts tell you whether it took 4 seconds because it read 900,000 pages it didn't need, or because it read the right 200 pages from a cold disk. Timing is the symptom. Page counts usually point to the cause, and they are more stable from run to run than wall-clock time.
| Field | Meaning | What it usually signals |
|---|---|---|
| shared hit | Page found in PostgreSQL's shared buffer cache | Cheap, but a huge count still means the query is doing too much work |
| shared read | Page requested from outside shared buffers, which may be the OS page cache or disk | I/O cost; high counts suggest scanning too much data |
| shared dirtied | Previously clean page modified by this query | Writes, hint-bit updates after bulk loads |
| shared written | Dirty page this backend had to evict and write itself | Cache pressure; the background writer is not keeping up |
| local hit/read/dirtied/written | Same counters, for temporary tables | Heavy temp-table use within the session |
| temp read/written | Spill files for sorts, hashes and materialisation | work_mem too small for the operation |
Counts are in blocks, which are 8 kB in a default build. Multiply by 8,192 to get bytes. For example, shared read=131072 is 131,072 × 8,192 = 1 GiB pulled into the cache for a single query.
Backend engineers use these counts to see which node in their own query is doing the excess work. Platform engineers use them to tell a query that thrashes the cache apart from one that is only slow on its first run.
-- Run the query and report per-node row counts, timing and page counts.
-- Note: ANALYZE really executes the statement, so wrap writes in a transaction you roll back.
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, sum(amount) AS total
FROM orders
WHERE created_at >= now() - interval '30 days'
GROUP BY customer_id
ORDER BY total DESC;
-- Illustrative output shape only; the numbers are made up for this example.
-- Zero-valued counters are omitted from the Buffers line.
-- Sort (actual time=...)
-- Sort Method: external merge Disk: ...kB
-- Buffers: shared hit=1024 read=131072, temp read=6400 written=6400
-- -> HashAggregate ...
-- -> Seq Scan on orders ...
-- Buffers: shared hit=1024 read=131072
--
-- shared read=131072 on a Seq Scan suggests the scan touches far more pages than the
-- 30-day filter needs. temp read/written on the Sort means it spilled past work_mem.
-- Convert block counts to bytes using this server's actual block size (8192 by default).
SELECT current_setting('block_size')::bigint AS block_size_bytes,
pg_size_pretty(131072 * current_setting('block_size')::bigint) AS shared_read_size,
pg_size_pretty(6400 * current_setting('block_size')::bigint) AS temp_written_size;
-- Test whether the spill goes away with more memory, for this session only.
SET work_mem = '64MB';
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, sum(amount) AS total
FROM orders
WHERE created_at >= now() - interval '30 days'
GROUP BY customer_id
ORDER BY total DESC;
RESET work_mem;
Runs EXPLAIN (ANALYZE, BUFFERS) on an aggregate query, shows an illustrative Buffers line with shared and temp counters, converts block counts to bytes with the server's block_size setting, and re-runs the query with a session-level work_mem increase to check whether the temp spill disappears.
Who should use EXPLAIN (ANALYZE, BUFFERS)?
Anyone responsible for a query's performance can use it. Running it needs no special role beyond the privileges the query itself requires. Three groups get the most out of it:
- Backend and application engineers who write SQL, or whose ORM generates it, and need to know which part of a specific statement is expensive. Buffer counts show whether a new filter, join or sort made the query touch more data. The answer holds even when timings on a development machine are too noisy to compare.
- DBAs and database reliability engineers who triage slow statements reported by
pg_stat_statementsor by users. They use buffer counts to separate the cases they must tell apart: a plan problem, a cache-sizing problem, a vacuum problem, or a memory setting such aswork_mem. - Platform and SRE engineers sizing shared buffers, memory and storage. Shared reads, writes and temp spills show how much I/O a workload drives per query. That gives them a per-statement view to set against host-level I/O metrics.
You need to be able to read a plan tree. You do not need to know PostgreSQL internals beyond the concept of 8 kB pages and the shared buffer cache.
When should you use it?
Use EXPLAIN (ANALYZE, BUFFERS) when you have one specific statement and you can run it, with realistic parameters, against realistic data. Typical situations:
- A known slow query.
pg_stat_statements, the slow-query log or a user report has pointed at a statement, and you need to find which node is responsible. - Before and after a change. You are adding or dropping an index, rewriting a query, changing
work_mem, or runningANALYZEon a table. Pages touched give a comparison that depends far less on cache state than elapsed time does. - Timings that vary between runs. The same query is sometimes fast and sometimes slow. Comparing
shared hitagainstshared readacross runs tells you whether the variation comes from cache state or from a different plan. - Regressions after data growth or an upgrade. A query that used to be fine is now slow. Buffer counts show whether it is reading proportionally more data or has switched to a worse plan.
- Suspected spills. A sort or hash aggregate is slower than expected.
temp read/writtenconfirms or rules out a spill to disk.
It is the wrong tool when you cannot safely execute the statement, when the slowness is intermittent and cannot be reproduced on demand, or when the time is spent outside the executor, for example in lock waits or network transfer. The alternatives section below covers those cases.
How does EXPLAIN (ANALYZE, BUFFERS) work?
Plain EXPLAIN asks the planner for its chosen plan and prints the estimated costs and row counts. Nothing is executed. Adding ANALYZE makes PostgreSQL build the same plan and then run it through the executor with instrumentation attached to every plan node. The result rows are discarded instead of being sent to the client.
The executor runs a plan as a tree. Each node asks its children for rows, processes them and passes rows upward. Instrumentation wraps each node's execution:
- Rows and loops. Every time a node is started,
loopsincrements. Every row it emits is counted. The output divides byloopsto show average rows per execution. - Timing. The executor reads the clock around the node's work. That is the source of
actual timeand of the instrumentation overhead discussed below.TIMING OFFskips these clock reads but keeps the counts. - Buffers. The backend keeps running counters of buffer activity: shared, local and temp blocks hit, read, dirtied and written. When a node runs, the instrumentation records the counters before and after and attributes the difference to that node. A child's page accesses happen while the parent is running, so they are included in the parent's difference. That is why buffer counts are cumulative up the tree.
A "hit" means the requested page was already in PostgreSQL's shared buffer cache. A "read" means the buffer manager had to ask the operating system for it. The OS may have served that request from its own page cache or from storage, so PostgreSQL's counters cannot tell the two apart. track_io_timing adds the time spent in those requests, which helps distinguish them. Temp blocks come from files that sorts, hashes and materialisation nodes write when their data exceeds work_mem.
These counters measure logical page accesses made by the executor. Some work happens outside the plan nodes: planning, trigger execution, sending rows to the client. That work is either reported separately in the output or not reflected in node buffers at all.
Which queries should you analyze first with pg_stat_statements?
Before reading any plan, decide which query deserves the effort. pg_stat_statements aggregates statistics per normalized statement. The default instinct is to sort by mean execution time, but that surfaces the rare report query that takes eight seconds once a night. It hides the lookup that takes 40 ms and runs thousands of times a minute.
Rank by total execution time first. This number is mean time multiplied by calls, so it reflects how much server time each statement actually consumes. Then rank a second time by shared_blks_hit + shared_blks_read. This shows which statements move the most pages through shared buffers, whether or not those pages were already cached. A query with moderate latency that sits near the top of both lists is usually the best target, for three reasons:
- It competes with every other workload for buffer cache and CPU.
- A small improvement in it is multiplied across every call.
- It is often an ordinary access path missing an index, which is quicker to fix than an analytical query that is slow by design.
Working through the lists in this order keeps the team from tuning statements that barely register in aggregate.
Normalization has a cost. pg_stat_statements replaces literals with placeholders, so one entry blends every parameter value into a single average. A query that is fast for most customers and slow for one large tenant appears as a moderately slow query. The planner may also pick a different plan for that tenant. Before running EXPLAIN (ANALYZE, BUFFERS), find the specific parameter values that cause the slowness, from application logs or slow-query logging, and reproduce the query with those values.
-- Requires pg_stat_statements in shared_preload_libraries and
-- CREATE EXTENSION pg_stat_statements; column names are for PostgreSQL 13+.
-- 1) Rank by total server time consumed (mean_exec_time * calls).
SELECT queryid,
calls,
round(total_exec_time::numeric, 1) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct_time,
left(query, 60) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
-- 2) Rank by pages moved through shared buffers, cached or not.
SELECT queryid,
calls,
shared_blks_hit + shared_blks_read AS shared_blks_total,
shared_blks_read,
round(mean_exec_time::numeric, 2) AS mean_ms,
left(query, 60) AS query
FROM pg_stat_statements
ORDER BY shared_blks_hit + shared_blks_read DESC
LIMIT 10;
-- 3) Statements in both lists show placeholders such as $1. The averages hide
-- which parameter values are slow, so take a slow value from the application
-- logs or the slow-query log and run the plan with that literal.
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.status, o.created_at
FROM orders o
WHERE o.tenant_id = 4821 -- the large tenant, not a typical one
ORDER BY o.created_at DESC
LIMIT 50;
Two pg_stat_statements rankings, one by total execution time and one by shared blocks touched, followed by an EXPLAIN (ANALYZE, BUFFERS) run with a specific slow parameter value, since the normalized entry averages that value away.
How do you run EXPLAIN ANALYZE safely on production-like data?
EXPLAIN ANALYZE is not a dry run. It executes the statement and discards the result rows, so an UPDATE really updates and a DELETE really deletes. For data-modifying statements, open a transaction with BEGIN, run the EXPLAIN (ANALYZE, BUFFERS), then issue ROLLBACK. Rollback undoes the visible effect but not the cost. Every row was still read and written, WAL was generated, row locks were held until the rollback, triggers fired, and sequences advanced, since nextval is never rolled back. The rolled-back row versions remain as dead tuples until vacuum removes them, so repeating the test on a large table creates bloat.
The reported times include instrumentation overhead, because PostgreSQL reads the clock around each node's work, often once per row. A plan that processes millions of rows can look noticeably slower under EXPLAIN ANALYZE than it runs in production. TIMING OFF keeps row counts and buffers while dropping per-node timing.
Run the analysis against data with production's size and distribution. The planner chooses a plan from table statistics, so a small development dataset can produce a completely different plan, such as a seq scan where production uses an index.
track_io_timing adds I/O read and write times to the buffer output. On hosts where reading the clock is expensive (pg_test_timing measures this), enable it with SET for your session only rather than cluster-wide. Changing it requires superuser or a granted privilege.
Do not run it on a busy production primary during peak load. Do not run it on statements with side effects that cannot be rolled back, such as calls to functions that send notifications or write to external systems.
-- Session-only I/O timing. Do not use ALTER SYSTEM here.
-- Requires superuser, or on PG15+: GRANT SET ON PARAMETER track_io_timing TO some_role;
SET track_io_timing = on;
BEGIN;
-- Guards scoped to this transaction: give up instead of queueing
-- behind other lockers or running unbounded on a large table
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '30s';
-- This really executes the UPDATE: rows are written, WAL is generated,
-- row locks are held, and triggers fire
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders
SET status = 'archived'
WHERE created_at < now() - interval '2 years';
-- Undoes the visible change. Does not undo sequence advances,
-- external side effects, or the dead tuples left for vacuum.
ROLLBACK;
-- Lower-overhead variant: actual row counts and buffers,
-- without per-node clock reads
BEGIN;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
DELETE FROM order_events
WHERE order_id = 42;
ROLLBACK;
RESET track_io_timing;
-- See the dead tuples the rolled-back tests left behind
-- (statistics are updated asynchronously, so counts may lag)
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'order_events');
Runs EXPLAIN (ANALYZE, BUFFERS) on data-modifying statements inside BEGIN ... ROLLBACK. It enables track_io_timing for the session only, uses TIMING OFF to reduce instrumentation overhead, and then checks the dead tuples the rollback left behind.
How do buffer counts aggregate through the plan tree?
Buffer counts in a plan are cumulative. Each node reports the pages it touched plus everything its children touched. The top node's totals therefore describe the whole query. To find the node actually responsible for the I/O, subtract the children's counts from the parent's. Whatever remains is that node's own work.
Buffers are totals, time and rows are averages
The loops field is where most misreadings start. For a node that executes more than once, actual time and rows are averages per loop. Buffer counts are totals across all loops. To compare a node's time or rows with its buffers, multiply time and rows by loops. Never multiply the buffers.
Nested loop example
Consider a nested loop joining orders to order_items (illustrative estimate, assumed numbers):
- Nested Loop:
shared hit=40120 read=310 - Outer Seq Scan on
orders:rows=1000 loops=1,shared hit=120 read=10 - Inner Index Scan on
order_items:actual time=0.020..0.030 rows=4 loops=1000,shared hit=40000 read=300
Subtracting both children from the join leaves zero buffers for the Nested Loop node itself. That is expected, because a join reads its children's output, not pages. The inner index scan accounts for nearly all the I/O: about 40 buffers per execution, repeated 1,000 times. Its real cost is roughly 0.030 ms × 1,000 = 30 ms, and it returns about 4,000 rows. Reading 0.030 ms and rows=4 at face value hides both figures.
Per-loop rows are also rounded. A value of rows=0 across many loops can still mean some rows were returned.
-- Capture actual timings and buffer counts; COSTS OFF keeps the output short.
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT o.id, oi.product_id, oi.quantity
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.created_at >= now() - interval '1 day';
-- Illustrative output with assumed numbers, not a measured result:
--
-- Nested Loop (actual time=0.050..35.200 rows=4000 loops=1)
-- Buffers: shared hit=40120 read=310
-- -> Seq Scan on orders o (actual time=0.010..1.500 rows=1000 loops=1)
-- Filter: (created_at >= (now() - '1 day'::interval))
-- Buffers: shared hit=120 read=10
-- -> Index Scan using order_items_order_id_idx on order_items oi
-- (actual time=0.020..0.030 rows=4 loops=1000)
-- Index Cond: (order_id = o.id)
-- Buffers: shared hit=40000 read=300
--
-- Reading it:
-- Buffers are cumulative. Nested Loop's own buffers are its total minus its children:
-- hit: 40120 - 120 - 40000 = 0
-- read: 310 - 10 - 300 = 0
-- The join touches no pages itself. It consumes its children's rows.
--
-- Buffers on the inner scan are already totals across all 1000 loops.
-- Do not multiply them. Per execution: (40000 + 300) / 1000 = about 40 pages.
--
-- Time and rows on the inner scan are per-loop averages, so multiply by loops:
-- time: 0.030 ms * 1000 = about 30 ms of the 35.2 ms total
-- rows: 4 * 1000 = about 4000 rows, matching the Nested Loop's rows=4000
An EXPLAIN (ANALYZE, BUFFERS) run on a nested loop, with an annotated illustrative plan that subtracts child buffers from the parent and multiplies per-loop time and rows by loops while leaving the buffer totals as they are.
How do you diagnose missing indexes, seq scans, heap fetches and temp spills from buffers?
Start by dividing blocks touched by rows returned. A node that reads 40,000 buffers to emit 12 rows is scanning far more data than the result needs. When Rows Removed by Filter is close to the rows the scan visited, the predicate is being checked row by row instead of through an index. Either no index matches the predicate, or one exists but the planner can't use it. Common causes of an unusable index are a function wrapped around the column, a type mismatch, or a leading wildcard in LIKE.
For a seq scan, look at how the buffers split between shared hit and shared read. A scan with high read pulled pages from outside shared buffers, which may mean the OS cache or disk. A scan that is almost entirely hit already had the data in memory. Both touched the same number of pages, so the hit/read split tells you about cache state. It does not tell you whether the plan is good.
| Pattern in output | Likely cause | Cost of the fix |
|---|---|---|
High Rows Removed by Filter, many buffers |
Missing or unusable index | Each index adds write amplification and storage |
Index-only scan with high Heap Fetches |
Visibility map stale; vacuum lagging | Vacuum I/O and scheduling |
temp read/written on Sort or Hash |
work_mem too small for the operation |
Memory, which multiplies under concurrency |
Heap fetches happen when the visibility map can't confirm a page is all-visible. Each fetch is a heap visit that the index-only scan was supposed to avoid.
Temp I/O on a sort, or a hash node with Batches above 1, means the operation spilled to disk. Raising work_mem fixes the spill, but the limit applies per sort or hash operation, not per query. A single query can use several multiples of work_mem, and many concurrent sessions multiply that again.
Is it a cold cache or a bad plan, and when is a seq scan actually correct?
Run the query twice in a row and compare the top node. If shared read falls to near zero on the second run while shared hit rises by the same amount, the first run was paying for a cold cache. If the total pages touched stays high on both runs, the plan is reading too much data, and warming the cache will not fix that.
A read only means the page was not in shared buffers. The operating system may still have served it from its page cache. Turning on track_io_timing adds I/O time to each node. Many reads with very little I/O time came from the OS cache. High I/O time means the reads went to storage. Buffer counts are logical page accesses, and one run is a sample, not a benchmark. Repeat runs and look for a consistent pattern.
A seq scan is the correct plan when the table spans only a few pages, or when the query returns a large fraction of the rows. In that case an index scan adds random heap visits to read the same pages. To confirm, disable seq scans for your session only with SET enable_seqscan = off. Then compare total buffers and timing for the two plans.
| Symptom | Buffers show it? | Use instead |
|---|---|---|
| Expensive functions, in-memory sorts | No | Node timing |
| JIT compilation | No | JIT section of the plan |
| Lock waits | No | wait_event in pg_stat_activity |
| Network transfer | No | Client-side timing |
| Intermittent production slowness | Not in ad hoc runs | auto_explain, pg_stat_statements I/O and time columns |
What are the alternatives to EXPLAIN (ANALYZE, BUFFERS)?
EXPLAIN (ANALYZE, BUFFERS) is one tool among several that ship with PostgreSQL or its contrib modules. Each alternative trades detail for safety or coverage:
- Plain
EXPLAIN. Shows the chosen plan with estimated costs and row counts, without executing anything. It is safe for any statement, including writes, and is useful for checking whether an index would be used at all. It cannot show actual rows, timings or pages, so it will not reveal a bad row estimate or a spill. EXPLAIN ANALYZEwithoutBUFFERS, or withTIMING OFF. Gives actual rows and loops. It is enough when the problem is an estimate that is wildly off. Without buffers you lose the page-level view that tells you why a node is expensive.auto_explain. A contrib module that logs the plans of statements exceeding a duration threshold (auto_explain.log_min_duration). It can include actual counts and buffers viaauto_explain.log_analyzeandauto_explain.log_buffers. This is the right choice for intermittent slowness that you cannot reproduce by hand. Enablinglog_analyzeinstruments every statement in scope, not only the slow ones, so it adds overhead; apply it with care on busy systems.pg_stat_statements. Aggregated time and block counters per normalized statement. It tells you which statements matter and how much I/O they drive in total, but not which node inside a plan is responsible.- Cumulative statistics views.
pg_stat_user_tablesshows sequential versus index scan counts and live and dead tuples per table.pg_statio_user_tablesandpg_statio_user_indexesshow block hits and reads per table and index. They give a workload-wide picture per relation rather than per query. pg_buffercache. A contrib extension that shows which relations currently occupy shared buffers. It helps when the question is about cache sizing rather than one query.- The slow-query log.
log_min_duration_statementrecords statements over a time threshold with their parameter values. It is often the quickest way to find the literal values worth feeding intoEXPLAIN (ANALYZE, BUFFERS).
In practice these tools work together. The aggregate views and logs tell you which statement and which parameters to examine. EXPLAIN (ANALYZE, BUFFERS) then tells you which node inside that plan does the work.
Worked walkthrough: fixing one slow query by comparing buffers before and after
Step 1: Start from the pg_stat_statements entry
Assume one statement sits at the top of pg_stat_statements when ranked by total execution time. It filters orders by customer_id and status and sorts by created_at. It is called often, and its shared_blks_hit plus shared_blks_read divided by calls comes to tens of thousands of pages per call. A query that returns about twenty rows should not touch that many pages. That gap is the first clue.
Step 2: Read the plan node by node
Running EXPLAIN (ANALYZE, BUFFERS) with a representative customer_id shows a Seq Scan on orders with a large Rows Removed by Filter. Its buffer count accounts for almost all of the total at the top node. The Sort above it adds very few buffers of its own. The scan reads the whole table to keep a handful of rows, so the hypothesis is an index on (customer_id, created_at). That index matches the equality filter, returns rows already in sort order, and leaves status as a cheap residual filter.
Step 3: Compare before and after
| Metric (illustrative estimate) | Before | After |
|---|---|---|
| Top scan node | Seq Scan | Index Scan |
| Shared buffers (hit + read) | ~40,000 | ~25 |
| Rows removed by filter | ~2,000,000 | ~5 |
| Sort node | Present | Gone |
Step 4: Weigh the cost against the savings
The index has costs. Every insert into orders, and every update that changes an indexed column, now also writes to the index. The index takes disk space and has to be vacuumed. For a read-heavy table, cutting the pages read per call by roughly three orders of magnitude (illustrative) usually outweighs that overhead. The bigger gain is the method itself: you formed the hypothesis from counted pages and confirmed it the same way, without relying on timings that shift with cache state.
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.
- Adding or removing a column goes through one review with the inheriting services listed, instead of ad-hoc messages to each team.
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.
- Services that depend on a column get the chance to object before it disappears, avoiding emergency fixes.
- 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.