SQL/MP Query Guide

Selectivity and Cost Estimates
HP NonStop SQL/MP Query Guide—524488-003
5-19
Evaluating Cost Estimates
Evaluating Cost Estimates
When examining cost, these guidelines apply:
•
High cost indicates that the given query appears to be (and probably is) expensive.
Always review high cost statements. Try to estimate how much I/O such a query
should take and if it is consistent with that reported by EXPLAIN. If your estimate
and that of EXPLAIN vary considerably, carefully review the EXPLAIN plan to
determine why the optimizer estimated that the query is so expensive. For
example, the data might not be distributed uniformly.
When up-to-date statistics are available, a high Total Cost might indicate that you
are reading too many rows. For a given database with good production level
statistics, you can plot the total cost of a statement with results from SQLSTMT
(see Using Measure on page 6-7) and look at the relationship between Total Cost,
rows accessed, and perceived response time (as experienced by the end user).
•
Although low cost is generally desirable, low cost does not necessarily indicate low
overhead. Instead, a low cost might indicate that the optimizer does not have
enough information to estimate the cost accurately. Catalog statistics might be
nonexistent or might not represent the production environment accurately.
•
The cost estimate might be inaccurate if data has an uneven distribution instead of
being distributed uniformly across blocks and partitions.
•
Beware of plans built for small tables. If your development or test tables are small,
the cost might be very different when the same plan is used on large production
tables. Whenever possible, evaluate EXPLAIN output as it would appear in
production during the query development and program test phases of
development.
For information about how to access cost estimates, see °Section 6, Analyzing Query
Performance.
How the Optimizer Chooses an Execution Plan
The optimizer selects the most efficient execution plan, defined as the one that takes
the least time to complete the evaluation of a query.
The estimated costs associated with several query plans might, however, be very
close; that is, within 10 percent of one another. To select between two plans whose
costs are close, the optimizer chooses a plan based on these priorities, listed in order
of preference:
•
A local table or index (as opposed to a remote table or index)
•
A table or index in which predicates of the form “column=value” have specified all
the key columns forming a unique access
•
A table or index with a lower selectivity