Execution plan
An execution plan is the tree of operators a database will run, in a specific order, to produce the result of a query — the concrete strategy chosen by the query planner and optimizer.
Overview
Every SQL query is ultimately executed as a sequence of lower-level operations: scan this table, apply this filter, join with that table, aggregate these groups, project these columns. An execution plan lays these operations out as a tree, typically read bottom-up, showing exactly what work the database will do and in what order.
Reading a Plan
A simple filtered join might produce a plan like:
Copy code
PROJECTION
HASH_JOIN (INNER)
SEQ_SCAN (orders) [Filter: order_date >= '2024-01-01']
SEQ_SCAN (customers)
Reading bottom-up: scan orders applying the date filter, scan customers, join the results, then project the requested columns. Real plans are often deeper, with multiple joins, aggregations, and sort steps.
Viewing Plans in DuckDB
DuckDB exposes its execution plan with EXPLAIN, and augments it with actual runtime statistics via EXPLAIN ANALYZE:
Copy code
EXPLAIN ANALYZE
SELECT c.region, sum(o.total)
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= '2024-01-01'
GROUP BY c.region;
EXPLAIN ANALYZE runs the query and shows, per operator, wall-clock time along with the estimated cardinality (EC) versus the actual number of rows produced. A large gap between estimated and actual cardinality on an operator is often the first clue that stale statistics are causing the optimizer to pick a suboptimal join order or algorithm — and is exactly the kind of thing VACUUM ANALYZE can help fix by refreshing those statistics.
Related terms
The query planner is the database component that translates a parsed SQL statement into an execution plan — a tree of operators describing how the query will actually be run.
EXPLAIN clause →The EXPLAIN clause is a powerful diagnostic tool that shows how DuckDB plans to execute your SQL query.
Query optimization →Query optimization is the process by which a database transforms a SQL query into an efficient execution plan, choosing among logically equivalent strategies for the one expected to run fastest.
Cost-based optimizer →A cost-based optimizer chooses among logically equivalent query plans by estimating the execution cost of each one, using statistics about table sizes and data distributions, and picking the cheapest estimate.
analyze →Analyze is an SQL statement used in various database systems, including DuckDB, to gather statistics about tables and columns.
query engine →A query engine parses, optimizes, and executes SQL queries against data sources. Learn its core architecture, how it differs from a database, and see examples like DuckDB and Presto.
FAQS
EXPLAIN shows the planned execution plan without running the query. EXPLAIN ANALYZE actually executes the query and annotates the plan with real timings and row counts per operator.
It usually indicates the optimizer's statistics are stale or the estimate was otherwise inaccurate, which can lead it to choose a worse join order or algorithm than an accurate estimate would.
