Cost-Based Query Optimizer: How It Chooses a Plan

A cost-based query optimizer is a database component that estimates the computational cost of various execution plans for a SQL query and selects the plan with the lowest estimated cost. It chooses a plan by using table statistics, index information, and cost models to assign a numeric cost to each possible operation (such as sequential scans, index scans, joins, and sorts) and then picks the combination with the smallest total cost.

Formula

The optimizer’s cost model is not a single formula but a set of equations that estimate the cost of each operation. A typical cost formula for a sequential scan is:

Cost_scan = (Number of pages) × (I/O cost per page) + (Number of rows) × (CPU cost per row)

For a join, the optimizer uses a similar formula, but the number of rows considered is the estimated cardinality of the join result, which is derived from the statistics of the input relations and the join selectivity. Each database system has its own constants for I/O and CPU costs, but the principle is the same: the optimizer sums the costs of all operations in a plan to get the total estimated cost.

Worked Example

Suppose a table employees has 10,000 rows stored in 500 pages. The database counts a sequential scan cost as 1 I/O per page plus 0.1 CPU per row. The estimated cost of a full table scan is:

1. I/O cost = 500 pages × 1 = 500
2. CPU cost = 10,000 rows × 0.1 = 1,000
3. Total scan cost = 500 + 1,000 = 1,500

If an index on the department_id column exists and the query filters by that column, the optimizer estimates the number of matching rows based on the index’s statistics. If the selectivity is 2% (200 rows) and the index access cost is 50, the total index scan cost might be 50 + (200 × 0.1) = 70. Since 70 < 1,500, the optimizer chooses the index scan.

Background: Why Cost-Based Optimization Exists

Early database systems used rule-based optimizers that applied a fixed set of heuristics, such as “always use an index if one exists� or “apply the most restrictive filter first.� These rules worked for simple queries but failed when the data distribution was skewed or when the number of rows in a table was very small. A rule might force an index scan on a table with only 10 rows, which is slower than a sequential scan. Cost-based optimization was introduced to make decisions based on actual data statistics rather than rigid rules. The idea was first implemented in research prototypes like System R in the 1970s and has since become the standard in all major commercial and open-source databases.

How the Optimizer Chooses a Plan

The optimizer’s decision process can be broken into several steps:

• Parse and rewrite: The SQL query is parsed into a logical tree, and the optimizer may rewrite it into an equivalent but more efficient form (e.g., pushing predicates down, eliminating redundant joins).
• Generate alternative plans: For each logical operation, the optimizer considers different physical implementations. For example, a join can be executed as a nested loop join, a hash join, or a merge join. Each implementation has different cost characteristics.
• Estimate cardinalities: The optimizer uses statistics (histograms, number of distinct values, null counts) to estimate how many rows will flow through each operation. This is the most critical step because a bad estimate leads to a bad plan.
• Calculate costs: Each physical operator is assigned a cost using the cost model. The total cost of a plan is the sum of the costs of all operators, plus the cost of passing rows between them.
• Choose the minimum: The optimizer compares the total costs of all candidate plans and selects the one with the lowest cost. In practice, the search space is pruned using dynamic programming or heuristic pruning to avoid exploring every possible plan.

Key Factors That Influence the Choice

Several factors affect the cost estimates and therefore the final plan:

• Table statistics: The number of rows, number of pages, average row size, and data distribution. If statistics are stale, the optimizer may make poor decisions.
• Indexes: The presence, type (B-tree, hash, bitmap), and selectivity of indexes. A highly selective index can dramatically reduce the number of rows read.
• Join algorithms: Nested loop joins are good for small inputs, hash joins for large unsorted inputs, and merge joins for sorted data. The optimizer chooses based on the estimated sizes and sort order.
• Memory and disk resources: Some operations, like hash joins, require memory for hash tables. The optimizer considers available memory and may choose a different algorithm if memory is limited.
• Parallelism: Some databases estimate the cost of parallel execution, which can change the optimal plan for large queries.

Common Problems and Misconceptions

One common problem is stale statistics. If a table has grown significantly but the statistics have not been updated, the optimizer may think the table is small and choose a nested loop join when a hash join would be faster. The fix is to update statistics regularly or automatically.

Another misconception is that a lower cost always means a faster query. The cost is an estimate, not a measurement. The optimizer’s cost model may not perfectly reflect actual I/O and CPU times, especially on modern hardware with caching and SSDs. Also, the optimizer cannot predict runtime conditions such as concurrent load. Therefore, a plan with a slightly higher estimated cost might occasionally be faster in practice.

How to Help the Optimizer Choose Well

Database administrators and developers can improve optimizer decisions by:

• Keeping statistics up to date, especially after large data loads or deletes.
• Creating appropriate indexes for the most frequent and selective queries.
• Writing queries that are simple and clear, avoiding unnecessary complexity that might confuse the optimizer.
• Using query hints sparingly, only when the optimizer consistently makes a wrong choice and you have verified the alternative is better.
• Monitoring actual execution plans and comparing them to estimated plans to spot discrepancies.

Frequently Asked Questions

What is the difference between rule-based and cost-based optimization?

A rule-based optimizer uses a fixed set of heuristics to choose a plan, regardless of data size or distribution. A cost-based optimizer uses statistics and a cost model to estimate the actual cost of each plan and picks the one with the lowest estimated cost. Cost-based is generally more flexible and accurate for real-world data.

Why does my query sometimes use a full table scan even though an index exists?

The optimizer may correctly decide that a full table scan is cheaper than using the index. This happens when the query is expected to return a large fraction of the table (e.g., more than 10-20%), so reading the whole table sequentially is faster than jumping through the index and then fetching each row individually. It can also happen if the index is not selective or if statistics are outdated.

How often should I update database statistics?

It depends on how frequently the data changes. For highly volatile tables, you might update statistics daily or even hourly. Many databases have automatic statistics updates that trigger after a certain percentage of rows change. The key is to ensure statistics reflect the current data distribution before running important queries.

What is a query plan and how do I read it?

A query plan is a tree of operations that the database will execute to answer your query. Each node shows an operation (like a scan or join), the estimated number of rows, and the cost. Reading it top-down shows the order of operations. The top node is the final result. You can usually view it with commands like EXPLAIN in PostgreSQL or SET SHOWPLAN_ALL ON in SQL Server.

Can I force the optimizer to use a specific plan?

Yes, most databases provide query hints, such as FORCE INDEX in MySQL or OPTION (HASH JOIN) in SQL Server. However, forcing a plan is a last resort because it bypasses the optimizer’s logic and may become suboptimal if data changes. It’s better to fix the underlying issue, like updating statistics or adding an index.

Why does the same query have different plans at different times?

Because the optimizer’s decisions depend on current statistics, which change as data is inserted, updated, or deleted. Parameter values in the query can also affect the estimate of selectivity. Additionally, some databases use adaptive optimization that learns from past executions. So the plan may change to reflect the current state of the data.

Related

• Evaluate STEAM in Early Childhood Classrooms
• Best Gaming Tables: Top Picks & Buying Guide
• TS Converter Mac: How to Convert TS Files

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0 | 100000
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2