Skip to main content

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

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.

Back to the full Database Management Systems roadmap