A query optimizer assigns each candidate plan a numeric cost. What goes into that number, why do engines separate sequential from random page access in the formula, and what does the number actually mean?

## What the cost model is for After enumerating candidate plans, the optimizer needs a single scalar per plan to rank them. That scalar is *cost*. It is produced by a model of the machine — a deliberately simplified one — applied to estimated data volumes. ## The terms Almost every engine's model reduces to a weighted sum of the same categories: **I/O terms.** How many pages must be brought in, split by access pattern: - *Sequential page access* — reading adjacent pages, as a full table scan does. Cheap: the storage layer and OS prefetch ahead, and each page is read once. - *Random page access* — jumping to a page identified by an index entry. Expensive: no useful prefetch, poor locality, and with an unclustered index the same page can be visited many times for different rows. **CPU terms.** - *Per tuple processed* — the base cost of pulling a row through an operator. - *Per operator/predicate evaluation* — comparisons, arithmetic, function calls. Multiplied by rows, so an expensive function in a WHERE clause over millions of rows genuinely shows up here — and note that the model usually assumes a *constant* per-call cost, so it badly under-prices genuinely expensive user-defined functions unless the engine lets you declare their cost. - *Per index entry examined* — walking index tuples during a scan. **Sometimes also:** the cost of sorting or building a hash table (which brings in memory availability and the risk of a spill), the cost of starting up an operator versus producing rows (the *startup versus total cost* split, which is what lets an optimizer prefer a plan with a fast first row when a small row limit is present), and parallel-execution overheads. A concrete shape: a sequential scan of a table with P pages and R rows costs about `P × seq_cost + R × cpu_tuple_cost + R × predicates × cpu_operator_cost`. An index scan that qualifies S rows costs roughly the index descent plus `S × index_entry_cost` plus up to `S` random page fetches for the heap rows — which is why the index path's cost climbs steeply with S while the scan's stays flat. ## Why the sequential/random split matters so much It is the single most consequential asymmetry in the model, because it produces the crossover point where a full scan beats an index. With sequential access cheap and random access expensive, an index that qualifies a small fraction of rows wins easily, but as the qualifying fraction rises, per-row random fetches accumulate until the flat cost of scanning everything is lower. That crossover typically arrives at a surprisingly small fraction of the table — often a few percent for a non-covering index on a heap — precisely because of this weighting. The constants are hardware assumptions baked into defaults. Classic defaults come from spinning disks where a random seek really was ~100× a sequential page read. On SSDs and NVMe the true ratio is much smaller, so leaving the historical default makes the optimizer *under-value* index paths, producing unnecessary full scans. This is one of the few cost-model constants worth deliberately tuning on modern hardware. A related assumption is **caching**. Some models include an assumed fraction of pages already resident (PostgreSQL's `effective_cache_size` influences index-path costing this way; Oracle can use measured system statistics). Without such an input the model would price a fully cached index the same as a cold one. ## What the number does not mean Three things candidates routinely get wrong: 1. **It is not time.** The units are arbitrary. In PostgreSQL, cost 1.0 is *defined* as one sequential page fetch and everything else is expressed relative to it; there is no calibration to your CPU or disk. 2. **It is not comparable across queries.** Cost only ranks alternatives for the same query, with the same estimates and constants. "This query costs 50,000 and that one costs 200" says nothing reliable about which runs longer. 3. **It is not the reason most plans are bad.** The cost formula is a reasonable approximation of a machine. The estimates it is applied to — cardinalities — are where the large errors live. A cost model fed a 1000× cardinality error will confidently pick a disastrous plan; the formula was never the problem. ## Practical implications - When a plan is wrong, check estimated versus actual rows *before* touching cost constants. Estimation errors are far more common and far larger. - When index paths are systematically ignored on flash storage, the random-page cost constant is a legitimate suspect. - Expensive predicates deserve to be declared expensive where the engine supports it, so the optimizer evaluates them after cheaper filters have cut the row count. - Startup versus total cost explains an otherwise puzzling behaviour: adding a small row limit can flip the plan entirely, because the optimizer now optimizes for the first few rows rather than the complete result. ## The one-line summary Cost is a weighted sum of estimated I/O and CPU work, with sequential and random access priced differently because the hardware treats them differently, expressed in an arbitrary unit whose only purpose is to rank plans for a single query.