The process inside a database management system of selecting an execution plan for a declarative query by transforming equivalent algebraic expressions, estimating costs, and choosing operators, join orders, and access paths that minimize expected resource usage or latency.

Principle

Search the space of semantically equivalent query plans using cost models and statistics, applying algebraic transformations (commutativity, associativity, predicate pushdown, projection) and heuristics to find low-cost plans under constraints.

Demonstration

The optimizer rewrites a SQL query to push filters before joins, chooses an index scan instead of a table scan based on cardinality estimates, and orders joins to materialize smaller intermediate results, reducing I/O and execution time.

Misapplication

Relying on stale or absent statistics produces poor cardinality estimates and bad plans; exhaustive search without pruning can be infeasible; premature materialization or overuse of heuristics can miss better plans.

Consequence

Effective query optimization substantially reduces query latency and resource consumption, enabling complex analytics and OLTP workloads to execute at scale with predictable performance.

Reversal

Without optimization, the DBMS may execute a naive plan (full scans, nested-loop joins on large inputs) that consumes excessive I/O and CPU and yields much higher latency.

Boundary

Applies to declarative queries in DBMS and query engines; excludes manual query rewriting done outside the optimizer and runtime scheduling unrelated to single-query plan selection.

Semantic Tension

Tension exists between rule-based and cost-based optimization, and between exhaustive search and heuristic pruning; additionally there is a trade-off between optimization time and runtime performance.

Synthesis

Query optimization is the cost-guided search and transformation process that maps a declarative query to an efficient execution plan by combining algebraic rewrites, statistics-driven cost models, and pragmatic heuristics.