DuckDB EXPLAIN ANALYZE: Measure the Rows Behind a Slow Query

The final result is one row. The join produced 16,000. That is not necessarily a problem—aggregates are meant to reduce rows—but here it exposes a mistake: every eligible order was counted twice.

EXPLAIN ANALYZE lets us follow the rows actually produced during execution. We will use that evidence to investigate a query, repair its meaning, and measure it again.

The business question comes first

We want the number of paid orders whose region has at least one promotional campaign. An order qualifies once, however many campaigns its region has.

The fixture contains 10,000 orders, of which 8,000 are paid. Each of the four regions has two campaigns. The expected count is therefore 8,000.

If you are new to plan operators, first read the short EXPLAIN tutorial. It explains scans, filters, and the difference between an estimate and an observation.

Execute the query with measurement

EXPLAIN ANALYZE executes the query and reports its plan with runtime information. Run this SELECT-only example and inspect its actual row counts:

EXPLAIN ANALYZE
SELECT count(*) AS qualifying_orders
FROM orders o JOIN promotions p ON o.region = p.region
WHERE o.status = 'paid';

Recorded query total time: 0.566 ms

UNGROUPED_AGGREGATE
  Aggregates: count_star()
  Estimated Cardinality: 1
  Actual output rows: 1
  Operator time: 0.001 ms

HASH_JOIN
  Join Type: INNER
  Conditions: region = region
  Estimated Cardinality: 10000
  Actual output rows: 16,000
  Operator time: 0.119 ms

TABLE_SCAN
  Table: memory.main.orders
  Type: Sequential Scan
  Filters: (status = 'paid')
  Estimated Cardinality: 5000
  Actual output rows: 8,000
  Operator time: 0.132 ms

TABLE_SCAN
  Table: memory.main.promotions
  Type: Sequential Scan
  Estimated Cardinality: 8
  Actual output rows: 8
  Operator time: 0.002 ms

The excerpt preserves selected operators and timings from one native DuckDB 2.0.0 run. Your browser’s values will differ. The full captured JSON profiles are available for inspection.

Because the statement runs the query, its data access and computation really happen. On your own workload, choose a representative query you are ready to execute; do not treat ANALYZE as a free preview.

Follow the actual row counts

The orders scan outputs 8,000 paid rows. The promotions scan outputs eight campaign rows. The inner join outputs 16,000 matching pairs because each order’s region matches two campaigns. The final aggregate reduces those pairs to one count.

These are operator output counts, not interchangeable measures of stored rows, rows inspected, or network bytes. The full profile separately records scan details. A scan that returns 8,000 filtered rows may have examined more input rows to find them.

The captured join estimate is 10,000, but its actual output is 16,000. Comparing estimates with observations can identify where the planner’s expectations diverge. Here, inspecting the join’s meaning reveals the more urgent issue: we are counting order–campaign pairs.

See the query’s ordinary result:

SELECT count(*) AS qualifying_orders
FROM orders o JOIN promotions p ON o.region = p.region
WHERE o.status = 'paid';

The single output row conceals the multiplication that produced its value. The measured plan makes that expansion visible.

Express eligibility instead of returning every match

We only need to know whether a campaign exists for the order’s region. An EXISTS condition expresses that question:

SELECT count(*) AS qualifying_orders
FROM orders o
WHERE o.status = 'paid'
  AND EXISTS (SELECT 1 FROM promotions p WHERE p.region = o.region);

Now the count is 8,000. Profile this version too:

EXPLAIN ANALYZE
SELECT count(*) AS qualifying_orders
FROM orders o
WHERE o.status = 'paid'
  AND EXISTS (SELECT 1 FROM promotions p WHERE p.region = o.region);

Recorded query total time: 0.496 ms

UNGROUPED_AGGREGATE
  Aggregates: count_star()
  Estimated Cardinality: 1
  Actual output rows: 1
  Operator time: 0.001 ms

HASH_JOIN
  Join Type: SEMI
  Conditions: region = region
  Estimated Cardinality: 1000
  Actual output rows: 8,000
  Operator time: 0.062 ms

TABLE_SCAN
  Table: memory.main.orders
  Type: Sequential Scan
  Filters: (status = 'paid')
  Estimated Cardinality: 5000
  Actual output rows: 8,000
  Operator time: 0.130 ms

TABLE_SCAN
  Table: memory.main.promotions
  Type: Sequential Scan
  Estimated Cardinality: 8
  Actual output rows: 8
  Operator time: 0.001 ms

In the captured build, DuckDB uses a semi join. It outputs each qualifying order once, leaving 8,000 rows for the aggregate. The exact plan may evolve, but the result and the intended one-count-per-order rule remain the checks that matter.

The rewrite deliberately changes the wrong answer. This is a correctness repair, not an equivalent-query speed comparison. The join multiplication tutorial explores the same mistake on five visible item rows.

Interpret timings with care

Operator timings help locate expensive work, but adding them is not a reliable way to reconstruct total elapsed time. Parallel execution and the way operator work is recorded matter. Use the reported query total as its own measure, and inspect the execution environment before interpreting a timing difference.

This tiny fixture is useful for row counts, not a performance benchmark. One cached run may differ from a cold run; profiling and browser startup have costs of their own. For a real optimization, keep input data and environment comparable, verify equivalent results, and repeat representative measurements. Record the engine and extension versions.

SELECT o.region, count(*) AS qualifying_orders
FROM orders o
WHERE o.status = 'paid'
  AND EXISTS (SELECT 1 FROM promotions p WHERE p.region = o.region)
GROUP BY o.region ORDER BY o.region;

When a query is slow, work outward from the evidence: an unexpectedly expanding join, a large intermediate sort, a scan with few useful filters, or work happening on the wrong side of a remote connection. Use Parquet metadata or remote aggregate diagnostics when those are the relevant next questions.