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.
Overview
The same SQL query can be executed in many different ways that all produce the same result — different join orders, different join algorithms, different orders of applying filters — but with very different performance. Query optimization is the process of exploring those equivalent strategies and picking a good (not necessarily perfect) one, without requiring the person writing the SQL to think about execution details at all.
Rule-Based vs. Cost-Based Optimization
Rule-based optimization applies transformations known to always (or almost always) help, like pushing filters down before joins, regardless of the actual data. Cost-based optimization goes further, using statistics about table sizes and value distributions to estimate the cost of different plan alternatives — such as which table should be on which side of a join — and picking the cheapest estimated option. Most production databases combine both: applying safe rule-based rewrites first, then using cost estimates to choose among the remaining options.
Common Optimizations
Typical optimizations include predicate pushdown (filtering as early as possible), projection pushdown (reading only needed columns), join reordering, common subexpression elimination, and choosing between join algorithms like hash join, merge join, and nested loop join based on estimated sizes.
DuckDB's Optimizer
DuckDB runs a query through a pipeline of logical optimizations — including filter and projection pushdown, join order optimization driven by cardinality estimates, and constant folding — before generating a physical, vectorized execution plan. You can inspect the result of this pipeline with EXPLAIN, and see actual runtime behavior with EXPLAIN ANALYZE, which is often the fastest way to understand why a particular query is slow.
Related terms
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.
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.
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.
Predicate pushdown →Predicate pushdown is a query optimization that moves filter conditions as close as possible to the data source, so fewer rows (or bytes) are read before filtering happens.
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.
NATURAL JOIN →A NATURAL JOIN automatically joins two tables on all columns that share the same name, without an explicit ON or USING clause.
FAQS
Rule-based optimization applies transformations known to generally help, independent of the data. Cost-based optimization uses statistics about the actual data to estimate and compare the cost of alternative plans, such as different join orders.
Run EXPLAIN before your query to see the optimized logical/physical plan, or EXPLAIN ANALYZE to also see actual runtime and row counts per operator.
