Recommended Free Tools
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
A cost-based query optimizer compares possible execution plans for a database query, estimates the resources each plan will use, then selects the plan with the lowest estimated cost among those it considered. It uses information such as the query, schema, data statistics, and available access paths. That cost is an internal estimate—not a promise of how many seconds the query will take.
What a cost-based query optimizer does
SQL describes the result a query requests, not necessarily the exact sequence of physical operations the database must perform to produce it. The optimizer turns that request into an execution plan: a sequence of operations for retrieving and processing data.
To choose a plan, the optimizer evaluates alternatives and estimates their costs. Options may include scanning a table or using an index, choosing different join methods or join orders, and applying query transformations. The optimizer’s job is to find a plan it expects to be efficient, not to change the requested result.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →How it selects a plan
- Read the query and schema. The optimizer identifies the requested data and relevant tables, columns, predicates, and relationships.
- Generate candidate plans. It considers available access paths, join strategies, join orders, and, depending on the database, transformations that can preserve the query’s meaning.
- Estimate how many rows each operation will produce. Selectivity is the estimated fraction of rows a predicate keeps; cardinality is the estimated row count produced by an operation. Statistics about tables and columns—including histograms in some systems—help inform these estimates.
- Estimate resource use. A database’s cost model assigns a cost to each candidate based on estimated work. Oracle documentation describes factors including I/O, CPU, memory, input sizes, data distribution, cardinality, and access structures; details differ among database products.
- Choose the lowest estimated-cost candidate it evaluated. The optimizer then uses that plan to execute the query.
“Lowest cost” is bounded by the plans the optimizer actually considers. Searching every possible plan can be impractical. For example, PostgreSQL 17 documents using its genetic query optimizer above a join-count threshold to find a reasonable plan in a reasonable time, rather than exhaustively evaluating every alternative. PostgreSQL 17: Genetic Query Optimizer
#1 Best Overall
What “cost” means—and what it does not
Cost is an optimizer’s numeric estimate for comparing plans under a particular query and optimizer environment. Oracle explicitly cautions that cost does not equal elapsed time: a plan with a lower estimated cost may run faster, slower, or about as quickly as another plan. Cost values are not meaningful as a direct comparison between different queries or different optimizer modes. Oracle Database 26: Query Optimizer Concepts
In short, cost is useful for ranking alternatives within the optimizer’s decision process. It is not a stopwatch reading, a universal unit shared by all databases, or proof that the chosen plan is globally optimal.
Why statistics and row estimates matter
Row estimates influence later decisions throughout a plan. If the optimizer expects only a few rows, repeated index lookups in a nested-loops join may appear attractive. If it expects many rows, a different join method may look less costly. Estimates also affect predicted work for operations such as sorting.
Statistics help the optimizer estimate predicate selectivity and cardinality in light of data distribution. Microsoft says SQL Server derives cardinality estimates primarily from histograms created when indexes or statistics are created, and recommends having relevant automatic or manual statistics available. Microsoft Learn: Cardinality Estimation in SQL Server
When estimates poorly reflect the data, the optimizer may rank plans based on a mistaken picture of the work. The underlying principle is consistent across cost-based systems, but the statistics, estimation methods, and corrective steps are product- and version-specific.
How to interpret an unexpectedly slow plan
Compare the plan’s estimated row counts with the actual rows reported at runtime, where your database’s plan tools expose both. Large differences can point to an estimation problem that affected downstream choices. They do not, by themselves, identify the cause; investigate the relevant predicates, data distribution, and statistics using the documentation for your database and version.
Keep estimated cost separate from runtime evidence. A plan’s cost explains why the optimizer preferred it based on its estimates; actual elapsed time and runtime row counts show what happened during execution.
What varies between database systems
“Cost-based” describes the way plans are ranked; it does not imply a shared formula or exhaustive search. For a particular database and version, the important questions are which statistics feed its estimates, which resources its cost model represents, which physical operations and transformations it considers, how it limits plan search, and what information its explain-plan and runtime tools expose.
Oracle, SQL Server, and PostgreSQL document different optimizer details, so their cost values and plan behaviors should not be treated as interchangeable. Use the documentation for the database and version in question when interpreting a plan.
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.
