Cost-based plan selection

A teaching model can add expected sequential page reads, random reads, and CPU operations, each multiplied by a configurable weight. PostgreSQL expresses costs in abstract relative units rather than milliseconds. Those weights encode a rough hardware and workload model; changing one does not make the underlying device faster. Calibration should represent the environment in which plans compete. Cache residency, concurrency, storage latency distributions, and expression complexity can make the same abstract estimate correspond to different measured times on different runs.

Do not sum an entire printed plan's costs as though every node listed independent work. Parent costs generally include their children. Instead, compare complete alternative paths or isolate an operator's incremental assumptions with care. An index scan can avoid reading many rows but incur scattered access; a sequential scan can examine more tuples while moving pages efficiently. The cheaper alternative depends on selectivity, needed columns, visibility checks, order requirements, and the data layout. A named operator alone does not establish which choice is better.

Startup cost measures work before useful output begins. Total cost models consuming the planned output. A blocking sort can have substantial startup work because it must inspect all relevant input before knowing the first globally smallest row. A suitable ordered index can sometimes supply that row earlier even when fetching every row through the index would be more expensive. A LIMIT therefore changes the optimization objective. The exact benefit depends on whether rows can really be stopped early and whether intervening operators must finish their own input first.

A simple interpolation between startup and total cost estimates the price of a requested fraction. It assumes post-startup work is approximately proportional to rows consumed. That can be inaccurate for skewed filters, uneven lookup costs, or late blocking phases. Treat it as a comparison model with stated premises, not a guarantee for every k-row prefix. A count estimate that is too small can also distort the fraction being requested, causing a planner to favor a path whose assumed early-stop advantage never materializes.

A path that is locally more expensive can provide useful ordering to a later merge or ORDER BY and avoid a separate sort. Parameterized paths require values from an outer input. Parallel paths change startup, coordination, and resource use. A planner must retain useful alternatives rather than discard everything except the cheapest unqualified subplan. Feasibility comes first: an operator must support the required predicates and semantics. Memory limits, data-type comparison behavior, and supported join conditions can rule out an apparently attractive algorithm before cost comparison begins.

The queue lab illustrates a resource-capacity assumption rather than optimizer logic. Reset, set both λ=2 and μ=4, predict the initial four-job backlog drains at t=2, then Play or Step. Separately Reset, set both λ=2 and μ=3, predict drainage at t=4, then Play or Step. These separate fluid runs isolate service capacity while holding arrivals fixed. They do not choose SQL plans or convert cost units to seconds; the M/M/1 mean requires separate stochastic assumptions. Identify which measured resource bottleneck a real cost-weight change is supposed to represent.