Archives of the TeradataForum

Message Posted: Thu, 31 Mar 2005 @ 21:49:59 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: The story of a wild query...

From:		Frank C. Martinez IV

Hello all!

Ok, I'll explain it another (more mundane) way. I was tracking down a product join which has been occurring for several users. There are two tables with a non-trivial number of rows (The account table has 457,602,531, the other has 434,404,251). We have these partitioned by the expiration date of the record, so at first I was worried that the partioning wasn't being used. That was not the case. When I reduce the query to just dealing with the account table, removing grouping, and just making it plain simple, it looks like this, with the corresponding explain (you didn't really want me to put the whole thing in there, right? Isn't this enough?)

     select a.C2t_Acct_Ky,
       a.C2t_Acct_Opn_Dt,
       a.C2t_Acct_Prod_Ky
      from T_C2T_ACCT a
      where a.C2t_Acct_Ky_Expir_Dt = '9999-12-31'
       and a.C2t_Acct_Opn_Dt between '2005-02-01' and '2005-02-28'
       and a.c2t_acct_con_acct_flg = 1
       and a.C2t_Crted_Src_Acct_Sys = 'BILLMGR'
       and a.C2t_Acct_Stat in ('NEW', 'REENROLLED')
       and a.C2t_Acct_Bm_Bra_Id = 725

       2) Next, we do an all-AMPs RETRIEVE step from a single partition of
          DTNG_C2T.T_C2T_ACCT with a condition of (
          "DTNG_C2T.T_C2T_ACCT.C2t_Acct_Ky_Expir_Dt = DATE '9999-12-31'")
          with a residual condition of (
          "(DTNG_C2T.T_C2T_ACCT.C2t_Acct_Bm_Bra_Id = 725) AND
          (((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Stat = 'NEW') OR
          (DTNG_C2T.T_C2T_ACCT.C2t_Acct_Stat = 'REENROLLED')) AND
          ((DTNG_C2T.T_C2T_ACCT.C2t_Crted_Src_Acct_Sys = 'BILLMGR') AND
          ((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Con_Acct_Flg = 1) AND
          ((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Ky_Expir_Dt = DATE '9999-12-31')
          AND ((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Opn_Dt <= DATE '2005-02-28')
          AND (DTNG_C2T.T_C2T_ACCT.C2t_Acct_Opn_Dt >= DATE
          '2005-02-01'))))))") into Spool 1 (group_amps), which is built
          locally on the AMPs.  The input table will not be cached in memory,
          but it is eligible for synchronized scanning.  The size of Spool 1
          is estimated with low confidence to be 14 rows.  The estimated
          time for this step is 1 minute and 46 seconds.

If I change the query to just look at the partition, the confidence changes to high and it has a wonderful (if large) estimate.

     select a.C2t_Acct_Ky,
       a.C2t_Acct_Opn_Dt,
       a.C2t_Acct_Prod_Ky
      from T_C2T_ACCT a
      where a.C2t_Acct_Ky_Expir_Dt = '9999-12-31'

       2) Next, we do an all-AMPs RETRIEVE step from a single partition of
          DTNG_C2T.T_C2T_ACCT with a condition of (
          "DTNG_C2T.T_C2T_ACCT.C2t_Acct_Ky_Expir_Dt = DATE '9999-12-31'")
          with a residual condition of (
          "DTNG_C2T.T_C2T_ACCT.C2t_Acct_Ky_Expir_Dt = DATE '9999-12-31'")
          into Spool 1 (group_amps), which is built locally on the AMPs.
          The input table will not be cached in memory, but it is eligible
          for synchronized scanning.  The size of Spool 1 is estimated with
          high confidence to be 90,061,104 rows.  The estimated time for
          this step is 2 minutes.

If I comment out the date check, while the confidence drops to low, it still estimates well enough that if I used these conditions in my original query, it uses a different plan. You'll notice that the explain here estimates around 100,000 rows coming back.

     select a.C2t_Acct_Ky,
       a.C2t_Acct_Opn_Dt,
       a.C2t_Acct_Prod_Ky
      from T_C2T_ACCT a
      where a.C2t_Acct_Ky_Expir_Dt = '9999-12-31'
     /*  and a.C2t_Acct_Opn_Dt between '2005-02-01' and '2005-02-28' */
       and a.c2t_acct_con_acct_flg = 1
       and a.C2t_Crted_Src_Acct_Sys = 'BILLMGR'
       and a.C2t_Acct_Stat in ('NEW', 'REENROLLED')
       and a.C2t_Acct_Bm_Bra_Id = 725

       2) Next, we do an all-AMPs RETRIEVE step from a single partition of
          DTNG_C2T.T_C2T_ACCT with a condition of (
          "DTNG_C2T.T_C2T_ACCT.C2t_Acct_Ky_Expir_Dt = DATE '9999-12-31'")
          with a residual condition of (
          "(DTNG_C2T.T_C2T_ACCT.C2t_Acct_Bm_Bra_Id = 725) AND
          (((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Stat = 'NEW') OR
          (DTNG_C2T.T_C2T_ACCT.C2t_Acct_Stat = 'REENROLLED')) AND
          ((DTNG_C2T.T_C2T_ACCT.C2t_Crted_Src_Acct_Sys = 'BILLMGR') AND
          ((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Con_Acct_Flg = 1) AND
          (DTNG_C2T.T_C2T_ACCT.C2t_Acct_Ky_Expir_Dt = DATE = '9999-12-31'))))")
          into Spool 1 (group_amps), which is built locally on the AMPs.
          The input table will not be cached in memory, but it is eligible
          for synchronized scanning.  The size of Spool 1 is estimated with
          low confidence to be 905,264 rows.  The estimated time for this
          step is 1 minute and 46 seconds.

The actual number of rows (replacing the columns selected with a count(*) is 1,728,378. While it's an order of magnitude off, it's still in the ballpark enough that on the more complex query, the optimizer tries to cut down the number of rows by first doing other stuff (joining to other tables) before doing the product join.

If I change the date condition to select one day out of the month, the following plan is produced:

     select a.C2t_Acct_Ky,
       a.C2t_Acct_Opn_Dt,
       a.C2t_Acct_Prod_Ky
      from T_C2T_ACCT a
      where a.C2t_Acct_Ky_Expir_Dt = '9999-12-31'
       and a.C2t_Acct_Opn_Dt = /* between */ '2005-02-01' /* and '2005-02-28' */
       and a.c2t_acct_con_acct_flg = 1
       and a.C2t_Crted_Src_Acct_Sys = 'BILLMGR'
       and a.C2t_Acct_Stat in ('NEW', 'REENROLLED')
       and a.C2t_Acct_Bm_Bra_Id = 725

       2) Next, we do an all-AMPs RETRIEVE step from a single partition of
          DTNG_C2T.T_C2T_ACCT with a condition of (
          "DTNG_C2T.T_C2T_ACCT.C2t_Acct_Ky_Expir_Dt = DATE '9999-12-31'")
          with a residual condition of (
          "(DTNG_C2T.T_C2T_ACCT.C2t_Acct_Bm_Bra_Id = 725) AND
          (((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Stat = 'NEW') OR
          (DTNG_C2T.T_C2T_ACCT.C2t_Acct_Stat = 'REENROLLED')) AND
          ((DTNG_C2T.T_C2T_ACCT.C2t_Crted_Src_Acct_Sys = 'BILLMGR') AND
          ((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Con_Acct_Flg = 1) AND
          ((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Opn_Dt = DATE '2005-02-01') AND
          (DTNG_C2T.T_C2T_ACCT.C2t_Acct_Ky_Expir_Dt = DATE = '9999-12-31')))))")
          into Spool 1 (group_amps), which is built locally on the AMPs.
          The input table will not be cached in memory, but it is eligible
          for synchronized scanning.  The size of Spool 1 is estimated with
          low confidence to be 761 rows.  The estimated time for this step
          is 1 minute and 46 seconds.

Well, this is also low confidence, but close to the actual figure of 3,039 rows. However, with the between, which by any shred of common sense should have more rows, the explain gets a little weird. Instead of having MORE rows in the estimate (a whole month probably having more records than a single day), it has less!

     select a.C2t_Acct_Ky,
       a.C2t_Acct_Opn_Dt,
       a.C2t_Acct_Prod_Ky
      from T_C2T_ACCT a
      where a.C2t_Acct_Ky_Expir_Dt = '9999-12-31'
       and a.C2t_Acct_Opn_Dt between '2005-02-01' and '2005-02-28'
       and a.c2t_acct_con_acct_flg = 1
       and a.C2t_Crted_Src_Acct_Sys = 'BILLMGR'
       and a.C2t_Acct_Stat in ('NEW', 'REENROLLED')
       and a.C2t_Acct_Bm_Bra_Id = 725

       2) Next, we do an all-AMPs RETRIEVE step from a single partition of
          DTNG_C2T.T_C2T_ACCT with a condition of (
          "DTNG_C2T.T_C2T_ACCT.C2t_Acct_Ky_Expir_Dt = DATE '9999-12-31'")
          with a residual condition of (
          "(DTNG_C2T.T_C2T_ACCT.C2t_Acct_Bm_Bra_Id = 725) AND
          (((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Stat = 'NEW') OR
          (DTNG_C2T.T_C2T_ACCT.C2t_Acct_Stat = 'REENROLLED')) AND
          ((DTNG_C2T.T_C2T_ACCT.C2t_Crted_Src_Acct_Sys = 'BILLMGR') AND
          ((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Con_Acct_Flg = 1) AND
          ((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Ky_Expir_Dt = DATE '9999-12-31')
          AND ((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Opn_Dt <= DATE '2005-02-28')
          AND (DTNG_C2T.T_C2T_ACCT.C2t_Acct_Opn_Dt >= DATE
          '2005-02-01'))))))") into Spool 1 (group_amps), which is built
          locally on the AMPs.  The input table will not be cached in memory,
          but it is eligible for synchronized scanning.  The size of Spool 1
          is estimated with low confidence to be 14 rows.  The estimated
          time for this step is 1 minute and 46 seconds.

With the one day, the estimate is ~700 rows, instead of the actual ~3,000. But the actual number of rows that result are 62,934, as opposed to the estimate of fourteen (14!). Hey, if I thought I was going to end up with 14 rows, I might do a product join immediately, but hey, even I know that if you have 700 rows for one day of a month, you might estimate 28*700 (or 19,600) rows. I know, I know, the optimizer doesn't know about "months" and "days", but it does have complete (important for a date, since sample stats do funny things) and recent statistics:

     help statistics T_C2T_ACCT column C2t_Acct_Opn_Dt

     Date   Time   Number of Rows   Number of Nulls   Number of Intervals   Number of
     Uniques   Numeric   Sampled   Sampled Percent   Version   Min Value   Mode Value
     Mode Frequency   Max Value   Mode Value   Mode Frequency  ...
     05/03/28   13:01:34   457602531   5910338   100   22859   Y   0   0   2   101   960810   1886902   =
     760316   101   421324  ...

I didn't put the entire results out, but then again, I don't know how to read all those anyway. But you can see that the stats aren't sampled. There is a goodly population of null values (5,910,338) but not compared to the number of rows in the table (457,602,531 or 1.3%). Now, obviously, I should use some other method to join these two tables, especially if it occurs often. However, my question still stands. Why is the estimate for a whole set of values (the between) WORSE than the equality condition? What's going on.

And that's the start of my story.


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference