How does DuckDB's cost-based optimizer decide on a query plan?

DuckDB's optimizer uses statistics about the data, row counts, distinct value counts, min/max ranges, collected for tables and maintained (or estimated) for external files like Parquet, to estimate the relative cost of different possible ways to execute a given query, then chooses the plan it estimates will be cheapest.

Concretely, this shows up in decisions like join ordering (starting with the smallest or most selectively-filtered tables first to minimize the size of intermediate results carried through subsequent joins), join algorithm selection (choosing a hash join versus other strategies based on estimated table sizes), and filter placement (pushing filters as early in the plan as possible, ideally all the way down to the storage-read step, so later operators process as little unnecessary data as possible).

Because these decisions depend on the accuracy of the underlying statistics, a plan can end up genuinely suboptimal when statistics are stale, missing, or hard to estimate accurately, for instance, for external files DuckDB hasn't fully scanned yet, or for complex expressions whose result cardinality is difficult to estimate in advance. This is part of why EXPLAIN ANALYZE's comparison of actual versus estimated row counts is such a useful troubleshooting signal: a large mismatch between the two is a direct indicator that the optimizer's cost estimates, and therefore its chosen plan, may be off.