Cost-Based vs Rule-Based Query Optimization: What is the Difference?
Learn the difference between cost-based and rule-based query optimizers and why modern databases default to cost-based plans.
Expected Interview Answer
A rule-based optimizer picks an execution plan by applying a fixed priority ranking of access methods regardless of the actual data, while a cost-based optimizer estimates the numeric cost of many candidate plans using real table statistics and picks the cheapest one, which is why virtually every modern relational database uses cost-based optimization.
Rule-based optimization assigns a static rank to each access path — for example, always preferring an index scan over a full table scan — so the same query always produces the same plan no matter how the underlying data looks. Cost-based optimization instead builds a cost model from statistics like row counts, value cardinality, and data distribution, estimates the CPU and I/O cost of several candidate plans, and picks the lowest-cost one, so the same query can get a different plan on a small table versus a huge one. The trade-off is that rule-based systems are predictable but can pick a terrible plan on skewed data, while cost-based systems need accurate, up-to-date statistics or their cost estimates — and therefore their plan choices — become unreliable.
- Cost-based plans adapt to actual data size and distribution
- Rule-based plans are simple and perfectly predictable
- Cost-based optimization avoids full scans when an index genuinely helps
- Understanding both explains why the same query can plan differently over time
AI Mentor Explanation
Think of a rule-based captain who always opens the bowling with the fastest bowler no matter the pitch, versus a cost-based captain who reads today's actual pitch report — grassy or dry — and picks whichever bowler the data says will take wickets fastest today. The rule-based approach is simple and consistent but can misfire on an unusual pitch; the cost-based approach studies today's real conditions, exactly like a cost-based optimizer studying today's real table statistics before choosing a plan.
Step-by-Step Explanation
Step 1
Rule-based: rank access methods statically
A fixed priority list (e.g. unique index > non-unique index > full scan) decides the plan.
Step 2
Rule-based: apply the top-ranked rule
The optimizer picks the highest-priority applicable access method without looking at data volume.
Step 3
Cost-based: gather statistics
Row counts, cardinality, and data distribution are collected for each table and index.
Step 4
Cost-based: estimate and compare candidate plans
Multiple plans are costed using the statistics, and the lowest estimated-cost plan is chosen.
What Interviewer Expects
- Clear articulation of the static-rule vs statistics-driven distinction
- Awareness that nearly all modern engines (PostgreSQL, MySQL, Oracle) default to cost-based
- Understanding of why stale statistics hurt cost-based optimizers specifically
- Ability to explain why rule-based plans are more predictable but less adaptive
Common Mistakes
- Claiming rule-based optimization is still the modern default
- Not mentioning that cost-based optimization depends on accurate statistics
- Confusing rule-based optimization with query rewrite rules or hints
- Failing to give a concrete example of how each approach differs in practice
Best Answer (HR Friendly)
“A rule-based optimizer always follows the same fixed priority list to pick a plan, no matter what the actual data looks like, while a cost-based optimizer measures the real data — row counts, distributions — and estimates the cost of several plans before picking the cheapest one. Almost every modern database uses cost-based optimization because it adapts to the real data, but that also means it needs accurate statistics to make good choices.”
Code Example
-- Refresh statistics so the cost-based optimizer has accurate input
ANALYZE Orders;
-- Same query, different plan depending on data distribution:
-- if 'status' is mostly one value, the optimizer may pick a scan;
-- if the filtered value is rare, it may pick an index instead.
EXPLAIN
SELECT * FROM Orders WHERE status = 'refunded';
-- Cost-based optimizers reconsider the plan when statistics change,
-- which is why running ANALYZE can change the chosen plan entirely.Follow-up Questions
- How often should table statistics be refreshed for a cost-based optimizer?
- What happens to a cost-based plan when statistics are severely out of date?
- Can you force a specific plan with hints, and when is that appropriate?
- How does parameter sniffing relate to cost-based plan selection?
MCQ Practice
1. A rule-based optimizer chooses a plan primarily based on?
Rule-based optimization applies a static, predetermined ranking of access paths regardless of actual data characteristics.
2. What does a cost-based optimizer rely on that a rule-based optimizer does not?
Cost-based optimization estimates plan costs using real statistics, which rule-based optimization ignores entirely.
3. Which optimization approach do most modern relational databases use by default?
Modern engines like PostgreSQL, MySQL, and Oracle default to cost-based optimization because it adapts to actual data.
Flash Cards
What is rule-based optimization? — Choosing a plan via a fixed priority ranking of access methods, ignoring actual data statistics.
What is cost-based optimization? — Estimating the cost of multiple candidate plans using real table statistics and picking the cheapest.
Which is the modern default? — Cost-based optimization, used by virtually all major relational databases today.
Weakness of cost-based optimization? — It depends on accurate, up-to-date statistics; stale statistics lead to poor plan choices.