Rocketq
Reputation: 5791
TD wrongly estimates amount of rows with collected statistics with grouping
This is pretty weird for me. TD overestimates amount of rows, actually 42 mln, estimated 943 mln.
The query is pretty easy:
select ID, sum(amount)
from v_tb -- view
where REPORT_DATE between Date '2017-11-01' and Date '2017-11-30' -- report_date has date format
group by 1
Plan:
1) First, we lock tb in view v_tb for access.
2) Next, we do an all-AMPs SUM step to aggregate from 1230 partitions
of tb in view v_tb with a
condition of ("(tb.REPORT_DATE >= DATE '2017-11-01') AND
(tb.REPORT_DATE <= DATE '2017-11-30')")
, grouping by field1 ( ID). Aggregate
Intermediate Results are computed locally, then placed in Spool 1.
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 943,975,437 rows (27,375,287,673 bytes). The
estimated time for this step is 1 minute and 26 seconds.
3) 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 1 minute and 26 seconds.
There is collected statistics according to DBC.statsV on ID, report_date, (ID, report_date) - they all up to date. There is no null values - TRUE
UniqueValueCount for ID, report_date, (ID, report_date) - 36 mln, 839, 1232 mln values - seems to be correct
Why TD overestimated the amount of rows? Isn't it should get the final result based on UniqueValueCount of ID only, because I group on it
UPD1:
-- estimates 32 mln rows
select ID, sum(amount)
from v_tb -- view
where REPORT_DATE between Date '2017-11-01' and Date '2017-11-01' -- report_date has date format
group by 1
-- estimates 89 mln rows
select ID, sum(amount)
from v_tb -- view
where REPORT_DATE between Date '2017-11-01' and Date '2017-11-02' -- report_date has date format
group by 1
So the problem is with where predicate
SampleSizePct is equal to 5.01 - does it mean, that sample size is only 5%? - yes it is
UPD2: Previous query was part of a bigger query, which looks like this:
select top 100000000
base.*
, case when CPE_MODEL_NEW.device_type in ('Smartphone', 'Phone', 'Tablet', 'USB modem') then CPE_MODEL_NEW.device_type
else 'other' end as device_type
, usg_mbou
, usg_arpu_content
, date '2017-11-30' as max_report_date
, macroregion_name
from (
select
a.SUBS_ID
, a.tac
, MSISDN
, BRANCH_ID
, max(bsegment) bsegment
, max((date '2017-11-30' - cast (activation_dttm as date))/30.4167) as LT_month
, Sum(REVENUE_COMMERCE) REVENUE_COMMERCE
, max(LAST_FLASH_DTTM) LAST_FLASH_DTTM
from PRD2_BDS_V2.SUBS_CLR_D a
where a.REPORT_DATE between Date '2017-11-01' and Date '2017-11-30'
group by 1,2,3,4 --, 8, 9
) base
left join CPE_MODEL_NEW on base.tac = CPE_MODEL_NEW.tac
left join
(
select SUBS_ID, sum(case when TRAFFIC_TYPE_ID = 4 /*DATA*/ then all_vol / (1024 * 1024) else 0 end) usg_mbou
,sum(case when COST_BAND_ID IN (3,46,49,56) then rated_amount else 0 end) usg_arpu_content
from PRD2_BDS_V2.SUBS_USG_D where SUBS_USG_D.REPORT_DATE between Date '2017-11-01' and Date '2017-11-30'
group by 1
) SUBS_USG_D
on SUBS_USG_D.SUBS_ID = base.SUBS_ID
LEFT JOIN PRD2_DIC_V.BRANCH AS BRANCH ON base.BRANCH_ID = BRANCH.BRANCH_ID
LEFT JOIN PRD2_DIC_V2.REGION AS REGION ON BRANCH.REGION_ID = REGION.REGION_ID
AND Date '2017-11-30' >= REGION.SDATE AND REGION.EDATE >= Date '2017-11-01'
LEFT JOIN PRD2_DIC_V2.MACROREGION AS MACROREGION ON REGION.MACROREGION_ID = MACROREGION.MACROREGION_ID
AND Date '2017-11-30' >= MACROREGION.SDATE AND Date '2017-11-01' <= MACROREGION.EDATE
Query fail on spool problem on a almost last steps:
We do an All-AMPs STAT FUNCTION step from Spool 10 by way of an all-rows scan into Spool 29, which is redistributed by hash code to all AMPs. The result rows are put into Spool 9, which is redistributed by hash code to all AMPs..
There is no product join, no wrong dublication to all amps, what offen lead to spool problem. However there is another problem, very high skew:
Snapshot CPU skew: 99.7%
Snapshot I/O skew: 99.7%
Spool usage just only 30 GB, but it easily uses more than 300 Gb at the beginning of query execution.
Tables aren't skewed
Full explain:
1) First, we lock TELE2_UAT.CPE_MODEL_NEW for access, we lock
PRD2_DIC.REGION in view PRD2_DIC_V2.REGION for access, we lock
PRD2_DIC.MACROREGION in view PRD2_DIC_V2.MACROREGION for access,
we lock PRD2_DIC.BRANCH in view PRD2_DIC_V.BRANCH for access, we
lock PRD2_BDS.SUBS_CLR_D for access, and we lock
PRD2_BDS.SUBS_USG_D for access.
2) Next, we do an all-AMPs SUM step to aggregate from 1230 partitions
of PRD2_BDS.SUBS_CLR_D with a condition of (
"(PRD2_BDS.SUBS_CLR_D.REPORT_DATE >= DATE '2017-11-01') AND
(PRD2_BDS.SUBS_CLR_D.REPORT_DATE <= DATE '2017-11-30')"), and the
grouping identifier in field 1. Aggregate Intermediate Results
are computed locally,skipping sort when applicable, then placed in
Spool 4. The input table will not be cached in memory, but it is
eligible for synchronized scanning. The size of Spool 4 is
estimated with low confidence to be 1,496,102,647 rows (
285,755,605,577 bytes). The estimated time for this step is 1
minute and 55 seconds.
3) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by
way of an all-rows scan into Spool 2 (used to materialize
view, derived table, table function or table operator base)
(all_amps) (compressed columns allowed), which is built
locally on the AMPs with Field1 ("UniqueId"). The size of
Spool 2 is estimated with low confidence to be 1,496,102,647
rows (140,633,648,818 bytes). Spool AsgnList:
"Field_1" = "UniqueId",
"Field_2" = "SUBS_ID",
"Field_3" = "TAC",
"Field_4" = "MSISDN",
"Field_5" = "BRANCH_ID",
"Field_6" = "Field_6",
"Field_7" = "Field_7",
"Field_8" = "Field_8",
"Field_9" = "Field_9".
The estimated time for this step is 57.85 seconds.
2) We do an all-AMPs SUM step to aggregate from 1230 partitions
of PRD2_BDS.SUBS_USG_D with a condition of ("(NOT
(PRD2_BDS.SUBS_USG_D.SUBS_ID IS NULL )) AND
((PRD2_BDS.SUBS_USG_D.REPORT_DATE >= DATE '2017-11-01') AND
(PRD2_BDS.SUBS_USG_D.REPORT_DATE <= DATE '2017-11-30'))"),
and the grouping identifier in field 1. Aggregate
Intermediate Results are computed locally,skipping sort when
applicable, then placed in Spool 7. The input table will not
be cached in memory, but it is eligible for synchronized
scanning. The size of Spool 7 is estimated with low
confidence to be 943,975,437 rows (42,478,894,665 bytes).
The estimated time for this step is 1 minute and 29 seconds.
4) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from Spool 7 (Last Use) by
way of an all-rows scan into Spool 1 (used to materialize
view, derived table, table function or table operator
SUBS_USG_D) (all_amps) (compressed columns allowed), which is
built locally on the AMPs with Field1 ("UniqueId"). The size
of Spool 1 is estimated with low confidence to be 943,975,437
rows (42,478,894,665 bytes). Spool AsgnList:
"Field_1" = "UniqueId",
"Field_2" = "SUBS_ID",
"Field_3" = "Field_3",
"Field_4" = "Field_4".
The estimated time for this step is 16.75 seconds.
2) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by
way of an all-rows scan into Spool 11 (all_amps) (compressed
columns allowed), which is redistributed by hash code to all
AMPs to all AMPs with hash fields ("Spool_2.SUBS_ID"). Then
we do a SORT to order Spool 11 by row hash. The size of
Spool 11 is estimated with low confidence to be 1,496,102,647
rows (128,664,827,642 bytes). Spool AsgnList:
"SUBS_ID" = "Spool_2.SUBS_ID",
"TAC" = "TAC",
"MSISDN" = "MSISDN",
"BRANCH_ID" = "BRANCH_ID",
"BSEGMENT" = "BSEGMENT",
"LT_MONTH" = "LT_MONTH",
"REVENUE_COMMERCE" = "REVENUE_COMMERCE",
"LAST_FLASH_DTTM" = "LAST_FLASH_DTTM".
The estimated time for this step is 4 minutes and 8 seconds.
5) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by
way of an all-rows scan into Spool 12 (all_amps) (compressed
columns allowed), which is redistributed by hash code to all
AMPs to all AMPs with hash fields ("Spool_1.SUBS_ID"). Then
we do a SORT to order Spool 12 by row hash. The size of
Spool 12 is estimated with low confidence to be 943,975,437
rows (34,927,091,169 bytes). Spool AsgnList:
"SUBS_ID" = "Spool_1.SUBS_ID",
"USG_MBOU" = "USG_MBOU",
"USG_ARPU_CONTENT" = "USG_ARPU_CONTENT".
The estimated time for this step is 1 minute and 5 seconds.
2) We do an all-AMPs RETRIEVE step from PRD2_DIC.BRANCH in view
PRD2_DIC_V.BRANCH by way of an all-rows scan with a condition
of ("NOT (PRD2_DIC.BRANCH in view PRD2_DIC_V.BRANCH.BRANCH_ID
IS NULL)") into Spool 13 (all_amps) (compressed columns
allowed), which is redistributed by hash code to all AMPs to
all AMPs with hash fields ("PRD2_DIC.BRANCH.REGION_ID").
Then we do a SORT to order Spool 13 by row hash. The size of
Spool 13 is estimated with high confidence to be 107 rows (
1,712 bytes). Spool AsgnList:
"BRANCH_ID" = "BRANCH_ID",
"REGION_ID" = "PRD2_DIC.BRANCH.REGION_ID".
The estimated time for this step is 0.02 seconds.
6) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step (No Sum) from PRD2_DIC.REGION in
view PRD2_DIC_V2.REGION by way of a RowHash match scan with a
condition of ("(PRD2_DIC.REGION in view
PRD2_DIC_V2.REGION.EDATE >= DATE '2017-11-01') AND
(PRD2_DIC.REGION in view PRD2_DIC_V2.REGION.SDATE <= DATE
'2017-11-30')"), which is joined to Spool 13 (Last Use) by
way of a RowHash match scan. PRD2_DIC.REGION and Spool 13
are right outer joined using a merge join, with condition(s)
used for non-matching on right table ("NOT
(Spool_13.REGION_ID IS NULL)"), with a join condition of (
"Spool_13.REGION_ID = PRD2_DIC.REGION.ID"). The result goes
into Spool 14 (all_amps) (compressed columns allowed), which
is redistributed by hash code to all AMPs to all AMPs with
hash fields ("PRD2_DIC.REGION.MACROREGION_CODE"). Then we do
a SORT to order Spool 14 by row hash. The size of Spool 14
is estimated with low confidence to be 107 rows (2,461 bytes).
Spool AsgnList:
"MACROREGION_CODE" = "PRD2_DIC.REGION.MACROREGION_CODE",
"BRANCH_ID" = "{RightTable}.BRANCH_ID".
The estimated time for this step is 0.03 seconds.
2) We do an all-AMPs RETRIEVE step from TELE2_UAT.CPE_MODEL_NEW
by way of an all-rows scan with no residual conditions into
Spool 17 (all_amps) (compressed columns allowed), which is
duplicated on all AMPs with hash fields (
"TELE2_UAT.CPE_MODEL_NEW.TAC"). Then we do a SORT to order
Spool 17 by row hash. The size of Spool 17 is estimated with
high confidence to be 49,024,320 rows (2,696,337,600 bytes).
Spool AsgnList:
"TAC" = "TELE2_UAT.CPE_MODEL_NEW.TAC",
"DEVICE_TYPE" = "DEVICE_TYPE".
The estimated time for this step is 2.81 seconds.
3) We do an all-AMPs JOIN step (No Sum) from Spool 11 (Last Use)
by way of a RowHash match scan, which is joined to Spool 12
(Last Use) by way of a RowHash match scan. Spool 11 and
Spool 12 are left outer joined using a merge join, with
condition(s) used for non-matching on left table ("NOT
(Spool_11.SUBS_ID IS NULL)"), with a join condition of (
"Spool_12.SUBS_ID = Spool_11.SUBS_ID"). The result goes into
Spool 18 (all_amps) (compressed columns allowed), which is
built locally on the AMPs with hash fields ("Spool_11.TAC").
Then we do a SORT to order Spool 18 by row hash. The size of
Spool 18 is estimated with low confidence to be 1,496,102,648
rows (152,602,470,096 bytes). Spool AsgnList:
"BRANCH_ID" = "{LeftTable}.BRANCH_ID",
"TAC" = "Spool_11.TAC",
"SUBS_ID" = "{LeftTable}.SUBS_ID",
"MSISDN" = "{LeftTable}.MSISDN",
"BSEGMENT" = "{LeftTable}.BSEGMENT",
"LT_MONTH" = "{LeftTable}.LT_MONTH",
"REVENUE_COMMERCE" = "{LeftTable}.REVENUE_COMMERCE",
"LAST_FLASH_DTTM" = "{LeftTable}.LAST_FLASH_DTTM",
"USG_MBOU" = "{RightTable}.USG_MBOU",
"USG_ARPU_CONTENT" = "{RightTable}.USG_ARPU_CONTENT".
The estimated time for this step is 3 minutes and 45 seconds.
7) We execute the following steps in parallel.
1) We do an all-AMPs JOIN step (No Sum) from
PRD2_DIC.MACROREGION in view PRD2_DIC_V2.MACROREGION by way
of a RowHash match scan with a condition of (
"(PRD2_DIC.MACROREGION in view PRD2_DIC_V2.MACROREGION.EDATE
>= DATE '2017-11-01') AND (PRD2_DIC.MACROREGION in view
PRD2_DIC_V2.MACROREGION.SDATE <= DATE '2017-11-30')"), which
is joined to Spool 14 (Last Use) by way of a RowHash match
scan. PRD2_DIC.MACROREGION and Spool 14 are right outer
joined using a merge join, with condition(s) used for
non-matching on right table ("NOT (Spool_14.MACROREGION_CODE
IS NULL)"), with a join condition of (
"Spool_14.MACROREGION_CODE = PRD2_DIC.MACROREGION.MR_CODE").
The result goes into Spool 19 (all_amps) (compressed columns
allowed), which is duplicated on all AMPs with hash fields (
"Spool_14.BRANCH_ID"). The size of Spool 19 is estimated
with low confidence to be 34,240 rows (1,712,000 bytes).
Spool AsgnList:
"BRANCH_ID" = "Spool_14.BRANCH_ID",
"MR_NAME" = "{LeftTable}.MR_NAME".
The estimated time for this step is 0.04 seconds.
2) We do an all-AMPs JOIN step (No Sum) from Spool 17 (Last Use)
by way of a RowHash match scan, which is joined to Spool 18
(Last Use) by way of a RowHash match scan. Spool 17 and
Spool 18 are right outer joined using a merge join, with
condition(s) used for non-matching on right table ("NOT
(Spool_18.TAC IS NULL)"), with a join condition of (
"Spool_18.TAC = Spool_17.TAC"). The result goes into Spool
22 (all_amps) (compressed columns allowed), which is built
locally on the AMPs with hash fields ("Spool_18.BRANCH_ID").
The size of Spool 22 is estimated with low confidence to be
1,496,102,648 rows (204,966,062,776 bytes). Spool AsgnList:
"BRANCH_ID" = "Spool_18.BRANCH_ID",
"SUBS_ID" = "{RightTable}.SUBS_ID",
"TAC" = "{RightTable}.TAC",
"MSISDN" = "{RightTable}.MSISDN",
"BSEGMENT" = "{RightTable}.BSEGMENT",
"LT_MONTH" = "{RightTable}.LT_MONTH",
"REVENUE_COMMERCE" = "{RightTable}.REVENUE_COMMERCE",
"LAST_FLASH_DTTM" = "{RightTable}.LAST_FLASH_DTTM",
"DEVICE_TYPE" = "{LeftTable}.DEVICE_TYPE",
"USG_MBOU" = "{RightTable}.USG_MBOU",
"USG_ARPU_CONTENT" = "{RightTable}.USG_ARPU_CONTENT".
The estimated time for this step is 1 minute and 23 seconds.
8) We do an all-AMPs JOIN step (No Sum) from Spool 19 (Last Use) by
way of an all-rows scan, which is joined to Spool 22 (Last Use) by
way of an all-rows scan. Spool 19 is used as the hash table and
Spool 22 is used as the probe table in a right outer joined using
a single partition classical hash join, with condition(s) used for
non-matching on right table ("NOT (Spool_22.BRANCH_ID IS NULL)"),
with a join condition of ("Spool_22.BRANCH_ID = Spool_19.BRANCH_ID").
The result goes into Spool 10 (all_amps) (compressed columns
allowed), which is built locally on the AMPs with Field1 ("28364").
The size of Spool 10 is estimated with low confidence to be
1,496,102,648 rows (260,321,860,752 bytes). Spool AsgnList:
"Field_1" = "28364",
"Spool_10.SUBS_ID" = "{ Copy }{RightTable}.SUBS_ID",
"Spool_10.TAC" = "{ Copy }{RightTable}.TAC",
"Spool_10.MSISDN" = "{ Copy }{RightTable}.MSISDN",
"Spool_10.BRANCH_ID" = "{ Copy }{RightTable}.BRANCH_ID",
"Spool_10.BSEGMENT" = "{ Copy }{RightTable}.BSEGMENT",
"Spool_10.LT_MONTH" = "{ Copy }{RightTable}.LT_MONTH",
"Spool_10.REVENUE_COMMERCE" = "{ Copy
}{RightTable}.REVENUE_COMMERCE",
"Spool_10.LAST_FLASH_DTTM" = "{ Copy }{RightTable}.LAST_FLASH_DTTM",
"Spool_10.DEVICE_TYPE" = "{ Copy }{RightTable}.DEVICE_TYPE",
"Spool_10.USG_MBOU" = "{ Copy }{RightTable}.USG_MBOU",
"Spool_10.USG_ARPU_CONTENT" = "{ Copy
}{RightTable}.USG_ARPU_CONTENT",
"Spool_10.MR_NAME" = "{ Copy }{LeftTable}.MR_NAME".
The estimated time for this step is 1 minute and 45 seconds.
9) We do an all-AMPs STAT FUNCTION step from Spool 10 by way of an
all-rows scan into Spool 29, which is redistributed by hash code
to all AMPs. The result rows are put into Spool 9 (group_amps),
which is built locally on the AMPs with Field1 ("Field_1"). This
step is used to retrieve the TOP 100000000 rows. Load
distribution optimization is used. If this step retrieves less
than 100000000 rows, then execute step 10. The size is estimated
with low confidence to be 100,000,000 rows (25,000,000,000 bytes).
10) We do an all-AMPs STAT FUNCTION step from Spool 10 (Last Use) by
way of an all-rows scan into Spool 29 (Last Use), which is
redistributed by hash code to all AMPs. The result rows are put
into Spool 9 (group_amps), which is built locally on the AMPs with
Field1 ("Field_1"). This step is used to retrieve the TOP
100000000 rows. The size is estimated with low confidence to be
100,000,000 rows (25,000,000,000 bytes).
11) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 9 are sent back to the user as the result of
statement 1.
What can I do here?
Upvotes: 1
Views: 360
Answers (1)
The Impaler
Reputation: 48850
Most databases show wrong estimates, and this is OK as long as the relationship between those estimates is good enough to produce a decent execution plan.
Now, if you think the execution plan is wrong, then you should seriously care about those estimates. Did you update the tables statistics recently?
Otherwise, I wouldn't worry too much about it.
Upvotes: 1
Related Questions
- Estimated number of rows is way off in execution plan
- Group by in SQL returning error: Selected non-aggregate values must be part of the associated group
- Teradata optimizer wrongly estimates row number then accessing to table through view with union
- JOIN with GROUP BY causing SUM() logic issues
- teradata collect stats using sample doesnt quite work
- Using COLLECT STATISTICS in Teradata
- T-SQL Group By causing data not to SUM
- issue with grouping (Teradata SQL)
- Tuning in Teradata (group by)
- Teradata 3504/3627 Issue