Almost every bad Teradata query plan traces back to one thing: the optimizer estimated the wrong number of rows. This guide collects everything on this site about statistics and cardinality estimation — what the optimizer does with the statistics you give it, how it copes when they are missing or stale, and how to diagnose an estimate that has gone wrong.
Optimizing Statistics and Avoiding Heuristics
The Optimizer typically excels in utilizing statistics, yet examining the execution plan and cardinality estimations can sometimes be beneficial.
Since Teradata 14.10, I have habitually included the SHOW STATISTICS statement in my considerations. The resulting metrics can aid in identifying statistical issues.
Teradata Statistics – Avoid the Heuristics
This case study demonstrates how the Optimizer can be compelled to utilize precise statistics rather than falling back on imprecise heuristics.
The case study relies on two tables as its foundation:
SHOW TABLE TheDatabase.TableOneDate;CREATE SET TABLE TheDatabase.TableOneDate
(
OneDate DATE FORMAT 'YYYY-MM-DD'
) UNIQUE PRIMARY INDEX ( OneDate );SELECT COUNT(*) FROM TheDatabase.TableOneDate;
--> 1 RowSHOW TABLE TheDatabase. TheCalendar
CREATE MULTISET TABLE TheDatabase.TheCalendar
(
CalendarDate DATE FORMAT 'yyyy-mm-dd'
) PRIMARY INDEX ( CalendarDate );SELECT COUNT(*) FROM TheDatabase.TheCalendar;–> 36.889 Rows
I excluded irrelevant columns from both tables and gathered statistics on the remaining ones.
COLLECT STATS ON TheDatabase. TableOneDate COLUMN OneDate;
COLLECT STATS ON TheDatabase. TheCalendar COLUMN CalendarDate;
This query, which is not identical but conveys the same meaning, has attracted my attention due to its use in numerous badly-performing reports:
Explain SELECT * FROM TheDatabase.TheCalendar
WHERE CalendarDate >=
(
SELECT
OneDate
FROM TheDatabase.TableOneDate
)1) First, we lock a distinct TheDatabase."pseudo table"
for read on a RowHash to prevent global deadlock for
TheDatabase.TableOneDate.
2) Next, we lock a distinct TheDatabase."pseudo table" for
read on a RowHash to prevent global deadlock for
TheDatabase.TheCalendar.
3) We lock TheDatabase.TableOneDate for read, and we
lock TheDatabase.TheCalendar for read.
4) We do an all-AMPs RETRIEVE step from
TheDatabase.TableOneDate by way of an all-rows
scan with no residual conditions into Spool 1 (all_amps)
built locally on the AMPs. The size of Spool 1 is estimated with
high confidence to be 1 row (25 bytes). The estimated time for
this step is 0.03 seconds.
5) We do an all-AMPs DISPATCHER RETRIEVE step from Spool 1 (Last Use)
by way of an all-rows scan and send the rows back to the
Dispatcher. The size is estimated with high confidence to be 1
row. The estimated time for this step is 0.01 seconds.
6) We do an all-AMPs RETRIEVE step from
TheDatabase.TheCalendar by way of an all-rows scan with a
condition of ("TheDatabase.TheCalendar.CalendarDate >= :%SSQ20")
into Spool 2 (group_amps), which is built locally on the AMPs.
The size of Spool 2 is estimated with no confidence to be 12,297
rows (4,943,394 bytes). The estimated time for this step is 0.04
seconds.
7) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 2 are sent back to the user as the result of
statement 1. The total estimated time is 0.08 seconds.Although the execution plan appears satisfactory, there is a concern regarding the estimated number of rows in step 6. The estimate of 12,297 is significantly lower than the actual result set of 32,482 rows.
The Optimizer is not utilizing the collected statistics and instead relies on heuristics with low confidence.
12,297 (estimation) / 36,889 (table cardinality) = 33%
This can be demonstrated easily with a comparable configuration: The Optimizer consistently approximates that the number of rows in the result set will be one-third of the total table rows.
Presented below are the histograms of related statistics pertaining to CalendarDate.
COLLECT STATISTICS
COLUMN ( CalendarDate )
ON TheDatabase.TheCalendar
VALUES
(
/** SummaryInfo **/
/* NumOfBiasedValues */ 0,
/* NumOfEHIntervals */ 249,
/* NumOfHistoryRecords */ 7,
/* MinVal */ DATE '2000-01-02',
/* MaxVal */ DATE '2100-12-31',
/* ModeVal */ DATE '2000-01-02',
/* HighModeFreq */ 1,
/* NumOfDistinctVals */ 36889,
/* NumOfRows */ 36889,
/** Interval: MaxVal, ModeVal, ModeFreq, LowFreq, OtherVals, OtherRows **/
/* 1 */ DATE '2000-10-04', DATE '2000-01-02', 1, 1, 276, 276,
..
/* 249 */ DATE '2100-12-31', DATE '2100-09-08', 1, 1, 114, 114, Although it may seem peculiar that the Optimizer is not utilizing accessible statistics, I have come to accept this behavior. I modified the query by substituting the subquery with a date literal (2012-01-26, which is the same date retrieved in the previous query’s subquery):
Explain SELECT * FROM TheDatabase.TheCalendar
WHERE CalendarDate >=
(
SELECT
DATE'2012-01-26'
)1) First, we lock a distinct TheDatabase."pseudo table"
for read on a RowHash to prevent global deadlock for
TheDatabase.TheCalendar.
2) Next, we lock TheDatabase.TheCalendar for read.
3) We do an all-AMPs RETRIEVE step from
TheDatabase.TheCalendar by way of an all-rows scan with a
condition of ("TheDatabase.TheCalendar.CalendarDate >= DATE
'2012-01-26'") into Spool 1 (group_amps), which is built locally
on the AMPs. The size of Spool 1 is estimated with high
confidence to be 32,482 rows (1,006,942 bytes). The estimated
time for this step is 0.03 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.03 seconds.The updated execution plan reveals that the Optimizer effectively utilizes the gathered statistics to accurately project the number of result set rows. Regrettably, my report optimization task does not allow for using a literal.
To better understand the Optimizer’s design, I modified the query once more and chose the literal from a “dummy table.”
Explain SELECT * FROM TheDatabase.TheCalendar
WHERE DAY_DATE >=
(
SELECT DATE'2012-01-26' FROM
(
SELECT 1 AS x
) x
)1) First, we lock a distinct TheDatabase."pseudo table"
for read on a RowHash to prevent global deadlock for
TheDatabase.TheCalendar.
2) Next, we lock TheDatabase.TheCalendar for read.
3) We do an INSERT into Spool 2.
4) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of
an all-rows scan into Spool 1 (used to materialize view, derived
table, table function or table operator x) (group_amps), which is
built locally on the AMPs. The size of Spool 1 is estimated with
high confidence to be 1 row (22 bytes). The estimated time for
this step is 0.01 seconds.
5) We do a group-AMP RETRIEVE step from Spool 1 (Last Use) by way of
an all-rows scan into Spool 3 (group_amps), which is built locally
on that AMP. The size of Spool 3 is estimated with high
confidence to be 1 row (25 bytes). The estimated time for this
step is 0.01 seconds.
6) We do a group-AMP DISPATCHER RETRIEVE step from Spool 3 (Last Use)
by way of an all-rows scan and send the rows back to the
Dispatcher. The size is estimated with high confidence to be 1
row. The estimated time for this step is 0.01 seconds.
7) We do an all-AMPs RETRIEVE step from
TheDatabase.TheCalendar by way of an all-rows scan with a
condition of ("TheDatabase.TheCalendar.DAY_DATE >= :%SSQ20")
into Spool 4 (group_amps), which is built locally on the AMPs.
The size of Spool 4 is estimated with no confidence to be 12,297
rows (381,207 bytes). The estimated time for this step is 0.03
seconds.
8) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 4 are sent back to the user as the result of
statement 1.The execution plan indicates a return to heuristics, which is not ideal. I intended to enhance all reports’ performance by optimizing the “calendar view section.”
After much consideration, I devised a correlated subquery for the following query:
Explain SELECT * FROM TheDatabase.TheCalendar t01
WHERE EXISTS
(
SELECT
*
FROM TheDatabase.TableOneDate t02
WHERE t01.DAY_DATE>=OneDate
)1) First, we lock a distinct TheDatabase."pseudo table"
for read on a RowHash to prevent global deadlock for
TheDatabase.t01.
2) Next, we lock a distinct TheDatabase."pseudo table" for
read on a RowHash to prevent global deadlock for
TheDatabase.t02.
3) We lock TheDatabase.t01 for read, and we lock
TheDatabase.t02 for read.
4) We do an all-AMPs SUM step to aggregate from
TheDatabase.t02 by way of an all-rows scan with no
residual conditions. Aggregate Intermediate Results are computed
globally, then placed in Spool 3.
5) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
an all-rows scan into Spool 2 (all_amps), which is duplicated on
all AMPs. The size of Spool 2 is estimated with high confidence
to be 72 rows (1,224 bytes).
6) We do an all-AMPs JOIN step from TheDatabase.t01 by way
of an all-rows scan with no residual conditions, which is joined
to Spool 2 (Last Use) by way of an all-rows scan.
TheDatabase.t01 and Spool 2 are joined using an
inclusion product join, with a join condition of (
"TheDatabase.t01.DAY_DATE >= OneDate"). The
result goes into Spool 1 (group_amps), which is built locally on
the AMPs. The size of Spool 1 is estimated with low confidence to be
32,482 rows (1,006,942 bytes). The estimated time for this
step is 3.77 seconds.
7) 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 execution plan has been revised, allowing the Optimizer to utilize the statistics effectively. As a result, the estimations precisely match the number of rows in the result set.
The product join in the updated execution plan is insignificant due to the limited amount of data. However, what holds greater significance is the improved estimations. Since the “calendar view part” is frequently cross-joined in various reports, precise estimates are imperative.
I hope this case study encourages you to delve further into the intricacies of Optimizer. As previously noted, the “SHOW STATISTICS” command offers a plethora of valuable information.
The optimizer can also be asked directly: DIAGNOSTIC HELPSTATS reports which statistics it wanted for a given plan.
Biased Value Histograms for Accurate Estimates
Biased histogram entries store accurate counts and should be used to update statistics reflecting the number of rows obtained for that particular value.
The Optimizer can rapidly ascertain the number of retrieved rows by referencing the histogram’s bias value.
Regrettably, this is not always true.
I generated a test scenario by populating a table with 50,000 rows, each with one of 100 unique dates.
CREATE MULTISET TABLE Statistics_Estimation1
(
TheDate DATE FORMAT ‘YYYY-MM-DD’ NOT NULL,
) PRIMARY INDEX (TheDate);
INSERT INTO Statistics_Estimation1
SELECT DATE’2010-01-01′ + (ROW_NUMBER() OVER (ORDER BY 1) MOD 100) AS TheDate,
FROM SYS_CALENDAR.CALENDAR
SAMPLE 50000;
I executed the following statement to generate a new statistics index. The system can condense 50,000 rows into 100 histograms, which will be saved as biased values.
Ensuring the estimated number of rows matches the retrieved number of rows is crucial in our test setup.
COLLECT STATS ON Statistics_Estimation1 COLUMN(TheDate);The histograms can be checked using the following statement:
SHOW STATISTICS VALUES ON Statistics_Estimation1 ;
/** Biased: Value, Frequency **/
/* 1 */ DATE ‘2010-01-01’, 515,
/* 2 */ DATE ‘2010-01-02’, 498,
/* 3 */ DATE ‘2010-01-03’, 510,
/* 4 */ DATE ‘2010-01-04’, 482,
/* 5 */ DATE ‘2010-01-05’, 490,
/* 6 */ DATE ‘2010-01-06’, 518,
/* 7 */ DATE ‘2010-01-07’, 510,
/* 8 */ DATE ‘2010-01-08’, 512,
/* 9 */ DATE ‘2010-01-09’, 493,
/* 10 */ DATE ‘2010-01-10’, 513,
/* 11 */ DATE ‘2010-01-11’, 498,
/* 12 */ DATE ‘2010-01-12’, 513,
/* 13 */ DATE ‘2010-01-13’, 509,
/* 14 */ DATE ‘2010-01-14’, 500,
/* 15 */ DATE ‘2010-01-15’, 491,
/* 16 */ DATE ‘2010-01-16’, 489,
/* 17 */ DATE ‘2010-01-17’, 481,
/* 18 */ DATE ‘2010-01-18’, 508,
/* 19 */ DATE ‘2010-01-19’, 496,
/* 20 */ DATE ‘2010-01-20’, 505,
/* 21 */ DATE ‘2010-01-21’, 498,
/* 22 */ DATE ‘2010-01-22’, 496,
/* 23 */ DATE ‘2010-01-23’, 505,
/* 24 */ DATE ‘2010-01-24’, 504,
/* 25 */ DATE ‘2010-01-25’, 485,
/* 26 */ DATE ‘2010-01-26’, 508,
/* 27 */ DATE ‘2010-01-27’, 494,
/* 28 */ DATE ‘2010-01-28’, 507,
/* 29 */ DATE ‘2010-01-29’, 495,
/* 30 */ DATE ‘2010-01-30’, 501,
/* 31 */ DATE ‘2010-01-31’, 486,
/* 32 */ DATE ‘2010-02-01’, 517,
/* 33 */ DATE ‘2010-02-02’, 501,
/* 34 */ DATE ‘2010-02-03’, 505,
/* 35 */ DATE ‘2010-02-04’, 496,
/* 36 */ DATE ‘2010-02-05’, 514,
/* 37 */ DATE ‘2010-02-06’, 492,
/* 38 */ DATE ‘2010-02-07’, 496,
/* 39 */ DATE ‘2010-02-08’, 504,
/* 40 */ DATE ‘2010-02-09’, 486,
/* 41 */ DATE ‘2010-02-10’, 509,
/* 42 */ DATE ‘2010-02-11’, 490,
/* 43 */ DATE ‘2010-02-12’, 499,
/* 44 */ DATE ‘2010-02-13’, 487,
/* 45 */ DATE ‘2010-02-14’, 513,
/* 46 */ DATE ‘2010-02-15’, 517,
/* 47 */ DATE ‘2010-02-16’, 498,
/* 48 */ DATE ‘2010-02-17’, 494,
/* 49 */ DATE ‘2010-02-18’, 484,
/* 50 */ DATE ‘2010-02-19’, 500,
/* 51 */ DATE ‘2010-02-20’, 488,
/* 52 */ DATE ‘2010-02-21’, 483,
/* 53 */ DATE ‘2010-02-22’, 484,
/* 54 */ DATE ‘2010-02-23’, 497,
/* 55 */ DATE ‘2010-02-24’, 487,
/* 56 */ DATE ‘2010-02-25’, 493,
/* 57 */ DATE ‘2010-02-26’, 515,
/* 58 */ DATE ‘2010-02-27’, 479,
/* 59 */ DATE ‘2010-02-28’, 505,
/* 60 */ DATE ‘2010-03-01’, 518,
/* 61 */ DATE ‘2010-03-02’, 491,
/* 62 */ DATE ‘2010-03-03’, 484,
/* 63 */ DATE ‘2010-03-04’, 510,
/* 64 */ DATE ‘2010-03-05’, 511,
/* 65 */ DATE ‘2010-03-06’, 494,
/* 66 */ DATE ‘2010-03-07’, 493,
/* 67 */ DATE ‘2010-03-08’, 500,
/* 68 */ DATE ‘2010-03-09’, 531,
/* 69 */ DATE ‘2010-03-10’, 497,
/* 70 */ DATE ‘2010-03-11’, 512,
/* 71 */ DATE ‘2010-03-12’, 481,
/* 72 */ DATE ‘2010-03-13’, 500,
/* 73 */ DATE ‘2010-03-14’, 510,
/* 74 */ DATE ‘2010-03-15’, 487,
/* 75 */ DATE ‘2010-03-16’, 511,
/* 76 */ DATE ‘2010-03-17’, 515,
/* 77 */ DATE ‘2010-03-18’, 513,
/* 78 */ DATE ‘2010-03-19’, 517,
/* 79 */ DATE ‘2010-03-20’, 516,
/* 80 */ DATE ‘2010-03-21’, 516,
/* 81 */ DATE ‘2010-03-22’, 504,
/* 82 */ DATE ‘2010-03-23’, 493,
/* 83 */ DATE ‘2010-03-24’, 510,
/* 84 */ DATE ‘2010-03-25’, 486,
/* 85 */ DATE ‘2010-03-26’, 495,
/* 86 */ DATE ‘2010-03-27’, 497,
/* 87 */ DATE ‘2010-03-28’, 481,
/* 88 */ DATE ‘2010-03-29’, 501,
/* 89 */ DATE ‘2010-03-30’, 486,
/* 90 */ DATE ‘2010-03-31’, 512,
/* 91 */ DATE ‘2010-04-01’, 487,
/* 92 */ DATE ‘2010-04-02’, 501,
/* 93 */ DATE ‘2010-04-03’, 490,
/* 94 */ DATE ‘2010-04-04’, 508,
/* 95 */ DATE ‘2010-04-05’, 518,
/* 96 */ DATE ‘2010-04-06’, 478,
/* 97 */ DATE ‘2010-04-07’, 494,
/* 98 */ DATE ‘2010-04-08’, 503,
/* 99 */ DATE ‘2010-04-09’, 494,
/* 100 */ DATE ‘2010-04-10’, 512,
Now we execute the explain for the below query:
Explain SELECT TheDate FROM Statistics_Estimation1 WHERE TheDate BETWEEN DATE’2010-02-14′ AND DATE’2010-02-16′;
1) First, we do a single-AMP RETRIEVE step from
DWHPRO.Statistics_Estimation1 by way of the primary index
“DWHPRO.Statistics_Estimation1.TheDate = DATE ‘2010-02-14′” with
no residual conditions into Spool 1 (group_amps), which is built
locally on that AMP. The size of Spool 1 is estimated with high
confidence to be 510 rows (15,810 bytes). The estimated time for
this step is 0.01 seconds.
2) Next, we do a single-AMP RETRIEVE step from
DWHPRO.Statistics_Estimation1 by way of the primary index
“DWHPRO.Statistics_Estimation1.TheDate = DATE ‘2010-02-15′” with
no residual conditions into Spool 1 (group_amps), which is built
locally on that AMP. The size of Spool 1 is estimated with high
confidence to be 1,020 rows (31,620 bytes). The estimated time
for this step is 0.01 seconds.
3) We do a single-AMP RETRIEVE step from
DWHPRO.Statistics_Estimation1 by way of the primary index
“DWHPRO.Statistics_Estimation1.TheDate = DATE ‘2010-02-16′” with
no residual conditions into Spool 1 (group_amps), which is built
locally on that AMP. The size of Spool 1 is estimated with high
confidence to be 1,530 rows (47,430 bytes). The estimated time
for this step is 0.01 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.02 seconds.
The Optimizer obtains primary index values through single-AMP primary index access because the selected rows are significantly fewer than the total number of rows in the table.
Did you know that the estimated count is 1530 rows? It’s peculiar because the total of the three histogram values for the dates (2010-02-14, 2010-02-15, and 2010-02-16) amounts to only 1528 rows!
/* 45 */ DATE ‘2010-02-14’, 513,
/* 46 */ DATE ‘2010-02-15’, 517,
/* 47 */ DATE ‘2010-02-16’, 498,
513+517+498 = 1528
Additionally, the Optimizer has estimated 510 rows for each single-AMP access. Is it possible that a bug exists within the Optimizer?
No.
Internal optimization occurs, resulting in a slightly imprecise but faster estimation. The Optimizer aggregates histogram values and calculates the mean by dividing the sum by their quantity. As in our example:
1528/3 = 509,33
The ceiling value determines the even distribution of the estimation among the single-AMP estimations, such as 510 – 510 – 510. Consequently, the estimation is slightly inaccurate.
We can demonstrate this by increasing the retrieved row count, compelling the Optimizer to perform a full table scan rather than individual-AMP lookups.
SELECT TheDate FROM Statistics_Estimation1 WHERE TheDate BETWEEN DATE'2010-02-14' AND DATE'2010-02-28';The plan changes when the date range is extended to 2010-02-28.
Explain SELECT TheDate FROM Statistics_Estimation1 WHERE TheDate BETWEEN DATE’2010-02-14′ AND DATE’2010-02-28′;
1) First, we lock DWHPRO.Statistics_Estimation1 for read on a
reserved RowHash to prevent global deadlock.
2) Next, we lock DWHPRO.Statistics_Estimation1 for read.
3) We do an all-AMPs RETRIEVE step from DWHPRO.Statistics_Estimation1
by way of an all-rows scan with a condition of (
“(DWHPRO.Statistics_Estimation1.TheDate >= DATE ‘2010-02-14’) AND
(DWHPRO.Statistics_Estimation1.TheDate <= DATE ‘2010-02-28’)”)
into Spool 1 (group_amps), which is built locally on the AMPs.
The size of Spool 1 is estimated with high confidence to be 7,438
rows (230,578 bytes). The estimated time for this step is 0.11
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.11 seconds.
Summing up the histogram values below confirms that the estimation of 7,438 matches the number of retrieved rows precisely.
/* 45 */ DATE ‘2010-02-14’, 513,
/* 46 */ DATE ‘2010-02-15’, 517,
/* 47 */ DATE ‘2010-02-16’, 498,
/* 48 */ DATE ‘2010-02-17’, 494,
/* 49 */ DATE ‘2010-02-18’, 484,
/* 50 */ DATE ‘2010-02-19’, 500,
/* 51 */ DATE ‘2010-02-20’, 488,
/* 52 */ DATE ‘2010-02-21’, 483,
/* 53 */ DATE ‘2010-02-22’, 484,
/* 54 */ DATE ‘2010-02-23’, 497,
/* 55 */ DATE ‘2010-02-24’, 487,
/* 56 */ DATE ‘2010-02-25’, 493,
/* 57 */ DATE ‘2010-02-26’, 515,
/* 58 */ DATE ‘2010-02-27’, 479,
/* 59 */ DATE ‘2010-02-28’, 505,
When analyzing an execution plan, strive to understand how the estimations are constructed. This skill improves with practice, allowing you to tackle statistical problems proficiently.
Also, check out:
Teradata Statistics – Case Study
Extrapolating Teradata Statistics for Equality Predicates
Alongside the detailed histogram, Teradata keeps a summary record – what summary statistics store and when the optimizer falls back to them.
Join Estimation Without Statistics
What is Teradata Join Estimation?
This article demonstrates the functioning of Teradata Join Estimation in the absence of statistics. It presents the heuristics employed to estimate row count and emphasizes the importance of collecting statistics on all join columns.
Teradata Join Estimation Heuristics
The worst-case scenario involves joining two tables without any collected statistics. We create both tables from scratch and insert rows without collecting summary statistics at the table level. We populate the two tables by drawing from a dummy table that is the foundation for all our examples.
CREATE SET TABLE DWHPRO.STATS1 ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
MAP = TD_MAP1
(
PI INTEGER NOT NULL,
COL1 INTEGER NOT NULL,
COL2 INTEGER NOT NULL)
PRIMARY INDEX ( PI );We add 50 rows to the stats1 table.
CREATE SET TABLE DWHPRO.STATS2 ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
MAP = TD_MAP1
(
PI INTEGER NOT NULL,
COL1 INTEGER NOT NULL,
COL2 INTEGER NOT NULL)
PRIMARY INDEX ( PI );We add 150 rows to the stats2 table.
The SHOW STATISTICS output indicates that neither table has any statistics. Consequently, the Optimizer needs to estimate the cardinality of both tables by conducting a random AMP sample over the primary index.
SHOW STATISTICS VALUES ON STATS1;
[3624] There are no statistics defined for the table
SHOW STATISTICS VALUES ON STATS2;
[3624] There are no statistics defined for the table We will execute a basic INNER JOIN of the two tables on an unindexed column.
EXPLAIN SELECT * FROM STATS2 inner JOIN STATS1 ON STATS1.COL1 = STATS2.COL1;
1) First, we lock DWHPRO.STATS1 in TD_MAP1 for read on a reserved
RowHash to prevent global deadlock.
2) Next, we lock DWHPRO.STATS2 in TD_MAP1 for read on a reserved
RowHash to prevent global deadlock.
3) We lock DWHPRO.STATS1 in TD_MAP1 for read, and we lock
DWHPRO.STATS2 in TD_MAP1 for read.
4) We do an all-AMPs RETRIEVE step in TD_MAP1 from DWHPRO.STATS1 by
way of an all-rows scan with no residual conditions into Spool 2
(all_amps), which is duplicated on all AMPs in TD_Map1. The size
of Spool 2 is estimated with low confidence to be 96 rows (2,400
bytes). The estimated time for this step is 0.00 seconds.
5) We do an all-AMPs JOIN step in TD_Map1 from Spool 2 (Last Use) by
way of an all-rows scan, which is joined to DWHPRO.STATS2 by way
of an all-rows scan with no residual conditions. Spool 2 and
DWHPRO.STATS2 are joined using a dynamic hash join, with a join
condition of ("COL1 = DWHPRO.STATS2.COL1"). The result goes into
Spool 1 (group_amps), which is built locally on the AMPs. The
size of Spool 1 is estimated with no confidence to be 600 rows (
27,000 bytes). The estimated time for this step is 0.01 seconds.
6) 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.01 seconds.The execution plan forecasts a result set with approximately 600 rows, while Table Stats1 is anticipated to contain roughly 96 rows. Due to table replication across 2 AMPs, this number must be halved. As such, the Optimizer predicts 48 rows in Table Stats1, which aligns closely with the row count of 50.
To get the number of estimated rows in Table Stats2, we can use the following trick:
EXPLAIN SELECT * FROM STATS2;
1) First, we lock DWHPRO.STATS2 in TD_MAP1 for read on a reserved
RowHash to prevent global deadlock.
2) Next, we lock DWHPRO.STATS2 in TD_MAP1 for read.
3) We do an all-AMPs RETRIEVE step in TD_MAP1 from DWHPRO.STATS2 by
way of an all-rows scan with no residual conditions into Spool 1
(group_amps), which is built locally on the AMPs. The size of
Spool 1 is estimated with low confidence to be 156 rows (5,148
bytes). The estimated time for this step is 0.01 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.01 seconds.The execution plan shows an estimated row count of 156.
How is the estimate for a result set of 600 rows determined? The estimate is based on the following heuristic approach:
SQRT(number of rows of the bigger table) * (Number of rows from the smaller table). The result is rounded up to the nearest whole number. (Many thanks to Diether Noeth, who gave me this information in the Teradata forum):
Let’s do the math for our example:
SQRT(156) = 12,489996 * 48 => 599,519808, rounded to 600.
If current statistics on the cardinality of the tables exist, these are used for the heuristic:
COLLECT STATS COLUMN(PI) ON STATS1;
COLLECT STATS COLUMN(PI) ON STATS2;
EXPLAIN SELECT * FROM STATS2 inner JOIN STATS1 ON STATS1.COL1 = STATS2.COL1
EXPLAIN SELECT * FROM STATS2 inner JOIN STATS1 ON STATS1.COL1 = STATS2.COL1;
1) First, we lock DWHPRO.STATS1 in TD_MAP1 for read on a reserved
RowHash to prevent global deadlock.
2) Next, we lock DWHPRO.STATS2 in TD_MAP1 for read on a reserved
RowHash to prevent global deadlock.
3) We lock DWHPRO.STATS1 in TD_MAP1 for read, and we lock
DWHPRO.STATS2 in TD_MAP1 for read.
4) We do an all-AMPs RETRIEVE step in TD_MAP1 from DWHPRO.STATS1 by
way of an all-rows scan with no residual conditions into Spool 2
(all_amps), which is duplicated on all AMPs in TD_Map1. The size
of Spool 2 is estimated with high confidence to be 100 rows (
2,500 bytes). The estimated time for this step is 0.00 seconds.
5) We do an all-AMPs JOIN step in TD_Map1 from Spool 2 (Last Use) by
way of an all-rows scan, which is joined to DWHPRO.STATS2 by way
of an all-rows scan with no residual conditions. Spool 2 and
DWHPRO.STATS2 are joined using a dynamic hash join, with a join
condition of ("COL1 = DWHPRO.STATS2.COL1"). The result goes into
Spool 1 (group_amps), which is built locally on the AMPs. The
size of Spool 1 is estimated with no confidence to be 613 rows (
27,585 bytes). The estimated time for this step is 0.01 seconds.
6) 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.01 seconds.The estimated result set has increased to 613 rows. Let us execute the query to see if the estimate has improved.
SELECT * FROM STATS2 inner JOIN STATS1 ON STATS1.COL1 = STATS2.COL1
-> SELECT completed. 0 rows returned.
The query produces no results, indicating that collecting statistics at the table level alone did not improve accuracy.
Comparing both execution plans can be confusing. In the first plan (without statistics), the retrieval step shows low confidence. In the second plan (with statistics), the retrieval step shows high confidence — yet the low-confidence estimate from the first plan was actually more accurate.
This is the introductory section on Join Estimations. Subsequent articles will delve into statistical analysis and its impact on the Optimizer’s estimates.
Incremental Planning and Execution (IPE)
Teradata IPE
Teradata traditionally generates execution plans using information available during query optimization. This information includes statistics and cost factors, such as storage devices, CPU models, and available memory.
The optimizer generates multiple execution plans and selects the one with the lowest cost.
Teradata 14.10 introduces Incremental Planning and Execution (IPE), which utilizes available information at query optimization and collects additional data during query execution.
IPE – Splitting the Execution Plan into Fragments
To gain greater statistical insight, the optimizer divides requests into “request fragments” rather than creating the entire execution plan simultaneously.
A “fragment execution plan” is generated for every request fragment, and the output of each plan fragment is passed on to the next one.
One query plan fragment may lead to a spool table where statistical information such as the number of rows can be fed into the execution of the subsequent plan fragment.
IPE is currently limited to:
- Uncorrelated scalar subqueries
- Single‑row accesses for UPI or USI
This is an example of an uncorrelated scalar subquery, which returns a single row and is not joined to another table:
TheTable t01 INNER JOIN TheTable2 t02 ON t01.Key = t02.Key
WHERE
t01.Col1 > (SELECT MIN(t03.Col1) FROM TheSubTable t03)
;
The optimizer can use IPE for the above statement by splitting the request into two request fragments:
(SELECT MIN(t03.Col1) FROM TheSubTable t03);
Fragment 2
SELECT * FROM
TheTable t01 INNER JOIN TheTable2 t02 ON t01.Key = t02.Key
WHERE
t01.Col1 > [Result of Fragment 1];
Feedback from a prior request fragment can activate sophisticated optimization methods, such as partition elimination.
The method employed for uncorrelated scalar subqueries is analogous to “hard coding” a predicate value within our query, thus enabling the optimizer to implement static partition elimination.
The technique discussed here generates execution plans that are referred to as “dynamic”.
The optimizer employs IPE for complex queries that are expected to take at least one minute to run. Parsing time amounts to less than 10% of the estimated run time. IPE does not replace the conventional static execution plan. A static plan is initially generated even when IPE is available, and the data derived from that static plan informs the decision to use IPE.
New query bands enable us to compel the optimizer to implement IPE for simple queries.
IPE Query Bands
Query bands can be utilized to either enable or disable IPE usage for eligible requests with estimated execution times under one minute.
– The system should determine eligibility (the default behavior):
SET QUERY_BAND = ‘DynamicPlan=SYSTEM;’ FOR SESSION;
– Bypasses the cost thresholds (one minute execution time). If IPE is available, Teradata will use it:
SET QUERY_BAND = ‘DynamicPlan=SYSTEMX;’ FOR SESSION;
– Turns off IPE for the session:
SET QUERY_BAND = ‘DynamicPlan=OFF;’ FOR SESSION;
How can we know if IPE is available?
Teradata has introduced a new option for the EXPLAIN modifier: DYNAMIC EXPLAIN. This option is needed to determine whether the optimizer uses the static or dynamic (IPE) execution plan.
Teradata displays the dynamic plan when the query is eligible for IPE; otherwise, it shows the static plan.
It is not necessary to always use DYNAMIC EXPLAIN to verify the execution plan because Teradata must execute every fragment in order to display the plan.
Starting from Teradata 14.10, the EXPLAIN modifier offers visibility on IPE utilization.
- Eligible for IPE:
This request is eligible for incremental planning and execution (IPE). The following is the static plan for the request.
- Eligible for IPE but does not meet cost thresholds (run time < 1 minute):
This request is eligible for incremental planning and execution (IPE) but does not meet cost thresholds. The following is the static plan for the request.
If the request is not eligible for IPE, the output of the EXPLAIN modifier will not mention it (i.e., the explained plan looks like in pre-Teradata 14.10 releases)
IPE and Workload Management
TASM does not fully support IPE as it enforces workload rules solely on the static plan, with workload exceptions being dependent on the dynamic plan.
Teradata 15.00 enables TASM to distinguish between requests that utilize IPE and those that do not, allowing for a straightforward categorization based on this distinction.
Statistics Extrapolation
Teradata introduced several new features, including one that caught our attention: object use counts (OUC). This feature optimizes the calculation of extrapolated statistics, improving query performance significantly. Before version 13.10, changes made by DML statements were not logged, and the optimizer relied solely on dynamic amp sampling, leading to incorrect estimates for skewed tables. Additionally, Teradata 13.10 and below relied on heuristics for extrapolation, which was a major drawback. This example will explain how Teradata 14.10 improved the extrapolation process. First, we create a test table, populate it with data, and gather statistics on relevant columns.
CREATE TABLE TheDB.TheTable
(
PK INTEGER NOT NULL,
TheCol CHAR(01)
) PRIMARY INDEX (PK);
INSERT INTO TheDB.TheTable
SELECT
ROW_NUMBER() OVER (ORDER BY 1) AS PK,
TRIM(CASE WHEN RANDOM(1,1000) <= 500 THEN 'A' ELSE 'B' END) AS TheCol,
FROM
(
SELECT NULL AS X FROM TheDB.AnyBigTable SAMPLE 200000
) x;
COLLECT STATISTICS ON TheDB.TheTable COLUMN(PK);
COLLECT STATISTICS ON TheDB.TheTable COLUMN(TheCol);Teradata Statistics Extrapolation with up to version 13.10
EXPLAIN SELECT * FROM TheDB.TheTable WHERE TheCol = 'C';
3) We do an all-AMPs RETRIEVE step from TheDB.TheTable by way of an all-rows scan with a condition of ("TheDB.TheTable.TheCol = 'C '") into Spool 1 (group_amps),
which is built locally on the AMPs. The size of Spool 1 is estimated with high confidence to be 20,000 rows (42,580,000 bytes).
The estimated time for this step is 0.31 seconds.We choose a value in the WHERE clause that is absent from the queried column. The optimizer endeavors to prevent underestimating the table’s cardinality.
The heuristic applied in this instance assumes that 10% of the rows in the result set will be present, even with high confidence. However, in this case, the accurate estimation is that there will be 0 rows. The rationale behind this approach is straightforward: overestimating the table size is preferable to underestimating it, as it will result in a conservative execution plan.
Teradata Statistics Extrapolation starting with Teradata version 14.10
Teradata version 14.10 introduced a novel feature, the UDI counts, which enables the optimizer to generate more accurate estimates. These counts record all alterations to tables carried out by DML statements, precisely determining changes made since the previous statistics collection.
The optimizer refrains from extrapolation as it correctly estimates the number of rows without any detected changes. However, in certain cases, it delivers exactly one row instead of delivering no rows, which has remained unexplained until now. Kindly comment in the blog post if you have insights on the matter. Here is the same select statement example executed on Teradata version 16.20:
3) We do an all-AMPs RETRIEVE step from TheDB.TheTable by way of
an all-rows scan with a condition of ("TheDB.TheTable.TheCol = 'C '") into Spool 1 (group_amps),
which is built locally on the AMPs. The size of Spool 1 is estimated with high confidence to be 1 row (1,079 bytes).
The estimated time for this step is 0.04 seconds.Based on the aforementioned data, we suggest activating Object Use Counts for all significant databases starting with Teradata 14.10. The advantages considerably outweigh the maintenance expenses. Object Use Counts utilize minimal resources as rows are cached and written periodically (typically every 10 minutes or when the cache reaches full capacity) to the DBC.ObjectUsage table. If disabled, newer Teradata versions entirely depend on dynamic AMP sampling.
Extrapolation only helps while the row count still moves; what happens when statistics go stale but the table does not grow is a separate problem.
Check Constraints and Row Estimates
Without Teradata CHECK Constraint
When a non-indexed column is utilized in a retrieval operation (i.e. WHERE statement) and lacks Teradata Check Constraints, the Optimizer employs heuristics to approximate the number of rows in the resulting set by inferring missing statistics.
The estimated number of rows is 7,342, equivalent to 10% of the table’s total rows. This heuristic is utilized by the Teradata optimizer when statistics on the non-indexed column are not available.
CREATE TABLE Heuristics_Without_Check
(
PK INTEGER NOT NULL,
GENDER BYTEINT NOT NULL
) PRIMARY INDEX (PK);
INSERT INTO Heuristics_Without_Check
SELECT
ROW_NUMBER() OVER (ORDER BY 1) AS PK,
RANDOM(0,1)
FROM SYS_CALENDAR.CALENDAR;
-> 73,414 Rows inserted
--> Collect PI Statistics to have the real row number:
COLLECT STATISTICS COLUMN(PK) ON Heuristics_Without_Check;
SELECT * FROM Heuristics_Without_Check WHERE GENDER = 0;
Explain SELECT * FROM Heuristics_Without_Check WHERE GENDER = 0;
1) First, we lock DWHPRO.Heuristics_Without_Check in TD_MAP1 for read
on a reserved RowHash to prevent global deadlock.
2) Next, we lock DWHPRO.Heuristics_Without_Check in TD_MAP1 for read.
3) We do an all-AMPs RETRIEVE step in TD_MAP1 from
DWHPRO.Heuristics_Without_Check by way of an all-rows scan with a
condition of ("DWHPRO.Heuristics_Without_Check.GENDER = 0") into
Spool 1 (group_amps), which is built locally on the AMPs. The
size of Spool 1 is estimated with no confidence to be 7,342 rows (
190,892 bytes). The estimated time for this step is 0.03 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.03 seconds.
But with a simple trick, the estimates can be massively improved even if the non-indexed column statistics are missing. The Optimizer uses existing CHECK constraints to optimize the estimates.
With CHECK Constraint
The following formula is used for this:
Estimated Rows = Total Rows x (1 / Possible Unique Values)
Since the RANDOM function we use to load our test table only returns the values 0 and 1, we can add the following CHECK constraint to our test table:
CREATE TABLE Heuristics_With_Check
(
PK INTEGER NOT NULL,
GENDER BYTEINT NOT NULL CHECK (GENDER IN (0,1))
) PRIMARY INDEX (PK);
INSERT INTO Heuristics_With_Check
SELECT
ROW_NUMBER() OVER (ORDER BY 1) AS PK,
RANDOM(0,1)
FROM SYS_CALENDAR.CALENDAR;
-> 73,414 Rows inserted
--> Collect PI Statistics to have the real row number:
COLLECT STATISTICS COLUMN(PK) ON Heuristics_With_Check;Applying the formula above produces the following estimated row count for the result set.
Estimated rows = 73,414 * (1 / 2) = 36,707 rows
Examining the Execution Plan:
SELECT * FROM Heuristics_With_Check WHERE GENDER = 0;
Explain SELECT * FROM Heuristics_With_Check WHERE GENDER = 0;
1) First, we lock DWHPRO.Heuristics_With_Check in TD_MAP1 for read on
a reserved RowHash to prevent global deadlock.
2) Next, we lock DWHPRO.Heuristics_With_Check in TD_MAP1 for read.
3) We do an all-AMPs RETRIEVE step in TD_MAP1 from
DWHPRO.Heuristics_With_Check by way of an all-rows scan with a
condition of ("DWHPRO.Heuristics_With_Check.GENDER = 0") into
Spool 1 (group_amps), which is built locally on the AMPs. The
size of Spool 1 is estimated with low confidence to be 36,707 rows
(954,382 bytes). The estimated time for this step is 0.07 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.07 seconds.
We execute the query on the table with the CHECK constraint and obtain a more accurate estimate of 36,707 rows. This estimate is vastly superior to the 10% heuristic. The number of rows closely aligns with the estimation because the RANDOM function generates a roughly equal number of zeros and ones. Adopting this approach can considerably enhance estimate precision.
SELECT COUNT(*) FROM Heuristics_With_Check WHERE GENDER = 0;
--> 36,711 rowsA Warning when using CHECK constraints
Do not define CHECK constraints solely to improve estimates. Keep in mind that validating the constraints requires extra processing and may trigger the creation of secondary indexes.
More information about Teradata CHECK constraints you can find here:
Check Constraints explained in Detail
Five shorter pieces cover individual parts of this in more detail: the practical rules for what to collect, judging whether a statistic was worth collecting, reading StatsUsageCountV to find unused statistics, how extrapolation fills the gap between collections and why some skew is natural and some is an artefact.
Two more on measuring what the optimizer knows: pulling query statistics out of the system tables and a worked example of statistics gone wrong.
Two more on collection itself: when a multi-column statistic earns its keep and the basics of how Teradata stores a statistic.
Related Services
⚡ Need Help Optimizing Your Data Platform?
We cut data platform costs by 30–60% without hardware changes. 25+ years of hands-on tuning experience.
Explore Our Services →📋 Considering a Move From Teradata?
Get a personalized migration roadmap in 2 minutes. We have migrated billions of rows from Teradata to Snowflake, Databricks, and more.
Free Migration Assessment →