阅读中文版本

Cost-Based Query Optimization

A cost-based optimizer compares alternative plans by estimated cost, so accurate statistics and selectivity estimates decide whether queries are fast.

Definition

A cost-based optimizer enumerates possible execution plans, estimates the cost of each using statistics about data distribution and available indexes, and chooses the cheapest one.

It governs whether a query uses an index, a sequential scan, or a particular join order, and its choices depend on the estimates it is given. Compared to simpler alternatives, this concept fits best when the trade-off it represents is the one the system actually cares about.

When to use it

A common use case is applying it at the boundary where the cost of getting it wrong is highest, and the scenario usually involves a concrete guarantee the system must uphold. Unlike an ad hoc approach, it makes that guarantee explicit, reviewable, and testable.

How it works

Cost is computed from row estimates, selectivity, and page costs, so a stale or unrepresentative statistic leads to a bad plan even when an index exists.

Correlated columns confuse the planner because it assumes independence, so it can badly underestimate the rows a combined predicate returns.

Trade-offs and alternatives

Diagnose with an explain plan that shows estimated versus actual rows and use that gap to decide whether to refresh statistics, add an index, or restructure the query. The main drawback is the extra complexity it introduces, so the benefit should clearly outweigh that cost before a team adopts it.

Common misconceptions

A persistent misconception is that the optimizer always chooses the objectively fastest plan. Another is that an index is used whenever one exists. For example, an engineer who assumes otherwise may design around a guarantee that does not actually exist, which only becomes obvious under load or failure.

Anti-patterns to avoid

Two recurring anti-patterns are to assuming statistics are current after a bulk load without analysing and to fighting the planner with hints before checking the estimates. Both tend to surface as hard-to-debug production issues rather than obvious test failures. Related concepts include Database Connection Multiplexing, Autovacuum and Dead Tuple Reclamation, LATERAL Joins.