Monitoring

How to Read PostgreSQL EXPLAIN ANALYZE Without Guessing

EXPLAIN ANALYZE tells you exactly where a slow query spends its time, but only if you know which numbers to trust. Here is how to read costs, actual rows, loops and buffers, spot the five patterns behind most bad plans, and use it safely on production-sized data.

· 13 min read · AG Data Team

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:

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

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

  1. Estimates off by 10× or more. Compare estimated rows with actual rows × loops on 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: run ANALYZE, raise the statistics target on that column, or add extended statistics for correlated columns.
  2. 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.
  3. 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).
  4. 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):
    Sort Method: external merge  Disk: 812344kB
    ...
    Hash  (actual time=... rows=... loops=1)
      Buckets: 131072  Batches: 16  Memory Usage: 4097kB
    The usual fix is more work_mem for that query or session, not globally, or a plan that does not need to sort that much.
  5. 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" is good news. In our experience a node marked (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:

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

PostgreSQL EXPLAIN Query tuning Performance
Work with us

A query plan you cannot explain?

Send us the plan. We read EXPLAIN output for a living and fix slow PostgreSQL queries on production-sized data, not on toy copies.

Get in touch More from the blog