Cost-Based Optimization | Optimizer Process | Teradata Vantage - 17.10 - Cost-Based Optimization - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

About Cost-Based Optimization

When processing requests, the Optimizer considers all of the following factors and attempts to choose the least costly access method and, when appropriate, the least costly join path:
  • Row selection criteria
  • Index references
  • Available statistics on indexes and columns

The longer a given request takes to complete, the more costly it is. (The unit for all Optimizer cost measures is time in milliseconds.)

The Optimizer uses the column and index demographics and statistics gathered by the COLLECT STATISTICS statement. This information permits the Optimizer to estimate the cardinalities of relations for single-table and join access, and identify skew in tables. With a more finely-tuned knowledge of these factors, the Optimizer can generate better plans for satisfying queries using Optimizer cost estimator functions. For more information on COLLECT STATISTICS (Optimizer form), see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 and Teradata Vantage™ - SQL Data Definition Language Detailed Topics, B035-1184.

Consider the following fragment of a table, table_x:

state serial_num
28 12345
28 23456
51 12345
51 23456

Assume the following query results in a full-table scan:

SELECT *
FROM table_x
WHERE state IN (28, 51)
AND   serial_num IN (12345, 23456);

Now modify the request as follows:

SELECT *
FROM table_x
WHERE (state=28 AND serial_num=12345)
OR (state=51 AND serial_num=23456)
OR (state=28 AND serial_num=23456)
OR (state=51 AND serial_num=12345);

The semantically identical modified query resulted in four primary index accesses, which is a marked performance enhancement over the full table scan used to solve the first query.

Path Selection

Expressions involving both AND and OR operators can be expressed in either of the following logically equivalent forms:
  • (A OR B) AND (C OR D)
  • (A AND C) OR (A AND D) OR (B AND C) OR (B AND D)

The first form is known as conjunctive normal form, or CNF. In this form, operand pairs are ORed within parentheses and ANDed between parenthetical groups. The advantage of CNF is that as soon as any individual condition in the expression evaluates to FALSE, the entire expression evaluates to FALSE and can be eliminated from further consideration by the Optimizer.

The second is called Disjunctive Normal Form, or DNF. Different access paths might be selected based on these 2 forms; depending on the expression, one form may be better than the other.

If A, B, C and D refers to columns of a single table or single column partition, the Optimizer generates the access path based on the form specified in the query; there is no attempt to convert from one form to another to find the best path. On the other hand, if A, B, C or D specifies a join condition, the second form is converted to the first.

Consider the following expression:

(NUSI=A OR NUSI=B) AND (X=3 OR X=4)

In this case, CNF is more high-performing because the access path consists of 2 NUSI SELECT requests with values of A and B. The condition (X=3 OR X=4) is then applied as a residual condition. If DNF had been used, then four NUSI SELECT requests would be required.

In the following expression the collection of (NUSI_A, NUSI_B) comprise a NUSI.

(NUSI_A=1 OR NUSI_A=2) AND (NUSI_B=3 OR NUSI_B=4)

In this case, DNF is better because the access path consists of four NUSI SELECTs, whereas the access path using CNF would require a full table scan.

Consider an expression that involves a single column comparison using IN, such as the following:

column IN (value_1, value_2, …)

Internally, that expression is converted to CNF.

column=value_1 OR column=value_2 OR …

Therefore, the same access path is generated for either form.

Assume an expression involves a multiple-column comparison using an IN list, such as in the following example:

column_1 IN (value_1, value_2, …)
AND column_2 IN (value_3, …)

The Optimizer converts this syntax internally to conjunctive normal form.

(column_1=value_1 OR column_1=value_2 OR …)
 AND (column_2=value_3 OR …)

Note how the converted form, conjunctive normal form (or CNF) differs from the second form, which is formulated in disjunctive normal form (DNF) as shown in the following example:

(column_1=value_1 AND column_2=value_3)
OR (column_1=value_2 AND column_2=value_4)
OR …

The point is that semantically equivalent queries, when formulated with different syntax, often produce different access plans.

About Predicting Costs for the Optimizer

The purpose of cost prediction is to provide the Optimizer with accurate estimates of the costs to perform various operations in various ways so the Optimizer can make cost-based decisions.

The cost of an operation is the service time required by subsystems to undertake an operation on an otherwise unloaded system. The Optimizer compares alternative ways of performing an operation based on estimates of CPU, I/O, and BYNET component service times and then chooses the alternative that has the lowest overall cost. There are practical situations where a particular approach should be excluded, or its likelihood greatly reduced, by increasing its predicted cost value, so a heuristic cost component is also included with the collection of various subsystem cost components.

Overall cost, which is a linear combination of the subsystem costs, is the value used by the Optimizer to compare the various alternatives available to it. The cost prediction formulas and methods maintain individual subsystem level values in order to make it possible to determine how much of a given overall cost is accounted for by each of its CPU, I/O, BYNET, and heuristic component values. This information makes it possible to distinguish between I/O-intensive and CPU-intensive approaches, enabling an analyst to have a clear view of error sources when a situation occurs in which a predicted cost value is grossly inaccurate.

You can capture subsystem cost values for individual steps in a query plan using either an SQL INSERT EXPLAIN request to funnel the appropriate information into the query capture database table QuerySteps, in the following column set:
  • EstCPUCost
  • EstIOCost
  • EstNetworkCost
  • EstHRCost

For more information, see Query Capture Facility and the information about INSERT EXPLAIN in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

If query logging is enabled, the system captures the same information in DBQLStepTbl in the following column set.
  • EstCPUCost
  • EstIOCost
  • EstNetCost
  • EstHRCost

For more information, see Teradata Vantage™ - Database Administration, B035-1093 and the information about BEGIN QUERY LOGGING in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

The corresponding overall cost values are captured in the Cost column of the QCD QuerySteps table and in the EstProcTime column of the DBQL DBQLStepTbl tables, respectively.

When predicting costs, there are 2 input data categories. First are values that characterize the runtime environment, and second are values which characterize the database entities involved in the request being optimized. The runtime environment includes hardware plat-form, operating system, I/O subsystem and database algorithms, essentially all the product set elements of a database system. I/O throughput rates, CPU speed, BYNET capacity, and code path lengths all are examples of runtime environment performance factors. Both CPU and I/O costing vary depending on the sizes of the data blocks containing the data accessed by a request that is being optimized.

During query planning, the Optimizer estimates database entity characteristics using various kinds of metadata, including statistics, dynamic AMP samples, and a variety of estimates derived from these base data sources.

Optimizer Cost Profiles

A cost profile is a suite of system-specific values (cost profile constants) that Vantage uses for Optimizer costing coefficients, enabling and disabling certain features, and other system settings. A cost profile can apply system-wide or be associated with a PROFILE and apply only to users assigned that PROFILE.

The following table lists some of the Optimizer cost profile constants and what they do:

Cost profiles should be set up and modified only by Teradata Support Center personnel.
Cost Profile Constant Description
COLLECT STATISTICS Cost Profile Constant
StatsDefaultTimeThreshold Defines a default time threshold value in days for statistics recollection. The database only recollects statistics if this many days or more have passed since statistics were last collected.

This setting is applicable only if a COLLECT STATISTICS request does not specify USING THRESHOLD DAYS.

StatsDefaultUserChangeThreshold Defines a user-determined default data change percentage threshold value for statistics recollection. The database only recollects statistics if the data demographics have changed by this percentage since statistics were last collected.

This setting is applicable only if a COLLECT STATISTICS request does not specify USING THRESHOLD PERCENT.

StatsSysChangeThresholdOption Defines a system-determined default change threshold option for statistics recollection. The database only recollects statistics if the data demographics have changed by this percentage since statistics were last collected.

This setting is only applicable if DefaultUserChangeThreshold is disabled and a COLLECT STATISTICS request does not specify USING THRESHOLD PERCENT.

StatsSysSampleOption Defines a system-determined default sampling option for statistics recollection.

This setting is only applicable if a COLLECT STATISTICS request does not specify the USING SAMPLE option.

Partitioning Cost Profile Constants
PPICacheThrP Specifies the maximum amount of memory to be used for disk read operations that involve multiple index partitions.
Parameterized Value Peeking Cost Profile Constants
TacticalResp1 Runtime CPU cost per AMP that determines whether a query is treated as a tactical or decision support request.

The value is derived from the CPU time used for query execution in the AMPs.

By definition, a request whose per-AMP CPU time ≤  1 second is considered to be tactical and a request whose per-AMP CPU time > 1 second is considered to be a decision support request.

TacticalResp2 Runtime CPU cost per AMP that determines whether a query is treated as a tactical or decision support query when it is submitted as a HiPriority request.

The value is derived from the CPU time used for query execution in the AMPs.

HighParsingPTThreshold Threshold percentage of the parsing cost compared to the runtime CPU cost on which a determination is made on whether the request has a high parsing cost.
HighParsingRTThreshold Threshold multiplication factor for the determination of runtime benefits for a query that has a high parsing cost.

If the request has a high parsing cost, then the runtime CPU times of specific and generic plans should differ by at least this value multiplied times the specific CPU parsing time.

LowParsingPTThreshold Threshold multiplication factor for the determination of runtime benefits for a query that has a low parsing cost.

If the request has a low parsing cost, then the runtime CPU times of specific and generic plans should differ by at least this value multiplied times the specific CPU parsing time.

UseHiPriority A flag that enables or disables the HiPriority-based decisions in the caching algorithm.
ElapsedTimeThreshold Threshold multiplication factor by which the elapsed time of a specific plan execution (the sum of its parsing and run times) should exceed the elapsed time of the equivalent generic plan.
EstimateCostFilter Threshold factor by which the estimated cost of a specific plan should be better than the estimated cost of the equivalent generic plan.

Used for comparison of estimated costs.

CompareEstimates A Cost Profile flag that enables or disables the estimate-based comparisons for deciding between using the generic and specific plan for a request.

Statistics And Cost Estimation

The following properties apply to Optimizer cost estimates when statistics are available:
  • The Optimizer evaluates access selectivity based on demographic information.

    If statistics have recently been collected on the primary index for a table, then it is likely, but not certain, that an accurate cardinality estimate is available to the Optimizer. If statistics are stale, then the derived statistics framework should minimize any problems with them.

  • The Optimizer uses primary index statistics to estimate the cardinality of a table.

    Therefore, you should keep those statistics current to ensure optimum access and join planning. Collect new statistics on primary indexes when more than 10% of the total number of rows are added or deleted.

When no statistics are available, or when the table has no primary index, the Optimizer estimates the total number of rows in a table based on information sampled from a dynamically selected AMP (see Dynamic AMP Sampling).

For the following reasons, this might not be an accurate estimate of the cardinality of the table:
  • The table is relatively small and distributed unevenly across the AMPs.
  • The NUPI selected for a table causes its rows to be distributed unevenly across the AMPs.

When either of these properties is true, then there might be AMPs on which the number of rows is highly unrepresentative of the average population of rows in the other AMPs.

If such an unrepresentative AMP is selected for the row estimate, the Optimizer might generate a bad estimate, and thus might not choose a plan (join, access path, and so on) that would execute the request most efficiently.

Stale statistics often cause the Optimizer to produce a worse join plan than one based on estimated information from a dynamically sampled AMP.

The only difference is that a plan based on outdated statistics is consistently bad, while a plan based on a dynamic AMP sample has a likelihood of providing a reasonably accurate statistical estimate.

Ensuring Indexed Access

To guarantee that an index is used in processing a request, specify a constraint in the WHERE clause of a query on a value from an indexed column.

If multiple NUSIs apply to such a WHERE constraint, and if the subject table is very large, then bit mapping provides the most efficient retrieval. For smaller tables, the Optimizer selects the index estimated to have the highest selectivity (fewest rows per index value).

If statistics are not available for a NUSI column, then the Optimizer assumes that indexed values are distributed evenly. The Optimizer estimates the number of rows per indexed value by selecting an AMP dynamically based on its table ID and dividing the total number of rows from the subject table on that AMP by the number of distinct indexed values on the AMP. When distribution of index values is uneven, such an estimate can be unrealistic and result in poor access performance.

Guidelines For Indexed Access

Always use the EXPLAIN request modifier to determine the plan the Optimizer will generate to perform a query.

The Optimizer follows these guidelines for indexed access to table columns:
  • For NoPI tables, column access is done using secondary or join indexes. If no secondary or join indexes are defined for the column in question, then access is always done using a full-table scan.
  • UPIs are used for fastest access to table data.
  • USIs are used only to process requests that employ equality constraints.
  • The best performance in joins is achieved when the following is possible.
    • Matching UPI values in one table with unique index values, either UPI or USI, in another table.
    • Using only a primary index when satisfying an equality or IN condition for a join.
  • NUPIs are used for single-AMP row selection or join processing to avoid sorting and redistribution of rows.
  • When statistics are not available for a table, the estimated cost of using an index is based on information from a single AMP. This estimate assumes an even distribution of index values. An uneven distribution impacts performance negatively.
  • Composite indexes are used only to process requests that employ equality constraints for all columns that comprise the index.

    Note that you can define an index on a single column that is also part of a multicolumn index on the same table.

  • Bit mapping is used only when equality or range constraints involving multiple nonunique secondary indexes are applied to very large tables.
  • Use either a nonuniquely indexed column or a USI column in a nested join to avoid redistributing and sorting a large table.

    For example, consider the following condition.

    table_1.column_1 = 1 AND table_1.column_2 = table_2.nusi

    Without using a Nested Join, where table_1 is duplicated and joined with table_1.nusi, both table_1 and table_2 might have to be sorted and redistributed before a Merge Join. This join is not high-performing when table_2 is large.