Indexes & Query Performance
Cost-Based Query Planning
A cost-based optimizer enumerates semantically valid physical alternatives, estimates intermediate cardinalities and resource work, and chooses the lowest modeled cost. Selinger’s System R work established dynamic-programming join-order search and “interesting orders”; modern engines extend and prune this space.
- Optimization maps logical algebra to physical operators.
- Selinger search builds plans by relation subsets.
- Cardinality drives cost.
- Cost units are model units, not elapsed time.
- Search is deliberately bounded.
- Parameters can cross plan regimes.
| Alternative | Looks attractive when | Can lose when |
|---|---|---|
| Index orders → nested-loop items | Few orders; indexed item lookup | Popular customer returns many orders |
| Hash items, scan orders | Large unsorted equijoin; hash fits | Build spills or input is tiny |
| Ordered scans → merge join | Inputs already ordered / order reused | Sorting both inputs dominates |
| Scan + filter | Large fraction needed; sequential locality | Predicate is rare and covering index exists |
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT o.order_id, sum(i.quantity * i.unit_price)
FROM orders o JOIN order_items i USING (order_id)
WHERE o.customer_id = 7 AND o.placed_at >= :from_time
GROUP BY o.order_id;
-- Repeat for representative small, median, and hot customers; do not infer from one literal.An estimated cost of 1200 is not 1.2 seconds. It is the optimizer’s weighted prediction under its configuration. Actual elapsed time also includes cache state, contention, parallel scheduling, client effects, storage, and estimation error.