🎯Cost-based optimizer

Statistics drive the search

The CBO explores alternative join orders, access paths, and parallel shapes. “Cost” is an abstract score built from histograms, distinct counts, and correlation guesses, excellent until the data distribution changes or your predicates hide sargability behind functions.

SQL

Update statistics (PostgreSQL)

ANALYZE orders;
ANALYZE order_items;

✓Illustrative result set

status
ANALYZE complete

SQL

Hypothetical plan choice

-- Plan A: hash join: total cost 1_200_000
-- Plan B: merge join on pre-sorted inputs: total cost 890_000
-- → optimizer picks Plan B when estimates favor sorted inputs

✓Illustrative result set

picked_plan
Merge Join

Blaming the optimizer first

Non-sargable expressions on indexed columns while expecting magic index use.
Assuming uniform keys when a few power users dominate cardinality.
Ignoring parameter sniffing and plan instability on skewed workloads.

Fix the model and the stats

Refresh statistics after large loads; automate ANALYZE where appropriate.
Treat bad plans like regressions: capture before/after in CI or runbooks for critical queries.
Use hints or plan freezing only after measuring alternatives and scheduling an escape hatch.

🧮Selectivity: The Probability of a Match

The DB doesn't count rows; it calculates probabilities.

Selectivity Math

Selectivity is a fraction from 0.0 to 1.0. For a filter like WHERE status = 'active', if 10% of rows match, selectivity is 0.1. The optimizer multiplies this by the Total Rows to get the Cardinality. If selectivity is low (selective), the engine chooses an Index; if high (non-selective), it chooses a Sequential Scan.

Rows Expected = Total Rows * (Selectivity of Predicate A) * (Selectivity of Predicate B)

Equi-depth Histograms

The DB divides the data range into "buckets" with an equal number of rows, not an equal interval. This allows capturing data skew (e.g., many values between 0-10, few between 10-1000) accurately.

Most Common Values (MCV)

The optimizer keeps a list of the top X most frequent values (and counts) for every column. This prevents "Uniformity" bugs for hot keys.

📐The Cost Model

How PostgreSQL (and others) score a plan.

Constant	Default Value	Mechanical Meaning
seq_page_cost	1.0	Baseline cost to read one 8KB page sequentially.
random_page_cost	4.0	Higher cost for random seeks (magnetic disks). Lower for SSDs.
cpu_tuple_cost	0.01	The overhead of the CPU processing one row.
cpu_index_tuple_cost	0.005	The cost to process one row inside an index scan.

Monitoring

Track predicates that drive the most total time (frequency × latency), not only the slowest single execution, the optimizer optimizes what it measures from stats you keep fresh.

TL;DR

A cost-based optimizer (CBO) picks join order, access paths (index seek vs scan), and physical operators by estimating rows and CPU/I/O using statistics (histograms, distinct counts), not by reading your intent. Stale stats or wrong cardinality guesses produce slow plans; refresh stats after large loads and compare EXPLAIN vs EXPLAIN ANALYZE when estimates diverge wildly from reality.