Incorrect Teradata explain estimates

Database
Enthusiast

Incorrect Teradata explain estimates

Hi,

I have a query where teradata expain is giving wrong esitmated time though the query is taking very few seconds.

It is estimating 9 mins due to which it is getting throttled.  Issue is in step 7. All the stats are ok and refreshed.

Could anyone pleae suggest what might be wrong.

DELETE FROM

    database1.TABLE1

WHERE

    SOURCE_SYSTEM_ID = '0009' 

    AND INVOICE_DATE>=

          ( SELECT MIN(INVOICE_DATE) 

               FROM db2.TAB2

               WHERE  SOURCE_SYSTEM_ID = '0009' )

AND     (REGID < 11   OR  REGID=20);

 This request is eligible for incremental planning and execution (IPE).

 The following is the static plan for the request.

  1) First, we lock a distinct db2."pseudo table" for read on a

     RowHash to prevent global deadlock for db2.TAB2.

  2) Next, we lock a distinct database1."pseudo table" for write on a

     RowHash to prevent global deadlock for

     database1.TABLE1.

  3) We lock db2.TAB2 for read, and we lock

     database1.TABLE1 for write.

  4) We do an all-AMPs SUM step to aggregate from

     db2.TAB2 by way of an all-rows scan with a

     condition of ("db2.TAB2.SOURCE_SYSTEM_ID = '0009'").

     Aggregate Intermediate Results are computed globally, then placed

     in Spool 3.  The size of Spool 3 is estimated with high confidence

     to be 1 row (19 bytes).  The estimated time for this step is 0.23

     seconds.

  5) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of

     an all-rows scan into Spool 1 (all_amps), which is 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.01 seconds.

  6) 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.

  7) We do an all-AMPs DELETE from a single partition of

     database1.TABLE1 with a condition of (

     "database1.TABLE1.SOURCE_SYSTEM_ID = '0009'") with a

     residual condition of (

     "(database1.TABLE1.SOURCE_SYSTEM_ID = '0009') AND

     ((database1.TABLE1.INVOICE_DATE >= :%SSQ20) AND

     ((database1.TABLE1.REGID < 11) OR

     (database1.TABLE1.REGID = 20 )))").  The size is

     estimated with no confidence to be 3,534,316 rows.  The estimated

     time for this step is 9 minutes and 56 seconds.

  8) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> No rows are returned to the user as the result of statement 1.

     The total estimated time is 9 minutes and 56 seconds.

Tags (2)
1 REPLY
Enthusiast

Re: Incorrect Teradata explain estimates

The time it takes for a query to execute has nothing to do with the labels used in teh explain plan.

There is absolutey no correlation between the labels on the cost values in an explain plan and clock time.  The words "days", "hours", "minutes" and "seconds" are all pronounced the same way when evaluating an explain plan, kost.  

Would you please post the table DDL and query, if you are able?

Cheers,