Recommended Free Tools
A cost-based query optimizer estimates the resource use of alternative ways to execute a query, then chooses the plan with the lowest estimated cost among the plans it considers. It relies on estimates of how many rows operations will process and on a database-specific cost model. That internal cost is useful for comparing plans; it is not a promise of elapsed time.
What a cost-based query optimizer does
SQL describes the result a user wants, not a mandatory sequence of physical steps for retrieving it. The optimizer translates that request into an execution plan: a set and order of operations the database can carry out. It compares possible strategies and selects one based on estimated cost. Microsoft describes SQL Server’s optimizer as cost-based, meaning it selects plans with the lowest estimated processing cost (Microsoft Learn: Query processing architecture guide).
As an Amazon Associate I earn from qualifying purchases.
The choice can involve different ways to access data, join tables, order joins, or transform parts of a query. A plan might, for example, use an index rather than scan a table, or choose one join method and order instead of another. Which alternatives are available depends on the database and query.
How the optimizer estimates and compares plans
- Read the query and schema. The optimizer identifies the requested operations, relevant tables, columns, predicates, and relationships.
- Generate candidate plans. It considers available access paths, join methods, join orders, and applicable query transformations.
- Estimate data flow. For each operation, it estimates cardinality—the number of rows produced. Selectivity is the estimated fraction of rows a predicate retains. Statistics about tables and columns, including histograms, help inform these estimates.
- Estimate resource use. A database-specific cost model estimates the work associated with candidate operations. Oracle describes inputs that can include estimated I/O, CPU, memory, cardinality, input sizes, data distribution, and access structures (Oracle Database 26: Query optimizer concepts).
- Choose among evaluated candidates. The optimizer selects the plan with the lowest estimated cost among the alternatives it evaluated.
These stages are connected: a row-count estimate affects downstream work estimates. If the optimizer expects few rows, repeated index lookups in a nested-loops join may appear economical. If it expects many rows, a different join approach or access path may be estimated to require less work.
#1 Best Overall
Why statistics and cardinality estimates matter
Statistics help the optimizer estimate how data is distributed and how many rows predicates will match. SQL Server documentation says its cardinality estimates are derived primarily from histograms created when indexes or statistics are created, and recommends having relevant automatic or manual statistics available (Microsoft Learn: Statistics; Microsoft Learn: Cardinality estimation).
An inaccurate estimate can change the optimizer’s assessment of later operations, such as the cost of repeated lookups or sorting. This is one reason a plan may perform poorly even when the optimizer is functioning as designed: it made a choice based on estimates that did not match the data encountered during execution.
What “lowest cost” does—and does not—mean
“Lowest cost” means lowest according to the optimizer’s model and among the plans it considered. It does not necessarily mean the globally optimal plan across every mathematically possible strategy. Searching every combination can be impractical; PostgreSQL documents using its genetic query optimizer above a join-count threshold to find a reasonable plan in a reasonable amount of time (PostgreSQL 17: Planner/Optimizer).
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Nor is an optimizer cost a direct measure of elapsed seconds. Oracle describes cost as an internal estimate of resource use, specific to the query and optimizer environment. A plan with lower estimated cost can run faster, slower, or in about the same time as another plan. Costs are therefore not meaningful as a direct comparison across different queries or optimizer modes (Oracle Database 18: Query optimizer concepts).
Rank #3
How to use estimated cost when investigating a slow query
An explain plan shows the optimizer’s proposed operations and estimates; it is not, by itself, a runtime measurement. When a query is unexpectedly slow, compare estimated row counts with actual row counts in the database’s plan or runtime diagnostics, where available. Large differences can point toward a cardinality-estimation issue and help focus investigation on relevant statistics, data distribution, or predicates. The specific commands and diagnostic displays vary by database product and version.
Cost-based optimization is a way of ranking execution alternatives, not a guarantee that every selected plan will be fast. To understand a particular plan, examine its estimates, operations, and runtime evidence in the context of the database implementation being used.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
