Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 38 ยท Extensions and Performance

Reading Plans: EXPLAIN and Profiling

Read DuckDB query plans with EXPLAIN and EXPLAIN ANALYZE, find the slow operator, use the profiler and JSON output, and fix common problems such as bad joins and scans.

When a query is slow, guessing wastes time. DuckDB will show you what it plans to do (EXPLAIN) and what it actually did, with timings and row counts (EXPLAIN ANALYZE). Learning to read a plan turns "it feels slow" into "this join produced 200 million rows."

The real DuckDB does not run on this site. Plans below are schematic: they show the structure and wording of real plans but are simplified, so your output will have more boxes and exact numbers will differ. Runnable examples use SQLite's own plan output to practice the same reading skill.

EXPLAIN: the planned operators

SQL
EXPLAIN
SELECT c.country, sum(o.total) AS revenue
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.ordered_at >= DATE '2026-01-01'
GROUP BY c.country;

The plan is a tree you read from the bottom up: data flows from the scans at the leaves, through joins and aggregates, to the result at the top. A simplified rendering:

TEXT
HASH_GROUP_BY      groups: country   aggregates: sum(total)
  HASH_JOIN        INNER  customer_id = id
    SEQ_SCAN orders        filters: ordered_at >= 2026-01-01   (estimated ~2M rows)
    SEQ_SCAN customers     (estimated ~50K rows)

Important operator names:

Operator Meaning
SEQ_SCAN / TABLE_SCAN read a table; shows pushed-down filters and projections
READ_PARQUET, READ_CSV file scans, with filter pushdown notes
HASH_JOIN build a hash table on the smaller side, probe with the other
PIECEWISE_MERGE_JOIN, IE_JOIN range and inequality joins
CROSS_PRODUCT every pair of rows: almost always a bug or a mistake
HASH_GROUP_BY, PERFECT_HASH_GROUP_BY, UNGROUPED_AGGREGATE aggregation variants
ORDER_BY, TOP_N full sort or a heap keeping the first N
WINDOW window function evaluation
PROJECTION, FILTER compute expressions or filter rows

EXPLAIN also prints cardinality estimates (~2M rows) from statistics. Big gaps between estimates and reality are the usual root of bad plans.

EXPLAIN ANALYZE: what really happened

SQL
EXPLAIN ANALYZE
SELECT ... same query ...;

This runs the query and annotates each operator with the actual rows produced and time spent (and total time at the top). Typical schematic output:

TEXT
Total Time: 1.84s
HASH_GROUP_BY     rows: 120        time: 0.02s
  HASH_JOIN       rows: 1,950,000  time: 1.31s
    SEQ_SCAN orders      rows: 1,950,000  time: 0.42s
    SEQ_SCAN customers   rows: 50,000     time: 0.01s

Read it like a detective:

  1. Find the operator with the largest time. Here the join.
  2. Compare actual rows with the estimate. A match means statistics are fine.
  3. Ask whether the operator should produce that many rows. A join that outputs 1.9 million rows from two modest inputs may be missing a condition or hitting duplicate keys.
  4. Look for filters that did not get pushed into the scan, and for columns scanned that you never use.
flowchart BT
    A["SEQ_SCAN orders: 1.95M rows, 0.42s"] --> C["HASH_JOIN: 1.95M rows, 1.31s"]
    B["SEQ_SCAN customers: 50K rows, 0.01s"] --> C
    C --> D["HASH_GROUP_BY: 120 rows, 0.02s"]

Plans read from the leaves upward; look for the widest, slowest node.

The profiler

Turn on the profiler to record timings for every query:

SQL
PRAGMA enable_profiling = 'json';
PRAGMA profiling_output = 'profile.json';
-- run queries...
PRAGMA disable_profiling;

json output can be visualized or processed (SELECT * FROM read_json('profile.json')). In the CLI, .timer on prints wall-clock time for each statement, and .explain formatting tweaks help with wide plans. EXPLAIN (FORMAT json) gives machine-readable plans, useful in tests that assert "this query must use a pushed-down filter."

Other helpers:

  • SELECT * FROM duckdb_memory(); and duckdb_temporary_files(); show memory use and spilled data.
  • PRAGMA database_size; summarises storage.
  • SUMMARIZE tbl; gives per-column statistics: min, max, approximate distinct count and null percentage, a quick sanity check before tuning.

Practicing on SQLite plans

The runner here is SQLite, which has a smaller plan language, but the habit is identical: confirm whether a lookup scans or searches.

SQL Scan versus index search (SQLite plan)

Press Run (or Ctrl + Enter) to see the result here.

The plan lists a scan of orders, a primary-key search of customers, and a temporary B-tree for GROUP BY. DuckDB would show hash operators instead, but you read it the same way.

Look at what a rewrite changes:

SQL Same answer, different plan

Press Run (or Ctrl + Enter) to see the result here.

Then run EXPLAIN QUERY PLAN SELECT * FROM t WHERE id = 3; in the same editor: a primary-key search appears instead of a scan.

Common problems and fixes

Symptom in the plan Likely cause Fix
CROSS_PRODUCT or huge join output missing or non-unique join key add the join condition; deduplicate the build side
Filter above the scan, not inside it expression wraps the column (CAST, function) rewrite as a range on the raw column
Scan reads every column SELECT * list the needed columns
ORDER_BY on millions of rows with LIMIT sort not needed in full DuckDB uses TOP_N for ORDER BY ... LIMIT; check you did not wrap it in a subquery that blocks it
Estimates far from actual stale or missing statistics, correlated filters ANALYZE; to refresh, or pre-filter in a CTE
Spilling to disk not enough memory for a huge join or sort raise memory_limit, reduce columns, aggregate earlier
Many small READ_PARQUET files tiny files compact into bigger files

Join order matters: DuckDB builds the hash table on the smaller side. If your estimates are wrong, the optimizer may build on the bigger table. Rewriting with an early aggregation (WITH small AS (SELECT ... GROUP BY ...)) shrinks one side and helps both memory and speed.

A debugging routine

  1. Run EXPLAIN first. If it looks wrong, do not run it yet.
  2. Run EXPLAIN ANALYZE on a sample (WHERE on a date range) to keep it fast.
  3. Find the slowest operator and its row counts.
  4. Change one thing at a time, and compare total time over repeated runs.
  5. Keep the final query plus a note of why in your repository.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In which direction do you read a DuckDB plan?

    Data flows from the scans at the bottom through joins and aggregates to the top.
  2. 2. What is the difference between EXPLAIN and EXPLAIN ANALYZE?

    ANALYZE executes the statement and reports measured cardinalities and time per operator.
  3. 3. A plan contains CROSS_PRODUCT unexpectedly. What is the likely cause?

    Without a usable join condition every row is paired with every other row.
  4. 4. Which command gives per-column min, max, distinct estimate and null percentage?

    SUMMARIZE profiles a table or query result column by column.

Summary

  • EXPLAIN shows the plan; EXPLAIN ANALYZE runs it and reports rows and time per operator.
  • Read plans bottom-up; find the slowest node and compare actual rows with estimates.
  • Use the profiler, .timer, SUMMARIZE and memory tables for deeper inspection.
  • Fix the cause: missing join keys, wrapped columns, SELECT *, stale statistics, tiny files.

Next: The ETL Pipeline Pattern