http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/results/clientpositive/perf/query31.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/query31.q.out b/ql/src/test/results/clientpositive/perf/query31.q.out new file mode 100644 index 0000000..c479d91 --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/query31.q.out @@ -0,0 +1,641 @@ +PREHOOK: query: explain with ss as (select ca_county,d_qoy, d_year,sum(ss_ext_sales_price) as store_sales from store_sales,date_dim,customer_address where ss_sold_date_sk = d_date_sk and ss_addr_sk=ca_address_sk group by ca_county,d_qoy, d_year), ws as (select ca_county,d_qoy, d_year,sum(ws_ext_sales_price) as web_sales from web_sales,date_dim,customer_address where ws_sold_date_sk = d_date_sk and ws_bill_addr_sk=ca_address_sk group by ca_county,d_qoy, d_year) select ss1.ca_county ,ss1.d_year ,ws2.web_sales/ws1.web_sales web_q1_q2_increase ,ss2.store_sales/ss1.store_sales store_q1_q2_increase ,ws3.web_sales/ws2.web_sales web_q2_q3_increase ,ss3.store_sales/ss2.store_sales store_q2_q3_increase from ss ss1 ,ss ss2 ,ss ss3 ,ws ws1 ,ws ws2 ,ws ws3 where ss1.d_qoy = 1 and ss1.d_year = 1998 and ss1.ca_county = ss2.ca_county and ss2.d_qoy = 2 and ss2.d_year = 1998 and ss2.ca_county = ss3.ca_county and ss3.d_qoy = 3 and ss3.d_year = 1998 and ss1.ca_county = ws1.ca_county and ws1.d_qoy = 1 a nd ws1.d_year = 1998 and ws1.ca_county = ws2.ca_county and ws2.d_qoy = 2 and ws2.d_year = 1998 and ws1.ca_county = ws3.ca_county and ws3.d_qoy = 3 and ws3.d_year =1998 and case when ws1.web_sales > 0 then ws2.web_sales/ws1.web_sales else null end > case when ss1.store_sales > 0 then ss2.store_sales/ss1.store_sales else null end and case when ws2.web_sales > 0 then ws3.web_sales/ws2.web_sales else null end > case when ss2.store_sales > 0 then ss3.store_sales/ss2.store_sales else null end order by web_q1_q2_increase +PREHOOK: type: QUERY +POSTHOOK: query: explain with ss as (select ca_county,d_qoy, d_year,sum(ss_ext_sales_price) as store_sales from store_sales,date_dim,customer_address where ss_sold_date_sk = d_date_sk and ss_addr_sk=ca_address_sk group by ca_county,d_qoy, d_year), ws as (select ca_county,d_qoy, d_year,sum(ws_ext_sales_price) as web_sales from web_sales,date_dim,customer_address where ws_sold_date_sk = d_date_sk and ws_bill_addr_sk=ca_address_sk group by ca_county,d_qoy, d_year) select ss1.ca_county ,ss1.d_year ,ws2.web_sales/ws1.web_sales web_q1_q2_increase ,ss2.store_sales/ss1.store_sales store_q1_q2_increase ,ws3.web_sales/ws2.web_sales web_q2_q3_increase ,ss3.store_sales/ss2.store_sales store_q2_q3_increase from ss ss1 ,ss ss2 ,ss ss3 ,ws ws1 ,ws ws2 ,ws ws3 where ss1.d_qoy = 1 and ss1.d_year = 1998 and ss1.ca_county = ss2.ca_county and ss2.d_qoy = 2 and ss2.d_year = 1998 and ss2.ca_county = ss3.ca_county and ss3.d_qoy = 3 and ss3.d_year = 1998 and ss1.ca_county = ws1.ca_county and ws1.d_qoy = 1 and ws1.d_year = 1998 and ws1.ca_county = ws2.ca_county and ws2.d_qoy = 2 and ws2.d_year = 1998 and ws1.ca_county = ws3.ca_county and ws3.d_qoy = 3 and ws3.d_year =1998 and case when ws1.web_sales > 0 then ws2.web_sales/ws1.web_sales else null end > case when ss1.store_sales > 0 then ss2.store_sales/ss1.store_sales else null end and case when ws2.web_sales > 0 then ws3.web_sales/ws2.web_sales else null end > case when ss2.store_sales > 0 then ss3.store_sales/ss2.store_sales else null end order by web_q1_q2_increase +POSTHOOK: type: QUERY +Plan optimized by CBO. + +Vertex dependency in root stage +Reducer 11 <- Map 10 (SIMPLE_EDGE), Map 14 (SIMPLE_EDGE) +Reducer 12 <- Map 15 (SIMPLE_EDGE), Reducer 11 (SIMPLE_EDGE) +Reducer 13 <- Reducer 12 (SIMPLE_EDGE) +Reducer 17 <- Map 16 (SIMPLE_EDGE), Map 20 (SIMPLE_EDGE) +Reducer 18 <- Map 21 (SIMPLE_EDGE), Reducer 17 (SIMPLE_EDGE) +Reducer 19 <- Reducer 18 (SIMPLE_EDGE) +Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE) +Reducer 23 <- Map 22 (SIMPLE_EDGE), Map 26 (SIMPLE_EDGE) +Reducer 24 <- Map 27 (SIMPLE_EDGE), Reducer 23 (SIMPLE_EDGE) +Reducer 25 <- Reducer 24 (SIMPLE_EDGE) +Reducer 29 <- Map 28 (SIMPLE_EDGE), Map 32 (SIMPLE_EDGE) +Reducer 3 <- Map 9 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) +Reducer 30 <- Map 33 (SIMPLE_EDGE), Reducer 29 (SIMPLE_EDGE) +Reducer 31 <- Reducer 30 (SIMPLE_EDGE) +Reducer 35 <- Map 34 (SIMPLE_EDGE), Map 38 (SIMPLE_EDGE) +Reducer 36 <- Map 39 (SIMPLE_EDGE), Reducer 35 (SIMPLE_EDGE) +Reducer 37 <- Reducer 36 (SIMPLE_EDGE) +Reducer 4 <- Reducer 3 (SIMPLE_EDGE) +Reducer 5 <- Reducer 13 (SIMPLE_EDGE), Reducer 19 (SIMPLE_EDGE), Reducer 25 (SIMPLE_EDGE), Reducer 31 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE) +Reducer 6 <- Reducer 37 (SIMPLE_EDGE), Reducer 5 (SIMPLE_EDGE) +Reducer 7 <- Reducer 6 (SIMPLE_EDGE) + +Stage-0 + Fetch Operator + limit:-1 + Stage-1 + Reducer 7 + File Output Operator [FS_166] + compressed:false + Statistics:Num rows: 11831111 Data size: 12007156967 Basic stats: COMPLETE Column stats: NONE + table:{"input format:":"org.apache.hadoop.mapred.TextInputFormat","output format:":"org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat","serde:":"org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe"} + Select Operator [SEL_165] + | outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5"] + | Statistics:Num rows: 11831111 Data size: 12007156967 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 6 [SIMPLE_EDGE] + Reduce Output Operator [RS_164] + key expressions:_col2 (type: decimal(37,20)) + sort order:+ + Statistics:Num rows: 11831111 Data size: 12007156967 Basic stats: COMPLETE Column stats: NONE + value expressions:_col0 (type: string), _col1 (type: int), _col3 (type: decimal(37,20)), _col4 (type: decimal(37,20)), _col5 (type: decimal(37,20)) + Select Operator [SEL_163] + outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5"] + Statistics:Num rows: 11831111 Data size: 12007156967 Basic stats: COMPLETE Column stats: NONE + Filter Operator [FIL_275] + predicate:(CASE WHEN ((_col19 > 0)) THEN ((_col23 / _col19)) ELSE (null) END > CASE WHEN ((_col7 > 0)) THEN ((_col11 / _col7)) ELSE (null) END) (type: boolean) + Statistics:Num rows: 11831111 Data size: 12007156967 Basic stats: COMPLETE Column stats: NONE + Merge Join Operator [MERGEJOIN_308] + | condition map:[{"":"Inner Join 0 to 1"}] + | keys:{"0":"_col12 (type: string)","1":"_col0 (type: string)"} + | outputColumnNames:["_col0","_col2","_col3","_col7","_col11","_col15","_col19","_col23"] + | Statistics:Num rows: 35493334 Data size: 36021471918 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 37 [SIMPLE_EDGE] + | Reduce Output Operator [RS_160] + | key expressions:_col0 (type: string) + | Map-reduce partition columns:_col0 (type: string) + | sort order:+ + | Statistics:Num rows: 22000000 Data size: 22327357890 Basic stats: COMPLETE Column stats: NONE + | value expressions:_col3 (type: decimal(17,2)) + | Select Operator [SEL_156] + | outputColumnNames:["_col0","_col3"] + | Statistics:Num rows: 22000000 Data size: 22327357890 Basic stats: COMPLETE Column stats: NONE + | Group By Operator [GBY_155] + | | aggregations:["sum(VALUE._col0)"] + | | keys:KEY._col0 (type: int), KEY._col1 (type: int), KEY._col2 (type: string) + | | outputColumnNames:["_col0","_col1","_col2","_col3"] + | | Statistics:Num rows: 22000000 Data size: 22327357890 Basic stats: COMPLETE Column stats: NONE + | |<-Reducer 36 [SIMPLE_EDGE] + | Reduce Output Operator [RS_154] + | key expressions:_col0 (type: int), _col1 (type: int), _col2 (type: string) + | Map-reduce partition columns:_col0 (type: int), _col1 (type: int), _col2 (type: string) + | sort order:+++ + | Statistics:Num rows: 44000000 Data size: 44654715780 Basic stats: COMPLETE Column stats: NONE + | value expressions:_col3 (type: decimal(17,2)) + | Group By Operator [GBY_153] + | aggregations:["sum(_col2)"] + | keys:_col4 (type: int), _col5 (type: int), _col7 (type: string) + | outputColumnNames:["_col0","_col1","_col2","_col3"] + | Statistics:Num rows: 44000000 Data size: 44654715780 Basic stats: COMPLETE Column stats: NONE + | Select Operator [SEL_152] + | outputColumnNames:["_col4","_col5","_col7","_col2"] + | Statistics:Num rows: 44000000 Data size: 44654715780 Basic stats: COMPLETE Column stats: NONE + | Merge Join Operator [MERGEJOIN_306] + | | condition map:[{"":"Inner Join 0 to 1"}] + | | keys:{"0":"_col1 (type: int)","1":"_col0 (type: int)"} + | | outputColumnNames:["_col2","_col7"] + | | Statistics:Num rows: 44000000 Data size: 44654715780 Basic stats: COMPLETE Column stats: NONE + | |<-Map 39 [SIMPLE_EDGE] + | | Reduce Output Operator [RS_150] + | | key expressions:_col0 (type: int) + | | Map-reduce partition columns:_col0 (type: int) + | | sort order:+ + | | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + | | value expressions:_col1 (type: string) + | | Select Operator [SEL_141] + | | outputColumnNames:["_col0","_col1"] + | | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + | | Filter Operator [FIL_294] + | | predicate:(ca_address_sk is not null and ca_county is not null) (type: boolean) + | | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + | | TableScan [TS_139] + | | alias:customer_address + | | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + | |<-Reducer 35 [SIMPLE_EDGE] + | Reduce Output Operator [RS_148] + | key expressions:_col1 (type: int) + | Map-reduce partition columns:_col1 (type: int) + | sort order:+ + | Statistics:Num rows: 20088 Data size: 22478696 Basic stats: COMPLETE Column stats: NONE + | value expressions:_col2 (type: decimal(7,2)) + | Merge Join Operator [MERGEJOIN_305] + | | condition map:[{"":"Inner Join 0 to 1"}] + | | keys:{"0":"_col0 (type: int)","1":"_col0 (type: int)"} + | | outputColumnNames:["_col1","_col2"] + | | Statistics:Num rows: 20088 Data size: 22478696 Basic stats: COMPLETE Column stats: NONE + | |<-Map 34 [SIMPLE_EDGE] + | | Reduce Output Operator [RS_143] + | | key expressions:_col0 (type: int) + | | Map-reduce partition columns:_col0 (type: int) + | | sort order:+ + | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | | value expressions:_col1 (type: int), _col2 (type: decimal(7,2)) + | | Select Operator [SEL_135] + | | outputColumnNames:["_col0","_col1","_col2"] + | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | | Filter Operator [FIL_292] + | | predicate:(ws_sold_date_sk is not null and ws_bill_addr_sk is not null) (type: boolean) + | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | | TableScan [TS_133] + | | alias:web_sales + | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | |<-Map 38 [SIMPLE_EDGE] + | Reduce Output Operator [RS_145] + | key expressions:_col0 (type: int) + | Map-reduce partition columns:_col0 (type: int) + | sort order:+ + | Statistics:Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE + | Select Operator [SEL_138] + | outputColumnNames:["_col0"] + | Statistics:Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE + | Filter Operator [FIL_293] + | predicate:(((d_qoy = 3) and (d_year = 1998)) and d_date_sk is not null) (type: boolean) + | Statistics:Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE + | TableScan [TS_136] + | alias:date_dim + | Statistics:Num rows: 73049 Data size: 81741831 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 5 [SIMPLE_EDGE] + Reduce Output Operator [RS_158] + key expressions:_col12 (type: string) + Map-reduce partition columns:_col12 (type: string) + sort order:+ + Statistics:Num rows: 32266667 Data size: 32746791943 Basic stats: COMPLETE Column stats: NONE + value expressions:_col0 (type: string), _col2 (type: int), _col3 (type: decimal(17,2)), _col7 (type: decimal(17,2)), _col11 (type: decimal(17,2)), _col15 (type: decimal(17,2)), _col19 (type: decimal(17,2)) + Select Operator [SEL_132] + outputColumnNames:["_col0","_col11","_col12","_col15","_col19","_col2","_col3","_col7"] + Statistics:Num rows: 32266667 Data size: 32746791943 Basic stats: COMPLETE Column stats: NONE + Filter Operator [FIL_276] + predicate:(CASE WHEN ((_col15 > 0)) THEN ((_col19 / _col15)) ELSE (null) END > CASE WHEN ((_col3 > 0)) THEN ((_col7 / _col3)) ELSE (null) END) (type: boolean) + Statistics:Num rows: 32266667 Data size: 32746791943 Basic stats: COMPLETE Column stats: NONE + Merge Join Operator [MERGEJOIN_307] + | condition map:[{"":"Inner Join 0 to 1"},{"":"Inner Join 1 to 2"},{"":"Inner Join 0 to 3"},{"":"Inner Join 3 to 4"}] + | keys:{"0":"_col0 (type: string)","1":"_col0 (type: string)","2":"_col0 (type: string)","3":"_col0 (type: string)","4":"_col0 (type: string)"} + | outputColumnNames:["_col0","_col2","_col3","_col7","_col11","_col12","_col15","_col19"] + | Statistics:Num rows: 96800002 Data size: 98240376845 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 13 [SIMPLE_EDGE] + | Reduce Output Operator [RS_123] + | key expressions:_col0 (type: string) + | Map-reduce partition columns:_col0 (type: string) + | sort order:+ + | Statistics:Num rows: 22000000 Data size: 22327357890 Basic stats: COMPLETE Column stats: NONE + | value expressions:_col3 (type: decimal(17,2)) + | Select Operator [SEL_47] + | outputColumnNames:["_col0","_col3"] + | Statistics:Num rows: 22000000 Data size: 22327357890 Basic stats: COMPLETE Column stats: NONE + | Group By Operator [GBY_46] + | | aggregations:["sum(VALUE._col0)"] + | | keys:KEY._col0 (type: int), KEY._col1 (type: int), KEY._col2 (type: string) + | | outputColumnNames:["_col0","_col1","_col2","_col3"] + | | Statistics:Num rows: 22000000 Data size: 22327357890 Basic stats: COMPLETE Column stats: NONE + | |<-Reducer 12 [SIMPLE_EDGE] + | Reduce Output Operator [RS_45] + | key expressions:_col0 (type: int), _col1 (type: int), _col2 (type: string) + | Map-reduce partition columns:_col0 (type: int), _col1 (type: int), _col2 (type: string) + | sort order:+++ + | Statistics:Num rows: 44000000 Data size: 44654715780 Basic stats: COMPLETE Column stats: NONE + | value expressions:_col3 (type: decimal(17,2)) + | Group By Operator [GBY_44] + | aggregations:["sum(_col2)"] + | keys:_col4 (type: int), _col5 (type: int), _col7 (type: string) + | outputColumnNames:["_col0","_col1","_col2","_col3"] + | Statistics:Num rows: 44000000 Data size: 44654715780 Basic stats: COMPLETE Column stats: NONE + | Select Operator [SEL_43] + | outputColumnNames:["_col4","_col5","_col7","_col2"] + | Statistics:Num rows: 44000000 Data size: 44654715780 Basic stats: COMPLETE Column stats: NONE + | Merge Join Operator [MERGEJOIN_298] + | | condition map:[{"":"Inner Join 0 to 1"}] + | | keys:{"0":"_col1 (type: int)","1":"_col0 (type: int)"} + | | outputColumnNames:["_col2","_col7"] + | | Statistics:Num rows: 44000000 Data size: 44654715780 Basic stats: COMPLETE Column stats: NONE + | |<-Map 15 [SIMPLE_EDGE] + | | Reduce Output Operator [RS_41] + | | key expressions:_col0 (type: int) + | | Map-reduce partition columns:_col0 (type: int) + | | sort order:+ + | | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + | | value expressions:_col1 (type: string) + | | Select Operator [SEL_32] + | | outputColumnNames:["_col0","_col1"] + | | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + | | Filter Operator [FIL_282] + | | predicate:(ca_address_sk is not null and ca_county is not null) (type: boolean) + | | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + | | TableScan [TS_30] + | | alias:customer_address + | | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + | |<-Reducer 11 [SIMPLE_EDGE] + | Reduce Output Operator [RS_39] + | key expressions:_col1 (type: int) + | Map-reduce partition columns:_col1 (type: int) + | sort order:+ + | Statistics:Num rows: 20088 Data size: 22478696 Basic stats: COMPLETE Column stats: NONE + | value expressions:_col2 (type: decimal(7,2)) + | Merge Join Operator [MERGEJOIN_297] + | | condition map:[{"":"Inner Join 0 to 1"}] + | | keys:{"0":"_col0 (type: int)","1":"_col0 (type: int)"} + | | outputColumnNames:["_col1","_col2"] + | | Statistics:Num rows: 20088 Data size: 22478696 Basic stats: COMPLETE Column stats: NONE + | |<-Map 10 [SIMPLE_EDGE] + | | Reduce Output Operator [RS_34] + | | key expressions:_col0 (type: int) + | | Map-reduce partition columns:_col0 (type: int) + | | sort order:+ + | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | | value expressions:_col1 (type: int), _col2 (type: decimal(7,2)) + | | Select Operator [SEL_26] + | | outputColumnNames:["_col0","_col1","_col2"] + | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | | Filter Operator [FIL_280] + | | predicate:(ss_sold_date_sk is not null and ss_addr_sk is not null) (type: boolean) + | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | | TableScan [TS_24] + | | alias:store_sales + | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | |<-Map 14 [SIMPLE_EDGE] + | Reduce Output Operator [RS_36] + | key expressions:_col0 (type: int) + | Map-reduce partition columns:_col0 (type: int) + | sort order:+ + | Statistics:Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE + | Select Operator [SEL_29] + | outputColumnNames:["_col0"] + | Statistics:Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE + | Filter Operator [FIL_281] + | predicate:(((d_qoy = 2) and (d_year = 1998)) and d_date_sk is not null) (type: boolean) + | Statistics:Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE + | TableScan [TS_27] + | alias:date_dim + | Statistics:Num rows: 73049 Data size: 81741831 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 19 [SIMPLE_EDGE] + | Reduce Output Operator [RS_125] + | key expressions:_col0 (type: string) + | Map-reduce partition columns:_col0 (type: string) + | sort order:+ + | Statistics:Num rows: 22000000 Data size: 22327357890 Basic stats: COMPLETE Column stats: NONE + | value expressions:_col3 (type: decimal(17,2)) + | Select Operator [SEL_71] + | outputColumnNames:["_col0","_col3"] + | Statistics:Num rows: 22000000 Data size: 22327357890 Basic stats: COMPLETE Column stats: NONE + | Group By Operator [GBY_70] + | | aggregations:["sum(VALUE._col0)"] + | | keys:KEY._col0 (type: int), KEY._col1 (type: int), KEY._col2 (type: string) + | | outputColumnNames:["_col0","_col1","_col2","_col3"] + | | Statistics:Num rows: 22000000 Data size: 22327357890 Basic stats: COMPLETE Column stats: NONE + | |<-Reducer 18 [SIMPLE_EDGE] + | Reduce Output Operator [RS_69] + | key expressions:_col0 (type: int), _col1 (type: int), _col2 (type: string) + | Map-reduce partition columns:_col0 (type: int), _col1 (type: int), _col2 (type: string) + | sort order:+++ + | Statistics:Num rows: 44000000 Data size: 44654715780 Basic stats: COMPLETE Column stats: NONE + | value expressions:_col3 (type: decimal(17,2)) + | Group By Operator [GBY_68] + | aggregations:["sum(_col2)"] + | keys:_col4 (type: int), _col5 (type: int), _col7 (type: string) + | outputColumnNames:["_col0","_col1","_col2","_col3"] + | Statistics:Num rows: 44000000 Data size: 44654715780 Basic stats: COMPLETE Column stats: NONE + | Select Operator [SEL_67] + | outputColumnNames:["_col4","_col5","_col7","_col2"] + | Statistics:Num rows: 44000000 Data size: 44654715780 Basic stats: COMPLETE Column stats: NONE + | Merge Join Operator [MERGEJOIN_300] + | | condition map:[{"":"Inner Join 0 to 1"}] + | | keys:{"0":"_col1 (type: int)","1":"_col0 (type: int)"} + | | outputColumnNames:["_col2","_col7"] + | | Statistics:Num rows: 44000000 Data size: 44654715780 Basic stats: COMPLETE Column stats: NONE + | |<-Map 21 [SIMPLE_EDGE] + | | Reduce Output Operator [RS_65] + | | key expressions:_col0 (type: int) + | | Map-reduce partition columns:_col0 (type: int) + | | sort order:+ + | | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + | | value expressions:_col1 (type: string) + | | Select Operator [SEL_56] + | | outputColumnNames:["_col0","_col1"] + | | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + | | Filter Operator [FIL_285] + | | predicate:(ca_address_sk is not null and ca_county is not null) (type: boolean) + | | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + | | TableScan [TS_54] + | | alias:customer_address + | | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + | |<-Reducer 17 [SIMPLE_EDGE] + | Reduce Output Operator [RS_63] + | key expressions:_col1 (type: int) + | Map-reduce partition columns:_col1 (type: int) + | sort order:+ + | Statistics:Num rows: 20088 Data size: 22478696 Basic stats: COMPLETE Column stats: NONE + | value expressions:_col2 (type: decimal(7,2)) + | Merge Join Operator [MERGEJOIN_299] + | | condition map:[{"":"Inner Join 0 to 1"}] + | | keys:{"0":"_col0 (type: int)","1":"_col0 (type: int)"} + | | outputColumnNames:["_col1","_col2"] + | | Statistics:Num rows: 20088 Data size: 22478696 Basic stats: COMPLETE Column stats: NONE + | |<-Map 16 [SIMPLE_EDGE] + | | Reduce Output Operator [RS_58] + | | key expressions:_col0 (type: int) + | | Map-reduce partition columns:_col0 (type: int) + | | sort order:+ + | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | | value expressions:_col1 (type: int), _col2 (type: decimal(7,2)) + | | Select Operator [SEL_50] + | | outputColumnNames:["_col0","_col1","_col2"] + | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | | Filter Operator [FIL_283] + | | predicate:(ss_sold_date_sk is not null and ss_addr_sk is not null) (type: boolean) + | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | | TableScan [TS_48] + | | alias:store_sales + | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | |<-Map 20 [SIMPLE_EDGE] + | Reduce Output Operator [RS_60] + | key expressions:_col0 (type: int) + | Map-reduce partition columns:_col0 (type: int) + | sort order:+ + | Statistics:Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE + | Select Operator [SEL_53] + | outputColumnNames:["_col0"] + | Statistics:Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE + | Filter Operator [FIL_284] + | predicate:(((d_qoy = 3) and (d_year = 1998)) and d_date_sk is not null) (type: boolean) + | Statistics:Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE + | TableScan [TS_51] + | alias:date_dim + | Statistics:Num rows: 73049 Data size: 81741831 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 25 [SIMPLE_EDGE] + | Reduce Output Operator [RS_127] + | key expressions:_col0 (type: string) + | Map-reduce partition columns:_col0 (type: string) + | sort order:+ + | Statistics:Num rows: 22000000 Data size: 22327357890 Basic stats: COMPLETE Column stats: NONE + | value expressions:_col3 (type: decimal(17,2)) + | Select Operator [SEL_95] + | outputColumnNames:["_col0","_col3"] + | Statistics:Num rows: 22000000 Data size: 22327357890 Basic stats: COMPLETE Column stats: NONE + | Group By Operator [GBY_94] + | | aggregations:["sum(VALUE._col0)"] + | | keys:KEY._col0 (type: int), KEY._col1 (type: int), KEY._col2 (type: string) + | | outputColumnNames:["_col0","_col1","_col2","_col3"] + | | Statistics:Num rows: 22000000 Data size: 22327357890 Basic stats: COMPLETE Column stats: NONE + | |<-Reducer 24 [SIMPLE_EDGE] + | Reduce Output Operator [RS_93] + | key expressions:_col0 (type: int), _col1 (type: int), _col2 (type: string) + | Map-reduce partition columns:_col0 (type: int), _col1 (type: int), _col2 (type: string) + | sort order:+++ + | Statistics:Num rows: 44000000 Data size: 44654715780 Basic stats: COMPLETE Column stats: NONE + | value expressions:_col3 (type: decimal(17,2)) + | Group By Operator [GBY_92] + | aggregations:["sum(_col2)"] + | keys:_col4 (type: int), _col5 (type: int), _col7 (type: string) + | outputColumnNames:["_col0","_col1","_col2","_col3"] + | Statistics:Num rows: 44000000 Data size: 44654715780 Basic stats: COMPLETE Column stats: NONE + | Select Operator [SEL_91] + | outputColumnNames:["_col4","_col5","_col7","_col2"] + | Statistics:Num rows: 44000000 Data size: 44654715780 Basic stats: COMPLETE Column stats: NONE + | Merge Join Operator [MERGEJOIN_302] + | | condition map:[{"":"Inner Join 0 to 1"}] + | | keys:{"0":"_col1 (type: int)","1":"_col0 (type: int)"} + | | outputColumnNames:["_col2","_col7"] + | | Statistics:Num rows: 44000000 Data size: 44654715780 Basic stats: COMPLETE Column stats: NONE + | |<-Map 27 [SIMPLE_EDGE] + | | Reduce Output Operator [RS_89] + | | key expressions:_col0 (type: int) + | | Map-reduce partition columns:_col0 (type: int) + | | sort order:+ + | | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + | | value expressions:_col1 (type: string) + | | Select Operator [SEL_80] + | | outputColumnNames:["_col0","_col1"] + | | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + | | Filter Operator [FIL_288] + | | predicate:(ca_address_sk is not null and ca_county is not null) (type: boolean) + | | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + | | TableScan [TS_78] + | | alias:customer_address + | | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + | |<-Reducer 23 [SIMPLE_EDGE] + | Reduce Output Operator [RS_87] + | key expressions:_col1 (type: int) + | Map-reduce partition columns:_col1 (type: int) + | sort order:+ + | Statistics:Num rows: 20088 Data size: 22478696 Basic stats: COMPLETE Column stats: NONE + | value expressions:_col2 (type: decimal(7,2)) + | Merge Join Operator [MERGEJOIN_301] + | | condition map:[{"":"Inner Join 0 to 1"}] + | | keys:{"0":"_col0 (type: int)","1":"_col0 (type: int)"} + | | outputColumnNames:["_col1","_col2"] + | | Statistics:Num rows: 20088 Data size: 22478696 Basic stats: COMPLETE Column stats: NONE + | |<-Map 22 [SIMPLE_EDGE] + | | Reduce Output Operator [RS_82] + | | key expressions:_col0 (type: int) + | | Map-reduce partition columns:_col0 (type: int) + | | sort order:+ + | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | | value expressions:_col1 (type: int), _col2 (type: decimal(7,2)) + | | Select Operator [SEL_74] + | | outputColumnNames:["_col0","_col1","_col2"] + | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | | Filter Operator [FIL_286] + | | predicate:(ws_sold_date_sk is not null and ws_bill_addr_sk is not null) (type: boolean) + | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | | TableScan [TS_72] + | | alias:web_sales + | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | |<-Map 26 [SIMPLE_EDGE] + | Reduce Output Operator [RS_84] + | key expressions:_col0 (type: int) + | Map-reduce partition columns:_col0 (type: int) + | sort order:+ + | Statistics:Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE + | Select Operator [SEL_77] + | outputColumnNames:["_col0"] + | Statistics:Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE + | Filter Operator [FIL_287] + | predicate:(((d_year = 1998) and (d_qoy = 1)) and d_date_sk is not null) (type: boolean) + | Statistics:Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE + | TableScan [TS_75] + | alias:date_dim + | Statistics:Num rows: 73049 Data size: 81741831 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 31 [SIMPLE_EDGE] + | Reduce Output Operator [RS_129] + | key expressions:_col0 (type: string) + | Map-reduce partition columns:_col0 (type: string) + | sort order:+ + | Statistics:Num rows: 22000000 Data size: 22327357890 Basic stats: COMPLETE Column stats: NONE + | value expressions:_col3 (type: decimal(17,2)) + | Select Operator [SEL_119] + | outputColumnNames:["_col0","_col3"] + | Statistics:Num rows: 22000000 Data size: 22327357890 Basic stats: COMPLETE Column stats: NONE + | Group By Operator [GBY_118] + | | aggregations:["sum(VALUE._col0)"] + | | keys:KEY._col0 (type: int), KEY._col1 (type: int), KEY._col2 (type: string) + | | outputColumnNames:["_col0","_col1","_col2","_col3"] + | | Statistics:Num rows: 22000000 Data size: 22327357890 Basic stats: COMPLETE Column stats: NONE + | |<-Reducer 30 [SIMPLE_EDGE] + | Reduce Output Operator [RS_117] + | key expressions:_col0 (type: int), _col1 (type: int), _col2 (type: string) + | Map-reduce partition columns:_col0 (type: int), _col1 (type: int), _col2 (type: string) + | sort order:+++ + | Statistics:Num rows: 44000000 Data size: 44654715780 Basic stats: COMPLETE Column stats: NONE + | value expressions:_col3 (type: decimal(17,2)) + | Group By Operator [GBY_116] + | aggregations:["sum(_col2)"] + | keys:_col4 (type: int), _col5 (type: int), _col7 (type: string) + | outputColumnNames:["_col0","_col1","_col2","_col3"] + | Statistics:Num rows: 44000000 Data size: 44654715780 Basic stats: COMPLETE Column stats: NONE + | Select Operator [SEL_115] + | outputColumnNames:["_col4","_col5","_col7","_col2"] + | Statistics:Num rows: 44000000 Data size: 44654715780 Basic stats: COMPLETE Column stats: NONE + | Merge Join Operator [MERGEJOIN_304] + | | condition map:[{"":"Inner Join 0 to 1"}] + | | keys:{"0":"_col1 (type: int)","1":"_col0 (type: int)"} + | | outputColumnNames:["_col2","_col7"] + | | Statistics:Num rows: 44000000 Data size: 44654715780 Basic stats: COMPLETE Column stats: NONE + | |<-Map 33 [SIMPLE_EDGE] + | | Reduce Output Operator [RS_113] + | | key expressions:_col0 (type: int) + | | Map-reduce partition columns:_col0 (type: int) + | | sort order:+ + | | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + | | value expressions:_col1 (type: string) + | | Select Operator [SEL_104] + | | outputColumnNames:["_col0","_col1"] + | | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + | | Filter Operator [FIL_291] + | | predicate:(ca_address_sk is not null and ca_county is not null) (type: boolean) + | | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + | | TableScan [TS_102] + | | alias:customer_address + | | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + | |<-Reducer 29 [SIMPLE_EDGE] + | Reduce Output Operator [RS_111] + | key expressions:_col1 (type: int) + | Map-reduce partition columns:_col1 (type: int) + | sort order:+ + | Statistics:Num rows: 20088 Data size: 22478696 Basic stats: COMPLETE Column stats: NONE + | value expressions:_col2 (type: decimal(7,2)) + | Merge Join Operator [MERGEJOIN_303] + | | condition map:[{"":"Inner Join 0 to 1"}] + | | keys:{"0":"_col0 (type: int)","1":"_col0 (type: int)"} + | | outputColumnNames:["_col1","_col2"] + | | Statistics:Num rows: 20088 Data size: 22478696 Basic stats: COMPLETE Column stats: NONE + | |<-Map 28 [SIMPLE_EDGE] + | | Reduce Output Operator [RS_106] + | | key expressions:_col0 (type: int) + | | Map-reduce partition columns:_col0 (type: int) + | | sort order:+ + | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | | value expressions:_col1 (type: int), _col2 (type: decimal(7,2)) + | | Select Operator [SEL_98] + | | outputColumnNames:["_col0","_col1","_col2"] + | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | | Filter Operator [FIL_289] + | | predicate:(ws_sold_date_sk is not null and ws_bill_addr_sk is not null) (type: boolean) + | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | | TableScan [TS_96] + | | alias:web_sales + | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | |<-Map 32 [SIMPLE_EDGE] + | Reduce Output Operator [RS_108] + | key expressions:_col0 (type: int) + | Map-reduce partition columns:_col0 (type: int) + | sort order:+ + | Statistics:Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE + | Select Operator [SEL_101] + | outputColumnNames:["_col0"] + | Statistics:Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE + | Filter Operator [FIL_290] + | predicate:(((d_qoy = 2) and (d_year = 1998)) and d_date_sk is not null) (type: boolean) + | Statistics:Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE + | TableScan [TS_99] + | alias:date_dim + | Statistics:Num rows: 73049 Data size: 81741831 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 4 [SIMPLE_EDGE] + Reduce Output Operator [RS_121] + key expressions:_col0 (type: string) + Map-reduce partition columns:_col0 (type: string) + sort order:+ + Statistics:Num rows: 22000000 Data size: 22327357890 Basic stats: COMPLETE Column stats: NONE + value expressions:_col2 (type: int), _col3 (type: decimal(17,2)) + Select Operator [SEL_23] + outputColumnNames:["_col0","_col2","_col3"] + Statistics:Num rows: 22000000 Data size: 22327357890 Basic stats: COMPLETE Column stats: NONE + Group By Operator [GBY_22] + | aggregations:["sum(VALUE._col0)"] + | keys:KEY._col0 (type: int), KEY._col1 (type: int), KEY._col2 (type: string) + | outputColumnNames:["_col0","_col1","_col2","_col3"] + | Statistics:Num rows: 22000000 Data size: 22327357890 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 3 [SIMPLE_EDGE] + Reduce Output Operator [RS_21] + key expressions:_col0 (type: int), _col1 (type: int), _col2 (type: string) + Map-reduce partition columns:_col0 (type: int), _col1 (type: int), _col2 (type: string) + sort order:+++ + Statistics:Num rows: 44000000 Data size: 44654715780 Basic stats: COMPLETE Column stats: NONE + value expressions:_col3 (type: decimal(17,2)) + Group By Operator [GBY_20] + aggregations:["sum(_col2)"] + keys:_col4 (type: int), _col5 (type: int), _col7 (type: string) + outputColumnNames:["_col0","_col1","_col2","_col3"] + Statistics:Num rows: 44000000 Data size: 44654715780 Basic stats: COMPLETE Column stats: NONE + Select Operator [SEL_19] + outputColumnNames:["_col4","_col5","_col7","_col2"] + Statistics:Num rows: 44000000 Data size: 44654715780 Basic stats: COMPLETE Column stats: NONE + Merge Join Operator [MERGEJOIN_296] + | condition map:[{"":"Inner Join 0 to 1"}] + | keys:{"0":"_col1 (type: int)","1":"_col0 (type: int)"} + | outputColumnNames:["_col2","_col7"] + | Statistics:Num rows: 44000000 Data size: 44654715780 Basic stats: COMPLETE Column stats: NONE + |<-Map 9 [SIMPLE_EDGE] + | Reduce Output Operator [RS_17] + | key expressions:_col0 (type: int) + | Map-reduce partition columns:_col0 (type: int) + | sort order:+ + | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + | value expressions:_col1 (type: string) + | Select Operator [SEL_8] + | outputColumnNames:["_col0","_col1"] + | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + | Filter Operator [FIL_279] + | predicate:(ca_address_sk is not null and ca_county is not null) (type: boolean) + | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + | TableScan [TS_6] + | alias:customer_address + | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 2 [SIMPLE_EDGE] + Reduce Output Operator [RS_15] + key expressions:_col1 (type: int) + Map-reduce partition columns:_col1 (type: int) + sort order:+ + Statistics:Num rows: 20088 Data size: 22478696 Basic stats: COMPLETE Column stats: NONE + value expressions:_col2 (type: decimal(7,2)) + Merge Join Operator [MERGEJOIN_295] + | condition map:[{"":"Inner Join 0 to 1"}] + | keys:{"0":"_col0 (type: int)","1":"_col0 (type: int)"} + | outputColumnNames:["_col1","_col2"] + | Statistics:Num rows: 20088 Data size: 22478696 Basic stats: COMPLETE Column stats: NONE + |<-Map 1 [SIMPLE_EDGE] + | Reduce Output Operator [RS_10] + | key expressions:_col0 (type: int) + | Map-reduce partition columns:_col0 (type: int) + | sort order:+ + | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | value expressions:_col1 (type: int), _col2 (type: decimal(7,2)) + | Select Operator [SEL_2] + | outputColumnNames:["_col0","_col1","_col2"] + | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | Filter Operator [FIL_277] + | predicate:(ss_sold_date_sk is not null and ss_addr_sk is not null) (type: boolean) + | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | TableScan [TS_0] + | alias:store_sales + | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + |<-Map 8 [SIMPLE_EDGE] + Reduce Output Operator [RS_12] + key expressions:_col0 (type: int) + Map-reduce partition columns:_col0 (type: int) + sort order:+ + Statistics:Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE + Select Operator [SEL_5] + outputColumnNames:["_col0"] + Statistics:Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE + Filter Operator [FIL_278] + predicate:(((d_year = 1998) and (d_qoy = 1)) and d_date_sk is not null) (type: boolean) + Statistics:Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE + TableScan [TS_3] + alias:date_dim + Statistics:Num rows: 73049 Data size: 81741831 Basic stats: COMPLETE Column stats: NONE +
http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/results/clientpositive/perf/query32.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/query32.q.out b/ql/src/test/results/clientpositive/perf/query32.q.out new file mode 100644 index 0000000..f905498 --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/query32.q.out @@ -0,0 +1,205 @@ +PREHOOK: query: explain SELECT sum(cs1.cs_ext_discount_amt) as excess_discount_amount +FROM (SELECT cs.cs_item_sk as cs_item_sk, + cs.cs_ext_discount_amt as cs_ext_discount_amt + FROM catalog_sales cs + JOIN date_dim d ON (d.d_date_sk = cs.cs_sold_date_sk) + WHERE d.d_date between '2000-01-27' and '2000-04-27') cs1 +JOIN item i ON (i.i_item_sk = cs1.cs_item_sk) +JOIN (SELECT cs2.cs_item_sk as cs_item_sk, + 1.3 * avg(cs_ext_discount_amt) as avg_cs_ext_discount_amt + FROM (SELECT cs.cs_item_sk as cs_item_sk, + cs.cs_ext_discount_amt as cs_ext_discount_amt + FROM catalog_sales cs + JOIN date_dim d ON (d.d_date_sk = cs.cs_sold_date_sk) + WHERE d.d_date between '2000-01-27' and '2000-04-27') cs2 + GROUP BY cs2.cs_item_sk) tmp1 +ON (i.i_item_sk = tmp1.cs_item_sk) +WHERE i.i_manufact_id = 436 and + cs1.cs_ext_discount_amt > tmp1.avg_cs_ext_discount_amt +PREHOOK: type: QUERY +POSTHOOK: query: explain SELECT sum(cs1.cs_ext_discount_amt) as excess_discount_amount +FROM (SELECT cs.cs_item_sk as cs_item_sk, + cs.cs_ext_discount_amt as cs_ext_discount_amt + FROM catalog_sales cs + JOIN date_dim d ON (d.d_date_sk = cs.cs_sold_date_sk) + WHERE d.d_date between '2000-01-27' and '2000-04-27') cs1 +JOIN item i ON (i.i_item_sk = cs1.cs_item_sk) +JOIN (SELECT cs2.cs_item_sk as cs_item_sk, + 1.3 * avg(cs_ext_discount_amt) as avg_cs_ext_discount_amt + FROM (SELECT cs.cs_item_sk as cs_item_sk, + cs.cs_ext_discount_amt as cs_ext_discount_amt + FROM catalog_sales cs + JOIN date_dim d ON (d.d_date_sk = cs.cs_sold_date_sk) + WHERE d.d_date between '2000-01-27' and '2000-04-27') cs2 + GROUP BY cs2.cs_item_sk) tmp1 +ON (i.i_item_sk = tmp1.cs_item_sk) +WHERE i.i_manufact_id = 436 and + cs1.cs_ext_discount_amt > tmp1.avg_cs_ext_discount_amt +POSTHOOK: type: QUERY +Plan optimized by CBO. + +Vertex dependency in root stage +Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 5 (SIMPLE_EDGE) +Reducer 3 <- Map 6 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE), Reducer 9 (SIMPLE_EDGE) +Reducer 4 <- Reducer 3 (SIMPLE_EDGE) +Reducer 8 <- Map 10 (SIMPLE_EDGE), Map 7 (SIMPLE_EDGE) +Reducer 9 <- Reducer 8 (SIMPLE_EDGE) + +Stage-0 + Fetch Operator + limit:-1 + Stage-1 + Reducer 4 + File Output Operator [FS_44] + compressed:false + Statistics:Num rows: 1 Data size: 112 Basic stats: COMPLETE Column stats: NONE + table:{"input format:":"org.apache.hadoop.mapred.TextInputFormat","output format:":"org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat","serde:":"org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe"} + Group By Operator [GBY_42] + | aggregations:["sum(VALUE._col0)"] + | outputColumnNames:["_col0"] + | Statistics:Num rows: 1 Data size: 112 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 3 [SIMPLE_EDGE] + Reduce Output Operator [RS_41] + sort order: + Statistics:Num rows: 1 Data size: 112 Basic stats: COMPLETE Column stats: NONE + value expressions:_col0 (type: decimal(17,2)) + Group By Operator [GBY_40] + aggregations:["sum(_col1)"] + outputColumnNames:["_col0"] + Statistics:Num rows: 1 Data size: 112 Basic stats: COMPLETE Column stats: NONE + Select Operator [SEL_39] + outputColumnNames:["_col1"] + Statistics:Num rows: 169400 Data size: 243305505 Basic stats: COMPLETE Column stats: NONE + Filter Operator [FIL_59] + predicate:(_col1 > CAST( _col5 AS decimal(20,15))) (type: boolean) + Statistics:Num rows: 169400 Data size: 243305505 Basic stats: COMPLETE Column stats: NONE + Merge Join Operator [MERGEJOIN_67] + | condition map:[{"":"Inner Join 0 to 1"},{"":"Inner Join 1 to 2"}] + | keys:{"0":"_col0 (type: int)","1":"_col0 (type: int)","2":"_col0 (type: int)"} + | outputColumnNames:["_col1","_col5"] + | Statistics:Num rows: 508200 Data size: 729916517 Basic stats: COMPLETE Column stats: NONE + |<-Map 6 [SIMPLE_EDGE] + | Reduce Output Operator [RS_34] + | key expressions:_col0 (type: int) + | Map-reduce partition columns:_col0 (type: int) + | sort order:+ + | Statistics:Num rows: 231000 Data size: 331780228 Basic stats: COMPLETE Column stats: NONE + | Select Operator [SEL_14] + | outputColumnNames:["_col0"] + | Statistics:Num rows: 231000 Data size: 331780228 Basic stats: COMPLETE Column stats: NONE + | Filter Operator [FIL_62] + | predicate:((i_manufact_id = 436) and i_item_sk is not null) (type: boolean) + | Statistics:Num rows: 231000 Data size: 331780228 Basic stats: COMPLETE Column stats: NONE + | TableScan [TS_12] + | alias:i + | Statistics:Num rows: 462000 Data size: 663560457 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 2 [SIMPLE_EDGE] + | Reduce Output Operator [RS_32] + | key expressions:_col0 (type: int) + | Map-reduce partition columns:_col0 (type: int) + | sort order:+ + | Statistics:Num rows: 40176 Data size: 44957392 Basic stats: COMPLETE Column stats: NONE + | value expressions:_col1 (type: decimal(7,2)) + | Select Operator [SEL_11] + | outputColumnNames:["_col0","_col1"] + | Statistics:Num rows: 40176 Data size: 44957392 Basic stats: COMPLETE Column stats: NONE + | Merge Join Operator [MERGEJOIN_65] + | | condition map:[{"":"Inner Join 0 to 1"}] + | | keys:{"0":"_col0 (type: int)","1":"_col0 (type: int)"} + | | outputColumnNames:["_col1","_col2"] + | | Statistics:Num rows: 40176 Data size: 44957392 Basic stats: COMPLETE Column stats: NONE + | |<-Map 1 [SIMPLE_EDGE] + | | Reduce Output Operator [RS_7] + | | key expressions:_col0 (type: int) + | | Map-reduce partition columns:_col0 (type: int) + | | sort order:+ + | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | | value expressions:_col1 (type: int), _col2 (type: decimal(7,2)) + | | Select Operator [SEL_2] + | | outputColumnNames:["_col0","_col1","_col2"] + | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | | Filter Operator [FIL_60] + | | predicate:(cs_sold_date_sk is not null and cs_item_sk is not null) (type: boolean) + | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | | TableScan [TS_0] + | | alias:cs + | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | |<-Map 5 [SIMPLE_EDGE] + | Reduce Output Operator [RS_9] + | key expressions:_col0 (type: int) + | Map-reduce partition columns:_col0 (type: int) + | sort order:+ + | Statistics:Num rows: 36524 Data size: 40870356 Basic stats: COMPLETE Column stats: NONE + | Select Operator [SEL_5] + | outputColumnNames:["_col0"] + | Statistics:Num rows: 36524 Data size: 40870356 Basic stats: COMPLETE Column stats: NONE + | Filter Operator [FIL_61] + | predicate:(d_date BETWEEN '2000-01-27' AND '2000-04-27' and d_date_sk is not null) (type: boolean) + | Statistics:Num rows: 36524 Data size: 40870356 Basic stats: COMPLETE Column stats: NONE + | TableScan [TS_3] + | alias:d + | Statistics:Num rows: 73049 Data size: 81741831 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 9 [SIMPLE_EDGE] + Reduce Output Operator [RS_36] + key expressions:_col0 (type: int) + Map-reduce partition columns:_col0 (type: int) + sort order:+ + Statistics:Num rows: 20088 Data size: 22478696 Basic stats: COMPLETE Column stats: NONE + value expressions:_col1 (type: double) + Select Operator [SEL_30] + outputColumnNames:["_col0","_col1"] + Statistics:Num rows: 20088 Data size: 22478696 Basic stats: COMPLETE Column stats: NONE + Group By Operator [GBY_29] + | aggregations:["avg(VALUE._col0)"] + | keys:KEY._col0 (type: int) + | outputColumnNames:["_col0","_col1"] + | Statistics:Num rows: 20088 Data size: 22478696 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 8 [SIMPLE_EDGE] + Reduce Output Operator [RS_28] + key expressions:_col0 (type: int) + Map-reduce partition columns:_col0 (type: int) + sort order:+ + Statistics:Num rows: 40176 Data size: 44957392 Basic stats: COMPLETE Column stats: NONE + value expressions:_col1 (type: struct<count:bigint,sum:decimal(17,2),input:decimal(7,2)>) + Group By Operator [GBY_27] + aggregations:["avg(_col2)"] + keys:_col1 (type: int) + outputColumnNames:["_col0","_col1"] + Statistics:Num rows: 40176 Data size: 44957392 Basic stats: COMPLETE Column stats: NONE + Merge Join Operator [MERGEJOIN_66] + | condition map:[{"":"Inner Join 0 to 1"}] + | keys:{"0":"_col0 (type: int)","1":"_col0 (type: int)"} + | outputColumnNames:["_col1","_col2"] + | Statistics:Num rows: 40176 Data size: 44957392 Basic stats: COMPLETE Column stats: NONE + |<-Map 10 [SIMPLE_EDGE] + | Reduce Output Operator [RS_24] + | key expressions:_col0 (type: int) + | Map-reduce partition columns:_col0 (type: int) + | sort order:+ + | Statistics:Num rows: 36524 Data size: 40870356 Basic stats: COMPLETE Column stats: NONE + | Select Operator [SEL_20] + | outputColumnNames:["_col0"] + | Statistics:Num rows: 36524 Data size: 40870356 Basic stats: COMPLETE Column stats: NONE + | Filter Operator [FIL_64] + | predicate:(d_date BETWEEN '2000-01-27' AND '2000-04-27' and d_date_sk is not null) (type: boolean) + | Statistics:Num rows: 36524 Data size: 40870356 Basic stats: COMPLETE Column stats: NONE + | TableScan [TS_18] + | alias:d + | Statistics:Num rows: 73049 Data size: 81741831 Basic stats: COMPLETE Column stats: NONE + |<-Map 7 [SIMPLE_EDGE] + Reduce Output Operator [RS_22] + key expressions:_col0 (type: int) + Map-reduce partition columns:_col0 (type: int) + sort order:+ + Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + value expressions:_col1 (type: int), _col2 (type: decimal(7,2)) + Select Operator [SEL_17] + outputColumnNames:["_col0","_col1","_col2"] + Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + Filter Operator [FIL_63] + predicate:(cs_sold_date_sk is not null and cs_item_sk is not null) (type: boolean) + Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + TableScan [TS_15] + alias:cs + Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE +
