Query Optimizer
Subject: Computer and Technology noun /ˈkwɪr.i ˈɑːp.təˌmaɪ.zər/ 1 definition
- database-systems
- query-processing
- data-engineering
- performance-optimization
-
Definition (noun)
Entry #238 · UpdatedA Query Optimizer is a core component of a database engine that transforms a parsed declarative query into an efficient physical execution plan. Because declarative languages such as SQL specify what data to retrieve rather than how to compute it, the optimizer evaluates logically equivalent algebraic rewrites, index access paths, and join orderings to minimize resource costs across disk I/O, CPU, memory, and network transfer. It acts as the bridge between query parsing and runtime execution, choosing a fast strategy without altering the semantic result of the query.
Common Types
Rule-Based Optimizer (RBO): Applies a fixed set of heuristic rules, such as predicate pushdown and projection pruning, to rewrite queries deterministically without consulting data statistics.
Cost-Based Optimizer (CBO): Estimates the CPU, I/O, and memory expense of candidate plans using table statistics and histograms, selecting the execution strategy with the lowest predicted cost.
Adaptive Query Optimizer: Monitors live runtime metrics, such as observed intermediate row counts and data skew, to revise join algorithms or re-plan remaining stages mid-execution.
Distributed Query Optimizer: Extends plan selection across clustered compute nodes by modeling partition pruning, data locality, and network shuffle overhead alongside local storage and compute costs.
Example
When a reporting query joins a 50-million-row
orderstable with a 10,000-rowcustomerstable filtered to a single country, a cost-based query optimizer consults catalog statistics to push the country filter below the join and select a hash join over a nested loop join. Using the small filteredcustomersset as the hash build input avoids unnecessary disk reads and drops query execution time from 18 seconds to under 50 milliseconds.