SQL EXPLAIN and Query Plans

Put EXPLAIN in front of a query and the database describes how it would run it instead of running it. The answer is a query plan: a tree of steps, each one feeding rows to the step above. It is the quickest way to see what a query actually does, which is not always what its SQL looks like it does.

explain
select product_name, price
from products
where price > 20

DuckDB returns one row with two columns. explain_key says physical_plan, and explain_value holds the plan, drawn as boxes:

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│        product_name       │
│           price           │
│                           │
│          ~4 rows          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│          SEQ_SCAN         │
│    ────────────────────   │
│           Table:          │
│    memory.main.products   │
│                           │
│        Projections:       │
│           price           │
│        product_name       │
│                           │
│    Filters: price>20.0    │
│                           │
│          ~4 rows          │
└───────────────────────────┘

Reading a plan

Read it from the bottom up. Rows start at the bottom, where the tables are read, and flow up through each box to the result at the top.

• SEQ_SCAN reads products. Its Projections are the only columns it reads, and its Filters line is the WHERE clause: DuckDB checks price > 20 while scanning, so rows that fail never leave the scan.
• PROJECTION picks the output columns, in the order the SELECT lists them.
• ~4 rows is an estimate. The query really returns 5 rows. DuckDB plans before it runs, so every count with a ~ is a guess.

Joins and grouping add boxes with two inputs:

explain
select c.state, count(*) as orders
from customers as c
join orders as o on o.customer_id = c.customer_id
group by c.state

Now two SEQ_SCAN boxes sit side by side at the bottom, one per table, and both feed a HASH_JOIN that shows its Join Type and its Conditions. Above it, HASH_GROUP_BY does the grouping, and lists the aggregates it computes (count_star()). Its estimate is ~6 rows; the query returns 7 states.

EXPLAIN ANALYZE runs the query

explain analyze
select product_name, price
from products
where price > 20

EXPLAIN ANALYZE runs the query, throws away its result, and reports what happened. explain_key becomes analyzed_plan, a Total Time appears at the top, and every box shows the rows it actually produced and how long it took: the scan now says 5 rows, with no ~. The scan is labeled TABLE_SCAN here rather than SEQ_SCAN; it is the same step.

Comparing estimates with real counts is how you find the step that does something you did not expect. That is the main use of a plan on small data.

Finding a fan-out

A join that matches each row to several rows multiplies them, and every count above it inflates. The plan shows exactly where.

explain analyze
select c.customer_id, count(*) as order_rows
from customers as c
join orders as o on o.customer_id = c.customer_id
join order_items as oi on oi.order_id = o.order_id
group by c.customer_id

The scan of orders produces 24 rows, and the join of orders to customers still produces 24. The join to order_items produces 48: one row per order line, not per order. So count(*) here counts lines: customer 2 placed one order, with two products in it, and shows 2. That is the bug on COUNT or SUM too high after a JOIN, visible as a row count that doubles between two boxes. count(distinct o.order_id) counts orders instead.

EXPLAIN on a change

EXPLAIN works on UPDATE, DELETE, and INSERT too, and it changes nothing. EXPLAIN ANALYZE does change data, because it runs the statement. Try it on a copy:

create or replace table orders_copy as
select * from orders

explain
delete from orders_copy
where status = 'cancelled'

The plan is a DELETE box over a scan filtered on status='cancelled', estimated at ~6 rows. orders_copy still has all 24 orders.

explain analyze
delete from orders_copy
where status = 'cancelled'

select count(*) from orders_copy

23. The one cancelled order is gone: the estimate said six, and EXPLAIN ANALYZE deleted the one that really matched. On a real database, wrap an analyzed change in a transaction and roll it back; see safe UPDATE and DELETE.

Other output formats

explain (format json)
select count(*) from orders

Each step becomes an object with a name, its children, and an extra_info holding the same details as the boxes, including Estimated Cardinality. JSON is easier to search or compare than box drawings when a plan is long.

What can go wrong

• Treating estimates as facts. On the queries above, DuckDB guessed 4 for 5, 6 for 7, and 6 for 1. An estimate tells you the shape the planner expected, not what the data holds. Use EXPLAIN ANALYZE when the number matters.
• Expecting the plan to mirror the SQL. The planner rewrites queries. With order by price desc limit 3, DuckDB 1.5 finds the top three rows by row id first, then joins back to the table to fetch their columns, and estimates ~0 rows for a step that returns 3. Read what the plan does, not what you expect it to do.
• Trusting the timings. Here the engine runs in your browser tab, on data a few dozen rows long, so every step takes a fraction of a millisecond and the times tell you nothing about a production database. Row counts are the useful part.
• Forgetting that ANALYZE executes. On a production database, EXPLAIN ANALYZE on a slow query is as slow as the query, and on a DELETE it deletes.

EXPLAIN does still check the query: a misspelled column fails with the same Binder Error the query would raise, without running anything. See Referenced column not found.

Across databases

Every engine has a plan; the command, and whether it runs the query, differ. Operator names differ too, so a plan from one engine does not read the same in another.