Reading EXPLAIN ANALYZE

SQL, indexing and PostgreSQL Mid-level Python 3.13+ PostgreSQL 17+ Reviewed 31 Oct 2026 By Best Latest Apps

Order the steps

Order the steps for diagnosing a query that became slow after a data import.

Run ANALYZE on the imported table and re-check the plan
Find it in pg_stat_statements by total time
Compare estimated vs actual rows node by node
Run EXPLAIN (ANALYZE, BUFFERS) inside BEGIN ... ROLLBACK

Choose an answer, then open Show answer below to check it.

Show answer

Answer: B D C A

Find it in pg_stat_statements by total time Pick the query that costs the most overall.
Run EXPLAIN (ANALYZE, BUFFERS) inside BEGIN ... ROLLBACK Real timings without keeping side effects.
Compare estimated vs actual rows node by node A big gap points at stale or missing statistics.
Run ANALYZE on the imported table and re-check the plan Bulk imports often leave statistics stale until autovacuum catches up.

30-second answer

Run EXPLAIN (ANALYZE, BUFFERS) and read the plan tree from the innermost nodes outward. For each node compare estimated rows with actual rows: a big mismatch means bad statistics, and the planner picked a join or scan method for the wrong size. Then look for where the time and buffers go: a sequential scan that removes most rows, a nested loop with many loops, or a sort that spills to disk.

Practise saying it · 40 s. No recording, just a timer

How do you read a query plan?

Say your answer out loud, then check which of these points you covered:

EXPLAIN (ANALYZE, BUFFERS)
Estimated vs actual rows
Where time and buffers go
Fix stats, index or query
pg_stat_statements to choose

Deep dive

ANALYZE actually runs the query; wrap writes in BEGIN ... ROLLBACK.

'Rows Removed by Filter' in the millions suggests a missing index.

'Sort Method: external merge Disk' means work_mem was too small for that sort.

Fix estimates with ANALYZE table, extended statistics (CREATE STATISTICS) for correlated columns, or a higher statistics target.

pg_stat_statements finds which queries to look at first: by total time, not just the slowest single call.

Plans you can read in a test Ran on PostgreSQL 17.11

SQL · PostgreSQL 17

CREATE TABLE t (id int PRIMARY KEY, v int);
INSERT INTO t SELECT g, g % 10 FROM generate_series(1, 10000) g;
ANALYZE t;
EXPLAIN (COSTS OFF) SELECT * FROM t WHERE id = 5;

Common mistakes

Reading only the top line's total cost.
Running EXPLAIN without ANALYZE and trusting the estimates.
Running EXPLAIN ANALYZE on a DELETE in production outside a transaction.

Senior take & follow-ups

Show a method: estimates vs actuals, then where buffers and time go, then fix the root cause (index, stats, query shape).

Follow-up questions

Think of your answer first, then open the hint.

What does 'loops=1000' on an inner node mean?
Show hint
It ran 1000 times; multiply its time and rows by loops.
How do you find the slowest queries overall?
Show hint
pg_stat_statements ordered by total_exec_time.

Suggest a fix if something here is wrong or out of date.