Is a cost model the same as a benchmark?

No. A model predicts; a benchmark measures a particular run or workload under stated conditions.

Why can the chosen plan be slow?

Possible reasons include wrong size estimates, unsuitable cost assumptions, changing runtime conditions or limited alternatives considered. Diagnose the specific case.

Can I compare scores across database products?

Not as though they use a common unit. Different systems and configurations may define costs differently, so direct numerical comparisons can mislead.

AI Glossary / Data engineering and architecture / Intermediate

Cost Model

Definition

A cost model is a set of estimates and rules used to predict the resources or effort required by an operation. In database query optimization, it helps compare possible execution plans before the database chooses one to run.

What it means

The same query can often be answered in several ways. A database might scan a table, use an index, change the order of joins or choose a different joining method. A cost model assigns estimates to these alternatives using information about the data and execution environment.

“Cost” does not necessarily mean money or elapsed milliseconds. A database may use internal comparison units representing estimated disk access, processor work and other operations. Such estimates help rank alternatives within the model; they should not be read as a cloud invoice or a guaranteed response time.

Outside query planning, the phrase has broader meanings, including financial and machine-learning serving estimates. This entry follows the database meaning in the source list while preserving that distinction.

How it works

The optimizer considers candidate plans. For each operation, it estimates quantities such as input rows, output rows and pages accessed. It combines those estimates with assumptions about the relative expense of operations and compares the resulting plan costs.

Two important ingredients are distinct: estimating how much work will occur, and estimating how expensive each unit of work is. Wrong row-count estimates can therefore produce poor choices even when the per-operation cost assumptions are reasonable.

PostgreSQL illustrates the idea with planner cost constants and plan displays containing startup and total cost. Startup concerns work before returning the first row; total cost concerns the complete output. The precise formulas, units and plan-search strategy vary by database. An optimizer chooses among the alternatives it considers, not necessarily every conceivable execution strategy.

Three practical examples

1. Customer lookup: a planner compares an index lookup with a table scan, considering how many records a filter is expected to select.

2. Analytics join: a system estimates intermediate result sizes to choose a join order, because joining a large unrestricted dataset first may create more work than applying a selective condition earlier.

3. AI retrieval support: a service queries document metadata before searching or generating answers. Engineers inspect whether the plan's assumptions about tenants and approved documents match the real distribution.

Think of it this way

Think of estimating two travel routes using distance, likely traffic and road speed. The estimate helps choose a route, but unusual congestion can make the prediction wrong. A query cost model similarly combines expected quantities with assumed rates. The analogy is limited because database costs may be relative scores rather than predicted minutes, and the model may omit parts of the user's total wait.

Worked example

Consider a deliberately simplified model. A full table scan reads 10,000 pages at one cost unit per page, for a score of 10,000. An indexed alternative has a fixed lookup score of 20 and then reads 100 selected pages at four units each, giving 20 + 400 = 420.

The model prefers the indexed alternative. Now suppose the selectivity estimate is wrong and the lookup actually needs 4,000 such page reads. The same simplified formula gives 20 + 16,000 = 16,020, which exceeds the scan score.

This example separates an estimation error from a calculation error: the arithmetic was correct, but the predicted work was not. The numbers are invented and omit caching, processor work, page reuse and other real considerations. They explain a decision mechanism rather than reproducing a particular database's full formula or predicting measured runtime.

Illustrative case study

Illustrative fictional case: Pine Ledger adds a large new tenant to a reporting service. A query that was previously selective now returns a substantial share of the table for that tenant. Staff initially propose reducing a global cost setting until an index plan appears.

A reviewer first compares estimated and actual row counts and checks the available statistics. The team then tests representative small and large tenants instead of optimizing for one convenient input. It records whether the revised plan improves measured behavior across the workload.

The fictional lesson is diagnostic order: investigate the workload and estimates before treating cost constants as arbitrary speed controls. No universal setting or measured performance improvement is claimed.

Common misconceptions and mistakes

Do not interpret a cost score of 500 as 500 milliseconds or 500 currency units. Do not add every displayed node's total cost without understanding whether parent values already include child work. Do not assume a lower estimated score always produces a lower measured runtime.

Avoid tuning one query at the expense of the wider workload. Distinguish stale or inadequate data statistics from inaccurate hardware assumptions. Finally, verify that the compared plans return the same required results; changing semantics is not a valid performance optimization.

Business use

Use cost models to explain plan selection and guide investigation, then validate important conclusions with appropriate measurements. Record estimated and actual row counts, timing, parameter values and relevant workload conditions.

For a business-facing AI application, connect database improvements to end-to-end experience. Faster metadata lookup may matter greatly in a high-volume service but contribute little when another stage dominates. A separate financial model is needed to estimate spending. Do not convert optimizer scores directly into expected charges or promise that a plan score alone predicts a service-level target.

Check your understanding

A plan is estimated to read 50 rows but actually reads 50,000. Does that discrepancy necessarily show that the processor-cost constant is wrong?

Answer: No. The row-count estimate is already wrong by a factor of 1,000. Investigate that estimate and its consequences before blaming the cost assigned to each row.

Related terms

Execution plan
Query optimiser
cardinality estimation
selectivity
database statistics
EXPLAIN
index scan
sequential scan
performance benchmark

Learn next

Learn to read a simple execution plan and separate estimated cost, estimated rows and measured time. Next, compare a selective query with a broad query on the same dataset. Study statistics and cardinality estimation before changing planner cost settings.

Execution plan

Sources

PostgreSQL, using EXPLAIN, plan estimates and measured execution: https://www.postgresql.org/docs/17/using-explain.html

PostgreSQL, query planning and planner cost constants: https://www.postgresql.org/docs/current/runtime-config-query.html

PostgreSQL, EXPLAIN command and startup versus total cost: https://www.postgresql.org/docs/18/sql-explain.html

The numerical model and fictional case are original simplified illustrations, not PostgreSQL benchmark results.

Previous: Cost efficiency Next: Cost-sensitive machine learning