http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/query7.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/query7.q.out b/ql/src/test/results/clientpositive/perf/query7.q.out deleted file mode 100644 index 00628db..0000000 --- a/ql/src/test/results/clientpositive/perf/query7.q.out +++ /dev/null @@ -1,132 +0,0 @@ -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/query70.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/query70.q.out b/ql/src/test/results/clientpositive/perf/query70.q.out deleted file mode 100644 index d700d60..0000000 --- a/ql/src/test/results/clientpositive/perf/query70.q.out +++ /dev/null @@ -1,205 +0,0 @@ -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/query71.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/query71.q.out b/ql/src/test/results/clientpositive/perf/query71.q.out deleted file mode 100644 index bd48e56..0000000 --- a/ql/src/test/results/clientpositive/perf/query71.q.out +++ /dev/null @@ -1,208 +0,0 @@ -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/query72.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/query72.q.out b/ql/src/test/results/clientpositive/perf/query72.q.out deleted file mode 100644 index a5e3501..0000000 --- a/ql/src/test/results/clientpositive/perf/query72.q.out +++ /dev/null @@ -1,250 +0,0 @@ -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/query73.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/query73.q.out b/ql/src/test/results/clientpositive/perf/query73.q.out deleted file mode 100644 index f666e29..0000000 --- a/ql/src/test/results/clientpositive/perf/query73.q.out +++ /dev/null @@ -1,150 +0,0 @@ -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/query74.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/query74.q.out b/ql/src/test/results/clientpositive/perf/query74.q.out deleted file mode 100644 index a75aaa1..0000000 --- a/ql/src/test/results/clientpositive/perf/query74.q.out +++ /dev/null @@ -1,313 +0,0 @@ -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] -