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.
Overview
For any non-trivial query, there are usually many valid execution strategies — different join orders, different join algorithms, different orders of applying filters. A cost-based optimizer doesn't rely purely on fixed rules to pick among them; it estimates the actual cost (typically in terms of rows processed, I/O, or estimated time) of several candidate plans and selects the one with the lowest estimated cost.
Statistics That Drive Cost Estimates
Cost estimation depends on statistics collected about the underlying data: table row counts, the number of distinct values per column, min/max ranges, and null counts. From these, the optimizer estimates cardinality — how many rows will flow out of each step — which in turn drives decisions like which table should be the build side of a hash join, and in what order to join more than two tables.
Rule-Based vs. Cost-Based
Rule-based optimizations (like pushing a filter below a join) tend to be safe regardless of data, so they're usually applied unconditionally. Cost-based decisions, like join order, genuinely depend on the data's shape — joining a 10-row table against a 10-billion-row table wants a very different plan than joining two similarly sized tables — so they need real statistics to get right.
DuckDB's Cost-Based Optimizations
DuckDB collects and maintains per-column statistics (min/max, distinct counts, null counts) as data is loaded, and its join order optimizer uses these to estimate cardinality and choose an efficient join order and join algorithm. If statistics go stale after large updates or deletes, VACUUM ANALYZE can be used to recompute them. EXPLAIN ANALYZE shows both the optimizer's estimated cardinality (EC) and the actual cardinality per operator, making it easy to spot cases where a cost estimate was significantly off.
Related terms
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.
Cardinality →Cardinality refers to the number of distinct values in a column or dataset, or, in data modeling, the nature of a relationship between two tables (such as one-to-many).
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.
JOIN clause →How do SQL JOINs work? Learn INNER, LEFT, RIGHT, FULL OUTER, CROSS, and ASOF joins with syntax examples — plus how to join directly against CSV and Parquet files.
Query planner →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.
analyze →Analyze is an SQL statement used in various database systems, including DuckDB, to gather statistics about tables and columns.
FAQS
Typically table row counts, distinct-value counts per column, min/max ranges, and null counts — used together to estimate how many rows each step of a plan will produce.
Run EXPLAIN ANALYZE and compare each operator's estimated cardinality (EC) to its actual row count. A large mismatch suggests stale or insufficient statistics.
