Optimizer Lies :)

Database

Optimizer Lies :)

I have a simple query:
SELECT
mil.locationid,
mil.menuitemmasterid,
mil.business_date,
gc.ordertypeid,
SUM(mil.item_count) AS SLS_CNT,
SUM(mil.item_total) AS SLS_TTL
FROM
bidw_sales.menu_item_line3 mil INNER JOIN
bidw_sales.guest_check2 gc ON
gc.guestcheckid = mil.guestcheckid AND
gc.locationid = mil.locationid AND
gc.business_date = mil.business_date

WHERE
mil.business_date = '2010-02-01' --(SEL cal_date FROM bidw_sales.d_date d WHERE d.cal_date BETWEEN '2010-02-01' AND '2010-02-28')
GROUP BY
1,2,3,4

The explain plan is as follows:
Explanation
1) First, we do a single-AMP JOIN step from a single partition of
bidw_sales.mil by way of the primary index
"bidw_sales.mil.Business_Date = DATE '2010-02-01'" with a residual
condition of ("bidw_sales.mil.Business_Date = DATE '2010-02-01'"),
which is joined to a single partition of bidw_sales.gc by way of
the primary index "bidw_sales.gc.Business_Date = DATE '2010-02-01'"
with a residual condition of ("bidw_sales.gc.Business_Date = DATE
'2010-02-01'"). bidw_sales.mil and bidw_sales.gc are joined using
a sliding-window merge join (contexts = 15, 1), with a join
condition of ("(bidw_sales.gc.LocationId =
bidw_sales.mil.LocationId) AND ((bidw_sales.gc.GuestcheckId =
bidw_sales.mil.GuestCheckId) AND (bidw_sales.gc.Business_Date =
bidw_sales.mil.Business_Date ))"). The input tables
bidw_sales.mil and bidw_sales.gc will not be cached in memory.
The result goes into Spool 3 (all_amps) (compressed columns
allowed), which is built locally on that AMP. The size of Spool 3
is estimated with low confidence to be 2 rows (94 bytes). The
estimated time for this step is 0.04 seconds.
2) Next, we do an all-AMPs SUM step to aggregate from Spool 3 (Last
Use) by way of an all-rows scan , grouping by field1 (
bidw_sales.mil.LocationId ,bidw_sales.mil.MenuItemMasterId
,bidw_sales.mil.Business_Date ,bidw_sales.gc.OrderTypeId).
Aggregate Intermediate Results are computed locally, then placed
in Spool 4. The size of Spool 4 is estimated with low confidence
to be 2 rows (154 bytes). The estimated time for this step is
0.01 seconds.
3) We do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by way of
an all-rows scan into Spool 1 (group_amps), which is built locally
on the AMPs. The size of Spool 1 is estimated with low confidence
to be 2 rows (122 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.06 seconds.

=====================================================
Awesome right? Wrong, the query does not return that fast! What is wrong? How can the plan be so far off actual execution which is >15 mins
ddl below for tables invovled

SELECT
mil.locationid,
mil.menuitemmasterid,
mil.business_date,
gc.ordertypeid,
SUM(mil.item_count) AS SLS_CNT,
SUM(mil.item_total) AS SLS_TTL
FROM
bidw_sales.menu_item_line3 mil INNER JOIN
bidw_sales.guest_check2 gc ON
gc.guestcheckid = mil.guestcheckid AND
gc.locationid = mil.locationid AND
gc.business_date = mil.business_date

WHERE
mil.business_date = '2010-02-01' --(SEL cal_date FROM bidw_sales.d_date d WHERE d.cal_date BETWEEN '2010-02-01' AND '2010-02-28')
GROUP BY
1,2,3,4

/* DDL for tables involved is next */
CREATE SET TABLE BIDW_SALES.MENU_ITEM_LINE3 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
GuestCheckId INTEGER NOT NULL,
Line BIGINT,
LocationId INTEGER,
Business_Date DATE FORMAT 'YYYY-MM-DD',
Trans_DateTime TIMESTAMP(0),
Service_Round INTEGER COMPRESS (1 ,2 ,3 ,4 ,5 ,6 ,7 ),
Item_Count INTEGER COMPRESS (0 ,1 ,-1 ),
Item_Total DECIMAL(15,2) COMPRESS (0.00 ,17.98 ,17.99 ,5.24 ,2.69 ,5.29 ,12.99 ,7.99 ,8.00 ,2.99 ,3.00 ,0.50 ,15.99 ,3.25 ,3.29 ,10.99 ,8.49 ,5.99 ,6.00 ,3.49 ,0.99 ,18.99 ,13.99 ,8.99 ,6.49 ,3.99 ,16.99 ,4.24 ,4.25 ,4.29 ,11.99 ,9.49 ,6.99 ,7.00 ,4.49 ,1.99 ,-2.99 ,2.19 ,14.99 ,2.29 ,9.99 ,7.49 ,2.39 ,4.99 ,5.00 ,2.49 ),
Guest_Count INTEGER COMPRESS (0 ,1 ,-1 ),
Reference_Info1 VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
Reference_Info2 VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,
FixedPeriodId INTEGER COMPRESS (21 ,22 ,23 ,24 ,25 ,26 ,27 ,28 ,29 ,30 ,31 ,32 ,33 ,34 ,35 ,36 ,42 ,43 ,67 ,68 ,69 ,70 ,71 ,72 ,73 ,74 ,75 ,76 ,77 ,78 ,79 ,80 ,81 ,82 ,83 ,84 ,85 ,86 ,87 ,88 ,89 ,90 ,91 ,92 ,93 ,94 ,95 ,96 ,97 ,98 ,99 ,100 ,101 ,102 ,103 ),
MenuItemMasterId BIGINT,
Hide INTEGER COMPRESS (0 ,1 ))
PRIMARY INDEX ( Business_Date )
PARTITION BY RANGE_N(Business_Date BETWEEN '2007-06-06' AND '2011-06-08' EACH INTERVAL '1' MONTH ,
NO RANGE OR UNKNOWN)
INDEX idx_gc ( GuestCheckId )
INDEX idx_perf ( GuestCheckId ,LocationId ,Business_Date );

CREATE SET TABLE BIDW_SALES.GUEST_CHECK2 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
GuestcheckId INTEGER NOT NULL,
OrganizationId SMALLINT,
LocationId INTEGER,
Open_DateTime TIMESTAMP(0),
Close_DateTime TIMESTAMP(0),
Open_DaypartHoursId INTEGER COMPRESS (8193 ,8449 ,8705 ,8195 ,8197 ,8453 ,8710 ,8199 ,8201 ,8458 ,8714 ,8203 ,8205 ,8462 ,8718 ,8466 ,8722 ,8213 ,8470 ,8726 ,8215 ,8217 ,8729 ,8474 ,8219 ,8477 ,8733 ,8481 ,8738 ,8229 ,8486 ,8742 ,8231 ,8233 ,8490 ,8746 ,7981 ,8494 ,8750 ,8498 ,8754 ,8243 ,8245 ,8757 ,8502 ,8247 ,7993 ,8505 ,8761 ,7997 ,8509 ,8766 ,8001 ,8257 ,8514 ,8770 ,8259 ,8261 ,8518 ,8774 ,8009 ,8522 ,8778 ,8013 ,8526 ,8782 ,8271 ,8017 ,8273 ,8785 ,8530 ,8275 ,8021 ,8533 ,8789 ,8025 ,8537 ,8029 ,8285 ,8542 ,8287 ,8033 ,8289 ,8546 ,8037 ,8550 ,8806 ,8041 ,8554 ,8810 ,8299 ,8045 ,8301 ,8813 ,8558 ,8303 ,8049 ,8561 ,8817 ,8053 ,8565 ,8311 ,8057 ,8313 ,8570 ,8315 ,8061 ,8317 ,8574 ,8319 ,8065 ,8321 ,8578 ,8067 ,8323 ,8069 ,8325 ,8582 ,8838 ,8071 ,8327 ,8073 ,8329 ,8841 ,8586 ,8075 ,8331 ,8077 ,8333 ,8589 ,8845 ,8079 ,8335 ,8081 ,8337 ,8593 ,8083 ,8339 ,8085 ,8341 ,8598 ,8087 ,8343 ,8089 ,8346 ,8602 ,8091 ,8093 ,8350 ,8606 ,8095 ,8097 ,8354 ,8610 ,8099 ,8101 ,8869 ,8358 ,8614 ,8103 ,8105 ,8617 ,8873 ,8362 ,8107 ,8109 ,8365 ,8621 ,8111 ,8113 ,8369 ,8626 ,8115 ,8117 ,8374 ,8630 ,8119 ,8121 ,8378 ,8634 ,8123 ,8125 ,8382 ,8638 ,8127 ,8129 ,8897 ,8386 ,8642 ,8131 ,8133 ,8645 ,8901 ,8390 ,8135 ,8137 ,8393 ,8649 ,8139 ,8141 ,8397 ,8654 ,8143 ,8145 ,8402 ,8658 ,8147 ,8149 ,8406 ,8662 ,8151 ,8153 ,8410 ,8666 ,8155 ,8157 ,8925 ,8414 ,8670 ,8159 ,8161 ,8673 ,8929 ,8418 ,8163 ,8165 ,8421 ,8677 ,8167 ,8169 ,8425 ,8682 ,8171 ,8173 ,8430 ,8686 ,8175 ,8177 ,8434 ,8690 ,8179 ,8181 ,8438 ,8694 ,8183 ,8185 ,8953 ,8442 ,8698 ,8187 ,8189 ,8701 ,8957 ,8446 ,8191 ),
Business_Date DATE FORMAT 'YY/MM/DD' NOT NULL,
DateId INTEGER COMPRESS (20100123 ,20100124 ,20100125 ,20100126 ,20100127 ,20100128 ,20100129 ,20100130 ,20100131 ,20100401 ,20100402 ,20100403 ,20100404 ,20100405 ,20100406 ,20100407 ,20100408 ,20100409 ,20100410 ,20100411 ,20100412 ,20100413 ,20100414 ,20100415 ,20100416 ,20100417 ,20100418 ,20100419 ,20100420 ,20100421 ,20100422 ,20100423 ,20100424 ,20100425 ,20100426 ,20100201 ,20100202 ,20100203 ,20100204 ,20100205 ,20100206 ,20100207 ,20100208 ,20100209 ,20100210 ,20100211 ,20100212 ,20100213 ,20100214 ,20100215 ,20100216 ,20100217 ,20100218 ,20100219 ,20100220 ,20100221 ,20100222 ,20100223 ,20100224 ,20100225 ,20100226 ,20100227 ,20100228 ,20100301 ,20100302 ,20100303 ,20100304 ,20100305 ,20100306 ,20100307 ,20100308 ,20100309 ,20100310 ,20100311 ,20100312 ,20100313 ,20100314 ,20100315 ,20100316 ,20100317 ,20100318 ,20100319 ,20100320 ,20100321 ,20100322 ,20100323 ,20100324 ,20100325 ,20100326 ,20100327 ,20100328 ,20100329 ,20100330 ,20100331 ),
Close_DaypartHoursId INTEGER COMPRESS (8193 ,8449 ,8705 ,8195 ,8197 ,8453 ,8710 ,8199 ,8201 ,8458 ,8714 ,8203 ,8205 ,8462 ,8718 ,8207 ,8209 ,8466 ,8722 ,8211 ,8213 ,8981 ,8470 ,8726 ,8215 ,8217 ,8729 ,8985 ,8474 ,8219 ,8221 ,8477 ,8733 ,8223 ,8225 ,8481 ,8738 ,8227 ,8229 ,8486 ,8742 ,8231 ,8233 ,8490 ,8746 ,8235 ,8494 ,8750 ,8241 ,9009 ,8498 ,8754 ,8243 ,8245 ,8757 ,9013 ,8502 ,8247 ,8505 ,8761 ,8509 ,8766 ,8257 ,8514 ,8770 ,8259 ,8261 ,8518 ,8774 ,8522 ,8778 ,9037 ,8526 ,8782 ,8271 ,8273 ,8785 ,9041 ,8530 ,8275 ,8533 ,8789 ,8537 ,8794 ,8285 ,8542 ,8798 ,8287 ,8289 ,8546 ,8802 ,8550 ,8806 ,8554 ,8810 ,8299 ,8301 ,8813 ,8558 ,8303 ,8561 ,8817 ,8565 ,8822 ,8313 ,8570 ,8826 ,8315 ,8317 ,8574 ,8830 ,8065 ,8578 ,8834 ,8067 ,8069 ,8582 ,8838 ,8071 ,8327 ,8073 ,8329 ,8841 ,8586 ,8075 ,8331 ,8589 ,8845 ,8079 ,8081 ,8593 ,8850 ,8083 ,8085 ,8341 ,8598 ,8854 ,8087 ,8343 ,8089 ,8346 ,8602 ,8858 ,8091 ,8093 ,8606 ,8862 ,8095 ,8097 ,8610 ,8866 ,8099 ,8101 ,8869 ,8614 ,8103 ,8105 ,8617 ,8873 ,8362 ,8107 ,8109 ,8365 ,8621 ,8878 ,8111 ,8113 ,8369 ,8626 ,8115 ,8117 ,8374 ,8630 ,8119 ,8121 ,8378 ,8634 ,8890 ,8123 ,8125 ,8382 ,8638 ,8894 ,8127 ,8129 ,8897 ,8386 ,8642 ,8131 ,8133 ,8645 ,8901 ,8390 ,8135 ,8137 ,8393 ,8649 ,8139 ,8141 ,8397 ,8654 ,8143 ,8145 ,8402 ,8658 ,8147 ,8149 ,8406 ,8662 ,8151 ,8153 ,8410 ,8666 ,8922 ,8155 ,8157 ,8925 ,8414 ,8670 ,8159 ,8161 ,8673 ,8929 ,8418 ,8163 ,8165 ,8421 ,8677 ,8167 ,8169 ,8425 ,8682 ,8171 ,8173 ,8430 ,8686 ,8175 ,8177 ,8434 ,8690 ,8179 ,8181 ,8438 ,8694 ,8183 ,8185 ,8953 ,8442 ,8698 ,8187 ,8189 ,8701 ,8957 ,8446 ,8191 ),
Check_Number INTEGER,
Line_Count INTEGER COMPRESS (0 ,1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ,11 ,12 ,13 ,14 ,15 ,16 ,17 ,18 ,19 ,20 ,21 ,22 ,23 ,24 ,25 ,26 ,27 ,28 ,29 ,30 ),
Check_Total DECIMAL(15,2) COMPRESS (0.00 ,12.82 ,12.83 ,23.07 ,41.00 ,18.00 ,20.62 ,36.00 ,13.00 ,54.00 ,18.17 ,31.00 ,10.59 ,49.00 ,26.00 ,10.69 ,23.50 ,44.00 ,15.88 ,21.00 ,23.60 ,39.00 ,3.20 ,16.00 ,10.90 ,16.03 ,34.00 ,11.00 ,8.47 ,52.00 ,11.08 ,29.00 ,8.55 ,23.91 ,70.00 ,47.00 ,8.61 ,8.63 ,24.00 ,11.21 ,13.77 ,65.00 ,21.49 ,8.71 ,42.00 ,8.73 ,19.00 ,13.90 ,24.14 ,60.00 ,19.06 ,37.00 ,14.00 ,21.69 ,6.35 ,55.00 ,14.07 ,32.00 ,6.41 ,11.53 ,21.79 ,9.00 ,19.24 ,24.36 ,50.00 ,11.62 ,9.08 ,27.00 ,19.37 ,45.00 ,22.00 ,24.59 ,16.94 ,40.00 ,19.55 ,17.00 ,4.23 ,19.60 ,4.27 ,11.96 ,35.00 ,17.10 ,12.00 ,22.24 ,53.00 ,12.07 ,30.00 ,9.53 ,17.26 ,12.15 ,48.00 ,9.62 ,25.00 ,9.69 ,7.14 ,14.82 ,9.71 ,43.00 ,12.30 ,17.42 ,20.00 ,7.21 ,9.80 ,9.82 ,14.96 ,38.00 ,15.00 ,15.07 ,33.00 ,10.00 ,15.14 ,51.00 ,22.85 ,28.00 ,5.00 ,12.70 ,12.71 ,15.28 ,46.00 ,20.43 ,23.00 ),
OrderTypeId INTEGER COMPRESS (20001 ,20002 ,20003 ),
RevenueCenterId INTEGER COMPRESS (21001 ,20001 ,20002 ),
MasterCheckId INTEGER,
TransferStatus VARCHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( Business_Date )
PARTITION BY RANGE_N(Business_Date BETWEEN '2007-06-06' AND '2011-06-08' EACH INTERVAL '1' MONTH ,
NO RANGE OR UNKNOWN)
UNIQUE INDEX idx_gc1 ( GuestcheckId );

7 REPLIES
Enthusiast

Re: Optimizer Lies :)

Do you need to collect Statistics? The optimiser thinks some of these tables have 2 rows? but with low confidence....

Random
Enthusiast

Re: Optimizer Lies :)

Also how skewed is the data? I assume that Business_date is well spread across amps?

Random

Re: Optimizer Lies :)

stats are collected, table is a bit high on the skew factor
Enthusiast

Re: Optimizer Lies :)

I may now proceed to show my ignorance on things SQL but here goes....

WHERE
mil.business_date = '2010-02-01' --(SEL cal_date FROM bidw_sales.d_date d WHERE d.cal_date BETWEEN '2010-02-01' AND '2010-02-28')

What is this trying to achieve?

should it read?

WHERE
mil.business_date in (SEL distinct cal_date FROM bidw_sales.d_date d WHERE d.cal_date BETWEEN '2010-02-01' AND '2010-02-28')

Random

Senior Apprentice

Re: Optimizer Lies :)

Let's assume both tables are large (because they are partitioned):

A PI on a column with a small number of distinct values is rather stupid.
Partitioning a single-column NUPI on business_date by business_date is ...
A SET table with a large number of rows per value is ...

Who created that table???

And for that kind of mess i'd expect: stats are probably defined, but not properly re-collected.

Dieter

Re: Optimizer Lies :)

Dieter - I could use your help so I'll forget you called me stupid :) The single column nupi was used because businessdate is the only column that is regularly used
in a where block when accessing the table, and the skew value was still very low. stats are collected 2x a week for all our tables. What would you suggest
? Would a multi column UPI be better even if it's never accessed in a query? The partitioning was placed there because that seems to get us the best
response times? The tables are large, 500 million + for one, 100 million for the other. Is that large enough for partitioning? Again thanks for any advice you can offer

Random - that's us trying to see how fast the query goes for one day vs a range
Senior Apprentice

Re: Optimizer Lies :)

For the table you created you don't need that partition, it's totally useless (just creates a larger table).

Partitioning is best on columns which are used a lot in WHERE, but a PI only on that column results in hundreds of thousands of rows per PI-value.
Maybe the table is not skewed, but the number of rows per PI-value should be a low as possible, a few hundreds, maybe a few thousands (and then you usually switch to a MULTISET table avoiding dup row checks.

PI is best on columns which are used a lot in joins or accessed a lot with equality constraints.
If Business_Date is already your partitioning then GuestcheckId or (GuestcheckId,Business_Date) will be a better choice as PI if the join is always
gc.guestcheckid = mil.guestcheckid AND
gc.locationid = mil.locationid AND
gc.business_date = mil.business_date

And then think about if you really need UNIQUE INDEX idx_gc1 ( GuestcheckId );
The PI on GuestcheckId will be non-unique (unless you include business_date), but uniqueness is usually cheched in your load job.

Btw, secondary indexes usually don't help in Teradata if you join a large amount of rows:
INDEX idx_perf ( GuestCheckId ,LocationId ,Business_Date );

Could you post the output of HELP STATS for both tables?

Dieter