http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/tez/query7.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/query7.q.out b/ql/src/test/results/clientpositive/perf/tez/query7.q.out new file mode 100644 index 0000000..00628db --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/query7.q.out @@ -0,0 +1,132 @@ +PREHOOK: query: explain +select i_item_id, + 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, item, promotion + where ss_sold_date_sk = d_date_sk and + ss_item_sk = i_item_sk and + ss_cdemo_sk = cd_demo_sk and + ss_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 +POSTHOOK: query: explain +select i_item_id, + 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, item, promotion + where ss_sold_date_sk = d_date_sk and + ss_item_sk = i_item_sk and + ss_cdemo_sk = cd_demo_sk and + ss_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 +Plan optimized by CBO. + +Vertex dependency in root stage +Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE) +Reducer 3 <- Map 9 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) +Reducer 4 <- Map 10 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE) +Reducer 5 <- Map 11 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE) +Reducer 6 <- Reducer 5 (SIMPLE_EDGE) +Reducer 7 <- Reducer 6 (SIMPLE_EDGE) + +Stage-0 + Fetch Operator + limit:100 + Stage-1 + Reducer 7 + File Output Operator [FS_35] + Limit [LIM_34] (rows=100 width=88) + Number of rows:100 + Select Operator [SEL_33] (rows=421657640 width=88) + Output:["_col0","_col1","_col2","_col3","_col4"] + <-Reducer 6 [SIMPLE_EDGE] + SHUFFLE [RS_32] + Group By Operator [GBY_30] (rows=421657640 width=88) + Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["avg(VALUE._col0)","avg(VALUE._col1)","avg(VALUE._col2)","avg(VALUE._col3)"],keys:KEY._col0 + <-Reducer 5 [SIMPLE_EDGE] + SHUFFLE [RS_29] + PartitionCols:_col0 + Group By Operator [GBY_28] (rows=843315281 width=88) + Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["avg(_col4)","avg(_col5)","avg(_col7)","avg(_col6)"],keys:_col18 + Merge Join Operator [MERGEJOIN_58] (rows=843315281 width=88) + Conds:RS_24._col1=RS_25._col0(Inner),Output:["_col4","_col5","_col6","_col7","_col18"] + <-Map 11 [SIMPLE_EDGE] + SHUFFLE [RS_25] + PartitionCols:_col0 + Select Operator [SEL_14] (rows=462000 width=1436) + Output:["_col0","_col1"] + Filter Operator [FIL_54] (rows=462000 width=1436) + predicate:i_item_sk is not null + TableScan [TS_12] (rows=462000 width=1436) + default@item,item,Tbl:COMPLETE,Col:NONE,Output:["i_item_sk","i_item_id"] + <-Reducer 4 [SIMPLE_EDGE] + SHUFFLE [RS_24] + PartitionCols:_col1 + Merge Join Operator [MERGEJOIN_57] (rows=766650239 width=88) + Conds:RS_21._col3=RS_22._col0(Inner),Output:["_col1","_col4","_col5","_col6","_col7"] + <-Map 10 [SIMPLE_EDGE] + SHUFFLE [RS_22] + PartitionCols:_col0 + Select Operator [SEL_11] (rows=2300 width=1179) + Output:["_col0"] + Filter Operator [FIL_53] (rows=2300 width=1179) + predicate:(((p_channel_email = 'N') or (p_channel_event = 'N')) and p_promo_sk is not null) + TableScan [TS_9] (rows=2300 width=1179) + default@promotion,promotion,Tbl:COMPLETE,Col:NONE,Output:["p_promo_sk","p_channel_email","p_channel_event"] + <-Reducer 3 [SIMPLE_EDGE] + SHUFFLE [RS_21] + PartitionCols:_col3 + Merge Join Operator [MERGEJOIN_56] (rows=696954748 width=88) + Conds:RS_18._col0=RS_19._col0(Inner),Output:["_col1","_col3","_col4","_col5","_col6","_col7"] + <-Map 9 [SIMPLE_EDGE] + SHUFFLE [RS_19] + PartitionCols:_col0 + Select Operator [SEL_8] (rows=36524 width=1119) + Output:["_col0"] + Filter Operator [FIL_52] (rows=36524 width=1119) + predicate:((d_year = 1998) and d_date_sk is not null) + TableScan [TS_6] (rows=73049 width=1119) + default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_year"] + <-Reducer 2 [SIMPLE_EDGE] + SHUFFLE [RS_18] + PartitionCols:_col0 + Merge Join Operator [MERGEJOIN_55] (rows=633595212 width=88) + Conds:RS_15._col2=RS_16._col0(Inner),Output:["_col0","_col1","_col3","_col4","_col5","_col6","_col7"] + <-Map 1 [SIMPLE_EDGE] + SHUFFLE [RS_15] + PartitionCols:_col2 + Select Operator [SEL_2] (rows=575995635 width=88) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"] + Filter Operator [FIL_50] (rows=575995635 width=88) + predicate:(ss_cdemo_sk is not null and ss_item_sk is not null and ss_promo_sk is not null and ss_sold_date_sk is not null) + TableScan [TS_0] (rows=575995635 width=88) + default@store_sales,store_sales,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_item_sk","ss_cdemo_sk","ss_promo_sk","ss_quantity","ss_list_price","ss_sales_price","ss_coupon_amt"] + <-Map 8 [SIMPLE_EDGE] + SHUFFLE [RS_16] + PartitionCols:_col0 + Select Operator [SEL_5] (rows=232725 width=385) + Output:["_col0"] + Filter Operator [FIL_51] (rows=232725 width=385) + predicate:((cd_education_status = 'Primary') and (cd_gender = 'F') and (cd_marital_status = 'W') and cd_demo_sk is not null) + TableScan [TS_3] (rows=1861800 width=385) + default@customer_demographics,customer_demographics,Tbl:COMPLETE,Col:NONE,Output:["cd_demo_sk","cd_gender","cd_marital_status","cd_education_status"] +
http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/tez/query70.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/query70.q.out b/ql/src/test/results/clientpositive/perf/tez/query70.q.out new file mode 100644 index 0000000..d700d60 --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/query70.q.out @@ -0,0 +1,205 @@ +PREHOOK: query: explain +select + sum(ss_net_profit) as total_sum + ,s_state + ,s_county + ,grouping(s_state)+grouping(s_county) as lochierarchy + ,rank() over ( + partition by grouping(s_state)+grouping(s_county), + case when grouping(s_county) = 0 then s_state end + order by sum(ss_net_profit) desc) as rank_within_parent + from + store_sales + ,date_dim d1 + ,store + where + d1.d_month_seq between 1212 and 1212+11 + and d1.d_date_sk = ss_sold_date_sk + and s_store_sk = ss_store_sk + and s_state in + ( select s_state + from (select s_state as s_state, + rank() over ( partition by s_state order by sum(ss_net_profit) desc) as ranking + from store_sales, store, date_dim + where d_month_seq between 1212 and 1212+11 + and d_date_sk = ss_sold_date_sk + and s_store_sk = ss_store_sk + group by s_state + ) tmp1 + where ranking <= 5 + ) + group by rollup(s_state,s_county) + order by + lochierarchy desc + ,case when lochierarchy = 0 then s_state end + ,rank_within_parent + limit 100 +PREHOOK: type: QUERY +POSTHOOK: query: explain +select + sum(ss_net_profit) as total_sum + ,s_state + ,s_county + ,grouping(s_state)+grouping(s_county) as lochierarchy + ,rank() over ( + partition by grouping(s_state)+grouping(s_county), + case when grouping(s_county) = 0 then s_state end + order by sum(ss_net_profit) desc) as rank_within_parent + from + store_sales + ,date_dim d1 + ,store + where + d1.d_month_seq between 1212 and 1212+11 + and d1.d_date_sk = ss_sold_date_sk + and s_store_sk = ss_store_sk + and s_state in + ( select s_state + from (select s_state as s_state, + rank() over ( partition by s_state order by sum(ss_net_profit) desc) as ranking + from store_sales, store, date_dim + where d_month_seq between 1212 and 1212+11 + and d_date_sk = ss_sold_date_sk + and s_store_sk = ss_store_sk + group by s_state + ) tmp1 + where ranking <= 5 + ) + group by rollup(s_state,s_county) + order by + lochierarchy desc + ,case when lochierarchy = 0 then s_state end + ,rank_within_parent + limit 100 +POSTHOOK: type: QUERY +Plan optimized by CBO. + +Vertex dependency in root stage +Reducer 10 <- Reducer 9 (SIMPLE_EDGE) +Reducer 11 <- Reducer 10 (SIMPLE_EDGE) +Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 12 (SIMPLE_EDGE) +Reducer 3 <- Map 13 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) +Reducer 4 <- Reducer 11 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE) +Reducer 5 <- Reducer 4 (SIMPLE_EDGE) +Reducer 6 <- Reducer 5 (SIMPLE_EDGE) +Reducer 7 <- Reducer 6 (SIMPLE_EDGE) +Reducer 8 <- Map 1 (SIMPLE_EDGE), Map 12 (SIMPLE_EDGE) +Reducer 9 <- Map 14 (SIMPLE_EDGE), Reducer 8 (SIMPLE_EDGE) + +Stage-0 + Fetch Operator + limit:-1 + Stage-1 + Reducer 7 + File Output Operator [FS_61] + Limit [LIM_59] (rows=100 width=88) + Number of rows:100 + Select Operator [SEL_58] (rows=1149975358 width=88) + Output:["_col0","_col1","_col2","_col3","_col4"] + <-Reducer 6 [SIMPLE_EDGE] + SHUFFLE [RS_57] + Select Operator [SEL_55] (rows=1149975358 width=88) + Output:["_col0","_col1","_col2","_col3","_col4","_col5"] + PTF Operator [PTF_54] (rows=1149975358 width=88) + Function definitions:[{},{"name:":"windowingtablefunction","order by:":"_col4 DESC NULLS LAST","partition by:":"(grouping(_col5, 1) + grouping(_col5, 0)), CASE WHEN ((grouping(_col5, 0) = 0)) THEN (_col0) ELSE (null) END"}] + Select Operator [SEL_53] (rows=1149975358 width=88) + Output:["_col0","_col1","_col4","_col5"] + <-Reducer 5 [SIMPLE_EDGE] + SHUFFLE [RS_52] + PartitionCols:(grouping(_col5, 1) + grouping(_col5, 0)), CASE WHEN ((grouping(_col5, 0) = 0)) THEN (_col0) ELSE (null) END + Select Operator [SEL_51] (rows=1149975358 width=88) + Output:["_col0","_col1","_col4","_col5"] + Group By Operator [GBY_50] (rows=1149975358 width=88) + Output:["_col0","_col1","_col2","_col3"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1, KEY._col2 + <-Reducer 4 [SIMPLE_EDGE] + SHUFFLE [RS_49] + PartitionCols:_col0, _col1, _col2 + Group By Operator [GBY_48] (rows=2299950717 width=88) + Output:["_col0","_col1","_col2","_col3"],aggregations:["sum(_col2)"],keys:_col0, _col1, 0 + Select Operator [SEL_46] (rows=766650239 width=88) + Output:["_col0","_col1","_col2"] + Merge Join Operator [MERGEJOIN_89] (rows=766650239 width=88) + Conds:RS_43._col7=RS_44._col0(Inner),Output:["_col2","_col6","_col7"] + <-Reducer 11 [SIMPLE_EDGE] + SHUFFLE [RS_44] + PartitionCols:_col0 + Select Operator [SEL_32] (rows=116159124 width=88) + Output:["_col0"] + Filter Operator [FIL_81] (rows=116159124 width=88) + predicate:(rank_window_0 <= 5) + PTF Operator [PTF_31] (rows=348477374 width=88) + Function definitions:[{},{"name:":"windowingtablefunction","order by:":"_col1 DESC NULLS LAST","partition by:":"_col0"}] + Select Operator [SEL_30] (rows=348477374 width=88) + Output:["_col0","_col1"] + <-Reducer 10 [SIMPLE_EDGE] + SHUFFLE [RS_29] + PartitionCols:_col0 + Group By Operator [GBY_27] (rows=348477374 width=88) + Output:["_col0","_col1"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0 + <-Reducer 9 [SIMPLE_EDGE] + SHUFFLE [RS_26] + PartitionCols:_col0 + Group By Operator [GBY_25] (rows=696954748 width=88) + Output:["_col0","_col1"],aggregations:["sum(_col2)"],keys:_col6 + Merge Join Operator [MERGEJOIN_88] (rows=696954748 width=88) + Conds:RS_21._col1=RS_22._col0(Inner),Output:["_col2","_col6"] + <-Map 14 [SIMPLE_EDGE] + SHUFFLE [RS_22] + PartitionCols:_col0 + Select Operator [SEL_17] (rows=1704 width=1910) + Output:["_col0","_col1"] + Filter Operator [FIL_84] (rows=1704 width=1910) + predicate:(s_state is not null and s_store_sk is not null) + TableScan [TS_15] (rows=1704 width=1910) + default@store,store,Tbl:COMPLETE,Col:NONE,Output:["s_store_sk","s_state"] + <-Reducer 8 [SIMPLE_EDGE] + SHUFFLE [RS_21] + PartitionCols:_col1 + Merge Join Operator [MERGEJOIN_87] (rows=633595212 width=88) + Conds:RS_18._col0=RS_19._col0(Inner),Output:["_col1","_col2"] + <-Map 1 [SIMPLE_EDGE] + SHUFFLE [RS_18] + PartitionCols:_col0 + Select Operator [SEL_2] (rows=575995635 width=88) + Output:["_col0","_col1","_col2"] + Filter Operator [FIL_78] (rows=575995635 width=88) + predicate:(ss_sold_date_sk is not null and ss_store_sk is not null) + TableScan [TS_0] (rows=575995635 width=88) + default@store_sales,store_sales,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_store_sk","ss_net_profit"] + <-Map 12 [SIMPLE_EDGE] + SHUFFLE [RS_19] + PartitionCols:_col0 + Select Operator [SEL_5] (rows=8116 width=1119) + Output:["_col0"] + Filter Operator [FIL_79] (rows=8116 width=1119) + predicate:(d_date_sk is not null and d_month_seq BETWEEN 1212 AND 1223) + TableScan [TS_3] (rows=73049 width=1119) + default@date_dim,d1,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_month_seq"] + <-Reducer 3 [SIMPLE_EDGE] + SHUFFLE [RS_43] + PartitionCols:_col7 + Merge Join Operator [MERGEJOIN_86] (rows=696954748 width=88) + Conds:RS_40._col1=RS_41._col0(Inner),Output:["_col2","_col6","_col7"] + <-Map 13 [SIMPLE_EDGE] + SHUFFLE [RS_41] + PartitionCols:_col0 + Select Operator [SEL_8] (rows=1704 width=1910) + Output:["_col0","_col1","_col2"] + Filter Operator [FIL_80] (rows=1704 width=1910) + predicate:(s_state is not null and s_store_sk is not null) + TableScan [TS_6] (rows=1704 width=1910) + default@store,store,Tbl:COMPLETE,Col:NONE,Output:["s_store_sk","s_county","s_state"] + <-Reducer 2 [SIMPLE_EDGE] + SHUFFLE [RS_40] + PartitionCols:_col1 + Merge Join Operator [MERGEJOIN_85] (rows=633595212 width=88) + Conds:RS_37._col0=RS_38._col0(Inner),Output:["_col1","_col2"] + <-Map 1 [SIMPLE_EDGE] + SHUFFLE [RS_37] + PartitionCols:_col0 + Please refer to the previous Select Operator [SEL_2] + <-Map 12 [SIMPLE_EDGE] + SHUFFLE [RS_38] + PartitionCols:_col0 + Please refer to the previous Select Operator [SEL_5] + http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/tez/query71.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/query71.q.out b/ql/src/test/results/clientpositive/perf/tez/query71.q.out new file mode 100644 index 0000000..bd48e56 --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/query71.q.out @@ -0,0 +1,208 @@ +PREHOOK: query: explain +select i_brand_id brand_id, i_brand brand,t_hour,t_minute, + sum(ext_price) ext_price + from item, (select ws_ext_sales_price as ext_price, + ws_sold_date_sk as sold_date_sk, + ws_item_sk as sold_item_sk, + ws_sold_time_sk as time_sk + from web_sales,date_dim + where d_date_sk = ws_sold_date_sk + and d_moy=12 + and d_year=2001 + union all + select cs_ext_sales_price as ext_price, + cs_sold_date_sk as sold_date_sk, + cs_item_sk as sold_item_sk, + cs_sold_time_sk as time_sk + from catalog_sales,date_dim + where d_date_sk = cs_sold_date_sk + and d_moy=12 + and d_year=2001 + union all + select ss_ext_sales_price as ext_price, + ss_sold_date_sk as sold_date_sk, + ss_item_sk as sold_item_sk, + ss_sold_time_sk as time_sk + from store_sales,date_dim + where d_date_sk = ss_sold_date_sk + and d_moy=12 + and d_year=2001 + ) as tmp,time_dim + where + sold_item_sk = i_item_sk + and i_manager_id=1 + and time_sk = t_time_sk + and (t_meal_time = 'breakfast' or t_meal_time = 'dinner') + group by i_brand, i_brand_id,t_hour,t_minute + order by ext_price desc, i_brand_id +PREHOOK: type: QUERY +POSTHOOK: query: explain +select i_brand_id brand_id, i_brand brand,t_hour,t_minute, + sum(ext_price) ext_price + from item, (select ws_ext_sales_price as ext_price, + ws_sold_date_sk as sold_date_sk, + ws_item_sk as sold_item_sk, + ws_sold_time_sk as time_sk + from web_sales,date_dim + where d_date_sk = ws_sold_date_sk + and d_moy=12 + and d_year=2001 + union all + select cs_ext_sales_price as ext_price, + cs_sold_date_sk as sold_date_sk, + cs_item_sk as sold_item_sk, + cs_sold_time_sk as time_sk + from catalog_sales,date_dim + where d_date_sk = cs_sold_date_sk + and d_moy=12 + and d_year=2001 + union all + select ss_ext_sales_price as ext_price, + ss_sold_date_sk as sold_date_sk, + ss_item_sk as sold_item_sk, + ss_sold_time_sk as time_sk + from store_sales,date_dim + where d_date_sk = ss_sold_date_sk + and d_moy=12 + and d_year=2001 + ) as tmp,time_dim + where + sold_item_sk = i_item_sk + and i_manager_id=1 + and time_sk = t_time_sk + and (t_meal_time = 'breakfast' or t_meal_time = 'dinner') + group by i_brand, i_brand_id,t_hour,t_minute + order by ext_price desc, i_brand_id +POSTHOOK: type: QUERY +Plan optimized by CBO. + +Vertex dependency in root stage +Reducer 10 <- Map 11 (SIMPLE_EDGE), Map 9 (SIMPLE_EDGE), Union 3 (CONTAINS) +Reducer 13 <- Map 12 (SIMPLE_EDGE), Map 14 (SIMPLE_EDGE), Union 3 (CONTAINS) +Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE), Union 3 (CONTAINS) +Reducer 4 <- Map 15 (SIMPLE_EDGE), Union 3 (SIMPLE_EDGE) +Reducer 5 <- Map 16 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE) +Reducer 6 <- Reducer 5 (SIMPLE_EDGE) +Reducer 7 <- Reducer 6 (SIMPLE_EDGE) + +Stage-0 + Fetch Operator + limit:-1 + Stage-1 + Reducer 7 + File Output Operator [FS_52] + Select Operator [SEL_51] (rows=670816149 width=108) + Output:["_col0","_col1","_col2","_col3","_col4"] + <-Reducer 6 [SIMPLE_EDGE] + SHUFFLE [RS_50] + Select Operator [SEL_48] (rows=670816149 width=108) + Output:["_col0","_col1","_col2","_col3","_col4"] + Group By Operator [GBY_47] (rows=670816149 width=108) + Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3 + <-Reducer 5 [SIMPLE_EDGE] + SHUFFLE [RS_46] + PartitionCols:_col0, _col1, _col2, _col3 + Group By Operator [GBY_45] (rows=1341632299 width=108) + Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(_col0)"],keys:_col4, _col8, _col9, _col5 + Merge Join Operator [MERGEJOIN_86] (rows=1341632299 width=108) + Conds:RS_41._col2=RS_42._col0(Inner),Output:["_col0","_col4","_col5","_col8","_col9"] + <-Map 16 [SIMPLE_EDGE] + SHUFFLE [RS_42] + PartitionCols:_col0 + Select Operator [SEL_37] (rows=86400 width=471) + Output:["_col0","_col1","_col2"] + Filter Operator [FIL_81] (rows=86400 width=471) + predicate:(((t_meal_time = 'breakfast') or (t_meal_time = 'dinner')) and t_time_sk is not null) + TableScan [TS_35] (rows=86400 width=471) + default@time_dim,time_dim,Tbl:COMPLETE,Col:NONE,Output:["t_time_sk","t_hour","t_minute","t_meal_time"] + <-Reducer 4 [SIMPLE_EDGE] + SHUFFLE [RS_41] + PartitionCols:_col2 + Merge Join Operator [MERGEJOIN_85] (rows=1219665700 width=108) + Conds:Union 3._col1=RS_39._col0(Inner),Output:["_col0","_col2","_col4","_col5"] + <-Map 15 [SIMPLE_EDGE] + SHUFFLE [RS_39] + PartitionCols:_col0 + Select Operator [SEL_34] (rows=231000 width=1436) + Output:["_col0","_col1","_col2"] + Filter Operator [FIL_80] (rows=231000 width=1436) + predicate:((i_manager_id = 1) and i_item_sk is not null) + TableScan [TS_32] (rows=462000 width=1436) + default@item,item,Tbl:COMPLETE,Col:NONE,Output:["i_item_sk","i_brand_id","i_brand","i_manager_id"] + <-Union 3 [SIMPLE_EDGE] + <-Reducer 10 [CONTAINS] + Reduce Output Operator [RS_38] + PartitionCols:_col1 + Select Operator [SEL_19] (rows=316788826 width=135) + Output:["_col0","_col1","_col2"] + Merge Join Operator [MERGEJOIN_83] (rows=316788826 width=135) + Conds:RS_16._col0=RS_17._col0(Inner),Output:["_col1","_col2","_col3"] + <-Map 11 [SIMPLE_EDGE] + SHUFFLE [RS_17] + PartitionCols:_col0 + Select Operator [SEL_15] (rows=18262 width=1119) + Output:["_col0"] + Filter Operator [FIL_77] (rows=18262 width=1119) + predicate:((d_moy = 12) and (d_year = 2001) and d_date_sk is not null) + TableScan [TS_13] (rows=73049 width=1119) + default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_year","d_moy"] + <-Map 9 [SIMPLE_EDGE] + SHUFFLE [RS_16] + PartitionCols:_col0 + Select Operator [SEL_12] (rows=287989836 width=135) + Output:["_col0","_col1","_col2","_col3"] + Filter Operator [FIL_76] (rows=287989836 width=135) + predicate:(cs_item_sk is not null and cs_sold_date_sk is not null and cs_sold_time_sk is not null) + TableScan [TS_10] (rows=287989836 width=135) + default@catalog_sales,catalog_sales,Tbl:COMPLETE,Col:NONE,Output:["cs_sold_date_sk","cs_sold_time_sk","cs_item_sk","cs_ext_sales_price"] + <-Reducer 13 [CONTAINS] + Reduce Output Operator [RS_38] + PartitionCols:_col1 + Select Operator [SEL_30] (rows=633595212 width=88) + Output:["_col0","_col1","_col2"] + Merge Join Operator [MERGEJOIN_84] (rows=633595212 width=88) + Conds:RS_27._col0=RS_28._col0(Inner),Output:["_col1","_col2","_col3"] + <-Map 12 [SIMPLE_EDGE] + SHUFFLE [RS_27] + PartitionCols:_col0 + Select Operator [SEL_23] (rows=575995635 width=88) + Output:["_col0","_col1","_col2","_col3"] + Filter Operator [FIL_78] (rows=575995635 width=88) + predicate:(ss_item_sk is not null and ss_sold_date_sk is not null and ss_sold_time_sk is not null) + TableScan [TS_21] (rows=575995635 width=88) + default@store_sales,store_sales,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_sold_time_sk","ss_item_sk","ss_ext_sales_price"] + <-Map 14 [SIMPLE_EDGE] + SHUFFLE [RS_28] + PartitionCols:_col0 + Select Operator [SEL_26] (rows=18262 width=1119) + Output:["_col0"] + Filter Operator [FIL_79] (rows=18262 width=1119) + predicate:((d_moy = 12) and (d_year = 2001) and d_date_sk is not null) + TableScan [TS_24] (rows=73049 width=1119) + default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_year","d_moy"] + <-Reducer 2 [CONTAINS] + Reduce Output Operator [RS_38] + PartitionCols:_col1 + Select Operator [SEL_9] (rows=158402938 width=135) + Output:["_col0","_col1","_col2"] + Merge Join Operator [MERGEJOIN_82] (rows=158402938 width=135) + Conds:RS_6._col0=RS_7._col0(Inner),Output:["_col1","_col2","_col3"] + <-Map 1 [SIMPLE_EDGE] + SHUFFLE [RS_6] + PartitionCols:_col0 + Select Operator [SEL_2] (rows=144002668 width=135) + Output:["_col0","_col1","_col2","_col3"] + Filter Operator [FIL_74] (rows=144002668 width=135) + predicate:(ws_item_sk is not null and ws_sold_date_sk is not null and ws_sold_time_sk is not null) + TableScan [TS_0] (rows=144002668 width=135) + default@web_sales,web_sales,Tbl:COMPLETE,Col:NONE,Output:["ws_sold_date_sk","ws_sold_time_sk","ws_item_sk","ws_ext_sales_price"] + <-Map 8 [SIMPLE_EDGE] + SHUFFLE [RS_7] + PartitionCols:_col0 + Select Operator [SEL_5] (rows=18262 width=1119) + Output:["_col0"] + Filter Operator [FIL_75] (rows=18262 width=1119) + predicate:((d_moy = 12) and (d_year = 2001) and d_date_sk is not null) + TableScan [TS_3] (rows=73049 width=1119) + default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_year","d_moy"] + http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/tez/query72.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/query72.q.out b/ql/src/test/results/clientpositive/perf/tez/query72.q.out new file mode 100644 index 0000000..a5e3501 --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/query72.q.out @@ -0,0 +1,250 @@ +PREHOOK: query: explain +select i_item_desc + ,w_warehouse_name + ,d1.d_week_seq + ,count(case when p_promo_sk is null then 1 else 0 end) no_promo + ,count(case when p_promo_sk is not null then 1 else 0 end) promo + ,count(*) total_cnt +from catalog_sales +join inventory on (cs_item_sk = inv_item_sk) +join warehouse on (w_warehouse_sk=inv_warehouse_sk) +join item on (i_item_sk = cs_item_sk) +join customer_demographics on (cs_bill_cdemo_sk = cd_demo_sk) +join household_demographics on (cs_bill_hdemo_sk = hd_demo_sk) +join date_dim d1 on (cs_sold_date_sk = d1.d_date_sk) +join date_dim d2 on (inv_date_sk = d2.d_date_sk) +join date_dim d3 on (cs_ship_date_sk = d3.d_date_sk) +left outer join promotion on (cs_promo_sk=p_promo_sk) +left outer join catalog_returns on (cr_item_sk = cs_item_sk and cr_order_number = cs_order_number) +where d1.d_week_seq = d2.d_week_seq + and inv_quantity_on_hand < cs_quantity + and d3.d_date > d1.d_date + 5 + and hd_buy_potential = '1001-5000' + and d1.d_year = 2001 + and hd_buy_potential = '1001-5000' + and cd_marital_status = 'M' + and d1.d_year = 2001 +group by i_item_desc,w_warehouse_name,d1.d_week_seq +order by total_cnt desc, i_item_desc, w_warehouse_name, d_week_seq +limit 100 +PREHOOK: type: QUERY +POSTHOOK: query: explain +select i_item_desc + ,w_warehouse_name + ,d1.d_week_seq + ,count(case when p_promo_sk is null then 1 else 0 end) no_promo + ,count(case when p_promo_sk is not null then 1 else 0 end) promo + ,count(*) total_cnt +from catalog_sales +join inventory on (cs_item_sk = inv_item_sk) +join warehouse on (w_warehouse_sk=inv_warehouse_sk) +join item on (i_item_sk = cs_item_sk) +join customer_demographics on (cs_bill_cdemo_sk = cd_demo_sk) +join household_demographics on (cs_bill_hdemo_sk = hd_demo_sk) +join date_dim d1 on (cs_sold_date_sk = d1.d_date_sk) +join date_dim d2 on (inv_date_sk = d2.d_date_sk) +join date_dim d3 on (cs_ship_date_sk = d3.d_date_sk) +left outer join promotion on (cs_promo_sk=p_promo_sk) +left outer join catalog_returns on (cr_item_sk = cs_item_sk and cr_order_number = cs_order_number) +where d1.d_week_seq = d2.d_week_seq + and inv_quantity_on_hand < cs_quantity + and d3.d_date > d1.d_date + 5 + and hd_buy_potential = '1001-5000' + and d1.d_year = 2001 + and hd_buy_potential = '1001-5000' + and cd_marital_status = 'M' + and d1.d_year = 2001 +group by i_item_desc,w_warehouse_name,d1.d_week_seq +order by total_cnt desc, i_item_desc, w_warehouse_name, d_week_seq +limit 100 +POSTHOOK: type: QUERY +Plan optimized by CBO. + +Vertex dependency in root stage +Reducer 10 <- Map 16 (SIMPLE_EDGE), Map 9 (SIMPLE_EDGE) +Reducer 11 <- Map 17 (SIMPLE_EDGE), Reducer 10 (SIMPLE_EDGE) +Reducer 12 <- Map 18 (SIMPLE_EDGE), Reducer 11 (SIMPLE_EDGE) +Reducer 13 <- Map 19 (SIMPLE_EDGE), Reducer 12 (SIMPLE_EDGE) +Reducer 14 <- Map 20 (SIMPLE_EDGE), Reducer 13 (SIMPLE_EDGE) +Reducer 15 <- Map 21 (SIMPLE_EDGE), Reducer 14 (SIMPLE_EDGE) +Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE) +Reducer 3 <- Reducer 15 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) +Reducer 4 <- Map 22 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE) +Reducer 5 <- Map 23 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE) +Reducer 6 <- Reducer 5 (SIMPLE_EDGE) +Reducer 7 <- Reducer 6 (SIMPLE_EDGE) + +Stage-0 + Fetch Operator + limit:100 + Stage-1 + Reducer 7 + File Output Operator [FS_75] + Limit [LIM_74] (rows=100 width=135) + Number of rows:100 + Select Operator [SEL_73] (rows=37725837 width=135) + Output:["_col0","_col1","_col2","_col3","_col4","_col5"] + <-Reducer 6 [SIMPLE_EDGE] + SHUFFLE [RS_72] + Select Operator [SEL_70] (rows=37725837 width=135) + Output:["_col0","_col1","_col2","_col5"] + Group By Operator [GBY_69] (rows=37725837 width=135) + Output:["_col0","_col1","_col2","_col3"],aggregations:["count(VALUE._col0)"],keys:KEY._col0, KEY._col1, KEY._col2 + <-Reducer 5 [SIMPLE_EDGE] + SHUFFLE [RS_68] + PartitionCols:_col0, _col1, _col2 + Group By Operator [GBY_67] (rows=75451675 width=135) + Output:["_col0","_col1","_col2","_col3"],aggregations:["count()"],keys:_col15, _col13, _col22 + Merge Join Operator [MERGEJOIN_141] (rows=75451675 width=135) + Conds:RS_63._col4, _col6=RS_64._col0, _col1(Left Outer),Output:["_col13","_col15","_col22"] + <-Map 23 [SIMPLE_EDGE] + SHUFFLE [RS_64] + PartitionCols:_col0, _col1 + Select Operator [SEL_62] (rows=28798881 width=106) + Output:["_col0","_col1"] + Filter Operator [FIL_131] (rows=28798881 width=106) + predicate:cr_item_sk is not null + TableScan [TS_60] (rows=28798881 width=106) + default@catalog_returns,catalog_returns,Tbl:COMPLETE,Col:NONE,Output:["cr_item_sk","cr_order_number"] + <-Reducer 4 [SIMPLE_EDGE] + SHUFFLE [RS_63] + PartitionCols:_col4, _col6 + Select Operator [SEL_59] (rows=68592431 width=135) + Output:["_col4","_col6","_col13","_col15","_col22"] + Merge Join Operator [MERGEJOIN_140] (rows=68592431 width=135) + Conds:RS_56._col0, _col20=RS_57._col0, _col1(Inner),Output:["_col5","_col9","_col14","_col16","_col20"] + <-Map 22 [SIMPLE_EDGE] + SHUFFLE [RS_57] + PartitionCols:_col0, _col1 + Select Operator [SEL_48] (rows=73049 width=1119) + Output:["_col0","_col1"] + Filter Operator [FIL_130] (rows=73049 width=1119) + predicate:(d_date_sk is not null and d_week_seq is not null) + TableScan [TS_46] (rows=73049 width=1119) + default@date_dim,d2,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_week_seq"] + <-Reducer 3 [SIMPLE_EDGE] + SHUFFLE [RS_56] + PartitionCols:_col0, _col20 + Filter Operator [FIL_55] (rows=62356755 width=135) + predicate:(_col3 < _col17) + Merge Join Operator [MERGEJOIN_139] (rows=187070265 width=135) + Conds:RS_52._col1=RS_53._col8(Inner),Output:["_col0","_col3","_col5","_col9","_col14","_col16","_col17","_col20"] + <-Reducer 15 [SIMPLE_EDGE] + SHUFFLE [RS_53] + PartitionCols:_col8 + Select Operator [SEL_45] (rows=170063874 width=135) + Output:["_col3","_col8","_col10","_col11","_col14"] + Filter Operator [FIL_44] (rows=170063874 width=135) + predicate:(UDFToDouble(_col20) > (UDFToDouble(_col9) + 5.0)) + Merge Join Operator [MERGEJOIN_138] (rows=510191624 width=135) + Conds:RS_41._col1=RS_42._col0(Inner),Output:["_col4","_col6","_col7","_col9","_col10","_col18","_col20"] + <-Map 21 [SIMPLE_EDGE] + SHUFFLE [RS_42] + PartitionCols:_col0 + Select Operator [SEL_25] (rows=73049 width=1119) + Output:["_col0","_col1"] + Filter Operator [FIL_129] (rows=73049 width=1119) + predicate:d_date_sk is not null + TableScan [TS_23] (rows=73049 width=1119) + default@date_dim,d3,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_date"] + <-Reducer 14 [SIMPLE_EDGE] + SHUFFLE [RS_41] + PartitionCols:_col1 + Merge Join Operator [MERGEJOIN_137] (rows=463810558 width=135) + Conds:RS_38._col4=RS_39._col0(Inner),Output:["_col1","_col4","_col6","_col7","_col9","_col10","_col18"] + <-Map 20 [SIMPLE_EDGE] + SHUFFLE [RS_39] + PartitionCols:_col0 + Select Operator [SEL_22] (rows=462000 width=1436) + Output:["_col0","_col1"] + Filter Operator [FIL_128] (rows=462000 width=1436) + predicate:i_item_sk is not null + TableScan [TS_20] (rows=462000 width=1436) + default@item,item,Tbl:COMPLETE,Col:NONE,Output:["i_item_sk","i_item_desc"] + <-Reducer 13 [SIMPLE_EDGE] + SHUFFLE [RS_38] + PartitionCols:_col4 + Merge Join Operator [MERGEJOIN_136] (rows=421645953 width=135) + Conds:RS_35._col5=RS_36._col0(Left Outer),Output:["_col1","_col4","_col6","_col7","_col9","_col10"] + <-Map 19 [SIMPLE_EDGE] + SHUFFLE [RS_36] + PartitionCols:_col0 + Select Operator [SEL_19] (rows=2300 width=1179) + Output:["_col0"] + TableScan [TS_18] (rows=2300 width=1179) + default@promotion,promotion,Tbl:COMPLETE,Col:NONE,Output:["p_promo_sk"] + <-Reducer 12 [SIMPLE_EDGE] + SHUFFLE [RS_35] + PartitionCols:_col5 + Merge Join Operator [MERGEJOIN_135] (rows=383314495 width=135) + Conds:RS_32._col3=RS_33._col0(Inner),Output:["_col1","_col4","_col5","_col6","_col7","_col9","_col10"] + <-Map 18 [SIMPLE_EDGE] + SHUFFLE [RS_33] + PartitionCols:_col0 + Select Operator [SEL_17] (rows=3600 width=107) + Output:["_col0"] + Filter Operator [FIL_126] (rows=3600 width=107) + predicate:((hd_buy_potential = '1001-5000') and hd_demo_sk is not null) + TableScan [TS_15] (rows=7200 width=107) + default@household_demographics,household_demographics,Tbl:COMPLETE,Col:NONE,Output:["hd_demo_sk","hd_buy_potential"] + <-Reducer 11 [SIMPLE_EDGE] + SHUFFLE [RS_32] + PartitionCols:_col3 + Merge Join Operator [MERGEJOIN_134] (rows=348467716 width=135) + Conds:RS_29._col2=RS_30._col0(Inner),Output:["_col1","_col3","_col4","_col5","_col6","_col7","_col9","_col10"] + <-Map 17 [SIMPLE_EDGE] + SHUFFLE [RS_30] + PartitionCols:_col0 + Select Operator [SEL_14] (rows=930900 width=385) + Output:["_col0"] + Filter Operator [FIL_125] (rows=930900 width=385) + predicate:((cd_marital_status = 'M') and cd_demo_sk is not null) + TableScan [TS_12] (rows=1861800 width=385) + default@customer_demographics,customer_demographics,Tbl:COMPLETE,Col:NONE,Output:["cd_demo_sk","cd_marital_status"] + <-Reducer 10 [SIMPLE_EDGE] + SHUFFLE [RS_29] + PartitionCols:_col2 + Merge Join Operator [MERGEJOIN_133] (rows=316788826 width=135) + Conds:RS_26._col0=RS_27._col0(Inner),Output:["_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col9","_col10"] + <-Map 16 [SIMPLE_EDGE] + SHUFFLE [RS_27] + PartitionCols:_col0 + Select Operator [SEL_11] (rows=36524 width=1119) + Output:["_col0","_col1","_col2"] + Filter Operator [FIL_124] (rows=36524 width=1119) + predicate:((d_year = 2001) and d_date_sk is not null and d_week_seq is not null) + TableScan [TS_9] (rows=73049 width=1119) + default@date_dim,d1,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_date","d_week_seq","d_year"] + <-Map 9 [SIMPLE_EDGE] + SHUFFLE [RS_26] + PartitionCols:_col0 + Select Operator [SEL_8] (rows=287989836 width=135) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"] + Filter Operator [FIL_123] (rows=287989836 width=135) + predicate:(cs_bill_cdemo_sk is not null and cs_bill_hdemo_sk is not null and cs_item_sk is not null and cs_ship_date_sk is not null and cs_sold_date_sk is not null) + TableScan [TS_6] (rows=287989836 width=135) + default@catalog_sales,catalog_sales,Tbl:COMPLETE,Col:NONE,Output:["cs_sold_date_sk","cs_ship_date_sk","cs_bill_cdemo_sk","cs_bill_hdemo_sk","cs_item_sk","cs_promo_sk","cs_order_number","cs_quantity"] + <-Reducer 2 [SIMPLE_EDGE] + SHUFFLE [RS_52] + PartitionCols:_col1 + Merge Join Operator [MERGEJOIN_132] (rows=41342400 width=15) + Conds:RS_49._col2=RS_50._col0(Inner),Output:["_col0","_col1","_col3","_col5"] + <-Map 1 [SIMPLE_EDGE] + SHUFFLE [RS_49] + PartitionCols:_col2 + Select Operator [SEL_2] (rows=37584000 width=15) + Output:["_col0","_col1","_col2","_col3"] + Filter Operator [FIL_121] (rows=37584000 width=15) + predicate:(inv_date_sk is not null and inv_item_sk is not null and inv_warehouse_sk is not null) + TableScan [TS_0] (rows=37584000 width=15) + default@inventory,inventory,Tbl:COMPLETE,Col:NONE,Output:["inv_date_sk","inv_item_sk","inv_warehouse_sk","inv_quantity_on_hand"] + <-Map 8 [SIMPLE_EDGE] + SHUFFLE [RS_50] + PartitionCols:_col0 + Select Operator [SEL_5] (rows=27 width=1029) + Output:["_col0","_col1"] + Filter Operator [FIL_122] (rows=27 width=1029) + predicate:w_warehouse_sk is not null + TableScan [TS_3] (rows=27 width=1029) + default@warehouse,warehouse,Tbl:COMPLETE,Col:NONE,Output:["w_warehouse_sk","w_warehouse_name"] + http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/tez/query73.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/query73.q.out b/ql/src/test/results/clientpositive/perf/tez/query73.q.out new file mode 100644 index 0000000..f666e29 --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/query73.q.out @@ -0,0 +1,150 @@ +PREHOOK: query: explain +select c_last_name + ,c_first_name + ,c_salutation + ,c_preferred_cust_flag + ,ss_ticket_number + ,cnt from + (select ss_ticket_number + ,ss_customer_sk + ,count(*) cnt + from store_sales,date_dim,store,household_demographics + where store_sales.ss_sold_date_sk = date_dim.d_date_sk + and store_sales.ss_store_sk = store.s_store_sk + and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk + and date_dim.d_dom between 1 and 2 + and (household_demographics.hd_buy_potential = '>10000' or + household_demographics.hd_buy_potential = 'unknown') + and household_demographics.hd_vehicle_count > 0 + and case when household_demographics.hd_vehicle_count > 0 then + household_demographics.hd_dep_count/ household_demographics.hd_vehicle_count else null end > 1 + and date_dim.d_year in (2000,2000+1,2000+2) + and store.s_county in ('Mobile County','Maverick County','Huron County','Kittitas County') + group by ss_ticket_number,ss_customer_sk) dj,customer + where ss_customer_sk = c_customer_sk + and cnt between 1 and 5 + order by cnt desc +PREHOOK: type: QUERY +POSTHOOK: query: explain +select c_last_name + ,c_first_name + ,c_salutation + ,c_preferred_cust_flag + ,ss_ticket_number + ,cnt from + (select ss_ticket_number + ,ss_customer_sk + ,count(*) cnt + from store_sales,date_dim,store,household_demographics + where store_sales.ss_sold_date_sk = date_dim.d_date_sk + and store_sales.ss_store_sk = store.s_store_sk + and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk + and date_dim.d_dom between 1 and 2 + and (household_demographics.hd_buy_potential = '>10000' or + household_demographics.hd_buy_potential = 'unknown') + and household_demographics.hd_vehicle_count > 0 + and case when household_demographics.hd_vehicle_count > 0 then + household_demographics.hd_dep_count/ household_demographics.hd_vehicle_count else null end > 1 + and date_dim.d_year in (2000,2000+1,2000+2) + and store.s_county in ('Mobile County','Maverick County','Huron County','Kittitas County') + group by ss_ticket_number,ss_customer_sk) dj,customer + where ss_customer_sk = c_customer_sk + and cnt between 1 and 5 + order by cnt desc +POSTHOOK: type: QUERY +Plan optimized by CBO. + +Vertex dependency in root stage +Reducer 2 <- Map 1 (SIMPLE_EDGE), Reducer 8 (SIMPLE_EDGE) +Reducer 3 <- Reducer 2 (SIMPLE_EDGE) +Reducer 5 <- Map 4 (SIMPLE_EDGE), Map 9 (SIMPLE_EDGE) +Reducer 6 <- Map 10 (SIMPLE_EDGE), Reducer 5 (SIMPLE_EDGE) +Reducer 7 <- Map 11 (SIMPLE_EDGE), Reducer 6 (SIMPLE_EDGE) +Reducer 8 <- Reducer 7 (SIMPLE_EDGE) + +Stage-0 + Fetch Operator + limit:-1 + Stage-1 + Reducer 3 + File Output Operator [FS_37] + Select Operator [SEL_36] (rows=88000001 width=860) + Output:["_col0","_col1","_col2","_col3","_col4","_col5"] + <-Reducer 2 [SIMPLE_EDGE] + SHUFFLE [RS_35] + Select Operator [SEL_34] (rows=88000001 width=860) + Output:["_col0","_col1","_col2","_col3","_col4","_col5"] + Merge Join Operator [MERGEJOIN_60] (rows=88000001 width=860) + Conds:RS_31._col0=RS_32._col1(Inner),Output:["_col1","_col2","_col3","_col4","_col5","_col7"] + <-Map 1 [SIMPLE_EDGE] + SHUFFLE [RS_31] + PartitionCols:_col0 + Select Operator [SEL_2] (rows=80000000 width=860) + Output:["_col0","_col1","_col2","_col3","_col4"] + Filter Operator [FIL_52] (rows=80000000 width=860) + predicate:c_customer_sk is not null + TableScan [TS_0] (rows=80000000 width=860) + default@customer,customer,Tbl:COMPLETE,Col:NONE,Output:["c_customer_sk","c_salutation","c_first_name","c_last_name","c_preferred_cust_flag"] + <-Reducer 8 [SIMPLE_EDGE] + SHUFFLE [RS_32] + PartitionCols:_col1 + Filter Operator [FIL_29] (rows=42591679 width=88) + predicate:_col2 BETWEEN 1 AND 5 + Select Operator [SEL_28] (rows=383325119 width=88) + Output:["_col0","_col1","_col2"] + Group By Operator [GBY_27] (rows=383325119 width=88) + Output:["_col0","_col1","_col2"],aggregations:["count(VALUE._col0)"],keys:KEY._col0, KEY._col1 + <-Reducer 7 [SIMPLE_EDGE] + SHUFFLE [RS_26] + PartitionCols:_col0, _col1 + Group By Operator [GBY_25] (rows=766650239 width=88) + Output:["_col0","_col1","_col2"],aggregations:["count()"],keys:_col1, _col4 + Merge Join Operator [MERGEJOIN_59] (rows=766650239 width=88) + Conds:RS_21._col3=RS_22._col0(Inner),Output:["_col1","_col4"] + <-Map 11 [SIMPLE_EDGE] + SHUFFLE [RS_22] + PartitionCols:_col0 + Select Operator [SEL_14] (rows=852 width=1910) + Output:["_col0"] + Filter Operator [FIL_56] (rows=852 width=1910) + predicate:((s_county) IN ('Mobile County', 'Maverick County', 'Huron County', 'Kittitas County') and s_store_sk is not null) + TableScan [TS_12] (rows=1704 width=1910) + default@store,store,Tbl:COMPLETE,Col:NONE,Output:["s_store_sk","s_county"] + <-Reducer 6 [SIMPLE_EDGE] + SHUFFLE [RS_21] + PartitionCols:_col3 + Merge Join Operator [MERGEJOIN_58] (rows=696954748 width=88) + Conds:RS_18._col2=RS_19._col0(Inner),Output:["_col1","_col3","_col4"] + <-Map 10 [SIMPLE_EDGE] + SHUFFLE [RS_19] + PartitionCols:_col0 + Select Operator [SEL_11] (rows=1200 width=107) + Output:["_col0"] + Filter Operator [FIL_55] (rows=1200 width=107) + predicate:(((hd_buy_potential = '>10000') or (hd_buy_potential = 'unknown')) and (hd_vehicle_count > 0) and CASE WHEN ((hd_vehicle_count > 0)) THEN (((UDFToDouble(hd_dep_count) / UDFToDouble(hd_vehicle_count)) > 1.0)) ELSE (null) END and hd_demo_sk is not null) + TableScan [TS_9] (rows=7200 width=107) + default@household_demographics,household_demographics,Tbl:COMPLETE,Col:NONE,Output:["hd_demo_sk","hd_buy_potential","hd_dep_count","hd_vehicle_count"] + <-Reducer 5 [SIMPLE_EDGE] + SHUFFLE [RS_18] + PartitionCols:_col2 + Merge Join Operator [MERGEJOIN_57] (rows=633595212 width=88) + Conds:RS_15._col0=RS_16._col0(Inner),Output:["_col1","_col2","_col3","_col4"] + <-Map 4 [SIMPLE_EDGE] + SHUFFLE [RS_15] + PartitionCols:_col0 + Select Operator [SEL_5] (rows=575995635 width=88) + Output:["_col0","_col1","_col2","_col3","_col4"] + Filter Operator [FIL_53] (rows=575995635 width=88) + predicate:(ss_customer_sk is not null and ss_hdemo_sk is not null and ss_sold_date_sk is not null and ss_store_sk is not null) + TableScan [TS_3] (rows=575995635 width=88) + default@store_sales,store_sales,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_customer_sk","ss_hdemo_sk","ss_store_sk","ss_ticket_number"] + <-Map 9 [SIMPLE_EDGE] + SHUFFLE [RS_16] + PartitionCols:_col0 + Select Operator [SEL_8] (rows=4058 width=1119) + Output:["_col0"] + Filter Operator [FIL_54] (rows=4058 width=1119) + predicate:((d_year) IN (2000, 2001, 2002) and d_date_sk is not null and d_dom BETWEEN 1 AND 2) + TableScan [TS_6] (rows=73049 width=1119) + default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_year","d_dom"] + http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/tez/query74.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/query74.q.out b/ql/src/test/results/clientpositive/perf/tez/query74.q.out new file mode 100644 index 0000000..a75aaa1 --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/query74.q.out @@ -0,0 +1,313 @@ +PREHOOK: query: explain +with year_total as ( + select c_customer_id customer_id + ,c_first_name customer_first_name + ,c_last_name customer_last_name + ,d_year as year + ,max(ss_net_paid) year_total + ,'s' sale_type + from customer + ,store_sales + ,date_dim + where c_customer_sk = ss_customer_sk + and ss_sold_date_sk = d_date_sk + and d_year in (2001,2001+1) + group by c_customer_id + ,c_first_name + ,c_last_name + ,d_year + union all + select c_customer_id customer_id + ,c_first_name customer_first_name + ,c_last_name customer_last_name + ,d_year as year + ,max(ws_net_paid) year_total + ,'w' sale_type + from customer + ,web_sales + ,date_dim + where c_customer_sk = ws_bill_customer_sk + and ws_sold_date_sk = d_date_sk + and d_year in (2001,2001+1) + group by c_customer_id + ,c_first_name + ,c_last_name + ,d_year + ) + select + t_s_secyear.customer_id, t_s_secyear.customer_first_name, t_s_secyear.customer_last_name + from year_total t_s_firstyear + ,year_total t_s_secyear + ,year_total t_w_firstyear + ,year_total t_w_secyear + where t_s_secyear.customer_id = t_s_firstyear.customer_id + and t_s_firstyear.customer_id = t_w_secyear.customer_id + and t_s_firstyear.customer_id = t_w_firstyear.customer_id + and t_s_firstyear.sale_type = 's' + and t_w_firstyear.sale_type = 'w' + and t_s_secyear.sale_type = 's' + and t_w_secyear.sale_type = 'w' + and t_s_firstyear.year = 2001 + and t_s_secyear.year = 2001+1 + and t_w_firstyear.year = 2001 + and t_w_secyear.year = 2001+1 + and t_s_firstyear.year_total > 0 + and t_w_firstyear.year_total > 0 + and case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else null end + > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else null end + order by 2,1,3 +limit 100 +PREHOOK: type: QUERY +POSTHOOK: query: explain +with year_total as ( + select c_customer_id customer_id + ,c_first_name customer_first_name + ,c_last_name customer_last_name + ,d_year as year + ,max(ss_net_paid) year_total + ,'s' sale_type + from customer + ,store_sales + ,date_dim + where c_customer_sk = ss_customer_sk + and ss_sold_date_sk = d_date_sk + and d_year in (2001,2001+1) + group by c_customer_id + ,c_first_name + ,c_last_name + ,d_year + union all + select c_customer_id customer_id + ,c_first_name customer_first_name + ,c_last_name customer_last_name + ,d_year as year + ,max(ws_net_paid) year_total + ,'w' sale_type + from customer + ,web_sales + ,date_dim + where c_customer_sk = ws_bill_customer_sk + and ws_sold_date_sk = d_date_sk + and d_year in (2001,2001+1) + group by c_customer_id + ,c_first_name + ,c_last_name + ,d_year + ) + select + t_s_secyear.customer_id, t_s_secyear.customer_first_name, t_s_secyear.customer_last_name + from year_total t_s_firstyear + ,year_total t_s_secyear + ,year_total t_w_firstyear + ,year_total t_w_secyear + where t_s_secyear.customer_id = t_s_firstyear.customer_id + and t_s_firstyear.customer_id = t_w_secyear.customer_id + and t_s_firstyear.customer_id = t_w_firstyear.customer_id + and t_s_firstyear.sale_type = 's' + and t_w_firstyear.sale_type = 'w' + and t_s_secyear.sale_type = 's' + and t_w_secyear.sale_type = 'w' + and t_s_firstyear.year = 2001 + and t_s_secyear.year = 2001+1 + and t_w_firstyear.year = 2001 + and t_w_secyear.year = 2001+1 + and t_s_firstyear.year_total > 0 + and t_w_firstyear.year_total > 0 + and case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else null end + > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else null end + order by 2,1,3 +limit 100 +POSTHOOK: type: QUERY +Plan optimized by CBO. + +Vertex dependency in root stage +Reducer 11 <- Map 10 (SIMPLE_EDGE), Map 17 (SIMPLE_EDGE) +Reducer 12 <- Map 18 (SIMPLE_EDGE), Reducer 11 (SIMPLE_EDGE) +Reducer 13 <- Reducer 12 (SIMPLE_EDGE) +Reducer 14 <- Map 10 (SIMPLE_EDGE), Map 17 (SIMPLE_EDGE) +Reducer 15 <- Map 18 (SIMPLE_EDGE), Reducer 14 (SIMPLE_EDGE) +Reducer 16 <- Reducer 15 (SIMPLE_EDGE) +Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 17 (SIMPLE_EDGE) +Reducer 3 <- Map 18 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) +Reducer 4 <- Reducer 3 (SIMPLE_EDGE) +Reducer 5 <- Reducer 13 (SIMPLE_EDGE), Reducer 16 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE), Reducer 9 (SIMPLE_EDGE) +Reducer 6 <- Reducer 5 (SIMPLE_EDGE) +Reducer 7 <- Map 1 (SIMPLE_EDGE), Map 17 (SIMPLE_EDGE) +Reducer 8 <- Map 18 (SIMPLE_EDGE), Reducer 7 (SIMPLE_EDGE) +Reducer 9 <- Reducer 8 (SIMPLE_EDGE) + +Stage-0 + Fetch Operator + limit:100 + Stage-1 + Reducer 6 + File Output Operator [FS_92] + Limit [LIM_91] (rows=100 width=88) + Number of rows:100 + Select Operator [SEL_90] (rows=574987679 width=88) + Output:["_col0","_col1","_col2"] + <-Reducer 5 [SIMPLE_EDGE] + SHUFFLE [RS_89] + Select Operator [SEL_88] (rows=574987679 width=88) + Output:["_col0","_col1","_col2"] + Filter Operator [FIL_87] (rows=574987679 width=88) + predicate:CASE WHEN ((_col9 > 0)) THEN (CASE WHEN ((_col14 > 0)) THEN (((_col4 / _col14) > (_col19 / _col9))) ELSE ((null > (_col19 / _col9))) END) ELSE (CASE WHEN ((_col14 > 0)) THEN (((_col4 / _col14) > null)) ELSE (null) END) END + Merge Join Operator [MERGEJOIN_171] (rows=1149975359 width=88) + Conds:RS_82._col0=RS_83._col0(Inner),RS_83._col0=RS_84._col0(Inner),RS_83._col0=RS_85._col0(Inner),Output:["_col4","_col9","_col14","_col15","_col16","_col17","_col19"] + <-Reducer 13 [SIMPLE_EDGE] + SHUFFLE [RS_85] + PartitionCols:_col0 + Select Operator [SEL_81] (rows=348477374 width=88) + Output:["_col0","_col1","_col2","_col4"] + Group By Operator [GBY_80] (rows=348477374 width=88) + Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["max(VALUE._col0)"],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3 + <-Reducer 12 [SIMPLE_EDGE] + SHUFFLE [RS_79] + PartitionCols:_col0, _col1, _col2, _col3 + Group By Operator [GBY_78] (rows=696954748 width=88) + Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["max(_col2)"],keys:_col6, _col7, _col8, _col4 + Merge Join Operator [MERGEJOIN_170] (rows=696954748 width=88) + Conds:RS_74._col1=RS_75._col0(Inner),Output:["_col2","_col4","_col6","_col7","_col8"] + <-Map 18 [SIMPLE_EDGE] + SHUFFLE [RS_75] + PartitionCols:_col0 + Select Operator [SEL_70] (rows=80000000 width=860) + Output:["_col0","_col1","_col2","_col3"] + Filter Operator [FIL_160] (rows=80000000 width=860) + predicate:(c_customer_id is not null and c_customer_sk is not null) + TableScan [TS_68] (rows=80000000 width=860) + default@customer,customer,Tbl:COMPLETE,Col:NONE,Output:["c_customer_sk","c_customer_id","c_first_name","c_last_name"] + <-Reducer 11 [SIMPLE_EDGE] + SHUFFLE [RS_74] + PartitionCols:_col1 + Merge Join Operator [MERGEJOIN_169] (rows=633595212 width=88) + Conds:RS_71._col0=RS_72._col0(Inner),Output:["_col1","_col2","_col4"] + <-Map 17 [SIMPLE_EDGE] + SHUFFLE [RS_72] + PartitionCols:_col0 + Select Operator [SEL_67] (rows=18262 width=1119) + Output:["_col0","_col1"] + Filter Operator [FIL_159] (rows=18262 width=1119) + predicate:((d_year = 2002) and (d_year) IN (2001, 2002) and d_date_sk is not null) + TableScan [TS_65] (rows=73049 width=1119) + default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_year"] + <-Map 10 [SIMPLE_EDGE] + SHUFFLE [RS_71] + PartitionCols:_col0 + Select Operator [SEL_64] (rows=575995635 width=88) + Output:["_col0","_col1","_col2"] + Filter Operator [FIL_158] (rows=575995635 width=88) + predicate:(ss_customer_sk is not null and ss_sold_date_sk is not null) + TableScan [TS_62] (rows=575995635 width=88) + default@store_sales,store_sales,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_customer_sk","ss_net_paid"] + <-Reducer 16 [SIMPLE_EDGE] + SHUFFLE [RS_83] + PartitionCols:_col0 + Filter Operator [FIL_39] (rows=116159124 width=88) + predicate:(_col4 > 0) + Select Operator [SEL_162] (rows=348477374 width=88) + Output:["_col0","_col4"] + Group By Operator [GBY_38] (rows=348477374 width=88) + Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["max(VALUE._col0)"],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3 + <-Reducer 15 [SIMPLE_EDGE] + SHUFFLE [RS_37] + PartitionCols:_col0, _col1, _col2, _col3 + Group By Operator [GBY_36] (rows=696954748 width=88) + Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["max(_col2)"],keys:_col6, _col7, _col8, _col4 + Merge Join Operator [MERGEJOIN_166] (rows=696954748 width=88) + Conds:RS_32._col1=RS_33._col0(Inner),Output:["_col2","_col4","_col6","_col7","_col8"] + <-Map 18 [SIMPLE_EDGE] + SHUFFLE [RS_33] + PartitionCols:_col0 + Please refer to the previous Select Operator [SEL_70] + <-Reducer 14 [SIMPLE_EDGE] + SHUFFLE [RS_32] + PartitionCols:_col1 + Merge Join Operator [MERGEJOIN_165] (rows=633595212 width=88) + Conds:RS_29._col0=RS_30._col0(Inner),Output:["_col1","_col2","_col4"] + <-Map 17 [SIMPLE_EDGE] + SHUFFLE [RS_30] + PartitionCols:_col0 + Select Operator [SEL_25] (rows=18262 width=1119) + Output:["_col0","_col1"] + Filter Operator [FIL_153] (rows=18262 width=1119) + predicate:((d_year = 2001) and (d_year) IN (2001, 2002) and d_date_sk is not null) + Please refer to the previous TableScan [TS_65] + <-Map 10 [SIMPLE_EDGE] + SHUFFLE [RS_29] + PartitionCols:_col0 + Please refer to the previous Select Operator [SEL_64] + <-Reducer 4 [SIMPLE_EDGE] + SHUFFLE [RS_82] + PartitionCols:_col0 + Select Operator [SEL_19] (rows=87121617 width=135) + Output:["_col0","_col4"] + Group By Operator [GBY_18] (rows=87121617 width=135) + Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["max(VALUE._col0)"],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3 + <-Reducer 3 [SIMPLE_EDGE] + SHUFFLE [RS_17] + PartitionCols:_col0, _col1, _col2, _col3 + Group By Operator [GBY_16] (rows=174243235 width=135) + Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["max(_col2)"],keys:_col6, _col7, _col8, _col4 + Merge Join Operator [MERGEJOIN_164] (rows=174243235 width=135) + Conds:RS_12._col1=RS_13._col0(Inner),Output:["_col2","_col4","_col6","_col7","_col8"] + <-Map 18 [SIMPLE_EDGE] + SHUFFLE [RS_13] + PartitionCols:_col0 + Please refer to the previous Select Operator [SEL_70] + <-Reducer 2 [SIMPLE_EDGE] + SHUFFLE [RS_12] + PartitionCols:_col1 + Merge Join Operator [MERGEJOIN_163] (rows=158402938 width=135) + Conds:RS_9._col0=RS_10._col0(Inner),Output:["_col1","_col2","_col4"] + <-Map 17 [SIMPLE_EDGE] + SHUFFLE [RS_10] + PartitionCols:_col0 + Please refer to the previous Select Operator [SEL_67] + <-Map 1 [SIMPLE_EDGE] + SHUFFLE [RS_9] + PartitionCols:_col0 + Select Operator [SEL_2] (rows=144002668 width=135) + Output:["_col0","_col1","_col2"] + Filter Operator [FIL_149] (rows=144002668 width=135) + predicate:(ws_bill_customer_sk is not null and ws_sold_date_sk is not null) + TableScan [TS_0] (rows=144002668 width=135) + default@web_sales,web_sales,Tbl:COMPLETE,Col:NONE,Output:["ws_sold_date_sk","ws_bill_customer_sk","ws_net_paid"] + <-Reducer 9 [SIMPLE_EDGE] + SHUFFLE [RS_84] + PartitionCols:_col0 + Filter Operator [FIL_60] (rows=29040539 width=135) + predicate:(_col4 > 0) + Select Operator [SEL_161] (rows=87121617 width=135) + Output:["_col0","_col4"] + Group By Operator [GBY_59] (rows=87121617 width=135) + Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["max(VALUE._col0)"],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3 + <-Reducer 8 [SIMPLE_EDGE] + SHUFFLE [RS_58] + PartitionCols:_col0, _col1, _col2, _col3 + Group By Operator [GBY_57] (rows=174243235 width=135) + Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["max(_col2)"],keys:_col6, _col7, _col8, _col4 + Merge Join Operator [MERGEJOIN_168] (rows=174243235 width=135) + Conds:RS_53._col1=RS_54._col0(Inner),Output:["_col2","_col4","_col6","_col7","_col8"] + <-Map 18 [SIMPLE_EDGE] + SHUFFLE [RS_54] + PartitionCols:_col0 + Please refer to the previous Select Operator [SEL_70] + <-Reducer 7 [SIMPLE_EDGE] + SHUFFLE [RS_53] + PartitionCols:_col1 + Merge Join Operator [MERGEJOIN_167] (rows=158402938 width=135) + Conds:RS_50._col0=RS_51._col0(Inner),Output:["_col1","_col2","_col4"] + <-Map 17 [SIMPLE_EDGE] + SHUFFLE [RS_51] + PartitionCols:_col0 + Select Operator [SEL_46] (rows=18262 width=1119) + Output:["_col0","_col1"] + Filter Operator [FIL_156] (rows=18262 width=1119) + predicate:((d_year = 2001) and (d_year) IN (2001, 2002) and d_date_sk is not null) + Please refer to the previous TableScan [TS_65] + <-Map 1 [SIMPLE_EDGE] + SHUFFLE [RS_50] + PartitionCols:_col0 + Please refer to the previous Select Operator [SEL_2] +
