DWHPro Insights

How to Improve Cardinality Estimations in Teradata: The Optimizer’s Toolkit

By Roland Wenzlofsky ·

How to Improve Cardinality Estimations in Teradata: The Optimizer’s Toolkit

Missing statistics can affect estimates for both indexed and nonindexed columns. Dynamic sampling and heuristics may help, but neither the presence nor absence of an index guarantees a good plan.

This article explores Optimizer’s ineffective method for estimating the cardinality of non-indexed columns.

Teradata Heuristics – A Nasty Surprise

In the absence of statistics on non-indexed WHERE condition columns, the Optimizer can resort to a basic estimation method based on the row count:

The percentage heuristics in this historical example are not universal optimizer rules. On a 10,000-row fixture, actual cardinality was 180: estimates were 748 without column statistics, 6,750 with only first-column statistics, and 180 with both single-column statistics or joint statistics. Joint statistics improved confidence; they do not eliminate all heuristics.

The collected or random-AMP statistics for the primary index determine the table’s cardinality.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Heuristics can be unexpectedly problematic.

It makes no difference if your query selects millions of rows or no row at all: the estimations are stupid and most times either too high or too low.

A brief comment is necessary regarding the estimation of columns joined with OR.

Teradata 14.10 implements the aforementioned rule. In prior releases of Teradata, the reduction of the above estimates is at a rate of 1% for each intersection in set theory, including:

Intersect

Altogether: Heuristics are a bad way to estimate the cardinality of retrieve steps.

The Optimizer’s Toolkit to Improve Estimations

Fortunately, the Optimizer can utilize supplementary information to enhance estimations for non-indexed WHERE condition columns.

COLLECT STATISTICS ON The_Table COLUMN first_column;

Based on the statistics in first_column, we assume that there will be 5,000 rows.

SELECT * FROM The_Table
WHERE
first_column = 'A'                  -- 5,000
AND second_column = 'B'   -- 75% of above estimation

The example illustrates how the Optimizer utilizes statistics to enhance the initial estimation point for subsequent calculations.

What occurs when we gather statistics for both the first_column and second_column?

COLLECT STATISTICS ON The_Table COLUMN first_column; - 5,000 rows with value 'A'
COLLECT STATISTICS ON The_Table COLUMN second_column;  - 200 rows with value 'B'
SELECT * FROM The_Table
WHERE
first_column = 'A'                  -- 75% of below  estimation
AND second_column = 'B'   --  200 rows

The Twisted World of the Optimizer: How Skew Improves the Estimations

The Optimizer utilizes the skew information regarding the first_column column to improve the estimation accuracy.

As previously mentioned, we assume that 90% of rows in the first column have the value ‘A’, resulting in a skewed distribution. To analyze this bias, we can utilize histograms designed for such values.

COLLECT STATISTICS ON The_Table COLUMN first_column; - 5,000 rows with value 'A'
COLLECT STATISTICS ON The_Table COLUMN second_column;  - 200 rows with value 'B'
SELECT * FROM The_Table
WHERE
first_column = 'A'                  -- 90% of below  estimation
AND second_column = 'B'   --  200 rows

Estimated number of rows: 180.

Utilizing biased value information enhances estimations.

To improve estimations beyond utilizing only one WHERE condition column, consider exploring alternative methods for optimization.

Multi-column statistics provide the solution.

COLLECT STATISTICS ON The_Table COLUMN (first_column,second_column);
SELECT * FROM The_Table
WHERE
first_column = 'A'
AND second_column = 'B';

Another reason to avoid heuristics is their potential drawbacks.

Suppose heuristics are used to estimate the cardinality of at least one column. In that case, the retrieval process will result in “no confidence” and limit the potential optimizations available to the Optimizer, creating a conservative execution plan.

I hope you enjoyed the content, and do not hesitate to ask for clarification or additional information.

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

4 thoughts on “How to Improve Cardinality Estimations in Teradata: The Optimizer’s Toolkit”

  1. Hi –

    It’s good stuff. Kindly clarify the below one,

    Multi-column stats are defined and a few columns are part of the SQL. How the estimation will happen there?

    COLLECT STATISTICS ON The_Table COLUMN (first_column,second_column);

    SELECT * FROM The_Table
    WHERE
    first_column = ‘A’;

    Thanks

    Reply

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam