DWHPro Insights

Teradata Statistics Extrapolation: What it is and How it Works

By Roland Wenzlofsky ·

Teradata Statistics Extrapolation: What it is and How it Works

This article explains the purpose of extrapolation rather than providing a reproducible formula for every release. The original example mixed collection dates and contained an incomplete equation; the query syntax and date narrative have been corrected. Recollect relevant statistics when extrapolated estimates do not represent the workload.

What is Teradata Statistics Extrapolation?

In data warehousing, populating historized tables with snapshot data valid for the current day is typical.

This is a common illustration:

SELECT Snapshot_Date,Count(*) CNT FROM The_Snapshot_Table GROUP BY Snapshot_Date;
Snapshot Date CNT
2015-04-10 10
2015-04-11 15
2015-04-12 10
2015-04-13 25
2015-04-14 15
2015-04-15 30

For a consistent illustration, assume statistics were collected after loading the snapshot dated 2015-04-15, then a new snapshot dated 2015-04-16 was loaded without recollection.

COLLECT STATISTICS ON THE_SNAPSHOT_TABLE COLUMN(SNAPSHOT_DATE);

A predicate on the newly loaded 2015-04-16 value falls beyond the previously observed maximum. The optimizer may use extrapolation or other estimation rules; absent a collected value does not guarantee an estimate of zero.

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
SELECT * FROM The_Snapshot_Table WHERE Snapshot_Date=DATE'2015-04-16'
Extrapolation on DATE columns allows the optimizer to make reasonable estimations in such cases, even when statistics are stale. The optimizer considers date extrapolation only if it assumes that the current statistics are stale.

Which Statistics Information is Extrapolated?

The optimizer extrapolates the following statistical information:

  • The number of table rows, based on the available statistics summary information and random-AMP sampling
  • The number of distinct primary index values and the maximum value per statistic histogram
  • The number of rows per value for the date column

How is the Statistics Information extrapolated?

It is important to understand that the optimizer estimates a limited number of future dates (i.e., any date after the last recorded statistics). The highest predicted date for extrapolation relies on a random AMP sample, which indicates the number of distinct dates in the extrapolation process.

The exact extrapolation formula and limits depend on the optimizer release and available statistics. The former formula here was incomplete and should not be used as a documented algorithm.

Maximum Extrapolated Date = Maximum Date with Collected Statistics + ((Maximum Date with Collected Statistics – Minimum Date with Collected Statistics) * ((Distinct Random AMP Sample Dates * Average Rows Per Date from Collected Statistics) / (Distinct Dates from Collected Statistics * Average Rows Per Date from Collected Statistics))

For a purely illustrative calculation, suppose the newly loaded snapshot has 1,000 rows. Estimating its contribution requires assumptions about the new distribution; the number of distinct future dates is not known merely from the previous histogram.

The historical formula has been withdrawn because its parentheses, date bounds and distinct-value assumptions were inconsistent.

teradata statistics extrapolation

For a query covering April 15–16, distinguish the April 15 rows represented by collected statistics from the newly loaded April 16 rows. Check the actual plan and row count to evaluate the estimate.

If both dates actually have 1,000 rows, the actual total is 2,000. That arithmetic is not a claim that the optimizer will estimate precisely 2,000 or use a particular extrapolation formula.

The arithmetic above illustrates how estimated additional days could affect a row estimate. It is not a reproduced optimizer result or a guarantee that extrapolation improves accuracy.

The quality of the estimation based on a random AMP sample highly depends on the skew factor of the table.

Random AMP sampling that delivers only unique future dates is an ideal scenario. However, a biased table could result in greatly inaccurate estimations of future dates.

Another crucial factor is the coverage of the date range in your query by the extrapolated dates.

Compare estimates with actual counts in the requested date range. Partial extrapolation can help or mislead depending on the new data distribution and the information available.

Extrapolation is not beneficial if the extrapolated data does not encompass your desired timeframe.

teradata statistics extrapolation

Utilizing statistics extrapolation can be beneficial for daily snapshot tables; however, it should not replace the collection of statistics entirely. Extrapolation can aid in moving the recollection process to a less loaded point in the Teradata system.

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.

2 thoughts on “Teradata Statistics Extrapolation: What it is and How it Works”

  1. You are right regarding point (1) and I always wondered why this may be the reason? My assumption was that during the process it’s easier as “division by zero” handling can be avoided?

    Regarding point (2), as far as I remember, the calculation formula is available in the official documentation.

    Reply
  2. Hi Roland,
    Interesting article, thanks. I have a couple of points/questions:
    1) (minor point), AFAIK the optimizer never estimates ‘0 rows’, the minimum value it uses is 1 row or ‘1 row per amp’.
    2) (question) where did you get the extrapolation calculation from?
    Cheers,
    Dave

    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