PostgreSQL EXPLAIN ANALYZE runs your query and shows the plan the planner chose, with real timings and row counts next to every estimate. It is the single most useful tool for a slow query, and also the most misread one: people look at the cost numbers, which are guesses, and skip the lines that actually explain the problem.
This guide is about reading the output quickly: which numbers matter, the handful of patterns behind most bad plans, and what to watch out for when the table has a billion rows instead of a thousand. If you still need to find which query to look at, start with our pg_stat_statements guide.
Run it the right way
Plain EXPLAIN only plans the query. EXPLAIN ANALYZE executes it. The manual is explicit that side effects happen as usual, and its advice for INSERT, UPDATE, DELETE, MERGE and similar statements is to wrap them in a transaction and roll back (EXPLAIN):
-- Read-only query: run it for real
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
-- Anything that writes: run it inside a transaction and throw it away
BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'archived' WHERE created_at < '2025-01-01';
ROLLBACK;
Always ask for buffers. Since PostgreSQL 18 BUFFERS is included automatically with ANALYZE (PostgreSQL 18 release notes); on older versions add it yourself. Other options worth knowing:
SETTINGSlists planner-related settings that differ from the built-in defaults. Useful when a plan differs between two servers.SERIALIZE(PostgreSQL 17+) includes the cost of converting the output for the client. By default EXPLAIN ANALYZE does not include it, nor network transfer, so a query that returns huge rows looks faster than it is (PostgreSQL 17 release notes).TIMING OFFkeeps actual row counts but drops per-node timing, which reduces measurement overhead.GENERIC_PLANplans a query with$1-style placeholders, the way prepared statements do. It cannot be combined withANALYZE.FORMAT JSONif you want to paste the plan into a visualizer.
Read one node
Index Scan using orders_customer_id_idx on orders
(cost=0.43..8.45 rows=1 width=64)
(actual time=0.031..0.120 rows=12.00 loops=2500)
Index Cond: (customer_id = c.id)
Buffers: shared hit=9800 read=240
cost=0.43..8.45is startup cost and total cost in the planner's arbitrary units, conventionally relative toseq_page_cost = 1.0. It is not time. Compare costs between plans for the same query; do not compare them to milliseconds (Using EXPLAIN).rows=1in the first bracket is the estimate of rows the node will emit, not rows it scans.widthis the estimated average row size in bytes.actual time=0.031..0.120is in milliseconds: time to the first row, then time to the last row.loops=2500changes everything. Actual time and rows are averages per execution, so this node really took about 0.12 ms × 2500 = 300 ms and returned about 30,000 rows in total. Most "the slow part is not where I expected" moments come from forgetting to multiply by loops.Buffers: shared hit=9800 read=240: hit means the 8 kB page was already in shared buffers, read means it came from the OS cache or disk. The counts include all child nodes and are not distinct pages, so a page read twice counts twice.
PostgreSQL 18 prints actual rows with decimals (rows=12.00) because they are averages over loops; older versions round them, which hides the difference between 0.4 and 0 rows per loop.
Five patterns behind most bad plans
- Estimates off by 10× or more. Compare estimated
rowswith actualrows×loopson each node, starting from the bottom. The first node where they diverge badly is usually the cause; everything above it was planned on wrong numbers. Typical fixes: runANALYZE, raise the statistics target on that column, or add extended statistics for correlated columns. - A nested loop with a huge loop count. The planner expected 10 outer rows and got 200,000, so the inner index scan ran 200,000 times. Often this is pattern 1 in disguise.
- Lots of
Rows Removed by Filter. A scan reads a million rows to keep fifty. Either an index is missing, or the existing index does not match the condition (wrong column order, a function on the column, a type mismatch). - Work spilling to disk. The Sort node tells you whether it sorted in memory or on disk, and a Hash node with more than one batch also uses disk space (Using EXPLAIN):
The usual fix is moreSort Method: external merge Disk: 812344kB ... Hash (actual time=... rows=... loops=1) Buckets: 131072 Batches: 16 Memory Usage: 4097kBwork_memfor that query or session, not globally, or a plan that does not need to sort that much. - Index Only Scan with many
Heap Fetches. In our experience this means the visibility map is behind, so the "index-only" scan still visits the table for most rows. Check that vacuum is keeping up on that table.
(never executed) means PostgreSQL skipped it at run time, for example the inner side of a join whose outer side returned no rows. It is not a bug, and its cost is not part of the problem.Prepared statements and generic plans
A query that is fast in psql and slow from the application is often a plan-cache issue. For prepared statements, PostgreSQL runs the first five executions with custom plans, then compares a generic plan with their average cost and may switch to the generic one (PREPARE). A generic plan cannot use the actual parameter value, so it can be terrible for skewed data, such as one customer with 40% of the rows. Compare EXPLAIN (GENERIC_PLAN) with a normal EXPLAIN using a real value, and if needed set plan_cache_mode = force_custom_plan for the affected role or session.
On large databases
EXPLAIN ANALYZE on a 50-row dev table tells you very little. The manual says so directly: results on a toy-sized table cannot be assumed to apply to large tables, because the planner's cost estimates are not linear and it may choose a different plan. On big production systems:
- Start with plain EXPLAIN. EXPLAIN ANALYZE runs the full query. A report that takes 40 minutes takes 40 minutes under EXPLAIN too, holds its locks and snapshot the whole time, and on a busy primary can hold back vacuum. Set
statement_timeoutin the session before you run it. - Test on production-sized data. A staging copy with 1% of the rows has different statistics and different plans. If you cannot test on production, use a recent full-size restore, which also tests your backups.
- Cold and warm cache are different queries. The second run is often much faster because pages moved from
readtohit. On tables larger than RAM, the cold number is the one your users see after a restart or a failover. - Turn on
track_io_timingto see I/O time in the buffers output. It is off by default because it queries the clock repeatedly; check the overhead on your hardware withpg_test_timingfirst (statistics settings). - Timing overhead adds up. On nodes executed millions of times, per-node timing can make EXPLAIN ANALYZE much slower than the query itself. Use
TIMING OFFwhen you only need row counts. - Catch plans in production with
auto_explain. It logs plans of statements slower than a threshold. Withlog_analyzeon, per-node timing applies to every statement, not just logged ones, and the manual warns this can have an extremely negative impact on performance, so combine it withlog_timing = offand a sample rate (auto_explain):# postgresql.conf session_preload_libraries = 'auto_explain' auto_explain.log_min_duration = '2s' # default -1 = off auto_explain.log_analyze = on # default off auto_explain.log_buffers = on # needs log_analyze auto_explain.log_timing = off # keeps overhead down auto_explain.sample_rate = 0.1 # explain ~10% of statements in each session
Once you know what the plan is telling you, the fixes are the usual ones: indexes, statistics, vacuum, then memory. Our slow query tuning checklist walks through them in order.
Key takeaways
- EXPLAIN ANALYZE executes the statement; wrap writes in
BEGIN/ROLLBACK. - Cost is in planner units, not milliseconds; actual time and rows are per loop, so multiply by
loops. - Find the lowest node where estimated and actual rows diverge; that is usually the root cause.
- Look for large
Rows Removed by Filter, on-disk sorts, multi-batch hashes and heavy heap fetches. - PostgreSQL 18 includes buffers automatically and shows fractional row counts.
- On large databases: plain EXPLAIN first, a statement timeout, production-sized data, and sampled auto_explain without per-node timing.