Join Estimations

Database
N/A

Join Estimations

Hi,


I experience a strange behaviour (TD 15.10) in the estimation of a join step. 

I was creating two tables with 100.000 rows and exactly the same content, and added primary index statistics. The primary index column is an almost unique INTEGER. Here is an excerpt from the SHOW STATISTICS table for both tables:

 /* OneAMPSampleEst */ 100002,
/* AllAMPSampleEst */ 100002,
/* RowCount */ 100000,


and

/* OneAMPSampleEst */ 100002,
/* AllAMPSampleEst */ 100002,
/* RowCount */ 100000,

I join each of them (primary index) to a skewed table:

/* OneAMPSampleEst */ 188918,
/* AllAMPSampleEst */ 100127,
/* RowCount */ 100000,

The skewed value is 88606 time in this table.

/** Biased: Value, Frequency **/
/* 1 */ 999, 88606,



SELECT * FROM  SKEWED T01 INNER JOIN BIGTABLE2 T02 ON     t01.PK = t02.PK

If i run this query  I have the following estimation:

1) First, we lock DWHPRO.T01 for read on a reserved RowHash to
prevent global deadlock.
2) Next, we lock DWHPRO.T02 for read on a reserved RowHash to prevent
global deadlock.
3) We lock DWHPRO.T01 for read, and we lock DWHPRO.T02 for read.
4) We do an all-AMPs JOIN step from DWHPRO.T02 by way of a RowHash
match scan with no residual conditions, which is joined to
DWHPRO.T01 by way of a RowHash match scan with no residual
conditions. DWHPRO.T02 and DWHPRO.T01 are joined using a merge
join, with a join condition of ("DWHPRO.T01.PK = DWHPRO.T02.PK").
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 100,000 rows (29,700,000 bytes). The estimated time for
this step is 4.26 seconds.
5) 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 4.26 seconds.

I ran the same for the second table: SELECT * FROM SKEWED T01 INNER JOIN BIGTABLE1 T02 ON t01.PK = t02.PK

1) First, we lock DWHPRO.T01 for read on a reserved RowHash to
prevent global deadlock.
2) Next, we lock DWHPRO.T02 for read on a reserved RowHash to prevent
global deadlock.
3) We lock DWHPRO.T01 for read, and we lock DWHPRO.T02 for read.
4) We do an all-AMPs JOIN step from DWHPRO.T02 by way of a RowHash
match scan with no residual conditions, which is joined to
DWHPRO.T01 by way of a RowHash match scan with no residual
conditions. DWHPRO.T02 and DWHPRO.T01 are joined using a merge
join, with a join condition of ("DWHPRO.T01.PK = DWHPRO.T02.PK").
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 100,000 rows (29,700,000 bytes). The estimated time for
this step is 4.26 seconds.
5) 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 4.26 seconds.

The estimation is the same.

Now i updated 50 rows of the first table to match the skewed value of the skewed table

UPDATE BigTable1 SET PK = 999 WHERE PK BETWEEN 1 AND 50;

I recollected the statistics on the primary index.

After reruning the join, i ended up with this estimation:

1) First, we lock DWHPRO.T01 for read on a reserved RowHash to
prevent global deadlock.
2) Next, we lock DWHPRO.T02 for read on a reserved RowHash to prevent
global deadlock.
3) We lock DWHPRO.T01 for read, and we lock DWHPRO.T02 for read.
4) We do an all-AMPs JOIN step from DWHPRO.T02 by way of a RowHash
match scan with no residual conditions, which is joined to
DWHPRO.T01 by way of a RowHash match scan with no residual
conditions. DWHPRO.T02 and DWHPRO.T01 are joined using a merge
join, with a join condition of ("DWHPRO.T01.PK = DWHPRO.T02.PK").
The result goes into Spool 1 (group_amps), which is built locally
on the AMPs. The result spool file will not be cached in memory.
The size of Spool 1 is estimated with low confidence to be
10,732,720 rows (3,187,617,840 bytes). The estimated time for
this step is 5 minutes and 1 second.
5) 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 5 minutes and 1 second.

As you can see: The estimation went from 100,000 to 10 Million!
Does anybody know the details behind the join cardinality estimations? I would like to understand how skew is considered in join estimation.

Thanks in Advance
Roland

Roland Wenzlofsky

Accepted Solutions
Teradata Employee

Re: Join Estimations

That estimate is a bit high but each of the 50 rows will join to all of the 88606 rows which makes a 4,430,300 result just from the skewed rows. Then the rest of the rows in the two tables will be added as they join. Optimizer perhaps assumes that the skew estimate is low when it computes the total estimate.
1 ACCEPTED SOLUTION
1 REPLY
Teradata Employee

Re: Join Estimations

That estimate is a bit high but each of the 50 rows will join to all of the 88606 rows which makes a 4,430,300 result just from the skewed rows. Then the rest of the rows in the two tables will be added as they join. Optimizer perhaps assumes that the skew estimate is low when it computes the total estimate.