Reading Postgres EXPLAIN ANALYZE: A Practical Guide to Query Plans

When a query is slow, guessing at indexes wastes time. EXPLAIN ANALYZE shows you exactly what Postgres did: which tables it scanned and how, which join algorithms it chose, how many rows each step produced, and where the time went. Reading plans fluently is the single most valuable Postgres performance skill.

EXPLAIN vs EXPLAIN ANALYZE

• EXPLAIN shows the planned strategy and the planner's estimates. It doesn't run the query.
• EXPLAIN ANALYZE runs the query and adds actual row counts and timings.

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'DE' AND o.created_at > now() - interval '30 days';

BUFFERS adds how many 8 KB pages were read from memory (shared hit) or from disk (read) — usually the best indicator of real work. (In PostgreSQL 18, ANALYZE includes buffer information by default.)

Careful: EXPLAIN ANALYZE really executes the statement. For UPDATE, DELETE or INSERT, wrap it in a transaction you roll back:

BEGIN;
EXPLAIN ANALYZE DELETE FROM sessions WHERE expires_at < now();
ROLLBACK;

Anatomy of a plan

Hash Join  (cost=12.50..845.20 rows=420 width=16) (actual time=0.31..9.84 rows=397 loops=1)
  Hash Cond: (o.customer_id = c.id)
  Buffers: shared hit=310
  ->  Index Scan using orders_created_at_idx on orders o  (cost=0.43..812.00 rows=8400 width=24) (actual time=0.02..6.10 rows=8120 loops=1)
        Index Cond: (created_at > (now() - '30 days'::interval))
  ->  Hash  (cost=11.00..11.00 rows=120 width=8) (actual time=0.25..0.25 rows=118 loops=1)
        ->  Seq Scan on customers c  (cost=0.00..11.00 rows=120 width=8) (actual time=0.01..0.20 rows=118 loops=1)
              Filter: (country = 'DE'::text)
              Rows Removed by Filter: 3882
Planning Time: 0.4 ms
Execution Time: 10.1 ms

• The tree runs inside-out. Indented child nodes feed their parent. Read from the most indented upwards.
• cost=startup..total — the planner's estimate in arbitrary units. Useful for comparing alternatives, not as milliseconds.
• rows= in the first brackets is the estimate; in the actual brackets, the real count.
• actual time=first..last — milliseconds to the first row and to the last row, per loop.
• loops — how many times the node ran. Multiply time and rows by loops for the true total — crucial inside nested loops.
• Rows Removed by Filter — rows read and thrown away. Large numbers suggest a missing or unsuitable index.

Scan types

Node	Meaning
Seq Scan	Reads the whole table. Fine for small tables or when most rows are needed; a red flag on a big table returning few rows.
Index Scan	Uses an index to find rows, then fetches each from the table.
Index Only Scan	Answers entirely from the index (needs a covering index and an up-to-date visibility map — see VACUUM). Watch Heap Fetches.
Bitmap Index / Heap Scan	Collects matching row locations from one or more indexes, then reads table pages in order. Good for medium selectivity and combining indexes.

Join types

Node	Good when
Nested Loop	The outer side is small; the inner side has an index. Terrible when the outer side is unexpectedly large.
Hash Join	Joining larger sets on equality; builds a hash table of the smaller side. Watch for Batches > 1 (spilled to disk).
Merge Join	Both inputs already sorted on the join key.

The most important thing to check: estimates vs actuals

Most bad plans come from bad row estimates. Compare estimated rows= with actual rows= node by node:

• Off by 10× or more — the planner chose a strategy for a situation that doesn't exist. Classic case: it expects 5 rows, picks a Nested Loop, gets 500,000 and runs the inner side 500,000 times.

Fixes:

1. ANALYZE tablename; — refresh statistics, especially after bulk loads.

2. Raise the statistics target for skewed columns: ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000; then ANALYZE.

3. Extended statistics for correlated columns (e.g. city and country), which the planner otherwise assumes are independent:

   CREATE STATISTICS orders_city_country (dependencies) ON city, country FROM orders;
   ANALYZE orders;
   

4. Rewrite the predicate so it can use statistics and indexes (avoid wrapping indexed columns in functions; see below).

Other things to look for

• Sort Method: external merge Disk: 51200kB — the sort didn't fit in work_mem and spilled to disk. Add an index that provides the order, reduce the rows being sorted, or raise work_mem for that query.
• Functions on indexed columns: WHERE lower(email) = $1 can't use a plain index on email. Create an expression index on lower(email). (Database indexes.)
• LIMIT with ORDER BY on an unindexed column — sorts everything to return ten rows.
• High Planning Time — huge numbers of partitions or complex queries; occasionally worth addressing.
• JIT sections on short queries — JIT compilation can cost more than it saves for OLTP queries; consider raising jit_above_cost or disabling it.

Finding which queries to explain

Don't optimise at random. Find the queries costing the most total time:

• pg_stat_statements (an extension) aggregates every query's calls, total and mean time:

  SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 1) AS mean_ms,
         left(query, 80)
  FROM pg_stat_statements
  ORDER BY total_exec_time DESC
  LIMIT 10;
  

  A 5 ms query called 2 million times a day matters more than a 2-second report run once.

• auto_explain logs the plans of queries slower than a threshold automatically, capturing real production plans with real parameters.

Tools that help

Paste plans (text or FORMAT JSON) into an online plan visualiser to see the tree, the slowest nodes and the estimate mismatches highlighted. Useful for long plans.

The summary

• EXPLAIN = plan and estimates; EXPLAIN (ANALYZE, BUFFERS) = actual execution. Roll back DML.
• Read inside-out; multiply by loops; compare estimated vs actual rows.
• Bad estimates → ANALYZE, statistics targets, extended statistics.
• Watch for big Seq Scans, rows removed by filter, disk sorts, and functions on indexed columns.
• Use pg_stat_statements to choose which queries to fix.

---

EasySpawn gives Claude Code direct access to your app's PostgreSQL on the same server, so it can run EXPLAIN ANALYZE on real data, propose an index, and measure the difference before and after. See how it works or join the waitlist.

Related: The N+1 Query Problem · Postgres Full-Text Search · pgvector Tutorial · Why Is My Website Slow?