DWHPro Insights

Analyzing and Fixing Teradata Statistics Estimation Problem

By Roland Wenzlofsky ·

Illustration of a stopwatch dial on an orange paint-splash background
by

This article will thoroughly examine a statistical problem. We will investigate the inaccuracies in the estimates for our sample query and propose solutions to rectify them.

The Scenario: 36,707 Estimated Rows, 501 Actual

The initial scenario is as follows:

We have executed an EXPLAIN statement on the query below, resulting in the following execution plan:

SELECT * FROM TheDatabase. TMP_STATS WHERE  CHAR1 = 'X';
1) First, we lock TheDatabase.TMP_STATS for read on a reserved RowHash to
prevent global deadlock.
2) Next, we lock TheDatabase.TMP_STATS for read.
3) We do an all-AMPs RETRIEVE step from TheDatabase.TMP_STATS by way of an
all-rows scan with a condition of (“TheDatabase.TMP_STATS.Char1 = ‘X ‘”)
into Spool 1 (group_amps), which is built locally on the AMPs.
The size of Spool 1 is estimated with high confidence to be 36,707
rows (16,040,959 bytes). The estimated time for this step is 1.70
seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 1.70 seconds.

The Optimizer estimates the result set to contain 36,707 rows with high confidence

Regrettably, the query only returns 501 rows. The Optimizer has overestimated the number of resulting rows.

SELECT COUNT(*) FROM TheDatabase. TMP_STATS WHERE  CHAR1 = 'X';
501

Investigating the Available Statistics

Our objective is to identify the issue and develop a resolution. We will commence by examining the data provided in the EXPLAIN plan.

The available statistics in column CHAR1 have a high confidence level as estimated by the Optimizer. However, their freshness is not known. To confirm their validity, we will conduct a prompt check.

HELP STATS TheDatabase. TMP_STATS;
16/02/1219:08:3473,414**“*”null
16/02/1216:41:3973,414PKPKPKnull
16/02/1216:41:3973,414TheDateTheDateTheDatenull
16/02/1219:08:34100Int1Int1Int1null
16/02/1216:41:40100Int2Int2Int2null
16/02/1218:32:092Char1Char1Char1null
16/02/1218:36:52957Char2CHar2CHar2null
HELP STATISTICS reports column-statistic metadata here; the distinct count for CHAR1 is not a table-summary statistic. Compare it with actual distinct values and inspect the corresponding histogram.
SELECT COUNT(DISTINCT CHAR1) FROM TheDatabase. TMP_STATS;
3

There is a discrepancy between the Optimizer’s estimated values and the three values presented in the table. To address this, we will conduct a brief verification check:

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 CHAR1,COUNT(*) FROM TheDatabase.TMP_STATS GROUP BY 1;
B36257
X501
A36656

Column CHAR1 presents three unique values. We must determine the source of inaccurate estimations and devise a solution. We will employ the SHOW STATISTICS command to examine the statistical histograms:

SHOW STATISTICS VALUES COLUMN(CHAR1) ON TheDatabase.TMP_STATS;
COLLECT STATISTICS
COLUMN ( Char1 )
ON TheDatabase.TMP_STATS
VALUES
(
/** SummaryInfo **/
/* Data Type and Length: 'CF:26' */
/* TimeStamp */ TIMESTAMP '2016-02-12 18:32:09-00:00',
/* Version */ 6,
/* OriginalVersion */ 6,
/* DBSVersion */ '15.10.00.07',
/* UsageType */ 'D',
/* ComplexStatInfo */ 'ComplexStatInfo',
/* NumOfBiasedValues */ 2,
/* NumOfEHIntervals */ 0,
/* NumOfHistoryRecords */ 0,
/* SamplePercent */ 0.00,
/* NumOfNulls */ 0,
/* NumOfAllNulls */ 0,
/* NumOfPartialNullVals */ 0,
/* PartialNullHMF */ 0,
/* AvgAmpRPV */ 0.000000,
/* MinVal */ 'A ',
/* MaxVal */ 'B ',
/* ModeVal */ 'A ',
/* HighModeFreq */ 36916,
/* NumOfDistinctVals */ 2, 
 /* NumOfRows */ 73414,
/* CPUUsage */ 0.000000,
/* IOUsage */ 0.000000,
/* Reserved */ 0,
/* Reserved */ 0,
/* Reserved */ 0.000000,
/* Reserved */ 0.000000,
/* Reserved */ 'T0000D00000S00000',
/* StatsSkipCount */ 0,
/* SysInsertCnt */ 0,
/* SysDeleteCnt */ 0,
/* SysUpdateCnt */ 0,
/* SysInsDelLastResetTS */ TIMESTAMP '9999-12-31 23:59:59-00:00',
/* SysUpdLastResetTS */ TIMESTAMP '9999-12-31 23:59:59-00:00',
/* IsSampleFollowingTrend*/ 0,
/** Biased: Value, Frequency **/
/* 1 */ 'A ', 36916, 
 /* 2 */ 'B ', 36498
);

The SHOW STATISTIC statement displays the statistics output.

The value ‘X’ in column CHAR1 is missing from the histograms due to its unavailability during the statistics collection process.

Why do the totals and estimated rows match? The answer is that an UPDATE statement modified the value while the total number of rows remained the same.

Where the 36,707-Row Estimate Comes From

Now that we comprehend the issue of outdated statistics due to an UPDATE statement, an unanswered question remains: What is the origin of the estimated count of 36,707 rows, and how is it computed?

Upon examining the SHOW STATISTICS command output, it becomes apparent that two distinct values are present.

/* NumOfDistinctVals */ 2,
/* NumOfRows */ 73414,

Suppose the predicate value, denoted as ‘X’, is not present in any histogram due to outdated statistics. In that case, the Optimizer will estimate the number of retrieved rows by dividing the value in the column NumOfRows by the value in the column NumOfDistinctVals:

73,414 / 2 = 36,707

This example illustrates the potential for misleading statistical estimations when one does not understand Teradata’s statistical implementation.

Extrapolation is ineffective in this scenario as the number of rows remains constant.

Recollecting the Stale Statistics

Our task is almost complete as we have identified the cause of our stagnant statistics and the reason behind the Optimizer’s estimation of 36,707 rows. The solution to our issue is to refresh the statistics in the CHAR1 column.

COLLECT STATS COLUMN(CHAR1) ON TheDatabase. TMP_STATS;

We can validate the accuracy of our estimations by executing the SHOW STATISTICS statement once more.

SHOW STATISTICS VALUES COLUMN(CHAR1) ON TheDatabase.TMP_STATS;
COLLECT STATISTICS
COLUMN ( Char1 )
ON TheDatabase.TMP_STATS
VALUES
(
/** SummaryInfo **/
/* Data Type and Length: 'CF:26' */
/* TimeStamp */ TIMESTAMP '2016-02-13 12:31:06-00:00',
/* Version */ 6,
/* OriginalVersion */ 6,
/* DBSVersion */ '15.10.00.07',
/* UsageType */ 'D',
/* ComplexStatInfo */ 'ComplexStatInfo',
/* NumOfBiasedValues */ 3,
/* NumOfEHIntervals */ 0,
/* NumOfHistoryRecords */ 1,
/* SamplePercent */ 0.00,
/* NumOfNulls */ 0,
/* NumOfAllNulls */ 0,
/* NumOfPartialNullVals */ 0,
/* PartialNullHMF */ 0,
/* AvgAmpRPV */ 0.000000,
/* MinVal */ 'A ',
/* MaxVal */ 'X ',
/* ModeVal */ 'A ',
/* HighModeFreq */ 36656,
/* NumOfDistinctVals */ 3, 
 /* NumOfRows */ 73414,
/* CPUUsage */ 0.000000,
/* IOUsage */ 0.000000,
/* Reserved */ 0,
/* Reserved */ 0,
/* Reserved */ 0.000000,
/* Reserved */ 0.000000,
/* Reserved */ 'T0000D00003S00003',
/* StatsSkipCount */ 1,
/* SysInsertCnt */ 0,
/* SysDeleteCnt */ 0,
/* SysUpdateCnt */ 501,
/* SysInsDelLastResetTS */ TIMESTAMP '2016-02-13 10:24:09-00:00',
/* SysUpdLastResetTS */ TIMESTAMP '2016-02-13 10:24:09-00:00',
/* IsSampleFollowingTrend*/ 0,
/** Biased: Value, Frequency **/
/* 1 */ 'A ', 36656, 
 /* 2 */ 'B ', 36257, 
 /* 3 */ 'X ', 501,
/** HistoryOfSummaryInfo **/
/* SummaryRecord[1] */
/* TimeStamp */ TIMESTAMP '2016-02-12 18:32:09-00:00',
/* NumOfBiasedValues */ 0,
/* NumOfEHIntervals */ 0,
/* NumOfHistoryRecords */ 0,
/* SamplePercent */ 0.00,
/* NumOfNulls */ 0,
/* NumOfAllNulls */ 0,
/* NumOfPartialNullVals */ 0,
/* PartialNullHMF */ 0,
/* AvgAMPRPV */ 0.000000,
/* ValuesNotPresengFlg */ 0,
/* MinVal */ 'A ',
/* MaxVal */ 'B ',
/* ModeVal */ 'A ',
/* HighModeFreq */ 36916,
/* NumDistinctVals */ 2,
/* NumRows */ 73414,
/* CPUUsage */ 0.000000,
/* IOUsage */ 0.000000,
/* Reserved */ 0,
/* Reserved */ 0,
/* Reserved */ 0.000000,
/* Reserved */ 0.000000,
/* AvgEHDeviation */ 0.000000,
/* UsageType */ 'D',
/* StatsSkipCount */ 0,
/* SysInsertCnt */ 0,
/* SysDeleteCnt */ 0,
/* SysUpdateCnt */ 0,
/* SysInsDelLastResetTS */ TIMESTAMP '9999-12-31 23:59:59-00:00',
/* SysUpdLastResetTS */ TIMESTAMP '9999-12-31 23:59:59-00:00',
/* IsSampleFollowingTrend*/ 0
);

The displayed output confirms the successful update of 501 rows.

/* SysUpdateCnt */ 501,

Verifying the Corrected Estimate

We are now reviewing the updated explain plan.

SELECT * FROM TheDatabase. TMP_STATS WHERE CHAR1 = 'X';
1) First, we lock TheDatabase.TMP_STATS for read on a reserved RowHash to
prevent global deadlock.
2) Next, we lock TheDatabase.TMP_STATS for read.
3) We do an all-AMPs RETRIEVE step from TheDatabase.TMP_STATS by way of an
all-rows scan with a condition of (“TheDatabase.TMP_STATS.Char1 = ‘X ‘”)
into Spool 1 (group_amps), which is built locally on the AMPs.
The size of Spool 1 is estimated with high confidence to be 501
Rows (218,937 bytes). The estimated time for this step is 0.89
seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 0.89 seconds.

Upon matching the value ‘X’ in the biased histogram, the estimation coincides with the number of rows containing ‘X’ in column CHAR1. This illustration’s main point is that a sense of “high confidence” does not guarantee accurate estimations.

Validation scope: The original 73,414-row serialized statistics are historical. In a 1,000-row fixture, changing 501 rows to an absent value gave estimate 500 with stale statistics and 501 after recollection, against actual 501. When thresholds apply, verify that a requested collection actually ran.

Related Insights

Written by Roland Wenzlofsky — 25+ years in enterprise data warehousing and author of Teradata Query Performance Tuning.

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.

📊 Data Platform Migration Survey

Help us map where the industry is heading. Results are public — see what others chose.

1. What is your current data platform?

2. Where are you migrating to (or evaluating)?

Migrating FROM
Migrating TO

Thanks for voting! Share this with your network.

Follow me on LinkedIn for daily insights on data warehousing and platform migrations.

Stay Ahead in Data Warehousing

Get expert insights on Teradata, Snowflake, BigQuery, Databricks, Microsoft Fabric, and modern data architecture — delivered to your inbox.

Read new articles here or follow our RSS feed while waiting for the next newsletter.

Read latest articles · Follow via RSS

If signup is unavailable, RSS keeps you up to date without email.

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