http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/query24.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query24.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query24.q.out new file mode 100644 index 0000000..7fbbc05 --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query24.q.out @@ -0,0 +1,343 @@ +Warning: Shuffle Join MERGEJOIN[287][tables = [$hdt$_0, $hdt$_1]] in Stage 'Reducer 8' is a cross product +PREHOOK: query: explain +with ssales as +(select c_last_name + ,c_first_name + ,s_store_name + ,ca_state + ,s_state + ,i_color + ,i_current_price + ,i_manager_id + ,i_units + ,i_size + ,sum(ss_sales_price) netpaid +from store_sales + ,store_returns + ,store + ,item + ,customer + ,customer_address +where ss_ticket_number = sr_ticket_number + and ss_item_sk = sr_item_sk + and ss_customer_sk = c_customer_sk + and ss_item_sk = i_item_sk + and ss_store_sk = s_store_sk + and c_birth_country = upper(ca_country) + and s_zip = ca_zip +and s_market_id=7 +group by c_last_name + ,c_first_name + ,s_store_name + ,ca_state + ,s_state + ,i_color + ,i_current_price + ,i_manager_id + ,i_units + ,i_size) +select c_last_name + ,c_first_name + ,s_store_name + ,sum(netpaid) paid +from ssales +where i_color = 'orchid' +group by c_last_name + ,c_first_name + ,s_store_name +having sum(netpaid) > (select 0.05*avg(netpaid) + from ssales) +PREHOOK: type: QUERY +PREHOOK: Input: default@customer +PREHOOK: Input: default@customer_address +PREHOOK: Input: default@item +PREHOOK: Input: default@store +PREHOOK: Input: default@store_returns +PREHOOK: Input: default@store_sales +PREHOOK: Output: hdfs://### HDFS PATH ### +POSTHOOK: query: explain +with ssales as +(select c_last_name + ,c_first_name + ,s_store_name + ,ca_state + ,s_state + ,i_color + ,i_current_price + ,i_manager_id + ,i_units + ,i_size + ,sum(ss_sales_price) netpaid +from store_sales + ,store_returns + ,store + ,item + ,customer + ,customer_address +where ss_ticket_number = sr_ticket_number + and ss_item_sk = sr_item_sk + and ss_customer_sk = c_customer_sk + and ss_item_sk = i_item_sk + and ss_store_sk = s_store_sk + and c_birth_country = upper(ca_country) + and s_zip = ca_zip +and s_market_id=7 +group by c_last_name + ,c_first_name + ,s_store_name + ,ca_state + ,s_state + ,i_color + ,i_current_price + ,i_manager_id + ,i_units + ,i_size) +select c_last_name + ,c_first_name + ,s_store_name + ,sum(netpaid) paid +from ssales +where i_color = 'orchid' +group by c_last_name + ,c_first_name + ,s_store_name +having sum(netpaid) > (select 0.05*avg(netpaid) + from ssales) +POSTHOOK: type: QUERY +POSTHOOK: Input: default@customer +POSTHOOK: Input: default@customer_address +POSTHOOK: Input: default@item +POSTHOOK: Input: default@store +POSTHOOK: Input: default@store_returns +POSTHOOK: Input: default@store_sales +POSTHOOK: Output: hdfs://### HDFS PATH ### +Plan optimized by CBO. + +Vertex dependency in root stage +Map 1 <- Reducer 10 (BROADCAST_EDGE), Reducer 17 (BROADCAST_EDGE) +Map 24 <- Reducer 20 (BROADCAST_EDGE) +Reducer 10 <- Map 9 (CUSTOM_SIMPLE_EDGE) +Reducer 11 <- Map 9 (SIMPLE_EDGE), Reducer 19 (SIMPLE_EDGE) +Reducer 12 <- Map 22 (SIMPLE_EDGE), Reducer 11 (SIMPLE_EDGE) +Reducer 13 <- Map 23 (SIMPLE_EDGE), Reducer 12 (SIMPLE_EDGE) +Reducer 14 <- Reducer 13 (SIMPLE_EDGE) +Reducer 15 <- Reducer 14 (CUSTOM_SIMPLE_EDGE) +Reducer 17 <- Map 16 (CUSTOM_SIMPLE_EDGE) +Reducer 18 <- Map 16 (SIMPLE_EDGE), Map 24 (SIMPLE_EDGE) +Reducer 19 <- Map 21 (SIMPLE_EDGE), Reducer 18 (SIMPLE_EDGE) +Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 9 (SIMPLE_EDGE) +Reducer 20 <- Map 16 (CUSTOM_SIMPLE_EDGE) +Reducer 3 <- Map 16 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) +Reducer 4 <- Map 21 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE) +Reducer 5 <- Map 22 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE) +Reducer 6 <- Map 23 (SIMPLE_EDGE), Reducer 5 (SIMPLE_EDGE) +Reducer 7 <- Reducer 6 (SIMPLE_EDGE) +Reducer 8 <- Reducer 15 (CUSTOM_SIMPLE_EDGE), Reducer 7 (CUSTOM_SIMPLE_EDGE) + +Stage-0 + Fetch Operator + limit:-1 + Stage-1 + Reducer 8 + File Output Operator [FS_88] + Select Operator [SEL_87] (rows=78393744 width=380) + Output:["_col0","_col1","_col2","_col3"] + Filter Operator [FIL_86] (rows=78393744 width=492) + predicate:(_col3 > _col4) + Merge Join Operator [MERGEJOIN_287] (rows=235181232 width=492) + Conds:(Inner),Output:["_col0","_col1","_col2","_col3","_col4"] + <-Reducer 15 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_340] + Select Operator [SEL_339] (rows=1 width=112) + Output:["_col0"] + Group By Operator [GBY_338] (rows=1 width=120) + Output:["_col0","_col1"],aggregations:["sum(VALUE._col0)","count(VALUE._col1)"] + <-Reducer 14 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_337] + Group By Operator [GBY_336] (rows=1 width=120) + Output:["_col0","_col1"],aggregations:["sum(_col10)","count(_col10)"] + Select Operator [SEL_335] (rows=2121289008973 width=932) + Output:["_col10"] + Group By Operator [GBY_334] (rows=2121289008973 width=932) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3, KEY._col4, KEY._col5, KEY._col6, KEY._col7, KEY._col8, KEY._col9 + <-Reducer 13 [SIMPLE_EDGE] + SHUFFLE [RS_75] + PartitionCols:_col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9 + Group By Operator [GBY_74] (rows=2121289008973 width=932) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10"],aggregations:["sum(_col4)"],keys:_col10, _col11, _col6, _col7, _col14, _col15, _col16, _col17, _col18, _col21 + Merge Join Operator [MERGEJOIN_286] (rows=2121289008973 width=932) + Conds:RS_70._col12, _col8=RS_323._col2, _col1(Inner),Output:["_col4","_col6","_col7","_col10","_col11","_col14","_col15","_col16","_col17","_col18","_col21"] + <-Map 23 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_323] + PartitionCols:_col2, _col1 + Select Operator [SEL_321] (rows=40000000 width=359) + Output:["_col0","_col1","_col2"] + Filter Operator [FIL_320] (rows=40000000 width=272) + predicate:(ca_zip is not null and upper(ca_country) is not null) + TableScan [TS_14] (rows=40000000 width=272) + default@customer_address,customer_address,Tbl:COMPLETE,Col:COMPLETE,Output:["ca_state","ca_zip","ca_country"] + <-Reducer 12 [SIMPLE_EDGE] + SHUFFLE [RS_70] + PartitionCols:_col12, _col8 + Merge Join Operator [MERGEJOIN_285] (rows=537799796 width=1023) + Conds:RS_67._col0, _col3=RS_319._col0, _col1(Inner),Output:["_col4","_col6","_col7","_col8","_col10","_col11","_col12","_col14","_col15","_col16","_col17","_col18"] + <-Map 22 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_319] + PartitionCols:_col0, _col1 + Select Operator [SEL_317] (rows=57591150 width=8) + Output:["_col0","_col1"] + TableScan [TS_12] (rows=57591150 width=8) + default@store_returns,store_returns,Tbl:COMPLETE,Col:COMPLETE,Output:["sr_item_sk","sr_ticket_number"] + <-Reducer 11 [SIMPLE_EDGE] + SHUFFLE [RS_67] + PartitionCols:_col0, _col3 + Merge Join Operator [MERGEJOIN_284] (rows=385681992 width=1029) + Conds:RS_64._col0=RS_291._col0(Inner),Output:["_col0","_col3","_col4","_col6","_col7","_col8","_col10","_col11","_col12","_col14","_col15","_col16","_col17","_col18"] + <-Map 9 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_291] + PartitionCols:_col0 + Select Operator [SEL_289] (rows=462000 width=384) + Output:["_col0","_col1","_col2","_col3","_col4","_col5"] + TableScan [TS_3] (rows=462000 width=384) + default@item,item,Tbl:COMPLETE,Col:COMPLETE,Output:["i_item_sk","i_current_price","i_size","i_color","i_units","i_manager_id"] + <-Reducer 19 [SIMPLE_EDGE] + SHUFFLE [RS_64] + PartitionCols:_col0 + Merge Join Operator [MERGEJOIN_283] (rows=385681992 width=648) + Conds:RS_61._col1=RS_316._col0(Inner),Output:["_col0","_col3","_col4","_col6","_col7","_col8","_col10","_col11","_col12"] + <-Map 21 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_316] + PartitionCols:_col0 + Select Operator [SEL_314] (rows=80000000 width=276) + Output:["_col0","_col1","_col2","_col3"] + Filter Operator [FIL_313] (rows=80000000 width=276) + predicate:c_birth_country is not null + TableScan [TS_9] (rows=80000000 width=276) + default@customer,customer,Tbl:COMPLETE,Col:COMPLETE,Output:["c_customer_sk","c_first_name","c_last_name","c_birth_country"] + <-Reducer 18 [SIMPLE_EDGE] + SHUFFLE [RS_61] + PartitionCols:_col1 + Merge Join Operator [MERGEJOIN_282] (rows=385681992 width=379) + Conds:RS_333._col2=RS_302._col0(Inner),Output:["_col0","_col1","_col3","_col4","_col6","_col7","_col8"] + <-Map 16 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_302] + PartitionCols:_col0 + Select Operator [SEL_299] (rows=155 width=267) + Output:["_col0","_col1","_col2","_col3"] + Filter Operator [FIL_298] (rows=155 width=271) + predicate:((s_market_id = 7) and s_zip is not null) + TableScan [TS_6] (rows=1704 width=270) + default@store,store,Tbl:COMPLETE,Col:COMPLETE,Output:["s_store_sk","s_store_name","s_market_id","s_state","s_zip"] + <-Map 24 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_333] + PartitionCols:_col2 + Select Operator [SEL_332] (rows=525333486 width=122) + Output:["_col0","_col1","_col2","_col3","_col4"] + Filter Operator [FIL_331] (rows=525333486 width=122) + predicate:((ss_store_sk BETWEEN DynamicValue(RS_59_store_s_store_sk_min) AND DynamicValue(RS_59_store_s_store_sk_max) and in_bloom_filter(ss_store_sk, DynamicValue(RS_59_store_s_store_sk_bloom_filter))) and ss_customer_sk is not null and ss_store_sk is not null) + TableScan [TS_42] (rows=575995635 width=122) + default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_item_sk","ss_customer_sk","ss_store_sk","ss_ticket_number","ss_sales_price"] + <-Reducer 20 [BROADCAST_EDGE] vectorized + BROADCAST [RS_330] + Group By Operator [GBY_329] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 16 [CUSTOM_SIMPLE_EDGE] vectorized + SHUFFLE [RS_307] + Group By Operator [GBY_305] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_303] (rows=155 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_299] + <-Reducer 7 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_328] + Select Operator [SEL_327] (rows=235181232 width=380) + Output:["_col0","_col1","_col2","_col3"] + Group By Operator [GBY_326] (rows=235181232 width=380) + Output:["_col0","_col1","_col2","_col3"],aggregations:["sum(_col9)"],keys:_col1, _col2, _col7 + Select Operator [SEL_325] (rows=365777643230 width=843) + Output:["_col1","_col2","_col7","_col9"] + Group By Operator [GBY_324] (rows=365777643230 width=843) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3, KEY._col4, KEY._col5, KEY._col6, KEY._col7, KEY._col8 + <-Reducer 6 [SIMPLE_EDGE] + SHUFFLE [RS_34] + PartitionCols:_col0, _col1, _col2 + Group By Operator [GBY_33] (rows=365777643230 width=843) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9"],aggregations:["sum(_col4)"],keys:_col15, _col16, _col11, _col20, _col6, _col7, _col8, _col9, _col12 + Merge Join Operator [MERGEJOIN_281] (rows=365777643230 width=843) + Conds:RS_29._col13, _col17=RS_322._col1, _col2(Inner),Output:["_col4","_col6","_col7","_col8","_col9","_col11","_col12","_col15","_col16","_col20"] + <-Map 23 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_322] + PartitionCols:_col1, _col2 + Please refer to the previous Select Operator [SEL_321] + <-Reducer 5 [SIMPLE_EDGE] + SHUFFLE [RS_29] + PartitionCols:_col13, _col17 + Merge Join Operator [MERGEJOIN_280] (rows=92733777 width=910) + Conds:RS_26._col0, _col3=RS_318._col0, _col1(Inner),Output:["_col4","_col6","_col7","_col8","_col9","_col11","_col12","_col13","_col15","_col16","_col17"] + <-Map 22 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_318] + PartitionCols:_col0, _col1 + Please refer to the previous Select Operator [SEL_317] + <-Reducer 4 [SIMPLE_EDGE] + SHUFFLE [RS_26] + PartitionCols:_col0, _col3 + Merge Join Operator [MERGEJOIN_279] (rows=56246341 width=899) + Conds:RS_23._col1=RS_315._col0(Inner),Output:["_col0","_col3","_col4","_col6","_col7","_col8","_col9","_col11","_col12","_col13","_col15","_col16","_col17"] + <-Map 21 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_315] + PartitionCols:_col0 + Please refer to the previous Select Operator [SEL_314] + <-Reducer 3 [SIMPLE_EDGE] + SHUFFLE [RS_23] + PartitionCols:_col1 + Merge Join Operator [MERGEJOIN_278] (rows=56246341 width=630) + Conds:RS_20._col2=RS_300._col0(Inner),Output:["_col0","_col1","_col3","_col4","_col6","_col7","_col8","_col9","_col11","_col12","_col13"] + <-Map 16 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_300] + PartitionCols:_col0 + Please refer to the previous Select Operator [SEL_299] + <-Reducer 2 [SIMPLE_EDGE] + SHUFFLE [RS_20] + PartitionCols:_col2 + Merge Join Operator [MERGEJOIN_277] (rows=76612563 width=382) + Conds:RS_312._col0=RS_292._col0(Inner),Output:["_col0","_col1","_col2","_col3","_col4","_col6","_col7","_col8","_col9"] + <-Map 9 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_292] + PartitionCols:_col0 + Select Operator [SEL_290] (rows=7000 width=295) + Output:["_col0","_col1","_col2","_col3","_col4"] + Filter Operator [FIL_288] (rows=7000 width=384) + predicate:(i_color = 'orchid') + Please refer to the previous TableScan [TS_3] + <-Map 1 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_312] + PartitionCols:_col0 + Select Operator [SEL_311] (rows=525333486 width=122) + Output:["_col0","_col1","_col2","_col3","_col4"] + Filter Operator [FIL_310] (rows=525333486 width=122) + predicate:((ss_item_sk BETWEEN DynamicValue(RS_18_item_i_item_sk_min) AND DynamicValue(RS_18_item_i_item_sk_max) and in_bloom_filter(ss_item_sk, DynamicValue(RS_18_item_i_item_sk_bloom_filter))) and (ss_store_sk BETWEEN DynamicValue(RS_21_store_s_store_sk_min) AND DynamicValue(RS_21_store_s_store_sk_max) and in_bloom_filter(ss_store_sk, DynamicValue(RS_21_store_s_store_sk_bloom_filter))) and ss_customer_sk is not null and ss_store_sk is not null) + TableScan [TS_0] (rows=575995635 width=122) + default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_item_sk","ss_customer_sk","ss_store_sk","ss_ticket_number","ss_sales_price"] + <-Reducer 10 [BROADCAST_EDGE] vectorized + BROADCAST [RS_297] + Group By Operator [GBY_296] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 9 [CUSTOM_SIMPLE_EDGE] vectorized + SHUFFLE [RS_295] + Group By Operator [GBY_294] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_293] (rows=7000 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_290] + <-Reducer 17 [BROADCAST_EDGE] vectorized + BROADCAST [RS_309] + Group By Operator [GBY_308] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 16 [CUSTOM_SIMPLE_EDGE] vectorized + SHUFFLE [RS_306] + Group By Operator [GBY_304] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_301] (rows=155 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_299] +
http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/query25.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query25.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query25.q.out new file mode 100644 index 0000000..a353c6a --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query25.q.out @@ -0,0 +1,321 @@ +PREHOOK: query: explain +select + i_item_id + ,i_item_desc + ,s_store_id + ,s_store_name + ,sum(ss_net_profit) as store_sales_profit + ,sum(sr_net_loss) as store_returns_loss + ,sum(cs_net_profit) as catalog_sales_profit + from + store_sales + ,store_returns + ,catalog_sales + ,date_dim d1 + ,date_dim d2 + ,date_dim d3 + ,store + ,item + where + d1.d_moy = 4 + and d1.d_year = 2000 + and d1.d_date_sk = ss_sold_date_sk + and i_item_sk = ss_item_sk + and s_store_sk = ss_store_sk + and ss_customer_sk = sr_customer_sk + and ss_item_sk = sr_item_sk + and ss_ticket_number = sr_ticket_number + and sr_returned_date_sk = d2.d_date_sk + and d2.d_moy between 4 and 10 + and d2.d_year = 2000 + and sr_customer_sk = cs_bill_customer_sk + and sr_item_sk = cs_item_sk + and cs_sold_date_sk = d3.d_date_sk + and d3.d_moy between 4 and 10 + and d3.d_year = 2000 + group by + i_item_id + ,i_item_desc + ,s_store_id + ,s_store_name + order by + i_item_id + ,i_item_desc + ,s_store_id + ,s_store_name + limit 100 +PREHOOK: type: QUERY +PREHOOK: Input: default@catalog_sales +PREHOOK: Input: default@date_dim +PREHOOK: Input: default@item +PREHOOK: Input: default@store +PREHOOK: Input: default@store_returns +PREHOOK: Input: default@store_sales +PREHOOK: Output: hdfs://### HDFS PATH ### +POSTHOOK: query: explain +select + i_item_id + ,i_item_desc + ,s_store_id + ,s_store_name + ,sum(ss_net_profit) as store_sales_profit + ,sum(sr_net_loss) as store_returns_loss + ,sum(cs_net_profit) as catalog_sales_profit + from + store_sales + ,store_returns + ,catalog_sales + ,date_dim d1 + ,date_dim d2 + ,date_dim d3 + ,store + ,item + where + d1.d_moy = 4 + and d1.d_year = 2000 + and d1.d_date_sk = ss_sold_date_sk + and i_item_sk = ss_item_sk + and s_store_sk = ss_store_sk + and ss_customer_sk = sr_customer_sk + and ss_item_sk = sr_item_sk + and ss_ticket_number = sr_ticket_number + and sr_returned_date_sk = d2.d_date_sk + and d2.d_moy between 4 and 10 + and d2.d_year = 2000 + and sr_customer_sk = cs_bill_customer_sk + and sr_item_sk = cs_item_sk + and cs_sold_date_sk = d3.d_date_sk + and d3.d_moy between 4 and 10 + and d3.d_year = 2000 + group by + i_item_id + ,i_item_desc + ,s_store_id + ,s_store_name + order by + i_item_id + ,i_item_desc + ,s_store_id + ,s_store_name + limit 100 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@catalog_sales +POSTHOOK: Input: default@date_dim +POSTHOOK: Input: default@item +POSTHOOK: Input: default@store +POSTHOOK: Input: default@store_returns +POSTHOOK: Input: default@store_sales +POSTHOOK: Output: hdfs://### HDFS PATH ### +Plan optimized by CBO. + +Vertex dependency in root stage +Map 1 <- Reducer 12 (BROADCAST_EDGE), Reducer 13 (BROADCAST_EDGE), Reducer 16 (BROADCAST_EDGE), Reducer 17 (BROADCAST_EDGE), Reducer 9 (BROADCAST_EDGE) +Map 18 <- Reducer 14 (BROADCAST_EDGE), Reducer 16 (BROADCAST_EDGE), Reducer 17 (BROADCAST_EDGE) +Reducer 10 <- Map 18 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE) +Reducer 11 <- Reducer 10 (SIMPLE_EDGE), Reducer 15 (SIMPLE_EDGE) +Reducer 12 <- Reducer 10 (CUSTOM_SIMPLE_EDGE) +Reducer 13 <- Reducer 10 (CUSTOM_SIMPLE_EDGE) +Reducer 14 <- Map 8 (CUSTOM_SIMPLE_EDGE) +Reducer 15 <- Map 19 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE) +Reducer 16 <- Reducer 15 (CUSTOM_SIMPLE_EDGE) +Reducer 17 <- Reducer 15 (CUSTOM_SIMPLE_EDGE) +Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE) +Reducer 3 <- Reducer 11 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) +Reducer 4 <- Map 20 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE) +Reducer 5 <- Map 21 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE) +Reducer 6 <- Reducer 5 (SIMPLE_EDGE) +Reducer 7 <- Reducer 6 (SIMPLE_EDGE) +Reducer 9 <- Map 8 (CUSTOM_SIMPLE_EDGE) + +Stage-0 + Fetch Operator + limit:100 + Stage-1 + Reducer 7 vectorized + File Output Operator [FS_259] + Limit [LIM_258] (rows=100 width=808) + Number of rows:100 + Select Operator [SEL_257] (rows=4248052806 width=808) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"] + <-Reducer 6 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_256] + Group By Operator [GBY_255] (rows=4248052806 width=808) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)"],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3 + <-Reducer 5 [SIMPLE_EDGE] + SHUFFLE [RS_47] + PartitionCols:_col0, _col1, _col2, _col3 + Group By Operator [GBY_46] (rows=4248052806 width=808) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"],aggregations:["sum(_col5)","sum(_col16)","sum(_col10)"],keys:_col19, _col20, _col22, _col23 + Top N Key Operator [TNK_93] (rows=4248052806 width=807) + keys:_col19, _col20, _col22, _col23,sort order:++++,top n:100 + Merge Join Operator [MERGEJOIN_212] (rows=4248052806 width=807) + Conds:RS_42._col3=RS_254._col0(Inner),Output:["_col5","_col10","_col16","_col19","_col20","_col22","_col23"] + <-Map 21 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_254] + PartitionCols:_col0 + Select Operator [SEL_253] (rows=1704 width=192) + Output:["_col0","_col1","_col2"] + TableScan [TS_31] (rows=1704 width=192) + default@store,store,Tbl:COMPLETE,Col:COMPLETE,Output:["s_store_sk","s_store_id","s_store_name"] + <-Reducer 4 [SIMPLE_EDGE] + SHUFFLE [RS_42] + PartitionCols:_col3 + Merge Join Operator [MERGEJOIN_211] (rows=4248052806 width=623) + Conds:RS_39._col1=RS_252._col0(Inner),Output:["_col3","_col5","_col10","_col16","_col19","_col20"] + <-Map 20 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_252] + PartitionCols:_col0 + Select Operator [SEL_251] (rows=462000 width=288) + Output:["_col0","_col1","_col2"] + TableScan [TS_29] (rows=462000 width=288) + default@item,item,Tbl:COMPLETE,Col:COMPLETE,Output:["i_item_sk","i_item_id","i_item_desc"] + <-Reducer 3 [SIMPLE_EDGE] + SHUFFLE [RS_39] + PartitionCols:_col1 + Merge Join Operator [MERGEJOIN_210] (rows=4248052806 width=343) + Conds:RS_36._col1, _col2, _col4=RS_37._col6, _col7, _col8(Inner),Output:["_col1","_col3","_col5","_col10","_col16"] + <-Reducer 11 [SIMPLE_EDGE] + SHUFFLE [RS_37] + PartitionCols:_col6, _col7, _col8 + Merge Join Operator [MERGEJOIN_209] (rows=1893811716 width=235) + Conds:RS_25._col2, _col1=RS_26._col1, _col2(Inner),Output:["_col3","_col6","_col7","_col8","_col9"] + <-Reducer 10 [SIMPLE_EDGE] + PARTITION_ONLY_SHUFFLE [RS_25] + PartitionCols:_col2, _col1 + Merge Join Operator [MERGEJOIN_207] (rows=54418158 width=119) + Conds:RS_243._col0=RS_221._col0(Inner),Output:["_col1","_col2","_col3"] + <-Map 8 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_221] + PartitionCols:_col0 + Select Operator [SEL_217] (rows=351 width=4) + Output:["_col0"] + Filter Operator [FIL_214] (rows=351 width=12) + predicate:((d_year = 2000) and d_moy BETWEEN 4 AND 10) + TableScan [TS_3] (rows=73049 width=12) + default@date_dim,d1,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_year","d_moy"] + <-Map 18 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_243] + PartitionCols:_col0 + Select Operator [SEL_242] (rows=285117831 width=123) + Output:["_col0","_col1","_col2","_col3"] + Filter Operator [FIL_241] (rows=285117831 width=123) + predicate:((cs_bill_customer_sk BETWEEN DynamicValue(RS_26_store_returns_sr_customer_sk_min) AND DynamicValue(RS_26_store_returns_sr_customer_sk_max) and in_bloom_filter(cs_bill_customer_sk, DynamicValue(RS_26_store_returns_sr_customer_sk_bloom_filter))) and (cs_item_sk BETWEEN DynamicValue(RS_26_store_returns_sr_item_sk_min) AND DynamicValue(RS_26_store_returns_sr_item_sk_max) and in_bloom_filter(cs_item_sk, DynamicValue(RS_26_store_returns_sr_item_sk_bloom_filter))) and (cs_sold_date_sk BETWEEN DynamicValue(RS_23_d3_d_date_sk_min) AND DynamicValue(RS_23_d3_d_date_sk_max) and in_bloom_filter(cs_sold_date_sk, DynamicValue(RS_23_d3_d_date_sk_bloom_filter))) and cs_bill_customer_sk is not null and cs_sold_date_sk is not null) + TableScan [TS_6] (rows=287989836 width=123) + default@catalog_sales,catalog_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["cs_sold_date_sk","cs_bill_customer_sk","cs_item_sk","cs_net_profit"] + <-Reducer 16 [BROADCAST_EDGE] vectorized + BROADCAST [RS_235] + Group By Operator [GBY_233] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Reducer 15 [CUSTOM_SIMPLE_EDGE] + PARTITION_ONLY_SHUFFLE [RS_106] + Group By Operator [GBY_105] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_104] (rows=9402909 width=8) + Output:["_col0"] + Merge Join Operator [MERGEJOIN_208] (rows=9402909 width=100) + Conds:RS_232._col0=RS_223._col0(Inner),Output:["_col1","_col2","_col3","_col4"] + <-Map 8 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_223] + PartitionCols:_col0 + Select Operator [SEL_218] (rows=351 width=4) + Output:["_col0"] + Filter Operator [FIL_215] (rows=351 width=12) + predicate:((d_year = 2000) and d_moy BETWEEN 4 AND 10) + Please refer to the previous TableScan [TS_3] + <-Map 19 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_232] + PartitionCols:_col0 + Select Operator [SEL_231] (rows=53632139 width=123) + Output:["_col0","_col1","_col2","_col3","_col4"] + Filter Operator [FIL_230] (rows=53632139 width=123) + predicate:(sr_customer_sk is not null and sr_returned_date_sk is not null) + TableScan [TS_12] (rows=57591150 width=123) + default@store_returns,store_returns,Tbl:COMPLETE,Col:COMPLETE,Output:["sr_returned_date_sk","sr_item_sk","sr_customer_sk","sr_ticket_number","sr_net_loss"] + <-Reducer 17 [BROADCAST_EDGE] vectorized + BROADCAST [RS_240] + Group By Operator [GBY_238] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Reducer 15 [CUSTOM_SIMPLE_EDGE] + PARTITION_ONLY_SHUFFLE [RS_121] + Group By Operator [GBY_120] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_119] (rows=9402909 width=6) + Output:["_col0"] + Please refer to the previous Merge Join Operator [MERGEJOIN_208] + <-Reducer 14 [BROADCAST_EDGE] vectorized + BROADCAST [RS_237] + Group By Operator [GBY_236] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 8 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_227] + Group By Operator [GBY_225] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_222] (rows=351 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_217] + <-Reducer 15 [SIMPLE_EDGE] + PARTITION_ONLY_SHUFFLE [RS_26] + PartitionCols:_col1, _col2 + Please refer to the previous Merge Join Operator [MERGEJOIN_208] + <-Reducer 2 [SIMPLE_EDGE] + SHUFFLE [RS_36] + PartitionCols:_col1, _col2, _col4 + Merge Join Operator [MERGEJOIN_206] (rows=13737330 width=8) + Conds:RS_250._col0=RS_219._col0(Inner),Output:["_col1","_col2","_col3","_col4","_col5"] + <-Map 8 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_219] + PartitionCols:_col0 + Select Operator [SEL_216] (rows=50 width=4) + Output:["_col0"] + Filter Operator [FIL_213] (rows=50 width=12) + predicate:((d_moy = 4) and (d_year = 2000)) + Please refer to the previous TableScan [TS_3] + <-Map 1 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_250] + PartitionCols:_col0 + Select Operator [SEL_249] (rows=501694138 width=126) + Output:["_col0","_col1","_col2","_col3","_col4","_col5"] + Filter Operator [FIL_248] (rows=501694138 width=126) + predicate:((ss_customer_sk BETWEEN DynamicValue(RS_25_catalog_sales_cs_bill_customer_sk_min) AND DynamicValue(RS_25_catalog_sales_cs_bill_customer_sk_max) and in_bloom_filter(ss_customer_sk, DynamicValue(RS_25_catalog_sales_cs_bill_customer_sk_bloom_filter))) and (ss_customer_sk BETWEEN DynamicValue(RS_26_store_returns_sr_customer_sk_min) AND DynamicValue(RS_26_store_returns_sr_customer_sk_max) and in_bloom_filter(ss_customer_sk, DynamicValue(RS_26_store_returns_sr_customer_sk_bloom_filter))) and (ss_item_sk BETWEEN DynamicValue(RS_25_catalog_sales_cs_item_sk_min) AND DynamicValue(RS_25_catalog_sales_cs_item_sk_max) and in_bloom_filter(ss_item_sk, DynamicValue(RS_25_catalog_sales_cs_item_sk_bloom_filter))) and (ss_item_sk BETWEEN DynamicValue(RS_26_store_returns_sr_item_sk_min) AND DynamicValue(RS_26_store_returns_sr_item_sk_max) and in_bloom_filter(ss_item_sk, DynamicValue(RS_26_store_returns_sr_item_sk_bloom_filter))) and (ss_sold_date_sk BETWEEN DynamicValue(RS_34_d1_d_date_sk_min) AND DynamicValue(RS_34_d1_d_date_sk_max) and in_bloom_filter(ss_sold_date_sk, DynamicValue(RS_34_d1_d_date_sk_bloom_filter))) and ss_customer_sk is not null and ss_sold_date_sk is not null and ss_store_sk is not null) + TableScan [TS_0] (rows=575995635 width=126) + default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_sold_date_sk","ss_item_sk","ss_customer_sk","ss_store_sk","ss_ticket_number","ss_net_profit"] + <-Reducer 16 [BROADCAST_EDGE] vectorized + BROADCAST [RS_234] + Please refer to the previous Group By Operator [GBY_233] + <-Reducer 17 [BROADCAST_EDGE] vectorized + BROADCAST [RS_239] + Please refer to the previous Group By Operator [GBY_238] + <-Reducer 12 [BROADCAST_EDGE] vectorized + BROADCAST [RS_245] + Group By Operator [GBY_244] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Reducer 10 [CUSTOM_SIMPLE_EDGE] + PARTITION_ONLY_SHUFFLE [RS_111] + Group By Operator [GBY_110] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_109] (rows=54418158 width=8) + Output:["_col0"] + Please refer to the previous Merge Join Operator [MERGEJOIN_207] + <-Reducer 13 [BROADCAST_EDGE] vectorized + BROADCAST [RS_247] + Group By Operator [GBY_246] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Reducer 10 [CUSTOM_SIMPLE_EDGE] + PARTITION_ONLY_SHUFFLE [RS_126] + Group By Operator [GBY_125] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_124] (rows=54418158 width=7) + Output:["_col0"] + Please refer to the previous Merge Join Operator [MERGEJOIN_207] + <-Reducer 9 [BROADCAST_EDGE] vectorized + BROADCAST [RS_229] + Group By Operator [GBY_228] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 8 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_226] + Group By Operator [GBY_224] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_220] (rows=50 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_216] + http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/query26.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query26.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query26.q.out new file mode 100644 index 0000000..ed032b1 --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query26.q.out @@ -0,0 +1,171 @@ +PREHOOK: query: explain +select i_item_id, + avg(cs_quantity) agg1, + avg(cs_list_price) agg2, + avg(cs_coupon_amt) agg3, + avg(cs_sales_price) agg4 + from catalog_sales, customer_demographics, date_dim, item, promotion + where cs_sold_date_sk = d_date_sk and + cs_item_sk = i_item_sk and + cs_bill_cdemo_sk = cd_demo_sk and + cs_promo_sk = p_promo_sk and + cd_gender = 'F' and + cd_marital_status = 'W' and + cd_education_status = 'Primary' and + (p_channel_email = 'N' or p_channel_event = 'N') and + d_year = 1998 + group by i_item_id + order by i_item_id + limit 100 +PREHOOK: type: QUERY +PREHOOK: Input: default@catalog_sales +PREHOOK: Input: default@customer_demographics +PREHOOK: Input: default@date_dim +PREHOOK: Input: default@item +PREHOOK: Input: default@promotion +PREHOOK: Output: hdfs://### HDFS PATH ### +POSTHOOK: query: explain +select i_item_id, + avg(cs_quantity) agg1, + avg(cs_list_price) agg2, + avg(cs_coupon_amt) agg3, + avg(cs_sales_price) agg4 + from catalog_sales, customer_demographics, date_dim, item, promotion + where cs_sold_date_sk = d_date_sk and + cs_item_sk = i_item_sk and + cs_bill_cdemo_sk = cd_demo_sk and + cs_promo_sk = p_promo_sk and + cd_gender = 'F' and + cd_marital_status = 'W' and + cd_education_status = 'Primary' and + (p_channel_email = 'N' or p_channel_event = 'N') and + d_year = 1998 + group by i_item_id + order by i_item_id + limit 100 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@catalog_sales +POSTHOOK: Input: default@customer_demographics +POSTHOOK: Input: default@date_dim +POSTHOOK: Input: default@item +POSTHOOK: Input: default@promotion +POSTHOOK: Output: hdfs://### HDFS PATH ### +Plan optimized by CBO. + +Vertex dependency in root stage +Map 1 <- Reducer 11 (BROADCAST_EDGE), Reducer 9 (BROADCAST_EDGE) +Reducer 11 <- Map 10 (CUSTOM_SIMPLE_EDGE) +Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE) +Reducer 3 <- Map 10 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) +Reducer 4 <- Map 12 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE) +Reducer 5 <- Map 13 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE) +Reducer 6 <- Reducer 5 (SIMPLE_EDGE) +Reducer 7 <- Reducer 6 (SIMPLE_EDGE) +Reducer 9 <- Map 8 (CUSTOM_SIMPLE_EDGE) + +Stage-0 + Fetch Operator + limit:100 + Stage-1 + Reducer 7 vectorized + File Output Operator [FS_128] + Limit [LIM_127] (rows=100 width=444) + Number of rows:100 + Select Operator [SEL_126] (rows=310774 width=444) + Output:["_col0","_col1","_col2","_col3","_col4"] + <-Reducer 6 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_125] + Select Operator [SEL_124] (rows=310774 width=444) + Output:["_col0","_col1","_col2","_col3","_col4"] + Group By Operator [GBY_123] (rows=310774 width=476) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"],aggregations:["sum(VALUE._col0)","count(VALUE._col1)","sum(VALUE._col2)","count(VALUE._col3)","sum(VALUE._col4)","count(VALUE._col5)","sum(VALUE._col6)","count(VALUE._col7)"],keys:KEY._col0 + <-Reducer 5 [SIMPLE_EDGE] + SHUFFLE [RS_28] + PartitionCols:_col0 + Group By Operator [GBY_27] (rows=462000 width=476) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"],aggregations:["sum(_col4)","count(_col4)","sum(_col5)","count(_col5)","sum(_col7)","count(_col7)","sum(_col6)","count(_col6)"],keys:_col12 + Top N Key Operator [TNK_54] (rows=809521 width=100) + keys:_col12,sort order:+,top n:100 + Merge Join Operator [MERGEJOIN_98] (rows=809521 width=100) + Conds:RS_23._col2=RS_122._col0(Inner),Output:["_col4","_col5","_col6","_col7","_col12"] + <-Map 13 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_122] + PartitionCols:_col0 + Select Operator [SEL_121] (rows=462000 width=104) + Output:["_col0","_col1"] + TableScan [TS_12] (rows=462000 width=104) + default@item,item,Tbl:COMPLETE,Col:COMPLETE,Output:["i_item_sk","i_item_id"] + <-Reducer 4 [SIMPLE_EDGE] + SHUFFLE [RS_23] + PartitionCols:_col2 + Merge Join Operator [MERGEJOIN_97] (rows=809521 width=4) + Conds:RS_20._col3=RS_120._col0(Inner),Output:["_col2","_col4","_col5","_col6","_col7"] + <-Map 12 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_120] + PartitionCols:_col0 + Select Operator [SEL_119] (rows=2300 width=4) + Output:["_col0"] + Filter Operator [FIL_118] (rows=2300 width=174) + predicate:((p_channel_email = 'N') or (p_channel_event = 'N')) + TableScan [TS_9] (rows=2300 width=174) + default@promotion,promotion,Tbl:COMPLETE,Col:COMPLETE,Output:["p_promo_sk","p_channel_email","p_channel_event"] + <-Reducer 3 [SIMPLE_EDGE] + SHUFFLE [RS_20] + PartitionCols:_col3 + Merge Join Operator [MERGEJOIN_96] (rows=809521 width=4) + Conds:RS_17._col0=RS_109._col0(Inner),Output:["_col2","_col3","_col4","_col5","_col6","_col7"] + <-Map 10 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_109] + PartitionCols:_col0 + Select Operator [SEL_108] (rows=652 width=4) + Output:["_col0"] + Filter Operator [FIL_107] (rows=652 width=8) + predicate:(d_year = 1998) + TableScan [TS_6] (rows=73049 width=8) + default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_year"] + <-Reducer 2 [SIMPLE_EDGE] + SHUFFLE [RS_17] + PartitionCols:_col0 + Merge Join Operator [MERGEJOIN_95] (rows=2283326 width=135) + Conds:RS_117._col1=RS_101._col0(Inner),Output:["_col0","_col2","_col3","_col4","_col5","_col6","_col7"] + <-Map 8 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_101] + PartitionCols:_col0 + Select Operator [SEL_100] (rows=14776 width=4) + Output:["_col0"] + Filter Operator [FIL_99] (rows=14776 width=268) + predicate:((cd_education_status = 'Primary') and (cd_gender = 'F') and (cd_marital_status = 'W')) + TableScan [TS_3] (rows=1861800 width=268) + default@customer_demographics,customer_demographics,Tbl:COMPLETE,Col:COMPLETE,Output:["cd_demo_sk","cd_gender","cd_marital_status","cd_education_status"] + <-Map 1 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_117] + PartitionCols:_col1 + Select Operator [SEL_116] (rows=283691050 width=354) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"] + Filter Operator [FIL_115] (rows=283691050 width=354) + predicate:((cs_bill_cdemo_sk BETWEEN DynamicValue(RS_15_customer_demographics_cd_demo_sk_min) AND DynamicValue(RS_15_customer_demographics_cd_demo_sk_max) and in_bloom_filter(cs_bill_cdemo_sk, DynamicValue(RS_15_customer_demographics_cd_demo_sk_bloom_filter))) and (cs_sold_date_sk BETWEEN DynamicValue(RS_18_date_dim_d_date_sk_min) AND DynamicValue(RS_18_date_dim_d_date_sk_max) and in_bloom_filter(cs_sold_date_sk, DynamicValue(RS_18_date_dim_d_date_sk_bloom_filter))) and cs_bill_cdemo_sk is not null and cs_promo_sk is not null and cs_sold_date_sk is not null) + TableScan [TS_0] (rows=287989836 width=354) + default@catalog_sales,catalog_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["cs_sold_date_sk","cs_bill_cdemo_sk","cs_item_sk","cs_promo_sk","cs_quantity","cs_list_price","cs_sales_price","cs_coupon_amt"] + <-Reducer 11 [BROADCAST_EDGE] vectorized + BROADCAST [RS_114] + Group By Operator [GBY_113] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 10 [CUSTOM_SIMPLE_EDGE] vectorized + SHUFFLE [RS_112] + Group By Operator [GBY_111] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_110] (rows=652 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_108] + <-Reducer 9 [BROADCAST_EDGE] vectorized + BROADCAST [RS_106] + Group By Operator [GBY_105] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 8 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_104] + Group By Operator [GBY_103] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_102] (rows=14776 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_100] + http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/query27.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query27.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query27.q.out new file mode 100644 index 0000000..fdc1791 --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query27.q.out @@ -0,0 +1,189 @@ +PREHOOK: query: explain +select i_item_id, + s_state, grouping(s_state) g_state, + avg(ss_quantity) agg1, + avg(ss_list_price) agg2, + avg(ss_coupon_amt) agg3, + avg(ss_sales_price) agg4 + from store_sales, customer_demographics, date_dim, store, item + where ss_sold_date_sk = d_date_sk and + ss_item_sk = i_item_sk and + ss_store_sk = s_store_sk and + ss_cdemo_sk = cd_demo_sk and + cd_gender = 'M' and + cd_marital_status = 'U' and + cd_education_status = '2 yr Degree' and + d_year = 2001 and + s_state in ('SD','FL', 'MI', 'LA', 'MO', 'SC') + group by rollup (i_item_id, s_state) + order by i_item_id + ,s_state + limit 100 +PREHOOK: type: QUERY +PREHOOK: Input: default@customer_demographics +PREHOOK: Input: default@date_dim +PREHOOK: Input: default@item +PREHOOK: Input: default@store +PREHOOK: Input: default@store_sales +PREHOOK: Output: hdfs://### HDFS PATH ### +POSTHOOK: query: explain +select i_item_id, + s_state, grouping(s_state) g_state, + avg(ss_quantity) agg1, + avg(ss_list_price) agg2, + avg(ss_coupon_amt) agg3, + avg(ss_sales_price) agg4 + from store_sales, customer_demographics, date_dim, store, item + where ss_sold_date_sk = d_date_sk and + ss_item_sk = i_item_sk and + ss_store_sk = s_store_sk and + ss_cdemo_sk = cd_demo_sk and + cd_gender = 'M' and + cd_marital_status = 'U' and + cd_education_status = '2 yr Degree' and + d_year = 2001 and + s_state in ('SD','FL', 'MI', 'LA', 'MO', 'SC') + group by rollup (i_item_id, s_state) + order by i_item_id + ,s_state + limit 100 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@customer_demographics +POSTHOOK: Input: default@date_dim +POSTHOOK: Input: default@item +POSTHOOK: Input: default@store +POSTHOOK: Input: default@store_sales +POSTHOOK: Output: hdfs://### HDFS PATH ### +Plan optimized by CBO. + +Vertex dependency in root stage +Map 1 <- Reducer 11 (BROADCAST_EDGE), Reducer 13 (BROADCAST_EDGE), Reducer 9 (BROADCAST_EDGE) +Reducer 11 <- Map 10 (CUSTOM_SIMPLE_EDGE) +Reducer 13 <- Map 12 (CUSTOM_SIMPLE_EDGE) +Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE) +Reducer 3 <- Map 10 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) +Reducer 4 <- Map 12 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE) +Reducer 5 <- Map 14 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE) +Reducer 6 <- Reducer 5 (SIMPLE_EDGE) +Reducer 7 <- Reducer 6 (SIMPLE_EDGE) +Reducer 9 <- Map 8 (CUSTOM_SIMPLE_EDGE) + +Stage-0 + Fetch Operator + limit:100 + Stage-1 + Reducer 7 vectorized + File Output Operator [FS_134] + Limit [LIM_133] (rows=100 width=538) + Number of rows:100 + Select Operator [SEL_132] (rows=4281825 width=538) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"] + <-Reducer 6 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_131] + Select Operator [SEL_130] (rows=4281825 width=538) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"] + Group By Operator [GBY_129] (rows=4281825 width=570) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10"],aggregations:["sum(VALUE._col0)","count(VALUE._col1)","sum(VALUE._col2)","count(VALUE._col3)","sum(VALUE._col4)","count(VALUE._col5)","sum(VALUE._col6)","count(VALUE._col7)"],keys:KEY._col0, KEY._col1, KEY._col2 + <-Reducer 5 [SIMPLE_EDGE] + SHUFFLE [RS_29] + PartitionCols:_col0, _col1, _col2 + Group By Operator [GBY_28] (rows=4281825 width=570) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10"],aggregations:["sum(_col2)","count(_col2)","sum(_col3)","count(_col3)","sum(_col4)","count(_col4)","sum(_col5)","count(_col5)"],keys:_col0, _col1, 0L + Top N Key Operator [TNK_55] (rows=1427275 width=186) + keys:_col0, _col1, 0L,sort order:+++,top n:100 + Select Operator [SEL_26] (rows=1427275 width=186) + Output:["_col0","_col1","_col2","_col3","_col4","_col5"] + Merge Join Operator [MERGEJOIN_99] (rows=1427275 width=186) + Conds:RS_23._col1=RS_128._col0(Inner),Output:["_col4","_col5","_col6","_col7","_col11","_col13"] + <-Map 14 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_128] + PartitionCols:_col0 + Select Operator [SEL_127] (rows=462000 width=104) + Output:["_col0","_col1"] + TableScan [TS_12] (rows=462000 width=104) + default@item,item,Tbl:COMPLETE,Col:COMPLETE,Output:["i_item_sk","i_item_id"] + <-Reducer 4 [SIMPLE_EDGE] + SHUFFLE [RS_23] + PartitionCols:_col1 + Merge Join Operator [MERGEJOIN_98] (rows=1427275 width=90) + Conds:RS_20._col3=RS_118._col0(Inner),Output:["_col1","_col4","_col5","_col6","_col7","_col11"] + <-Map 12 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_118] + PartitionCols:_col0 + Select Operator [SEL_117] (rows=209 width=90) + Output:["_col0","_col1"] + Filter Operator [FIL_116] (rows=209 width=90) + predicate:(s_state) IN ('SD', 'FL', 'MI', 'LA', 'MO', 'SC') + TableScan [TS_9] (rows=1704 width=90) + default@store,store,Tbl:COMPLETE,Col:COMPLETE,Output:["s_store_sk","s_state"] + <-Reducer 3 [SIMPLE_EDGE] + SHUFFLE [RS_20] + PartitionCols:_col3 + Merge Join Operator [MERGEJOIN_97] (rows=1441779 width=4) + Conds:RS_17._col0=RS_110._col0(Inner),Output:["_col1","_col3","_col4","_col5","_col6","_col7"] + <-Map 10 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_110] + PartitionCols:_col0 + Select Operator [SEL_109] (rows=652 width=4) + Output:["_col0"] + Filter Operator [FIL_108] (rows=652 width=8) + predicate:(d_year = 2001) + TableScan [TS_6] (rows=73049 width=8) + default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_year"] + <-Reducer 2 [SIMPLE_EDGE] + SHUFFLE [RS_17] + PartitionCols:_col0 + Merge Join Operator [MERGEJOIN_96] (rows=4037920 width=4) + Conds:RS_126._col2=RS_102._col0(Inner),Output:["_col0","_col1","_col3","_col4","_col5","_col6","_col7"] + <-Map 8 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_102] + PartitionCols:_col0 + Select Operator [SEL_101] (rows=14776 width=4) + Output:["_col0"] + Filter Operator [FIL_100] (rows=14776 width=268) + predicate:((cd_education_status = '2 yr Degree') and (cd_gender = 'M') and (cd_marital_status = 'U')) + TableScan [TS_3] (rows=1861800 width=268) + default@customer_demographics,customer_demographics,Tbl:COMPLETE,Col:COMPLETE,Output:["cd_demo_sk","cd_gender","cd_marital_status","cd_education_status"] + <-Map 1 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_126] + PartitionCols:_col2 + Select Operator [SEL_125] (rows=501690006 width=340) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"] + Filter Operator [FIL_124] (rows=501690006 width=340) + predicate:((ss_cdemo_sk BETWEEN DynamicValue(RS_15_customer_demographics_cd_demo_sk_min) AND DynamicValue(RS_15_customer_demographics_cd_demo_sk_max) and in_bloom_filter(ss_cdemo_sk, DynamicValue(RS_15_customer_demographics_cd_demo_sk_bloom_filter))) and (ss_sold_date_sk BETWEEN DynamicValue(RS_18_date_dim_d_date_sk_min) AND DynamicValue(RS_18_date_dim_d_date_sk_max) and in_bloom_filter(ss_sold_date_sk, DynamicValue(RS_18_date_dim_d_date_sk_bloom_filter))) and (ss_store_sk BETWEEN DynamicValue(RS_21_store_s_store_sk_min) AND DynamicValue(RS_21_store_s_store_sk_max) and in_bloom_filter(ss_store_sk, DynamicValue(RS_21_store_s_store_sk_bloom_filter))) and ss_cdemo_sk is not null and ss_sold_date_sk is not null and ss_store_sk is not null) + TableScan [TS_0] (rows=575995635 width=340) + default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_sold_date_sk","ss_item_sk","ss_cdemo_sk","ss_store_sk","ss_quantity","ss_list_price","ss_sales_price","ss_coupon_amt"] + <-Reducer 11 [BROADCAST_EDGE] vectorized + BROADCAST [RS_115] + Group By Operator [GBY_114] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 10 [CUSTOM_SIMPLE_EDGE] vectorized + SHUFFLE [RS_113] + Group By Operator [GBY_112] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_111] (rows=652 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_109] + <-Reducer 13 [BROADCAST_EDGE] vectorized + BROADCAST [RS_123] + Group By Operator [GBY_122] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 12 [CUSTOM_SIMPLE_EDGE] vectorized + SHUFFLE [RS_121] + Group By Operator [GBY_120] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_119] (rows=209 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_117] + <-Reducer 9 [BROADCAST_EDGE] vectorized + BROADCAST [RS_107] + Group By Operator [GBY_106] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 8 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_105] + Group By Operator [GBY_104] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_103] (rows=14776 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_101] +
