What does EXPLAIN show in a SQL database?
A query plan is the list of steps a database will follow to answer a query. Put the word EXPLAIN in front of a query, and DuckDB prints the plan instead of running it.
Reading a plan tells you why one query is cheaper than another, and whether a filter was applied early.
By the end of this lesson
Note
I can read EXPLAIN and say why one query is cheaper than another.
Key words
| Word | What it means |
|---|---|
| Query plan | the steps a database takes to answer a query |
| Scan | a step that reads rows from a table |
| Filter | a step that throws away rows that fail a test |
| Join | a step that matches rows from two tables |
| Optimiser | the part of the database that chooses the plan |
The cells students fill in are left blank in this preview.
Read a plan from the bottom up
Each step in a plan is a box. Data flows upwards: the bottom boxes read tables, and the top box gives the answer.
The boxes you will meet most are the scan, the filter, the join and the aggregate. The plan also lists which columns each scan reads.
Run the plan for a one column total. It has two boxes that matter.
Try it: the plan of a total
-- The plan of a one column total.
explain
select sum(amount) from purchases;
Worked example: the steps in a plan
| Step | What it does | Why it matters |
|---|---|---|
| Scan | Reads rows from a table, and only the columns named | The first cost of every query |
| Filter | Drops rows that fail the where test | Fewer rows for every later step |
| Join | Matches rows from two tables | Cost grows with the rows it receives |
| Aggregate | Turns many rows into group totals | Usually the last step |
The cells students fill in are left blank in this preview.
Filter before a join to cut its work
Say purchases has a million rows, customers has 5,000, and a filter keeps one purchase in ten. If the filter runs first, the join receives 100,000 rows. If it runs after, the join receives a million.
The join does about ten times less work when the filter goes first. That is why the position of the filter in the plan is worth looking for.
Try it: where does the filter sit?
-- The filter is written after the join.
-- Look for it in the plan: where does it sit?
explain
select c.name, sum(p.amount) as total
from customers c
join purchases p on p.customer_id = c.id
where p.bought >= date '2026-03-01'
group by c.name;
DuckDB often moves the filter for you
In that query the where is written after the join. Yet in the plan, the filter appears inside the scan of purchases, below the join. The optimiser moved it down.
So you cannot judge a query by how it is written. Read the plan, and trust what it shows.
A common mistake
Warning
Many people think a query runs in the order it is written. It does not. The optimiser picks the order of the steps. Two queries written differently can have the same plan.
Writing the filter first makes your intention clear
When a query is long, name the filtered rows first in a with query. The reader sees which rows are kept before the join starts.
The result is the same, and the plan is usually the same too. It is the clearest way to say "filter, then join".
Try it: filter first, then join
-- The same answer, with the filter written first.
with recent as (
select customer_id, amount
from purchases
where bought >= date '2026-03-01'
)
select c.name, sum(r.amount) as total
from recent r
join customers c on c.id = r.customer_id
group by c.name
order by c.name;
Going further
Put explain in front of the with query and compare its plan with the plan of the join query. Say what is the same.
In the playground you can also try explain analyze. It runs the query and adds timings, which change from run to run, so never compare two runs on a tiny table.