Optimisation for Database Management Systemss
Query optimisation is the process by which a DBMS turns a declarative SQL query into an efficient execution plan. It rewrites logical plans, estimates the cost of alternatives using statistics such as cardinalities and histograms, and searches for good join orders and access paths. Poor estimates are the most common reason an optimiser picks a bad plan.
Stage: Query Processing · Part of the Database Management Systems roadmap · Last reviewed 19 November 2026
What to learn in Optimisation
- Read an EXPLAIN output and identify scans, joins and estimated rows
- Apply rewrites such as predicate pushdown and projection pushdown
- Estimate selectivity of a predicate using histograms
- Explain dynamic programming for choosing join order
- Diagnose a bad plan caused by stale statistics
- Compare rule-based and cost-based optimisation
Optimisation interview questions
Why does join order matter so much?
Joining tables in a different order can change intermediate result sizes by orders of magnitude. A good order filters early so later joins handle few rows. The number of possible orders grows factorially, so optimisers use dynamic programming or heuristics to search it.
What causes the optimiser to choose a bad plan?
Usually inaccurate cardinality estimates, from stale statistics, correlated columns or skewed data, which the independence assumption mishandles. Fixes include refreshing statistics with ANALYZE, adding extended statistics, rewriting the query or adding an index.
Where Optimisation fits in the Database Management Systems roadmap
Optimisation belongs to the Query Processing stage (advanced level). Execute and optimise.
Other units in this stage
Back to the full Database Management Systems roadmap