Skip to content
Tutorials Center
My learning

Topics

Lesson 8 of 38 ยท Concepts and CLI

Reading Query Plans with EXPLAIN

Learn to read DuckDB query plans with EXPLAIN and EXPLAIN ANALYZE, recognize scans, filters, hash joins and aggregates, and use profiling to find slow queries.

When a query is slow, guessing rarely helps. A query plan shows what the engine actually does: which tables it scans, in what order it joins them, and where the time goes. DuckDB gives you two tools. EXPLAIN prints the plan it intends to run, and EXPLAIN ANALYZE runs the query and prints the plan with real numbers.

DuckDB's plan output cannot be produced in the browser here. The plan text below is read-only, shaped like what DuckDB prints, and simplified. The stand-in examples use SQLite's own EXPLAIN QUERY PLAN, which has a different format but teaches the same skill: finding scans and join order.

What a plan is

SQL says what you want, never how to get it. The optimizer turns your query into a tree of operators and picks an efficient arrangement: filters pushed close to the data, joins in a good order, unused columns dropped. The tree is read from the bottom up: data flows from the scans at the leaves toward the final result at the top.

flowchart BT
  A["SEQ_SCAN products"] --> B["FILTER price > 50"]
  B --> C["HASH_GROUP_BY category"]
  C --> D["ORDER_BY"]
  D --> E["Result"]

EXPLAIN

Put EXPLAIN before any query (read-only example):

SQL
-- DuckDB (read-only example)
EXPLAIN
SELECT category_id, count(*) AS n, avg(price) AS avg_price
FROM products
WHERE price > 50
GROUP BY category_id;

DuckDB prints boxes joined by lines, roughly like this (simplified):

TEXT
HASH_GROUP_BY
  Groups: category_id
  Aggregates: count_star(), avg(price)
        |
PROJECTION
        |
SEQ_SCAN products
  Projections: category_id, price
  Filters: price>50.0

Read it bottom-up. SEQ_SCAN reads the table and already applies the filter price>50 and only needs two columns (projection pushdown and filter pushdown). Then HASH_GROUP_BY builds the groups. You wrote the WHERE before the GROUP BY; the engine put the filter right in the scan, where it is cheapest.

SQLite has a different but related display. Look at how it reports the same idea:

SQL Stand-in: EXPLAIN QUERY PLAN ยท database: shop

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

Expect a scan of products plus a temporary structure for GROUP BY. Compare with a lookup by primary key:

SQL Stand-in: a primary-key lookup ยท database: shop

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

This one uses the primary-key index to jump straight to the row, a "SEARCH" instead of a "SCAN".

Operators you will meet

Operator What it does
SEQ_SCAN / TABLE_SCAN Reads a table (or a file: READ_PARQUET, READ_CSV)
FILTER Drops rows that fail a condition
PROJECTION Computes or selects output columns
HASH_JOIN Joins two inputs using a hash table on the join key
HASH_GROUP_BY / PERFECT_HASH_GROUP_BY Groups and aggregates
ORDER_BY, TOP_N Sorts; TOP_N is the efficient form of ORDER BY ... LIMIT
WINDOW Computes window functions
CROSS_PRODUCT Every row with every row: usually a sign of a missing join condition
PIECEWISE_MERGE_JOIN, RANGE_JOIN Joins on inequalities and ranges

A CROSS_PRODUCT in a plan you did not expect is a classic bug: a forgotten ON clause. Watch the estimated row counts, shown as ~N rows or EC: N, and compare them to what you know.

EXPLAIN ANALYZE: the real numbers

EXPLAIN ANALYZE runs the query, discards the output rows, and prints the plan with actual row counts and time per operator:

SQL
-- DuckDB (read-only example)
EXPLAIN ANALYZE
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 '2025-01-01'
GROUP BY c.country;

For each operator you get the rows it produced, the time it took, and the share of total time. Look for:

  • The slowest operator. Spend your effort where the time is.
  • Row counts that shrink late. If a filter that could run first only reduces rows near the top, check why it was not pushed down.
  • A big surprise. An estimate of 1,000 rows against an actual of 40 million means the optimizer chose a plan on bad information.
  • A scan that reads many more columns than needed. Replace SELECT * with column names.

Profiling without EXPLAIN

You can also turn profiling on for the session so every query records its plan with timings:

SQL
-- DuckDB (read-only example)
PRAGMA enable_profiling = 'json';
PRAGMA profiling_output = 'profile.json';
SELECT ... ;
PRAGMA disable_profiling;

In the shell, .timer on is a lighter way to compare two versions of a query by total time.

Fixing slow queries: a checklist

  1. Name only the columns you need.
  2. Filter as early as possible; use the same column for filtering that your data is sorted by (zone maps, see Columnar storage).
  3. Check that every join has a proper ON condition on the right keys.
  4. Pre-aggregate large tables before joining them to small ones.
  5. Convert slow-to-parse CSV into a table or Parquet once.
  6. Look at threads and memory_limit if you suspect resource limits.
  7. Only then consider rewriting the query's structure.

DuckDB rarely needs manual index hints or tuning. Its optimizer handles join order, filter pushdown and common subexpression elimination for you, so the plan is mostly useful as a confirmation tool.

Indexes in DuckDB

Unlike transactional databases, DuckDB does not need many indexes. Zone maps already skip data, and scans are fast. DuckDB automatically creates an ART (adaptive radix tree) index for PRIMARY KEY and UNIQUE constraints, which helps point lookups and constraint checks. You can add one with CREATE INDEX, but it rarely speeds up analytical scans and it adds cost to bulk loads, so do not add indexes "just in case".

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In which direction do you read a query plan tree?

    Data flows from the leaf scans up through filters, joins and aggregates to the final output.
  2. 2. How does EXPLAIN ANALYZE differ from EXPLAIN?

    EXPLAIN shows the planned operators; EXPLAIN ANALYZE executes them and reports real numbers.
  3. 3. What does an unexpected CROSS_PRODUCT in a plan usually mean?

    A cross product pairs every row with every row and is normally caused by a missing or wrong join condition.
  4. 4. Why does DuckDB need fewer indexes than a transactional database?

    Analytical queries read big ranges, which scans with min-max skipping handle well, so indexes are seldom needed.

Summary

  • EXPLAIN shows the planned operators; EXPLAIN ANALYZE runs the query and adds real rows and times.
  • Read plans bottom-up; look for the slowest operator, bad row estimates and cross products.
  • Filter and projection pushdown mean the scan only reads needed columns and rows.
  • Rely on column names, early filters and sorted loads before reaching for indexes.
  • ART indexes exist for primary keys and unique constraints but rarely help analytics.

Next: Friendly SQL