How to Actually Read a Query Plan
A practical guide to reading execution plans inside out, comparing estimated rows against actual rows, and fixing the one operator that matters.
What a plan actually is
SQL is declarative. You describe the result you want, and the database decides
how to produce it: which table to read first, whether to build a hash table or
sort, whether an index is worth using. That decision is the execution plan,
and EXPLAIN prints it.
Most people glance at a plan, see words like Hash Join and Seq Scan, decide
it is intimidating, and go back to guessing at indexes. It is worth ten minutes
of your life to learn properly, because the plan is the only place the database
tells you what it is really doing.
Read it inside out
A plan is a tree. The top line is the last thing that happens: it produces the final rows. Each indented child runs first and feeds its parent, so you read a plan the way you read a nested function call: innermost first.
Sort (cost=182.41..182.66 rows=100 width=48) (actual time=3.412..3.418 rows=87 loops=1)
Sort Key: (sum((o.quantity * p.price))) DESC
-> HashAggregate (cost=176.10..179.10 rows=100 width=48) (actual time=3.204..3.301 rows=87 loops=1)
Group Key: c.name
-> Hash Join (cost=38.25..151.10 rows=2000 width=44) (actual time=0.412..2.118 rows=1843 loops=1)
Hash Cond: (o.customer_id = c.id)
-> Seq Scan on orders o (actual time=0.011..0.402 rows=1843 loops=1)
Filter: (status = 'completed')
Rows Removed by Filter: 657
-> Hash (actual time=0.381..0.381 rows=15 loops=1)
-> Seq Scan on customers c (actual time=0.008..0.190 rows=15 loops=1)Bottom up, in English: scan orders and throw away everything that is not
completed, scan customers and build a hash table from it, join the two, group
by customer name, then sort. Four sentences. That is the whole plan.
Start with the deepest, widest line
The operator that took the most time is usually a leaf, not the root. Parent timings in Postgres are cumulative (they include their children), so a slow root does not mean the root is the problem.
The operators that cover most plans
| Operator | What it means | When it hurts |
|---|---|---|
| Seq Scan / Table Scan | Reads every row in the table | On a large table with a selective filter, meaning a missing index |
| Index Scan | Walks an index, fetches matching rows | Rarely; but it is slower than a seq scan when it matches most rows |
| Hash Join | Builds a hash table from one side, probes with the other | When the build side is too big for memory and spills to disk |
| Nested Loop | For each row on the left, look up the right | When the left side has far more rows than the planner estimated |
| Sort | Orders rows | When it spills to disk, shown as an external merge |
| Aggregate / HashAggregate | Collapses groups | When the group count is much higher than estimated |
You do not need more vocabulary than this to diagnose the majority of slow queries.
The number that matters is estimated versus actual
EXPLAIN alone shows the planner's estimates. EXPLAIN ANALYZE actually runs
the query and shows the real numbers next to them. That comparison is the single
most useful thing in the output.
In the plan above, the join estimated rows=2000 and actually produced
rows=1843. Close enough: the planner's statistics are good, and its choice of
a hash join was informed. When you instead see rows=100 estimated against
rows=480000 actual, you have found your bug: the planner chose a nested loop
because it expected a hundred rows, and then executed it half a million times.
The fix in that case is almost never a query rewrite. It is stale statistics
(run ANALYZE), a correlation the planner cannot see across two columns, or a
filter it cannot estimate through, such as a function wrapped around a column.
A wrapped column silently disables the index
WHERE YEAR(order_date) = 2022 cannot use an index on order_date, because
the index stores dates and not years. Rewrite it as a range,
order_date >= '2022-01-01' AND order_date < '2023-01-01', and the same
query becomes an index range scan.
Watch the row counts yourself
You can develop the same instinct without a plan, by running the stages of a query separately and watching how many rows survive each one. Start with the raw scan.
Then the join, then the aggregate. Each stage should shrink the data or explain why it does not.
A query that reads a million rows to return twelve is doing filtering too late. That is the shape you are looking for in a plan: a wide, expensive scan feeding a narrow result.
Running EXPLAIN here
The playground speaks DuckDB, so EXPLAIN SELECT ... works, but DuckDB
returns its plan as one big block of text in a single cell, so copy it out to
read the tree properly.
The vocabulary map
Every database prints the same ideas with different words. Postgres says
Seq Scan, MySQL's EXPLAIN puts ALL in the type column, SQL Server draws
a Table Scan icon, DuckDB prints SEQ_SCAN. Postgres says Hash Join, MySQL
says hash join in the Extra column of newer versions, SQL Server calls it a
Hash Match. Learn the concepts once and the translation is mechanical.
Make it a habit
Run EXPLAIN ANALYZE on any query before it goes near production, and ask three
questions: what is the deepest operator, how far off is the row estimate, and is
the filter happening at the scan or after the join. Almost every real
optimisation you will ever do starts with one of those three answers.
Cite this resource
SQLSimplified. "How to Actually Read a Query Plan". Available at: https://sqlsimplified.online/blog/how-to-read-a-query-execution-plan