TopolearnReading optimized plans tells you when a query is slow because of your SQL and when the engine already fixed it for you.
Before execution, DuckDB's optimizer rewrites the logical plan in a series of passes. The ones that matter most in practice:
- Filter pushdown: WHERE conditions move as low as possible, into joins and into the scan itself, where zone maps or Parquet statistics can skip data. Filters on one side of a join are also copied to the other side when the join key makes them equivalent.
- Projection pushdown: only referenced columns are read.
SELECT *on a wide Parquet file costs real I/O. - Join ordering: a cost-based join-order optimizer uses cardinality estimates (from table statistics and distinct counts) to choose the order of multi-way joins and, for hash joins, puts the smaller input on the build side.
- Top-N:
ORDER BY ... LIMIT nbecomes a TOP_N operator that keeps only n rows in a heap instead of sorting everything. - Statistics propagation: known min/max values flow through expressions, letting DuckDB remove impossible filters, use perfect hash aggregates for small integer ranges and compress intermediate integers.
- Common subexpression and CTE handling: CTEs referenced several times may be materialized once.
EXPLAIN shows the optimized physical plan with estimated cardinalities (~rows). When estimates are far from actual counts in EXPLAIN ANALYZE, the join order may be poor; rewriting the query, adding a filter earlier or materializing an intermediate table can help. SET disabled_optimizers = '...' exists for debugging, not for production.
Example
import duckdb
con = duckdb.connect()
con.sql("CREATE TABLE users AS SELECT i AS id, i % 50 AS country FROM range(100000) r(i)")
con.sql("CREATE TABLE events AS SELECT i % 100000 AS user_id FROM range(1000000) r(i)")
plan = con.sql("""
EXPLAIN SELECT u.country, count(*) AS n
FROM events e JOIN users u ON e.user_id = u.id
WHERE u.country = 3
GROUP BY ALL ORDER BY n DESC LIMIT 5
""").fetchall()[0][1]
print("TOP_N" in plan) # True sort + limit became Top-N
print("Filters: country=3" in plan) # True filter pushed into the users scan
print("HASH_JOIN" in plan) # TrueRemember
- Filters and projections are pushed down into scans automatically
- Join order and build side come from cardinality estimates
- ORDER BY + LIMIT becomes a cheap Top-N operator
- Compare estimated (~rows) with actual counts to spot bad plans
Watch out for
- Hand-optimizing join order in SQL text; DuckDB reorders inner joins anyway, so fix estimates or filters instead.