SQL EXPLAIN query plans: read what the database actually did

Seq Scan on orders  (cost=0.00..24518.00 rows=1 width=88)
                    (actual time=0.031..842.117 rows=1 loops=1)
  Filter: (account_id = 42)
  Rows Removed by Filter: 999999
Planning Time: 0.094 ms
Execution Time: 842.201 ms

That is a query taking 842ms to return one row, and the plan tells you exactly why in two numbers. Seq Scan means the database read the whole orders table. Rows Removed by Filter: 999999 means it threw away a million rows to find your one. This is a missing index, and you didn't have to guess — you read it. EXPLAIN is how you stop arguing about performance and start looking at it.

Run EXPLAIN alone to see the plan the planner intends; run EXPLAIN ANALYZE to actually execute it and get real timings and real row counts alongside the estimates.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE account_id = 42 ORDER BY created_at DESC LIMIT 50;

The one number that predicts everything: estimate vs actual

The planner picks a strategy based on how many rows it thinks each step returns. When that estimate is wrong, every downstream decision is wrong. In the plan above, rows=1 (estimate) next to actual ... rows=1 happened to match, but the real tell is when they diverge by orders of magnitude — rows=12 estimated, rows=340000 actual. That means the planner's statistics are stale or the columns are correlated, and it chose a nested loop that's catastrophic at the real cardinality. Fix it with ANALYZE orders; to refresh statistics before you touch indexes.

EXPLAIN vs EXPLAIN ANALYZE

	`EXPLAIN`	`EXPLAIN ANALYZE`
Runs the query?	No	Yes — it executes
Shows estimated rows	Yes	Yes
Shows actual rows and time	No	Yes
Safe on a `DELETE`/`UPDATE`?	Yes	No — wrap in a transaction and `ROLLBACK`
Use it to...	check a plan cheaply	diagnose why it's actually slow

The estimate-only form is free and safe. The analyze form is where the truth lives, but it runs the statement — including writes.

FAQ

Why is the cost not in milliseconds?

Postgres cost is a unitless number for comparing plans, anchored to seq_page_cost = 1.0. A cost of 24518 doesn't mean 24 seconds; it means "roughly 24,518 sequential page reads' worth of work." Only EXPLAIN ANALYZE's actual time is in real milliseconds.

The plan looks fine but the query is still slow — now what?

Look at loops. A cheap-looking node with loops=50000 runs fifty thousand times; multiply its per-loop time out. Nested loops hide their total cost this way.

Can I EXPLAIN a query that modifies data?

Yes, but EXPLAIN ANALYZE UPDATE ... performs the update. Wrap it: BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK;. You get the real plan, and the change is discarded.

Change one thing, re-run EXPLAIN ANALYZE, and compare. If the Seq Scan becomes an Index Scan and Rows Removed by Filter drops to zero, the index earned its keep. If nothing moves, you indexed the wrong column — the plan will say so.

Cover photo by Stanislav Kondratiev on Pexels.