Skip to main content
Definition Website Computer and Technology

Query Optimizer

Subject: Computer and Technology noun /ˈkwɪr.i ˈɑːp.təˌmaɪ.zər/ 1 definition

  1. Definition (noun)

    Entry #238 · Updated

    A 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 orders table with a 10,000-row customers table 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 filtered customers set as the hash build input avoids unnecessary disk reads and drops query execution time from 18 seconds to under 50 milliseconds.