EXPLAIN Plan Cost Model Lab (Interactive)

Feed the optimizer stats, work_mem, and selectivity — watch it pick the wrong plan. Compare estimated vs actual costs for sequential scans, index scans, and sort strategies to see how stale statistics, false selectivity, and small work_mem distort plan choice.

Cost-Based Optimizer: EXPLAIN ANALYZE Workbench

Feed the planner statistics and see when estimates vs reality pick the wrong physical plan.

orders rows1e+7 · 244,141 pages

TRUE selectivity of user_id=428%

work_mem4 MB

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42 AND total > 100 ORDER BY order_date DESC LIMIT 10;

→ Index Scan Backward using idx_order_date (no sort) (cost=131.3.. estimate 800,000 rows)

actual time=250.3..325.4 rows=10 loops=1 · true matching rows=800,000

Sort Method: quicksort Memory

Execution Time: 250.31 ms

Estimates track reality (est 800,000 vs actual 800,000 rows) — the CBO chose the cheapest true plan (250.3 ms).

How It Works Under the Hood

A cost-based optimizer does not run queries — it predicts. Using statistics (row counts, histograms, most-common-values) it sums CPU and I/O estimates for candidate plans and picks the cheapest. When statistics are fresh, estimates track reality; when they are stale or a predicate is wildly selective, the planner can choose a sequential scan plus an external disk sort that runs orders of magnitude slower than the ideal index plan. EXPLAIN ANALYZE exposes the lie by comparing estimated to actual rows per node and reporting spill events.

Core Architectural Principles

The optimizer picks the plan with the lowest estimated cost, which tracks reality only while statistics (ANALYZE) stay fresh.
Selectivity misestimation compounds: a predicate believed to match 0.1% but matching 40% turns an index scan into a random-heap-page disaster.
Sorts and hash joins spill to external disk when inputs exceed work_mem, visible as external-merge steps in EXPLAIN ANALYZE.

Interview Round Script

Diagnose plans like an SRE: compare estimated versus actual row counts per node, treat 10x divergence as a statistics problem, and treat seq scans on big tables as either honest costs or plan-selection bugs. Mention that forcing plan shapes is a diagnostic tool, while fixing ANALYZE freshness, indexes, and work_mem is the real cure.

Key Trade-Offs

Cost models avoid expensive runtime experimentation but inherit the errors of their statistics; the planner optimizes the predicted plan, not the actual one.

Related Curriculum Chapter