A query optimiser is the component of a database management system that transforms a declarative query into an efficient physical execution plan. It enumerates candidate plans, estimates their cost using statistics about data distribution and access paths, and selects the plan expected to minimise resource usage. Cost-based optimisers rely on cardinality estimation and index awareness, while rule-based optimisers apply heuristic transformations.
Overview
- The optimiser sits between the parser and the execution engine, turning a logical query tree into a chosen physical plan.
- Cost-based optimisation depends on accurate statistics; stale or missing statistics lead to poor plans and unpredictable performance.
- Join ordering, access-path selection, and predicate pushdown are among the most impactful decisions an optimiser makes.
Mechanisms
- Plan enumeration generates alternative logical and physical plans for a query.
- Cardinality estimation predicts how many rows each operator will produce.
- Cost modelling assigns estimated CPU, I/O, and memory costs to candidate plans.
- Index selection and join-method choice (nested loop, hash, merge) determine runtime behaviour.
Applications
- Accelerating analytical and transactional workloads in relational databases such as PostgreSQL.
- Powering query planning in distributed and columnar query engines.
- Informing database administrators where indexes or rewritten queries are needed.