http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/query61.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query61.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query61.q.out new file mode 100644 index 0000000..d9543ad --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query61.q.out @@ -0,0 +1,388 @@ +Warning: Shuffle Join MERGEJOIN[266][tables = [$hdt$_0, $hdt$_1]] in Stage 'Reducer 5' is a cross product +PREHOOK: query: explain +select promotions,total,cast(promotions as decimal(15,4))/cast(total as decimal(15,4))*100 +from + (select sum(ss_ext_sales_price) promotions + from store_sales + ,store + ,promotion + ,date_dim + ,customer + ,customer_address + ,item + where ss_sold_date_sk = d_date_sk + and ss_store_sk = s_store_sk + and ss_promo_sk = p_promo_sk + and ss_customer_sk= c_customer_sk + and ca_address_sk = c_current_addr_sk + and ss_item_sk = i_item_sk + and ca_gmt_offset = -7 + and i_category = 'Electronics' + and (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y') + and s_gmt_offset = -7 + and d_year = 1999 + and d_moy = 11) promotional_sales, + (select sum(ss_ext_sales_price) total + from store_sales + ,store + ,date_dim + ,customer + ,customer_address + ,item + where ss_sold_date_sk = d_date_sk + and ss_store_sk = s_store_sk + and ss_customer_sk= c_customer_sk + and ca_address_sk = c_current_addr_sk + and ss_item_sk = i_item_sk + and ca_gmt_offset = -7 + and i_category = 'Electronics' + and s_gmt_offset = -7 + and d_year = 1999 + and d_moy = 11) all_sales +order by promotions, total +limit 100 +PREHOOK: type: QUERY +PREHOOK: Input: default@customer +PREHOOK: Input: default@customer_address +PREHOOK: Input: default@date_dim +PREHOOK: Input: default@item +PREHOOK: Input: default@promotion +PREHOOK: Input: default@store +PREHOOK: Input: default@store_sales +PREHOOK: Output: hdfs://### HDFS PATH ### +POSTHOOK: query: explain +select promotions,total,cast(promotions as decimal(15,4))/cast(total as decimal(15,4))*100 +from + (select sum(ss_ext_sales_price) promotions + from store_sales + ,store + ,promotion + ,date_dim + ,customer + ,customer_address + ,item + where ss_sold_date_sk = d_date_sk + and ss_store_sk = s_store_sk + and ss_promo_sk = p_promo_sk + and ss_customer_sk= c_customer_sk + and ca_address_sk = c_current_addr_sk + and ss_item_sk = i_item_sk + and ca_gmt_offset = -7 + and i_category = 'Electronics' + and (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y') + and s_gmt_offset = -7 + and d_year = 1999 + and d_moy = 11) promotional_sales, + (select sum(ss_ext_sales_price) total + from store_sales + ,store + ,date_dim + ,customer + ,customer_address + ,item + where ss_sold_date_sk = d_date_sk + and ss_store_sk = s_store_sk + and ss_customer_sk= c_customer_sk + and ca_address_sk = c_current_addr_sk + and ss_item_sk = i_item_sk + and ca_gmt_offset = -7 + and i_category = 'Electronics' + and s_gmt_offset = -7 + and d_year = 1999 + and d_moy = 11) all_sales +order by promotions, total +limit 100 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@customer +POSTHOOK: Input: default@customer_address +POSTHOOK: Input: default@date_dim +POSTHOOK: Input: default@item +POSTHOOK: Input: default@promotion +POSTHOOK: Input: default@store +POSTHOOK: Input: default@store_sales +POSTHOOK: Output: hdfs://### HDFS PATH ### +Plan optimized by CBO. + +Vertex dependency in root stage +Map 12 <- Reducer 18 (BROADCAST_EDGE), Reducer 24 (BROADCAST_EDGE), Reducer 27 (BROADCAST_EDGE), Reducer 7 (BROADCAST_EDGE) +Map 30 <- Reducer 10 (BROADCAST_EDGE), Reducer 22 (BROADCAST_EDGE), Reducer 25 (BROADCAST_EDGE), Reducer 28 (BROADCAST_EDGE) +Reducer 10 <- Reducer 2 (CUSTOM_SIMPLE_EDGE) +Reducer 13 <- Map 12 (SIMPLE_EDGE), Map 17 (SIMPLE_EDGE) +Reducer 14 <- Map 23 (SIMPLE_EDGE), Reducer 13 (SIMPLE_EDGE) +Reducer 15 <- Map 26 (SIMPLE_EDGE), Reducer 14 (SIMPLE_EDGE) +Reducer 16 <- Map 29 (SIMPLE_EDGE), Reducer 15 (SIMPLE_EDGE) +Reducer 18 <- Map 17 (CUSTOM_SIMPLE_EDGE) +Reducer 19 <- Map 17 (SIMPLE_EDGE), Map 30 (SIMPLE_EDGE) +Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 11 (SIMPLE_EDGE) +Reducer 20 <- Map 23 (SIMPLE_EDGE), Reducer 19 (SIMPLE_EDGE) +Reducer 21 <- Map 26 (SIMPLE_EDGE), Reducer 20 (SIMPLE_EDGE) +Reducer 22 <- Map 17 (CUSTOM_SIMPLE_EDGE) +Reducer 24 <- Map 23 (CUSTOM_SIMPLE_EDGE) +Reducer 25 <- Map 23 (CUSTOM_SIMPLE_EDGE) +Reducer 27 <- Map 26 (CUSTOM_SIMPLE_EDGE) +Reducer 28 <- Map 26 (CUSTOM_SIMPLE_EDGE) +Reducer 3 <- Reducer 16 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) +Reducer 4 <- Reducer 3 (CUSTOM_SIMPLE_EDGE) +Reducer 5 <- Reducer 4 (CUSTOM_SIMPLE_EDGE), Reducer 9 (CUSTOM_SIMPLE_EDGE) +Reducer 6 <- Reducer 5 (SIMPLE_EDGE) +Reducer 7 <- Reducer 2 (CUSTOM_SIMPLE_EDGE) +Reducer 8 <- Reducer 2 (SIMPLE_EDGE), Reducer 21 (SIMPLE_EDGE) +Reducer 9 <- Reducer 8 (CUSTOM_SIMPLE_EDGE) + +Stage-0 + Fetch Operator + limit:100 + Stage-1 + Reducer 6 vectorized + File Output Operator [FS_336] + Limit [LIM_335] (rows=1 width=336) + Number of rows:100 + Select Operator [SEL_334] (rows=1 width=336) + Output:["_col0","_col1","_col2"] + <-Reducer 5 [SIMPLE_EDGE] + SHUFFLE [RS_88] + Select Operator [SEL_87] (rows=1 width=336) + Output:["_col0","_col1","_col2"] + Merge Join Operator [MERGEJOIN_266] (rows=1 width=448) + Conds:(Inner),Output:["_col0","_col1","_col2","_col3"] + <-Reducer 4 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_319] + Select Operator [SEL_318] (rows=1 width=224) + Output:["_col0","_col1"] + Group By Operator [GBY_317] (rows=1 width=112) + Output:["_col0"],aggregations:["sum(VALUE._col0)"] + <-Reducer 3 [CUSTOM_SIMPLE_EDGE] + PARTITION_ONLY_SHUFFLE [RS_42] + Group By Operator [GBY_41] (rows=1 width=112) + Output:["_col0"],aggregations:["sum(_col8)"] + Merge Join Operator [MERGEJOIN_264] (rows=505397 width=0) + Conds:RS_37._col0=RS_38._col2(Inner),Output:["_col8"] + <-Reducer 2 [SIMPLE_EDGE] + SHUFFLE [RS_37] + PartitionCols:_col0 + Merge Join Operator [MERGEJOIN_255] (rows=16000001 width=4) + Conds:RS_269._col1=RS_272._col0(Inner),Output:["_col0"] + <-Map 1 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_269] + PartitionCols:_col1 + Select Operator [SEL_268] (rows=80000000 width=8) + Output:["_col0","_col1"] + Filter Operator [FIL_267] (rows=80000000 width=8) + predicate:c_current_addr_sk is not null + TableScan [TS_0] (rows=80000000 width=8) + default@customer,customer,Tbl:COMPLETE,Col:COMPLETE,Output:["c_customer_sk","c_current_addr_sk"] + <-Map 11 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_272] + PartitionCols:_col0 + Select Operator [SEL_271] (rows=8000000 width=4) + Output:["_col0"] + Filter Operator [FIL_270] (rows=8000000 width=112) + predicate:(ca_gmt_offset = -7) + TableScan [TS_3] (rows=40000000 width=112) + default@customer_address,customer_address,Tbl:COMPLETE,Col:COMPLETE,Output:["ca_address_sk","ca_gmt_offset"] + <-Reducer 16 [SIMPLE_EDGE] + SHUFFLE [RS_38] + PartitionCols:_col2 + Merge Join Operator [MERGEJOIN_259] (rows=2526982 width=0) + Conds:RS_30._col4=RS_316._col0(Inner),Output:["_col2","_col5"] + <-Map 29 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_316] + PartitionCols:_col0 + Select Operator [SEL_315] (rows=2300 width=4) + Output:["_col0"] + Filter Operator [FIL_314] (rows=2300 width=259) + predicate:((p_channel_dmail = 'Y') or (p_channel_email = 'Y') or (p_channel_tv = 'Y')) + TableScan [TS_18] (rows=2300 width=259) + default@promotion,promotion,Tbl:COMPLETE,Col:COMPLETE,Output:["p_promo_sk","p_channel_dmail","p_channel_email","p_channel_tv"] + <-Reducer 15 [SIMPLE_EDGE] + SHUFFLE [RS_30] + PartitionCols:_col4 + Merge Join Operator [MERGEJOIN_258] (rows=2526982 width=0) + Conds:RS_27._col3=RS_299._col0(Inner),Output:["_col2","_col4","_col5"] + <-Map 26 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_299] + PartitionCols:_col0 + Select Operator [SEL_298] (rows=341 width=4) + Output:["_col0"] + Filter Operator [FIL_297] (rows=341 width=115) + predicate:(s_gmt_offset = -7) + TableScan [TS_15] (rows=1704 width=115) + default@store,store,Tbl:COMPLETE,Col:COMPLETE,Output:["s_store_sk","s_gmt_offset"] + <-Reducer 14 [SIMPLE_EDGE] + SHUFFLE [RS_27] + PartitionCols:_col3 + Merge Join Operator [MERGEJOIN_257] (rows=12627499 width=0) + Conds:RS_24._col1=RS_287._col0(Inner),Output:["_col2","_col3","_col4","_col5"] + <-Map 23 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_287] + PartitionCols:_col0 + Select Operator [SEL_286] (rows=46200 width=4) + Output:["_col0"] + Filter Operator [FIL_285] (rows=46200 width=94) + predicate:(i_category = 'Electronics') + TableScan [TS_12] (rows=462000 width=94) + default@item,item,Tbl:COMPLETE,Col:COMPLETE,Output:["i_item_sk","i_category"] + <-Reducer 13 [SIMPLE_EDGE] + SHUFFLE [RS_24] + PartitionCols:_col1 + Merge Join Operator [MERGEJOIN_256] (rows=13119234 width=4) + Conds:RS_313._col0=RS_275._col0(Inner),Output:["_col1","_col2","_col3","_col4","_col5"] + <-Map 17 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_275] + PartitionCols:_col0 + Select Operator [SEL_274] (rows=50 width=4) + Output:["_col0"] + Filter Operator [FIL_273] (rows=50 width=12) + predicate:((d_moy = 11) and (d_year = 1999)) + TableScan [TS_9] (rows=73049 width=12) + default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_year","d_moy"] + <-Map 12 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_313] + PartitionCols:_col0 + Select Operator [SEL_312] (rows=479120970 width=126) + Output:["_col0","_col1","_col2","_col3","_col4","_col5"] + Filter Operator [FIL_311] (rows=479120970 width=126) + predicate:((ss_customer_sk BETWEEN DynamicValue(RS_37_customer_c_customer_sk_min) AND DynamicValue(RS_37_customer_c_customer_sk_max) and in_bloom_filter(ss_customer_sk, DynamicValue(RS_37_customer_c_customer_sk_bloom_filter))) and (ss_item_sk BETWEEN DynamicValue(RS_25_item_i_item_sk_min) AND DynamicValue(RS_25_item_i_item_sk_max) and in_bloom_filter(ss_item_sk, DynamicValue(RS_25_item_i_item_sk_bloom_filter))) and (ss_sold_date_sk BETWEEN DynamicValue(RS_22_date_dim_d_date_sk_min) AND DynamicValue(RS_22_date_dim_d_date_sk_max) and in_bloom_filter(ss_sold_date_sk, DynamicValue(RS_22_date_dim_d_date_sk_bloom_filter))) and (ss_store_sk BETWEEN DynamicValue(RS_28_store_s_store_sk_min) AND DynamicValue(RS_28_store_s_store_sk_max) and in_bloom_filter(ss_store_sk, DynamicValue(RS_28_store_s_store_sk_bloom_filter))) and ss_customer_sk is not null and ss_promo_sk is not null and ss_sold_date_sk is not null and ss_store_sk is not null) + TableScan [TS_6] (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_promo_sk","ss_ext_sales_price"] + <-Reducer 18 [BROADCAST_EDGE] vectorized + BROADCAST [RS_284] + Group By Operator [GBY_283] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 17 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_281] + Group By Operator [GBY_279] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_276] (rows=50 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_274] + <-Reducer 24 [BROADCAST_EDGE] vectorized + BROADCAST [RS_296] + Group By Operator [GBY_295] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 23 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_293] + Group By Operator [GBY_291] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_288] (rows=46200 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_286] + <-Reducer 27 [BROADCAST_EDGE] vectorized + BROADCAST [RS_308] + Group By Operator [GBY_307] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 26 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_305] + Group By Operator [GBY_303] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_300] (rows=341 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_298] + <-Reducer 7 [BROADCAST_EDGE] vectorized + BROADCAST [RS_310] + Group By Operator [GBY_309] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=14591048)"] + <-Reducer 2 [CUSTOM_SIMPLE_EDGE] + SHUFFLE [RS_182] + Group By Operator [GBY_181] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=14591048)"] + Select Operator [SEL_180] (rows=16000001 width=4) + Output:["_col0"] + Please refer to the previous Merge Join Operator [MERGEJOIN_255] + <-Reducer 9 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_333] + Select Operator [SEL_332] (rows=1 width=224) + Output:["_col0","_col1"] + Group By Operator [GBY_331] (rows=1 width=112) + Output:["_col0"],aggregations:["sum(VALUE._col0)"] + <-Reducer 8 [CUSTOM_SIMPLE_EDGE] + PARTITION_ONLY_SHUFFLE [RS_81] + Group By Operator [GBY_80] (rows=1 width=112) + Output:["_col0"],aggregations:["sum(_col7)"] + Merge Join Operator [MERGEJOIN_265] (rows=529208 width=0) + Conds:RS_76._col0=RS_77._col2(Inner),Output:["_col7"] + <-Reducer 2 [SIMPLE_EDGE] + SHUFFLE [RS_76] + PartitionCols:_col0 + Please refer to the previous Merge Join Operator [MERGEJOIN_255] + <-Reducer 21 [SIMPLE_EDGE] + SHUFFLE [RS_77] + PartitionCols:_col2 + Merge Join Operator [MERGEJOIN_263] (rows=2646038 width=0) + Conds:RS_69._col3=RS_301._col0(Inner),Output:["_col2","_col4"] + <-Map 26 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_301] + PartitionCols:_col0 + Please refer to the previous Select Operator [SEL_298] + <-Reducer 20 [SIMPLE_EDGE] + SHUFFLE [RS_69] + PartitionCols:_col3 + Merge Join Operator [MERGEJOIN_262] (rows=13222427 width=0) + Conds:RS_66._col1=RS_289._col0(Inner),Output:["_col2","_col3","_col4"] + <-Map 23 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_289] + PartitionCols:_col0 + Please refer to the previous Select Operator [SEL_286] + <-Reducer 19 [SIMPLE_EDGE] + SHUFFLE [RS_66] + PartitionCols:_col1 + Merge Join Operator [MERGEJOIN_261] (rows=13737330 width=4) + Conds:RS_330._col0=RS_277._col0(Inner),Output:["_col1","_col2","_col3","_col4"] + <-Map 17 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_277] + PartitionCols:_col0 + Please refer to the previous Select Operator [SEL_274] + <-Map 30 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_330] + PartitionCols:_col0 + Select Operator [SEL_329] (rows=501694138 width=122) + Output:["_col0","_col1","_col2","_col3","_col4"] + Filter Operator [FIL_328] (rows=501694138 width=122) + predicate:((ss_customer_sk BETWEEN DynamicValue(RS_76_customer_c_customer_sk_min) AND DynamicValue(RS_76_customer_c_customer_sk_max) and in_bloom_filter(ss_customer_sk, DynamicValue(RS_76_customer_c_customer_sk_bloom_filter))) and (ss_item_sk BETWEEN DynamicValue(RS_67_item_i_item_sk_min) AND DynamicValue(RS_67_item_i_item_sk_max) and in_bloom_filter(ss_item_sk, DynamicValue(RS_67_item_i_item_sk_bloom_filter))) and (ss_sold_date_sk BETWEEN DynamicValue(RS_64_date_dim_d_date_sk_min) AND DynamicValue(RS_64_date_dim_d_date_sk_max) and in_bloom_filter(ss_sold_date_sk, DynamicValue(RS_64_date_dim_d_date_sk_bloom_filter))) and (ss_store_sk BETWEEN DynamicValue(RS_70_store_s_store_sk_min) AND DynamicValue(RS_70_store_s_store_sk_max) and in_bloom_filter(ss_store_sk, DynamicValue(RS_70_store_s_store_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_51] (rows=575995635 width=122) + default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_sold_date_sk","ss_item_sk","ss_customer_sk","ss_store_sk","ss_ext_sales_price"] + <-Reducer 10 [BROADCAST_EDGE] vectorized + BROADCAST [RS_327] + Group By Operator [GBY_326] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=14591048)"] + <-Reducer 2 [CUSTOM_SIMPLE_EDGE] + SHUFFLE [RS_237] + Group By Operator [GBY_236] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=14591048)"] + Select Operator [SEL_235] (rows=16000001 width=4) + Output:["_col0"] + Please refer to the previous Merge Join Operator [MERGEJOIN_255] + <-Reducer 22 [BROADCAST_EDGE] vectorized + BROADCAST [RS_321] + Group By Operator [GBY_320] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 17 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_282] + Group By Operator [GBY_280] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_278] (rows=50 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_274] + <-Reducer 25 [BROADCAST_EDGE] vectorized + BROADCAST [RS_323] + Group By Operator [GBY_322] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 23 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_294] + Group By Operator [GBY_292] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_290] (rows=46200 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_286] + <-Reducer 28 [BROADCAST_EDGE] vectorized + BROADCAST [RS_325] + Group By Operator [GBY_324] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 26 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_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_302] (rows=341 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_298] +
http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/query63.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query63.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query63.q.out new file mode 100644 index 0000000..a1f8413 --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query63.q.out @@ -0,0 +1,163 @@ +PREHOOK: query: explain +select * +from (select i_manager_id + ,sum(ss_sales_price) sum_sales + ,avg(sum(ss_sales_price)) over (partition by i_manager_id) avg_monthly_sales + from item + ,store_sales + ,date_dim + ,store + where ss_item_sk = i_item_sk + and ss_sold_date_sk = d_date_sk + and ss_store_sk = s_store_sk + and d_month_seq in (1212,1212+1,1212+2,1212+3,1212+4,1212+5,1212+6,1212+7,1212+8,1212+9,1212+10,1212+11) + and (( i_category in ('Books','Children','Electronics') + and i_class in ('personal','portable','refernece','self-help') + and i_brand in ('scholaramalgamalg #14','scholaramalgamalg #7', + 'exportiunivamalg #9','scholaramalgamalg #9')) + or( i_category in ('Women','Music','Men') + and i_class in ('accessories','classical','fragrances','pants') + and i_brand in ('amalgimporto #1','edu packscholar #1','exportiimporto #1', + 'importoamalg #1'))) +group by i_manager_id, d_moy) tmp1 +where case when avg_monthly_sales > 0 then abs (sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1 +order by i_manager_id + ,avg_monthly_sales + ,sum_sales +limit 100 +PREHOOK: type: QUERY +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 * +from (select i_manager_id + ,sum(ss_sales_price) sum_sales + ,avg(sum(ss_sales_price)) over (partition by i_manager_id) avg_monthly_sales + from item + ,store_sales + ,date_dim + ,store + where ss_item_sk = i_item_sk + and ss_sold_date_sk = d_date_sk + and ss_store_sk = s_store_sk + and d_month_seq in (1212,1212+1,1212+2,1212+3,1212+4,1212+5,1212+6,1212+7,1212+8,1212+9,1212+10,1212+11) + and (( i_category in ('Books','Children','Electronics') + and i_class in ('personal','portable','refernece','self-help') + and i_brand in ('scholaramalgamalg #14','scholaramalgamalg #7', + 'exportiunivamalg #9','scholaramalgamalg #9')) + or( i_category in ('Women','Music','Men') + and i_class in ('accessories','classical','fragrances','pants') + and i_brand in ('amalgimporto #1','edu packscholar #1','exportiimporto #1', + 'importoamalg #1'))) +group by i_manager_id, d_moy) tmp1 +where case when avg_monthly_sales > 0 then abs (sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1 +order by i_manager_id + ,avg_monthly_sales + ,sum_sales +limit 100 +POSTHOOK: type: QUERY +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 7 (BROADCAST_EDGE), Reducer 9 (BROADCAST_EDGE) +Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 6 (SIMPLE_EDGE) +Reducer 3 <- Map 8 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) +Reducer 4 <- Reducer 3 (SIMPLE_EDGE) +Reducer 5 <- Reducer 4 (SIMPLE_EDGE) +Reducer 7 <- Map 6 (CUSTOM_SIMPLE_EDGE) +Reducer 9 <- Map 8 (CUSTOM_SIMPLE_EDGE) + +Stage-0 + Fetch Operator + limit:100 + Stage-1 + Reducer 5 vectorized + File Output Operator [FS_86] + Limit [LIM_85] (rows=71 width=228) + Number of rows:100 + Select Operator [SEL_84] (rows=71 width=228) + Output:["_col0","_col1","_col2"] + <-Reducer 4 [SIMPLE_EDGE] + SHUFFLE [RS_27] + Select Operator [SEL_24] (rows=71 width=228) + Output:["_col0","_col1","_col2"] + Filter Operator [FIL_36] (rows=71 width=228) + predicate:CASE WHEN ((avg_window_0 > 0)) THEN (((abs((_col2 - avg_window_0)) / avg_window_0) > 0.1)) ELSE (null) END + Select Operator [SEL_23] (rows=143 width=116) + Output:["avg_window_0","_col0","_col2"] + PTF Operator [PTF_22] (rows=143 width=116) + Function definitions:[{},{"name:":"windowingtablefunction","order by:":"_col0 ASC NULLS FIRST","partition by:":"_col0"}] + Select Operator [SEL_19] (rows=143 width=116) + Output:["_col0","_col2"] + Group By Operator [GBY_18] (rows=143 width=120) + Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1 + <-Reducer 3 [SIMPLE_EDGE] + SHUFFLE [RS_17] + PartitionCols:_col0 + Group By Operator [GBY_16] (rows=143 width=120) + Output:["_col0","_col1","_col2"],aggregations:["sum(_col2)"],keys:_col4, _col6 + Merge Join Operator [MERGEJOIN_62] (rows=129200 width=8) + Conds:RS_12._col0=RS_73._col0(Inner),Output:["_col2","_col4","_col6"] + <-Map 8 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_73] + PartitionCols:_col0 + Select Operator [SEL_72] (rows=317 width=8) + Output:["_col0","_col1"] + Filter Operator [FIL_71] (rows=317 width=12) + predicate:(d_month_seq) IN (1212, 1213, 1214, 1215, 1216, 1217, 1218, 1219, 1220, 1221, 1222, 1223) + TableScan [TS_6] (rows=73049 width=12) + default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_month_seq","d_moy"] + <-Reducer 2 [SIMPLE_EDGE] + SHUFFLE [RS_12] + PartitionCols:_col0 + Merge Join Operator [MERGEJOIN_61] (rows=744232 width=4) + Conds:RS_81._col1=RS_65._col0(Inner),Output:["_col0","_col2","_col4"] + <-Map 6 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_65] + PartitionCols:_col0 + Select Operator [SEL_64] (rows=68 width=8) + Output:["_col0","_col1"] + Filter Operator [FIL_63] (rows=68 width=290) + predicate:((((i_category) IN ('Books', 'Children', 'Electronics') and (i_class) IN ('personal', 'portable', 'refernece', 'self-help') and (i_brand) IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9')) or ((i_category) IN ('Women', 'Music', 'Men') and (i_class) IN ('accessories', 'classical', 'fragrances', 'pants') and (i_brand) IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1'))) and (i_brand) IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9', 'amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1') and (i_category) IN ('Books', 'Children', 'Electronics', 'Women', 'Music', 'Men') and (i_class) IN ('personal', 'portable', 'refernece', 'self-help', 'accessories', 'classical', 'fragrances', 'pants')) + TableScan [TS_3] (rows=462000 width=289) + default@item,item,Tbl:COMPLETE,Col:COMPLETE,Output:["i_item_sk","i_brand","i_class","i_category","i_manager_id"] + <-Map 1 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_81] + PartitionCols:_col1 + Select Operator [SEL_80] (rows=525329897 width=114) + Output:["_col0","_col1","_col2"] + Filter Operator [FIL_79] (rows=525329897 width=118) + predicate:((ss_item_sk BETWEEN DynamicValue(RS_10_item_i_item_sk_min) AND DynamicValue(RS_10_item_i_item_sk_max) and in_bloom_filter(ss_item_sk, DynamicValue(RS_10_item_i_item_sk_bloom_filter))) and (ss_sold_date_sk BETWEEN DynamicValue(RS_13_date_dim_d_date_sk_min) AND DynamicValue(RS_13_date_dim_d_date_sk_max) and in_bloom_filter(ss_sold_date_sk, DynamicValue(RS_13_date_dim_d_date_sk_bloom_filter))) and ss_sold_date_sk is not null and ss_store_sk is not null) + TableScan [TS_0] (rows=575995635 width=118) + default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_sold_date_sk","ss_item_sk","ss_store_sk","ss_sales_price"] + <-Reducer 7 [BROADCAST_EDGE] vectorized + BROADCAST [RS_70] + Group By Operator [GBY_69] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 6 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_68] + Group By Operator [GBY_67] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_66] (rows=68 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_64] + <-Reducer 9 [BROADCAST_EDGE] vectorized + BROADCAST [RS_78] + Group By Operator [GBY_77] (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_76] + Group By Operator [GBY_75] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_74] (rows=317 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_72] +
