Your SQL says what answer you want. EXPLAIN shows how DuckDB plans to produce it. The two descriptions need not appear in the same order, and some SQL clauses may be absorbed into other operations.

We will follow a small regional-sales query from the table scan to its final ordering. By the end, the plan should read like a flow of records rather than a wall of operator names.

Explore the idea

Follow the plan from the input upward

Select an operator to read its role and its captured estimate.

~4

estimated scan output rows

Keep paid orders at the scan

The captured planner estimate is four. The fixture actually contains six paid orders; EXPLAIN has not measured execution.

Start with a question you can check

The question is: “What is the total amount of paid orders in each region?” Cancelled orders must contribute nothing. We sort the resulting totals from largest to smallest, using the region name to break ties.

orders8 rows

Eight orders to plan around

Six orders are paid. Two cancelled orders should contribute nothing to the regional totals.

idregionstatusamount
1Northpaid40
2Southpaid30
3Northcancelled100
4Westpaid20
5Southpaid70
6Northpaid60
7Westcancelled50
8Eastpaid10

There are eight input records and six paid orders. Because the dataset is small, you can calculate the answer before worrying about the plan.

Put EXPLAIN before the SELECT

Run the example to see your browser engine’s plan. The excerpt underneath is captured from native DuckDB 2.0.0 on the same input, with projection and internal operators omitted for readability.

Ask DuckDB how it plans to answer the question

EXPLAIN
SELECT region, sum(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY region
ORDER BY total DESC, region;
Captured physical plan · selected operators
ORDER_BY
  Order By: total DESC, region ASC
  Estimated Cardinality: 2

HASH_GROUP_BY
  Groups: #0
  Aggregates: sum_no_overflow(#1)
  Estimated Cardinality: 2

SEQ_SCAN
  Table: memory.main.orders
  Type: Sequential Scan
  Filters: (status = 'paid')
  Estimated Cardinality: 4

Captured with native DuckDB 2.0.0 on these fixtures. Projection/internal operators omitted; browser formatting can differ. Cardinalities are estimates; this statement did not execute the SELECT.

The ordinary EXPLAIN statement shows the physical plan without executing this SELECT. Planning still requires binding the query and obtaining information about its inputs, so it is not a promise of zero metadata or file access.

Operator labels and formatting can change between engine versions. Follow the operations and their relationships instead of relying on exact typography.

Begin at the scan

The SEQ_SCAN reads orders and carries the filter status = 'paid'. There is no separate filter box in this excerpt because the condition has been pushed into the scan.

Its estimated cardinality is 4. That means the planner estimates four output rows from this filtered scan. It does not mean there are four rows stored in the table, and it is not a measurement of how many rows were inspected.

In this fixture, we know six orders are paid. A different estimate is not itself a query correctness problem: the estimate helps plan the work, while execution still evaluates the predicate against the data.

Follow aggregation, then ordering

The HASH_GROUP_BY groups the scan’s output by region and computes a sum. The excerpt refers to internal column positions such as #0 and #1; these are the values passed between operators, not your table’s permanent column names.

The final ORDER_BY sorts the regional results by total and region. This is the smaller, aggregated result, not a sort of every raw order.

Read the displayed tree from its input scans upward toward its output. This is a way to trace dependencies; it does not mean DuckDB must fully materialize each intermediate result before doing the next operation. Execution can pipeline work between operators.

Run the actual query separately

Remove EXPLAIN when you want the rows:

Now run the query and see the answer

SELECT region, sum(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY region
ORDER BY total DESC, region;
Expected result · sample data4 rows
region total
North 100
South 100
West 20
East 10

North and South each total 100, West totals 20, and East totals 10. There are four groups, even though the captured plan estimated two. Again, estimates describe expectations, not the answer.

EXPLAIN does not tell us the measured time, actual operator output counts, or whether this tiny query feels fast on someone else’s machine. Those are questions for EXPLAIN ANALYZE.

Your turn · check your answer

Write the query described by the plan

Return region and total. The scan keeps paid orders; the aggregate sums amount by region. Return those regional totals.

See the expected result
Your target result4 rows
region total
North 100
South 100
West 20
East 10
Sample data is loaded for you.

Preparing the editor…

Need a hint?

The filter belongs on the input orders. A cancelled order must never reach the sum.

Show a solution
SELECT region, sum(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY region
ORDER BY total DESC, region;

Use the plan to ask a specific question

For a query you are investigating, start with three checks: which tables or files are scanned, which filters are available at those scans, and where joins or aggregates change the number of rows. Then look for work such as a large sort that may deserve measurement.

A sequential scan is not automatically a bad plan. Analytical queries often need substantial portions of a table, and the operator name alone does not identify a bottleneck. Likewise, a large estimate is a reason to investigate, not proof of wasted work.

For remote data, check where the aggregate runs. For a Parquet date filter, inspect row-group metadata. Both are applications of the same habit: connect each part of the plan to the data it receives and the result it produces.