Core Code
Core Code Modern Dev Handbook
DATABASE-ADVANCED Contents
Storage & Index Internals
DB Page Layouts B-Tree Internals Hash, GIN, and GiST Indexes Selectivity & Cardinality Index Maintenance & Bloat Covering & Partial Indexes WAL & Write Amplification MVCC and HOT Updates
Query Planner & Execution
Query Lifecycle Cost-Based Optimizer Basics Reading EXPLAIN ANALYZE Join Strategies Index Scan vs Seq Scan Statistics & Misestimation Plan Regressions Query Optimization Checklist
Transactions & Isolation
ACID Realities Isolation Levels Deep Dive Phantoms, Lost Updates, and Anomalies Deadlocks: Detection and Retries Locking Mechanisms Long-Running Transactions Optimistic vs Pessimistic Concurrency Transaction Safety Checklist
Replication & High Availability
Replication Types Postgres Replication Model MySQL Replication Model Replica Lag and Read-After-Write Failover Strategies Backup Strategies Point-in-Time Recovery HA Architecture Patterns
Sharding & Horizontal Scale
Vertical vs Horizontal Scaling Sharding Patterns Shard Key Design Cross-Shard Queries Rebalancing and Resharding Multi-Region Data Architectures Global Consistency Tradeoffs Sharding Operational Checklist
Postgres vs MySQL Tradeoffs
Storage Engine Differences JSON Support Comparison Indexing Capability Differences Replication Differences Ecosystem and Extensions Interpreting Benchmarks Choosing Between Postgres and MySQL Migration Surface Area
Caching & Redis Strategies
Caching Patterns Redis Data Structures in Production Cache Invalidation Cache Stampede and Thundering Herd TTL Design Strategies Read-Heavy Optimization Cache Consistency Models Cache Observability Checklist
Data Modeling at Scale
Normalization vs Denormalization Soft Delete Patterns Event-Driven Modeling Time-Series Modeling Multi-Tenant Schema Design Audit Trail Design Schema Migration Strategies Data Modeling Checklist

Cost-Based Optimizer Basics

Cost model inputs, assumptions, and how planners choose “cheapest” plans.
On this page

Cost-Based Optimization: The Planner Is an Economist

The planner does not execute queries to see which one is faster. It estimates cost and chooses the lowest-cost plan. This is called cost-based optimization.

Important: cost is not time. It is a unitless internal score derived from assumptions about IO, CPU, and row counts. If the assumptions are wrong, the chosen plan can be catastrophically wrong.

What Goes Into the Cost Model

  • Row count estimates (from statistics)
  • IO cost (sequential vs random page reads)
  • CPU cost (per-row processing)
  • Memory usage (hash tables, sorts)
  • Parallel overhead (if enabled)

The cost model balances these factors to approximate total work.

Sequential vs Random IO Cost

Sequential reads are cheaper than random reads. Cost models reflect this by assigning a higher penalty to random page access (like index lookups that jump across the table).

This explains a common confusion: why does the planner choose a sequential scan even when an index exists?

If the predicate is low selectivity, a sequential scan touching each page once can be cheaper than thousands of random page reads via index.

Row Estimates Drive Everything

Row count estimation is the multiplier in the cost equation. If the planner thinks a predicate returns 10 rows instead of 1,000,000, it may choose nested loops instead of hash joins.

EXPLAIN (ANALYZE)
SELECT *
FROM orders
WHERE status = 'PAID';

If estimated rows differ greatly from actual rows, the cost model decision is already compromised.

Join Enumeration: Searching the Plan Space

For multi-table queries, the planner must choose:

  • Join order
  • Join strategy per pair

The number of possible join orders grows factorially. Planners use heuristics and pruning strategies to avoid exploring every possibility.

Bad cardinality estimates early in the tree propagate and distort the rest of the plan.

Join Strategy Cost Comparison

Each join type has a cost formula:

  • Nested Loop: outer_rows × inner_cost
  • Hash Join: build_cost + probe_cost
  • Merge Join: sort_cost + merge_cost

The planner compares these numbers using estimates. If the outer_rows estimate is too small, nested loop may appear cheaper than it really is.

Parameter Sensitivity (Plan Stability Problem)

Queries with parameters can behave very differently depending on the value:

SELECT *
FROM users
WHERE country = ?;

If country = 'US' returns millions of rows but country = 'IS' returns hundreds, the optimal plan differs. A single cached plan may not fit all parameter values.

This is a common source of production regressions after deployments.

Parallelism and Cost Thresholds

Planners enable parallel execution only if estimated cost crosses certain thresholds. If row estimates are low, parallelism may not activate even when beneficial. If overestimated, parallelism may increase overhead.

Parallel workers reduce execution time only when coordination overhead is lower than workload size.

Cost Is Relative, Not Absolute

Costs cannot be compared across systems or even across different databases. They are relative inside one instance. A plan with cost 1000 vs 2000 means the planner believes the first is half as expensive.

Always validate with actual timing.

Diagnosing Why a Plan Was Chosen

To understand planner decisions:

  • Compare estimated vs actual rows.
  • Look at node-level cost estimates.
  • Examine join order.
  • Consider selectivity of predicates.
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.email, o.total_amount
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country = 'TR'
AND o.created_at >= '2026-01-01';

Look for row estimate mismatches and large loop multipliers.

Failure Modes in Production

  • Catastrophic nested loop: outer row estimate too small.
  • Hash spill: underestimated rows overflow memory.
  • Parallel misfire: overhead outweighs benefit.
  • Seq scan surprise: selectivity misestimated.
  • Parameter-sensitive regression: one value ruins cached plan.
  • Upgrade-induced change: new planner heuristics shift cost balance.

Operational Checklist

  • Always compare estimated vs actual rows in EXPLAIN ANALYZE.
  • Investigate large row estimate discrepancies first.
  • Test queries with multiple representative parameter values.
  • After major data changes, refresh statistics.
  • Benchmark join-heavy queries under realistic row counts.
  • Monitor temp file usage and hash/sort spill indicators.
  • Validate critical query plans after database upgrades.
  • Avoid relying on hints as first solution; fix estimation first.
  • Track p95/p99 latency to detect plan instability.
  • Document known parameter-sensitive queries and mitigation strategy.

Summary

The cost-based optimizer is a decision engine driven by statistics and heuristics. It chooses what appears cheapest. When statistics are wrong or workload assumptions shift, cost calculations mislead the planner. Production query engineering is about making the planner's model align with reality.

Reading EXPLAIN ANALYZE →