http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/query95.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/query95.q.out b/ql/src/test/results/clientpositive/perf/query95.q.out deleted file mode 100644 index dd3e344..0000000 --- a/ql/src/test/results/clientpositive/perf/query95.q.out +++ /dev/null @@ -1,231 +0,0 @@ -PREHOOK: query: explain -with ws_wh as -(select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2 - from web_sales ws1,web_sales ws2 - where ws1.ws_order_number = ws2.ws_order_number - and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk) - select - count(distinct ws_order_number) as `order count` - ,sum(ws_ext_ship_cost) as `total shipping cost` - ,sum(ws_net_profit) as `total net profit` -from - web_sales ws1 - ,date_dim - ,customer_address - ,web_site -where - d_date between '1999-5-01' and - (cast('1999-5-01' as date) + 60 days) -and ws1.ws_ship_date_sk = d_date_sk -and ws1.ws_ship_addr_sk = ca_address_sk -and ca_state = 'TX' -and ws1.ws_web_site_sk = web_site_sk -and web_company_name = 'pri' -and ws1.ws_order_number in (select ws_order_number - from ws_wh) -and ws1.ws_order_number in (select wr_order_number - from web_returns,ws_wh - where wr_order_number = ws_wh.ws_order_number) -order by count(distinct ws_order_number) -limit 100 -PREHOOK: type: QUERY -POSTHOOK: query: explain -with ws_wh as -(select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2 - from web_sales ws1,web_sales ws2 - where ws1.ws_order_number = ws2.ws_order_number - and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk) - select - count(distinct ws_order_number) as `order count` - ,sum(ws_ext_ship_cost) as `total shipping cost` - ,sum(ws_net_profit) as `total net profit` -from - web_sales ws1 - ,date_dim - ,customer_address - ,web_site -where - d_date between '1999-5-01' and - (cast('1999-5-01' as date) + 60 days) -and ws1.ws_ship_date_sk = d_date_sk -and ws1.ws_ship_addr_sk = ca_address_sk -and ca_state = 'TX' -and ws1.ws_web_site_sk = web_site_sk -and web_company_name = 'pri' -and ws1.ws_order_number in (select ws_order_number - from ws_wh) -and ws1.ws_order_number in (select wr_order_number - from web_returns,ws_wh - where wr_order_number = ws_wh.ws_order_number) -order by count(distinct ws_order_number) -limit 100 -POSTHOOK: type: QUERY -Plan optimized by CBO. - -Vertex dependency in root stage -Reducer 13 <- Map 12 (SIMPLE_EDGE), Map 18 (SIMPLE_EDGE) -Reducer 14 <- Map 19 (SIMPLE_EDGE), Reducer 13 (SIMPLE_EDGE) -Reducer 15 <- Reducer 14 (SIMPLE_EDGE) -Reducer 16 <- Map 12 (SIMPLE_EDGE), Map 18 (SIMPLE_EDGE) -Reducer 17 <- Reducer 16 (SIMPLE_EDGE) -Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 9 (SIMPLE_EDGE) -Reducer 3 <- Map 10 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) -Reducer 4 <- Map 11 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE) -Reducer 5 <- Reducer 15 (ONE_TO_ONE_EDGE), Reducer 17 (ONE_TO_ONE_EDGE), Reducer 4 (SIMPLE_EDGE) -Reducer 6 <- Reducer 5 (SIMPLE_EDGE) -Reducer 7 <- Reducer 6 (CUSTOM_SIMPLE_EDGE) -Reducer 8 <- Reducer 7 (SIMPLE_EDGE) - -Stage-0 - Fetch Operator - limit:-1 - Stage-1 - Reducer 8 - File Output Operator [FS_71] - Limit [LIM_69] (rows=1 width=344) - Number of rows:100 - Select Operator [SEL_68] (rows=1 width=344) - Output:["_col0","_col1","_col2"] - <-Reducer 7 [SIMPLE_EDGE] - SHUFFLE [RS_67] - Select Operator [SEL_66] (rows=1 width=344) - Output:["_col1","_col2","_col3"] - Group By Operator [GBY_120] (rows=1 width=344) - Output:["_col0","_col1","_col2"],aggregations:["count(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)"] - <-Reducer 6 [CUSTOM_SIMPLE_EDGE] - PARTITION_ONLY_SHUFFLE [RS_119] - Group By Operator [GBY_118] (rows=1 width=344) - Output:["_col0","_col1","_col2"],aggregations:["count(_col0)","sum(_col1)","sum(_col2)"] - Group By Operator [GBY_117] (rows=421668645 width=135) - Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)"],keys:KEY._col0 - <-Reducer 5 [SIMPLE_EDGE] - SHUFFLE [RS_116] - PartitionCols:_col0 - Group By Operator [GBY_115] (rows=421668645 width=135) - Output:["_col0","_col2","_col3"],aggregations:["sum(_col4)","sum(_col5)"],keys:_col3 - Merge Join Operator [MERGEJOIN_127] (rows=421668645 width=135) - Conds:RS_58._col3=RS_59._col0(Inner),RS_58._col3=RS_60._col0(Inner),Output:["_col3","_col4","_col5"] - <-Reducer 15 [ONE_TO_ONE_EDGE] - FORWARD [RS_60] - PartitionCols:_col0 - Group By Operator [GBY_47] (rows=87121617 width=135) - Output:["_col0"],keys:KEY._col0 - <-Reducer 14 [SIMPLE_EDGE] - SHUFFLE [RS_46] - PartitionCols:_col0 - Group By Operator [GBY_45] (rows=174243235 width=135) - Output:["_col0"],keys:_col1 - Merge Join Operator [MERGEJOIN_126] (rows=174243235 width=135) - Conds:RS_41._col0=RS_42._col0(Inner),Output:["_col1"] - <-Map 19 [SIMPLE_EDGE] - SHUFFLE [RS_42] - PartitionCols:_col0 - Select Operator [SEL_40] (rows=14398467 width=92) - Output:["_col0"] - Filter Operator [FIL_114] (rows=14398467 width=92) - predicate:wr_order_number is not null - TableScan [TS_38] (rows=14398467 width=92) - default@web_returns,web_returns,Tbl:COMPLETE,Col:NONE,Output:["wr_order_number"] - <-Reducer 13 [SIMPLE_EDGE] - SHUFFLE [RS_41] - PartitionCols:_col0 - Select Operator [SEL_37] (rows=158402938 width=135) - Output:["_col0"] - Filter Operator [FIL_36] (rows=158402938 width=135) - predicate:(_col0 <> _col2) - Merge Join Operator [MERGEJOIN_125] (rows=158402938 width=135) - Conds:RS_33._col1=RS_34._col1(Inner),Output:["_col0","_col1","_col2"] - <-Map 12 [SIMPLE_EDGE] - SHUFFLE [RS_33] - PartitionCols:_col1 - Select Operator [SEL_29] (rows=144002668 width=135) - Output:["_col0","_col1"] - Filter Operator [FIL_112] (rows=144002668 width=135) - predicate:ws_order_number is not null - TableScan [TS_27] (rows=144002668 width=135) - default@web_sales,ws1,Tbl:COMPLETE,Col:NONE,Output:["ws_warehouse_sk","ws_order_number"] - <-Map 18 [SIMPLE_EDGE] - SHUFFLE [RS_34] - PartitionCols:_col1 - Select Operator [SEL_32] (rows=144002668 width=135) - Output:["_col0","_col1"] - Filter Operator [FIL_113] (rows=144002668 width=135) - predicate:ws_order_number is not null - TableScan [TS_30] (rows=144002668 width=135) - default@web_sales,ws2,Tbl:COMPLETE,Col:NONE,Output:["ws_warehouse_sk","ws_order_number"] - <-Reducer 17 [ONE_TO_ONE_EDGE] - FORWARD [RS_59] - PartitionCols:_col0 - Group By Operator [GBY_25] (rows=79201469 width=135) - Output:["_col0"],keys:KEY._col0 - <-Reducer 16 [SIMPLE_EDGE] - SHUFFLE [RS_24] - PartitionCols:_col0 - Group By Operator [GBY_23] (rows=158402938 width=135) - Output:["_col0"],keys:_col1 - Select Operator [SEL_22] (rows=158402938 width=135) - Output:["_col1"] - Filter Operator [FIL_21] (rows=158402938 width=135) - predicate:(_col0 <> _col2) - Merge Join Operator [MERGEJOIN_124] (rows=158402938 width=135) - Conds:RS_18._col1=RS_19._col1(Inner),Output:["_col0","_col1","_col2"] - <-Map 12 [SIMPLE_EDGE] - SHUFFLE [RS_18] - PartitionCols:_col1 - Please refer to the previous Select Operator [SEL_29] - <-Map 18 [SIMPLE_EDGE] - SHUFFLE [RS_19] - PartitionCols:_col1 - Please refer to the previous Select Operator [SEL_32] - <-Reducer 4 [SIMPLE_EDGE] - SHUFFLE [RS_58] - PartitionCols:_col3 - Merge Join Operator [MERGEJOIN_123] (rows=191667562 width=135) - Conds:RS_55._col2=RS_56._col0(Inner),Output:["_col3","_col4","_col5"] - <-Map 11 [SIMPLE_EDGE] - SHUFFLE [RS_56] - PartitionCols:_col0 - Select Operator [SEL_11] (rows=42 width=1850) - Output:["_col0"] - Filter Operator [FIL_109] (rows=42 width=1850) - predicate:((web_company_name = 'pri') and web_site_sk is not null) - TableScan [TS_9] (rows=84 width=1850) - default@web_site,web_site,Tbl:COMPLETE,Col:NONE,Output:["web_site_sk","web_company_name"] - <-Reducer 3 [SIMPLE_EDGE] - SHUFFLE [RS_55] - PartitionCols:_col2 - Merge Join Operator [MERGEJOIN_122] (rows=174243235 width=135) - Conds:RS_52._col1=RS_53._col0(Inner),Output:["_col2","_col3","_col4","_col5"] - <-Map 10 [SIMPLE_EDGE] - SHUFFLE [RS_53] - PartitionCols:_col0 - Select Operator [SEL_8] (rows=20000000 width=1014) - Output:["_col0"] - Filter Operator [FIL_108] (rows=20000000 width=1014) - predicate:((ca_state = 'TX') and ca_address_sk is not null) - TableScan [TS_6] (rows=40000000 width=1014) - default@customer_address,customer_address,Tbl:COMPLETE,Col:NONE,Output:["ca_address_sk","ca_state"] - <-Reducer 2 [SIMPLE_EDGE] - SHUFFLE [RS_52] - PartitionCols:_col1 - Merge Join Operator [MERGEJOIN_121] (rows=158402938 width=135) - Conds:RS_49._col0=RS_50._col0(Inner),Output:["_col1","_col2","_col3","_col4","_col5"] - <-Map 1 [SIMPLE_EDGE] - SHUFFLE [RS_49] - PartitionCols:_col0 - Select Operator [SEL_2] (rows=144002668 width=135) - Output:["_col0","_col1","_col2","_col3","_col4","_col5"] - Filter Operator [FIL_106] (rows=144002668 width=135) - predicate:(ws_order_number is not null and ws_ship_addr_sk is not null and ws_ship_date_sk is not null and ws_web_site_sk is not null) - TableScan [TS_0] (rows=144002668 width=135) - default@web_sales,ws1,Tbl:COMPLETE,Col:NONE,Output:["ws_ship_date_sk","ws_ship_addr_sk","ws_web_site_sk","ws_order_number","ws_ext_ship_cost","ws_net_profit"] - <-Map 9 [SIMPLE_EDGE] - SHUFFLE [RS_50] - PartitionCols:_col0 - Select Operator [SEL_5] (rows=8116 width=1119) - Output:["_col0"] - Filter Operator [FIL_107] (rows=8116 width=1119) - predicate:(CAST( d_date AS TIMESTAMP) BETWEEN 1999-05-01 00:00:00.0 AND 1999-06-30 00:00:00.0 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_date"] -
http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/query96.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/query96.q.out b/ql/src/test/results/clientpositive/perf/query96.q.out deleted file mode 100644 index 313fc7e..0000000 --- a/ql/src/test/results/clientpositive/perf/query96.q.out +++ /dev/null @@ -1,108 +0,0 @@ -PREHOOK: query: explain -select count(*) -from store_sales - ,household_demographics - ,time_dim, store -where ss_sold_time_sk = time_dim.t_time_sk - and ss_hdemo_sk = household_demographics.hd_demo_sk - and ss_store_sk = s_store_sk - and time_dim.t_hour = 8 - and time_dim.t_minute >= 30 - and household_demographics.hd_dep_count = 5 - and store.s_store_name = 'ese' -order by count(*) -limit 100 -PREHOOK: type: QUERY -POSTHOOK: query: explain -select count(*) -from store_sales - ,household_demographics - ,time_dim, store -where ss_sold_time_sk = time_dim.t_time_sk - and ss_hdemo_sk = household_demographics.hd_demo_sk - and ss_store_sk = s_store_sk - and time_dim.t_hour = 8 - and time_dim.t_minute >= 30 - and household_demographics.hd_dep_count = 5 - and store.s_store_name = 'ese' -order by count(*) -limit 100 -POSTHOOK: type: QUERY -Plan optimized by CBO. - -Vertex dependency in root stage -Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 7 (SIMPLE_EDGE) -Reducer 3 <- Map 8 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) -Reducer 4 <- Map 9 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE) -Reducer 5 <- Reducer 4 (CUSTOM_SIMPLE_EDGE) -Reducer 6 <- Reducer 5 (SIMPLE_EDGE) - -Stage-0 - Fetch Operator - limit:-1 - Stage-1 - Reducer 6 - File Output Operator [FS_30] - Limit [LIM_28] (rows=1 width=8) - Number of rows:100 - Select Operator [SEL_27] (rows=1 width=8) - Output:["_col0"] - <-Reducer 5 [SIMPLE_EDGE] - SHUFFLE [RS_26] - Select Operator [SEL_25] (rows=1 width=8) - Output:["_col1"] - Group By Operator [GBY_24] (rows=1 width=8) - Output:["_col0"],aggregations:["count(VALUE._col0)"] - <-Reducer 4 [CUSTOM_SIMPLE_EDGE] - PARTITION_ONLY_SHUFFLE [RS_23] - Group By Operator [GBY_22] (rows=1 width=8) - Output:["_col0"],aggregations:["count()"] - Merge Join Operator [MERGEJOIN_46] (rows=766650239 width=88) - Conds:RS_18._col2=RS_19._col0(Inner) - <-Map 9 [SIMPLE_EDGE] - SHUFFLE [RS_19] - PartitionCols:_col0 - Select Operator [SEL_11] (rows=852 width=1910) - Output:["_col0"] - Filter Operator [FIL_43] (rows=852 width=1910) - predicate:((s_store_name = 'ese') and s_store_sk is not null) - TableScan [TS_9] (rows=1704 width=1910) - default@store,store,Tbl:COMPLETE,Col:NONE,Output:["s_store_sk","s_store_name"] - <-Reducer 3 [SIMPLE_EDGE] - SHUFFLE [RS_18] - PartitionCols:_col2 - Merge Join Operator [MERGEJOIN_45] (rows=696954748 width=88) - Conds:RS_15._col1=RS_16._col0(Inner),Output:["_col2"] - <-Map 8 [SIMPLE_EDGE] - SHUFFLE [RS_16] - PartitionCols:_col0 - Select Operator [SEL_8] (rows=3600 width=107) - Output:["_col0"] - Filter Operator [FIL_42] (rows=3600 width=107) - predicate:((hd_dep_count = 5) and hd_demo_sk is not null) - TableScan [TS_6] (rows=7200 width=107) - default@household_demographics,household_demographics,Tbl:COMPLETE,Col:NONE,Output:["hd_demo_sk","hd_dep_count"] - <-Reducer 2 [SIMPLE_EDGE] - SHUFFLE [RS_15] - PartitionCols:_col1 - Merge Join Operator [MERGEJOIN_44] (rows=633595212 width=88) - Conds:RS_12._col0=RS_13._col0(Inner),Output:["_col1","_col2"] - <-Map 1 [SIMPLE_EDGE] - SHUFFLE [RS_12] - PartitionCols:_col0 - Select Operator [SEL_2] (rows=575995635 width=88) - Output:["_col0","_col1","_col2"] - Filter Operator [FIL_40] (rows=575995635 width=88) - predicate:(ss_hdemo_sk is not null and ss_sold_time_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_time_sk","ss_hdemo_sk","ss_store_sk"] - <-Map 7 [SIMPLE_EDGE] - SHUFFLE [RS_13] - PartitionCols:_col0 - Select Operator [SEL_5] (rows=14400 width=471) - Output:["_col0"] - Filter Operator [FIL_41] (rows=14400 width=471) - predicate:((t_hour = 8) and (t_minute >= 30) and t_time_sk is not null) - TableScan [TS_3] (rows=86400 width=471) - default@time_dim,time_dim,Tbl:COMPLETE,Col:NONE,Output:["t_time_sk","t_hour","t_minute"] - http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/query97.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/query97.q.out b/ql/src/test/results/clientpositive/perf/query97.q.out deleted file mode 100644 index 0388e61..0000000 --- a/ql/src/test/results/clientpositive/perf/query97.q.out +++ /dev/null @@ -1,132 +0,0 @@ -PREHOOK: query: explain -with ssci as ( -select ss_customer_sk customer_sk - ,ss_item_sk item_sk -from store_sales,date_dim -where ss_sold_date_sk = d_date_sk - and d_month_seq between 1212 and 1212 + 11 -group by ss_customer_sk - ,ss_item_sk), -csci as( - select cs_bill_customer_sk customer_sk - ,cs_item_sk item_sk -from catalog_sales,date_dim -where cs_sold_date_sk = d_date_sk - and d_month_seq between 1212 and 1212 + 11 -group by cs_bill_customer_sk - ,cs_item_sk) - select sum(case when ssci.customer_sk is not null and csci.customer_sk is null then 1 else 0 end) store_only - ,sum(case when ssci.customer_sk is null and csci.customer_sk is not null then 1 else 0 end) catalog_only - ,sum(case when ssci.customer_sk is not null and csci.customer_sk is not null then 1 else 0 end) store_and_catalog -from ssci full outer join csci on (ssci.customer_sk=csci.customer_sk - and ssci.item_sk = csci.item_sk) -limit 100 -PREHOOK: type: QUERY -POSTHOOK: query: explain -with ssci as ( -select ss_customer_sk customer_sk - ,ss_item_sk item_sk -from store_sales,date_dim -where ss_sold_date_sk = d_date_sk - and d_month_seq between 1212 and 1212 + 11 -group by ss_customer_sk - ,ss_item_sk), -csci as( - select cs_bill_customer_sk customer_sk - ,cs_item_sk item_sk -from catalog_sales,date_dim -where cs_sold_date_sk = d_date_sk - and d_month_seq between 1212 and 1212 + 11 -group by cs_bill_customer_sk - ,cs_item_sk) - select sum(case when ssci.customer_sk is not null and csci.customer_sk is null then 1 else 0 end) store_only - ,sum(case when ssci.customer_sk is null and csci.customer_sk is not null then 1 else 0 end) catalog_only - ,sum(case when ssci.customer_sk is not null and csci.customer_sk is not null then 1 else 0 end) store_and_catalog -from ssci full outer join csci on (ssci.customer_sk=csci.customer_sk - and ssci.item_sk = csci.item_sk) -limit 100 -POSTHOOK: type: QUERY -Plan optimized by CBO. - -Vertex dependency in root stage -Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 6 (SIMPLE_EDGE) -Reducer 3 <- Reducer 2 (SIMPLE_EDGE) -Reducer 4 <- Reducer 3 (ONE_TO_ONE_EDGE), Reducer 8 (ONE_TO_ONE_EDGE) -Reducer 5 <- Reducer 4 (CUSTOM_SIMPLE_EDGE) -Reducer 7 <- Map 6 (SIMPLE_EDGE), Map 9 (SIMPLE_EDGE) -Reducer 8 <- Reducer 7 (SIMPLE_EDGE) - -Stage-0 - Fetch Operator - limit:100 - Stage-1 - Reducer 5 - File Output Operator [FS_38] - Limit [LIM_37] (rows=1 width=24) - Number of rows:100 - Group By Operator [GBY_35] (rows=1 width=24) - Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)"] - <-Reducer 4 [CUSTOM_SIMPLE_EDGE] - PARTITION_ONLY_SHUFFLE [RS_34] - Group By Operator [GBY_33] (rows=1 width=24) - Output:["_col0","_col1","_col2"],aggregations:["sum(_col0)","sum(_col1)","sum(_col2)"] - Select Operator [SEL_31] (rows=348477374 width=88) - Output:["_col0","_col1","_col2"] - Merge Join Operator [MERGEJOIN_49] (rows=348477374 width=88) - Conds:RS_28._col0, _col1=RS_29._col0, _col1(Outer),Output:["_col0","_col2"] - <-Reducer 3 [ONE_TO_ONE_EDGE] - FORWARD [RS_28] - PartitionCols:_col0, _col1 - Group By Operator [GBY_12] (rows=316797606 width=88) - Output:["_col0","_col1"],keys:KEY._col0, KEY._col1 - <-Reducer 2 [SIMPLE_EDGE] - SHUFFLE [RS_11] - PartitionCols:_col0, _col1 - Group By Operator [GBY_10] (rows=633595212 width=88) - Output:["_col0","_col1"],keys:_col2, _col1 - Merge Join Operator [MERGEJOIN_47] (rows=633595212 width=88) - Conds:RS_6._col0=RS_7._col0(Inner),Output:["_col1","_col2"] - <-Map 6 [SIMPLE_EDGE] - SHUFFLE [RS_7] - PartitionCols:_col0 - Select Operator [SEL_5] (rows=8116 width=1119) - Output:["_col0"] - Filter Operator [FIL_44] (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,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_month_seq"] - <-Map 1 [SIMPLE_EDGE] - SHUFFLE [RS_6] - PartitionCols:_col0 - Select Operator [SEL_2] (rows=575995635 width=88) - Output:["_col0","_col1","_col2"] - Filter Operator [FIL_43] (rows=575995635 width=88) - predicate: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_customer_sk"] - <-Reducer 8 [ONE_TO_ONE_EDGE] - FORWARD [RS_29] - PartitionCols:_col0, _col1 - Group By Operator [GBY_26] (rows=158394413 width=135) - Output:["_col0","_col1"],keys:KEY._col0, KEY._col1 - <-Reducer 7 [SIMPLE_EDGE] - SHUFFLE [RS_25] - PartitionCols:_col0, _col1 - Group By Operator [GBY_24] (rows=316788826 width=135) - Output:["_col0","_col1"],keys:_col1, _col2 - Merge Join Operator [MERGEJOIN_48] (rows=316788826 width=135) - Conds:RS_20._col0=RS_21._col0(Inner),Output:["_col1","_col2"] - <-Map 6 [SIMPLE_EDGE] - SHUFFLE [RS_21] - PartitionCols:_col0 - Please refer to the previous Select Operator [SEL_5] - <-Map 9 [SIMPLE_EDGE] - SHUFFLE [RS_20] - PartitionCols:_col0 - Select Operator [SEL_16] (rows=287989836 width=135) - Output:["_col0","_col1","_col2"] - Filter Operator [FIL_45] (rows=287989836 width=135) - predicate:cs_sold_date_sk is not null - TableScan [TS_14] (rows=287989836 width=135) - default@catalog_sales,catalog_sales,Tbl:COMPLETE,Col:NONE,Output:["cs_sold_date_sk","cs_bill_customer_sk","cs_item_sk"] - http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/query98.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/query98.q.out b/ql/src/test/results/clientpositive/perf/query98.q.out deleted file mode 100644 index da534cd..0000000 --- a/ql/src/test/results/clientpositive/perf/query98.q.out +++ /dev/null @@ -1,134 +0,0 @@ -PREHOOK: query: explain -select i_item_desc - ,i_category - ,i_class - ,i_current_price - ,sum(ss_ext_sales_price) as itemrevenue - ,sum(ss_ext_sales_price)*100/sum(sum(ss_ext_sales_price)) over - (partition by i_class) as revenueratio -from - store_sales - ,item - ,date_dim -where - ss_item_sk = i_item_sk - and i_category in ('Jewelry', 'Sports', 'Books') - and ss_sold_date_sk = d_date_sk - and d_date between cast('2001-01-12' as date) - and (cast('2001-01-12' as date) + 30 days) -group by - i_item_id - ,i_item_desc - ,i_category - ,i_class - ,i_current_price -order by - i_category - ,i_class - ,i_item_id - ,i_item_desc - ,revenueratio -PREHOOK: type: QUERY -POSTHOOK: query: explain -select i_item_desc - ,i_category - ,i_class - ,i_current_price - ,sum(ss_ext_sales_price) as itemrevenue - ,sum(ss_ext_sales_price)*100/sum(sum(ss_ext_sales_price)) over - (partition by i_class) as revenueratio -from - store_sales - ,item - ,date_dim -where - ss_item_sk = i_item_sk - and i_category in ('Jewelry', 'Sports', 'Books') - and ss_sold_date_sk = d_date_sk - and d_date between cast('2001-01-12' as date) - and (cast('2001-01-12' as date) + 30 days) -group by - i_item_id - ,i_item_desc - ,i_category - ,i_class - ,i_current_price -order by - i_category - ,i_class - ,i_item_id - ,i_item_desc - ,revenueratio -POSTHOOK: type: QUERY -Plan optimized by CBO. - -Vertex dependency in root stage -Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 7 (SIMPLE_EDGE) -Reducer 3 <- Map 8 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) -Reducer 4 <- Reducer 3 (SIMPLE_EDGE) -Reducer 5 <- Reducer 4 (SIMPLE_EDGE) -Reducer 6 <- Reducer 5 (SIMPLE_EDGE) - -Stage-0 - Fetch Operator - limit:-1 - Stage-1 - Reducer 6 - File Output Operator [FS_28] - Select Operator [SEL_26] (rows=348477374 width=88) - Output:["_col0","_col1","_col2","_col3","_col4","_col5"] - <-Reducer 5 [SIMPLE_EDGE] - SHUFFLE [RS_25] - Select Operator [SEL_23] (rows=348477374 width=88) - Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"] - PTF Operator [PTF_22] (rows=348477374 width=88) - Function definitions:[{},{"name:":"windowingtablefunction","order by:":"_col3 ASC NULLS FIRST","partition by:":"_col3"}] - Select Operator [SEL_21] (rows=348477374 width=88) - Output:["_col0","_col1","_col2","_col3","_col4","_col5"] - <-Reducer 4 [SIMPLE_EDGE] - SHUFFLE [RS_20] - PartitionCols:_col3 - Select Operator [SEL_19] (rows=348477374 width=88) - Output:["_col0","_col1","_col2","_col3","_col4","_col5"] - Group By Operator [GBY_18] (rows=348477374 width=88) - Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3, KEY._col4 - <-Reducer 3 [SIMPLE_EDGE] - SHUFFLE [RS_17] - PartitionCols:_col0, _col1, _col2, _col3, _col4 - Group By Operator [GBY_16] (rows=696954748 width=88) - Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col2)"],keys:_col10, _col9, _col6, _col7, _col8 - Merge Join Operator [MERGEJOIN_38] (rows=696954748 width=88) - Conds:RS_12._col1=RS_13._col0(Inner),Output:["_col2","_col6","_col7","_col8","_col9","_col10"] - <-Map 8 [SIMPLE_EDGE] - SHUFFLE [RS_13] - PartitionCols:_col0 - Select Operator [SEL_8] (rows=231000 width=1436) - Output:["_col0","_col1","_col2","_col3","_col4","_col5"] - Filter Operator [FIL_36] (rows=231000 width=1436) - predicate:((i_category) IN ('Jewelry', 'Sports', 'Books') and i_item_sk is not null) - TableScan [TS_6] (rows=462000 width=1436) - default@item,item,Tbl:COMPLETE,Col:NONE,Output:["i_item_sk","i_item_id","i_item_desc","i_current_price","i_class","i_category"] - <-Reducer 2 [SIMPLE_EDGE] - SHUFFLE [RS_12] - PartitionCols:_col1 - Merge Join Operator [MERGEJOIN_37] (rows=633595212 width=88) - Conds:RS_9._col0=RS_10._col0(Inner),Output:["_col1","_col2"] - <-Map 1 [SIMPLE_EDGE] - SHUFFLE [RS_9] - PartitionCols:_col0 - Select Operator [SEL_2] (rows=575995635 width=88) - Output:["_col0","_col1","_col2"] - Filter Operator [FIL_34] (rows=575995635 width=88) - predicate:(ss_item_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_ext_sales_price"] - <-Map 7 [SIMPLE_EDGE] - SHUFFLE [RS_10] - PartitionCols:_col0 - Select Operator [SEL_5] (rows=8116 width=1119) - Output:["_col0"] - Filter Operator [FIL_35] (rows=8116 width=1119) - predicate:(CAST( d_date AS TIMESTAMP) BETWEEN 2001-01-12 00:00:00.0 AND 2001-02-11 00:00:00.0 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_date"] - http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/query99.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/query99.q.out b/ql/src/test/results/clientpositive/perf/query99.q.out deleted file mode 100644 index db6bd04..0000000 --- a/ql/src/test/results/clientpositive/perf/query99.q.out +++ /dev/null @@ -1,164 +0,0 @@ -PREHOOK: query: explain -select - substr(w_warehouse_name,1,20) - ,sm_type - ,cc_name - ,sum(case when (cs_ship_date_sk - cs_sold_date_sk <= 30 ) then 1 else 0 end) as `30 days` - ,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 30) and - (cs_ship_date_sk - cs_sold_date_sk <= 60) then 1 else 0 end ) as `31-60 days` - ,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 60) and - (cs_ship_date_sk - cs_sold_date_sk <= 90) then 1 else 0 end) as `61-90 days` - ,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 90) and - (cs_ship_date_sk - cs_sold_date_sk <= 120) then 1 else 0 end) as `91-120 days` - ,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 120) then 1 else 0 end) as `>120 days` -from - catalog_sales - ,warehouse - ,ship_mode - ,call_center - ,date_dim -where - d_month_seq between 1212 and 1212 + 11 -and cs_ship_date_sk = d_date_sk -and cs_warehouse_sk = w_warehouse_sk -and cs_ship_mode_sk = sm_ship_mode_sk -and cs_call_center_sk = cc_call_center_sk -group by - substr(w_warehouse_name,1,20) - ,sm_type - ,cc_name -order by substr(w_warehouse_name,1,20) - ,sm_type - ,cc_name -limit 100 -PREHOOK: type: QUERY -POSTHOOK: query: explain -select - substr(w_warehouse_name,1,20) - ,sm_type - ,cc_name - ,sum(case when (cs_ship_date_sk - cs_sold_date_sk <= 30 ) then 1 else 0 end) as `30 days` - ,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 30) and - (cs_ship_date_sk - cs_sold_date_sk <= 60) then 1 else 0 end ) as `31-60 days` - ,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 60) and - (cs_ship_date_sk - cs_sold_date_sk <= 90) then 1 else 0 end) as `61-90 days` - ,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 90) and - (cs_ship_date_sk - cs_sold_date_sk <= 120) then 1 else 0 end) as `91-120 days` - ,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 120) then 1 else 0 end) as `>120 days` -from - catalog_sales - ,warehouse - ,ship_mode - ,call_center - ,date_dim -where - d_month_seq between 1212 and 1212 + 11 -and cs_ship_date_sk = d_date_sk -and cs_warehouse_sk = w_warehouse_sk -and cs_ship_mode_sk = sm_ship_mode_sk -and cs_call_center_sk = cc_call_center_sk -group by - substr(w_warehouse_name,1,20) - ,sm_type - ,cc_name -order by substr(w_warehouse_name,1,20) - ,sm_type - ,cc_name -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:-1 - Stage-1 - Reducer 7 - File Output Operator [FS_37] - Limit [LIM_35] (rows=100 width=135) - Number of rows:100 - Select Operator [SEL_34] (rows=210822976 width=135) - Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"] - <-Reducer 6 [SIMPLE_EDGE] - SHUFFLE [RS_33] - Select Operator [SEL_32] (rows=210822976 width=135) - Output:["_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"] - Group By Operator [GBY_31] (rows=210822976 width=135) - Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)","sum(VALUE._col3)","sum(VALUE._col4)"],keys:KEY._col0, KEY._col1, KEY._col2 - <-Reducer 5 [SIMPLE_EDGE] - SHUFFLE [RS_30] - PartitionCols:_col0, _col1, _col2 - Group By Operator [GBY_29] (rows=421645953 width=135) - Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"],aggregations:["sum(_col3)","sum(_col4)","sum(_col5)","sum(_col6)","sum(_col7)"],keys:_col0, _col1, _col2 - Select Operator [SEL_27] (rows=421645953 width=135) - Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"] - Merge Join Operator [MERGEJOIN_60] (rows=421645953 width=135) - Conds:RS_24._col3=RS_25._col0(Inner),Output:["_col0","_col1","_col8","_col10","_col12"] - <-Map 11 [SIMPLE_EDGE] - SHUFFLE [RS_25] - PartitionCols:_col0 - Select Operator [SEL_14] (rows=1 width=0) - Output:["_col0","_col1"] - Filter Operator [FIL_56] (rows=1 width=0) - predicate:sm_ship_mode_sk is not null - TableScan [TS_12] (rows=1 width=0) - default@ship_mode,ship_mode,Tbl:PARTIAL,Col:NONE,Output:["sm_ship_mode_sk","sm_type"] - <-Reducer 4 [SIMPLE_EDGE] - SHUFFLE [RS_24] - PartitionCols:_col3 - Merge Join Operator [MERGEJOIN_59] (rows=383314495 width=135) - Conds:RS_21._col4=RS_22._col0(Inner),Output:["_col0","_col1","_col3","_col8","_col10"] - <-Map 10 [SIMPLE_EDGE] - SHUFFLE [RS_22] - PartitionCols:_col0 - Select Operator [SEL_11] (rows=27 width=1029) - Output:["_col0","_col1"] - Filter Operator [FIL_55] (rows=27 width=1029) - predicate:w_warehouse_sk is not null - TableScan [TS_9] (rows=27 width=1029) - default@warehouse,warehouse,Tbl:COMPLETE,Col:NONE,Output:["w_warehouse_sk","w_warehouse_name"] - <-Reducer 3 [SIMPLE_EDGE] - SHUFFLE [RS_21] - PartitionCols:_col4 - Merge Join Operator [MERGEJOIN_58] (rows=348467716 width=135) - Conds:RS_18._col2=RS_19._col0(Inner),Output:["_col0","_col1","_col3","_col4","_col8"] - <-Map 9 [SIMPLE_EDGE] - SHUFFLE [RS_19] - PartitionCols:_col0 - Select Operator [SEL_8] (rows=60 width=2045) - Output:["_col0","_col1"] - Filter Operator [FIL_54] (rows=60 width=2045) - predicate:cc_call_center_sk is not null - TableScan [TS_6] (rows=60 width=2045) - default@call_center,call_center,Tbl:COMPLETE,Col:NONE,Output:["cc_call_center_sk","cc_name"] - <-Reducer 2 [SIMPLE_EDGE] - SHUFFLE [RS_18] - PartitionCols:_col2 - Merge Join Operator [MERGEJOIN_57] (rows=316788826 width=135) - Conds:RS_15._col1=RS_16._col0(Inner),Output:["_col0","_col1","_col2","_col3","_col4"] - <-Map 1 [SIMPLE_EDGE] - SHUFFLE [RS_15] - PartitionCols:_col1 - Select Operator [SEL_2] (rows=287989836 width=135) - Output:["_col0","_col1","_col2","_col3","_col4"] - Filter Operator [FIL_52] (rows=287989836 width=135) - predicate:(cs_call_center_sk is not null and cs_ship_date_sk is not null and cs_ship_mode_sk is not null and cs_warehouse_sk is not null) - TableScan [TS_0] (rows=287989836 width=135) - default@catalog_sales,catalog_sales,Tbl:COMPLETE,Col:NONE,Output:["cs_sold_date_sk","cs_ship_date_sk","cs_call_center_sk","cs_ship_mode_sk","cs_warehouse_sk"] - <-Map 8 [SIMPLE_EDGE] - SHUFFLE [RS_16] - PartitionCols:_col0 - Select Operator [SEL_5] (rows=8116 width=1119) - Output:["_col0"] - Filter Operator [FIL_53] (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,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_month_seq"] - http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/tez/query1.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/query1.q.out b/ql/src/test/results/clientpositive/perf/tez/query1.q.out new file mode 100644 index 0000000..ad350c1 --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/query1.q.out @@ -0,0 +1,169 @@ +PREHOOK: query: explain +with customer_total_return as +(select sr_customer_sk as ctr_customer_sk +,sr_store_sk as ctr_store_sk +,sum(SR_FEE) as ctr_total_return +from store_returns +,date_dim +where sr_returned_date_sk = d_date_sk +and d_year =2000 +group by sr_customer_sk +,sr_store_sk) + select c_customer_id +from customer_total_return ctr1 +,store +,customer +where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2 +from customer_total_return ctr2 +where ctr1.ctr_store_sk = ctr2.ctr_store_sk) +and s_store_sk = ctr1.ctr_store_sk +and s_state = 'NM' +and ctr1.ctr_customer_sk = c_customer_sk +order by c_customer_id +limit 100 +PREHOOK: type: QUERY +POSTHOOK: query: explain +with customer_total_return as +(select sr_customer_sk as ctr_customer_sk +,sr_store_sk as ctr_store_sk +,sum(SR_FEE) as ctr_total_return +from store_returns +,date_dim +where sr_returned_date_sk = d_date_sk +and d_year =2000 +group by sr_customer_sk +,sr_store_sk) + select c_customer_id +from customer_total_return ctr1 +,store +,customer +where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2 +from customer_total_return ctr2 +where ctr1.ctr_store_sk = ctr2.ctr_store_sk) +and s_store_sk = ctr1.ctr_store_sk +and s_state = 'NM' +and ctr1.ctr_customer_sk = c_customer_sk +order by c_customer_id +limit 100 +POSTHOOK: type: QUERY +Plan optimized by CBO. + +Vertex dependency in root stage +Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 10 (SIMPLE_EDGE) +Reducer 3 <- Reducer 2 (SIMPLE_EDGE) +Reducer 4 <- Map 11 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE) +Reducer 5 <- Map 12 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE) +Reducer 6 <- Reducer 5 (SIMPLE_EDGE), Reducer 9 (SIMPLE_EDGE) +Reducer 7 <- Reducer 6 (SIMPLE_EDGE) +Reducer 8 <- Map 1 (SIMPLE_EDGE), Map 10 (SIMPLE_EDGE) +Reducer 9 <- Reducer 8 (SIMPLE_EDGE) + +Stage-0 + Fetch Operator + limit:100 + Stage-1 + Reducer 7 + File Output Operator [FS_53] + Limit [LIM_52] (rows=100 width=860) + Number of rows:100 + Select Operator [SEL_51] (rows=32266667 width=860) + Output:["_col0"] + <-Reducer 6 [SIMPLE_EDGE] + SHUFFLE [RS_50] + Select Operator [SEL_49] (rows=32266667 width=860) + Output:["_col0"] + Filter Operator [FIL_48] (rows=32266667 width=860) + predicate:(_col2 > CASE WHEN (_col8 is null) THEN (null) ELSE (_col7) END) + Merge Join Operator [MERGEJOIN_81] (rows=96800003 width=860) + Conds:RS_45._col1=RS_46._col2(Inner),Output:["_col2","_col6","_col7","_col8"] + <-Reducer 5 [SIMPLE_EDGE] + SHUFFLE [RS_45] + PartitionCols:_col1 + Merge Join Operator [MERGEJOIN_79] (rows=88000001 width=860) + Conds:RS_42._col0=RS_43._col0(Inner),Output:["_col1","_col2","_col6"] + <-Map 12 [SIMPLE_EDGE] + SHUFFLE [RS_43] + PartitionCols:_col0 + Select Operator [SEL_19] (rows=80000000 width=860) + Output:["_col0","_col1"] + Filter Operator [FIL_74] (rows=80000000 width=860) + predicate:c_customer_sk is not null + TableScan [TS_17] (rows=80000000 width=860) + default@customer,customer,Tbl:COMPLETE,Col:NONE,Output:["c_customer_sk","c_customer_id"] + <-Reducer 4 [SIMPLE_EDGE] + SHUFFLE [RS_42] + PartitionCols:_col0 + Merge Join Operator [MERGEJOIN_78] (rows=34842647 width=77) + Conds:RS_39._col1=RS_40._col0(Inner),Output:["_col0","_col1","_col2"] + <-Map 11 [SIMPLE_EDGE] + SHUFFLE [RS_40] + PartitionCols:_col0 + Select Operator [SEL_16] (rows=852 width=1910) + Output:["_col0"] + Filter Operator [FIL_73] (rows=852 width=1910) + predicate:((s_state = 'NM') and s_store_sk is not null) + TableScan [TS_14] (rows=1704 width=1910) + default@store,store,Tbl:COMPLETE,Col:NONE,Output:["s_store_sk","s_state"] + <-Reducer 3 [SIMPLE_EDGE] + SHUFFLE [RS_39] + PartitionCols:_col1 + Select Operator [SEL_13] (rows=31675133 width=77) + Output:["_col0","_col1","_col2"] + Group By Operator [GBY_12] (rows=31675133 width=77) + Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1 + <-Reducer 2 [SIMPLE_EDGE] + SHUFFLE [RS_11] + PartitionCols:_col0, _col1 + Group By Operator [GBY_10] (rows=63350266 width=77) + Output:["_col0","_col1","_col2"],aggregations:["sum(_col3)"],keys:_col2, _col1 + Merge Join Operator [MERGEJOIN_77] (rows=63350266 width=77) + 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=57591150 width=77) + Output:["_col0","_col1","_col2","_col3"] + Filter Operator [FIL_71] (rows=57591150 width=77) + predicate:(sr_customer_sk is not null and sr_returned_date_sk is not null and sr_store_sk is not null) + TableScan [TS_0] (rows=57591150 width=77) + default@store_returns,store_returns,Tbl:COMPLETE,Col:NONE,Output:["sr_returned_date_sk","sr_customer_sk","sr_store_sk","sr_fee"] + <-Map 10 [SIMPLE_EDGE] + SHUFFLE [RS_7] + PartitionCols:_col0 + Select Operator [SEL_5] (rows=36524 width=1119) + Output:["_col0"] + Filter Operator [FIL_72] (rows=36524 width=1119) + predicate:((d_year = 2000) 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"] + <-Reducer 9 [SIMPLE_EDGE] + SHUFFLE [RS_46] + PartitionCols:_col2 + Select Operator [SEL_38] (rows=15837566 width=77) + Output:["_col0","_col1","_col2"] + Group By Operator [GBY_37] (rows=15837566 width=77) + Output:["_col0","_col1"],aggregations:["avg(_col2)"],keys:_col1 + Select Operator [SEL_33] (rows=31675133 width=77) + Output:["_col1","_col2"] + Group By Operator [GBY_32] (rows=31675133 width=77) + Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1 + <-Reducer 8 [SIMPLE_EDGE] + SHUFFLE [RS_31] + PartitionCols:_col0 + Group By Operator [GBY_30] (rows=63350266 width=77) + Output:["_col0","_col1","_col2"],aggregations:["sum(_col3)"],keys:_col2, _col1 + Merge Join Operator [MERGEJOIN_80] (rows=63350266 width=77) + Conds:RS_26._col0=RS_27._col0(Inner),Output:["_col1","_col2","_col3"] + <-Map 1 [SIMPLE_EDGE] + SHUFFLE [RS_26] + PartitionCols:_col0 + Select Operator [SEL_22] (rows=57591150 width=77) + Output:["_col0","_col1","_col2","_col3"] + Filter Operator [FIL_75] (rows=57591150 width=77) + predicate:(sr_returned_date_sk is not null and sr_store_sk is not null) + Please refer to the previous TableScan [TS_0] + <-Map 10 [SIMPLE_EDGE] + SHUFFLE [RS_27] + 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/query10.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/query10.q.out b/ql/src/test/results/clientpositive/perf/tez/query10.q.out new file mode 100644 index 0000000..cf3651b --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/query10.q.out @@ -0,0 +1,288 @@ +PREHOOK: query: explain +select + cd_gender, + cd_marital_status, + cd_education_status, + count(*) cnt1, + cd_purchase_estimate, + count(*) cnt2, + cd_credit_rating, + count(*) cnt3, + cd_dep_count, + count(*) cnt4, + cd_dep_employed_count, + count(*) cnt5, + cd_dep_college_count, + count(*) cnt6 + from + customer c,customer_address ca,customer_demographics + where + c.c_current_addr_sk = ca.ca_address_sk and + ca_county in ('Walker County','Richland County','Gaines County','Douglas County','Dona Ana County') and + cd_demo_sk = c.c_current_cdemo_sk and + exists (select * + from store_sales,date_dim + where c.c_customer_sk = ss_customer_sk and + ss_sold_date_sk = d_date_sk and + d_year = 2002 and + d_moy between 4 and 4+3) and + (exists (select * + from web_sales,date_dim + where c.c_customer_sk = ws_bill_customer_sk and + ws_sold_date_sk = d_date_sk and + d_year = 2002 and + d_moy between 4 ANd 4+3) or + exists (select * + from catalog_sales,date_dim + where c.c_customer_sk = cs_ship_customer_sk and + cs_sold_date_sk = d_date_sk and + d_year = 2002 and + d_moy between 4 and 4+3)) + group by cd_gender, + cd_marital_status, + cd_education_status, + cd_purchase_estimate, + cd_credit_rating, + cd_dep_count, + cd_dep_employed_count, + cd_dep_college_count + order by cd_gender, + cd_marital_status, + cd_education_status, + cd_purchase_estimate, + cd_credit_rating, + cd_dep_count, + cd_dep_employed_count, + cd_dep_college_count +limit 100 +PREHOOK: type: QUERY +POSTHOOK: query: explain +select + cd_gender, + cd_marital_status, + cd_education_status, + count(*) cnt1, + cd_purchase_estimate, + count(*) cnt2, + cd_credit_rating, + count(*) cnt3, + cd_dep_count, + count(*) cnt4, + cd_dep_employed_count, + count(*) cnt5, + cd_dep_college_count, + count(*) cnt6 + from + customer c,customer_address ca,customer_demographics + where + c.c_current_addr_sk = ca.ca_address_sk and + ca_county in ('Walker County','Richland County','Gaines County','Douglas County','Dona Ana County') and + cd_demo_sk = c.c_current_cdemo_sk and + exists (select * + from store_sales,date_dim + where c.c_customer_sk = ss_customer_sk and + ss_sold_date_sk = d_date_sk and + d_year = 2002 and + d_moy between 4 and 4+3) and + (exists (select * + from web_sales,date_dim + where c.c_customer_sk = ws_bill_customer_sk and + ws_sold_date_sk = d_date_sk and + d_year = 2002 and + d_moy between 4 ANd 4+3) or + exists (select * + from catalog_sales,date_dim + where c.c_customer_sk = cs_ship_customer_sk and + cs_sold_date_sk = d_date_sk and + d_year = 2002 and + d_moy between 4 and 4+3)) + group by cd_gender, + cd_marital_status, + cd_education_status, + cd_purchase_estimate, + cd_credit_rating, + cd_dep_count, + cd_dep_employed_count, + cd_dep_college_count + order by cd_gender, + cd_marital_status, + cd_education_status, + cd_purchase_estimate, + cd_credit_rating, + cd_dep_count, + cd_dep_employed_count, + cd_dep_college_count +limit 100 +POSTHOOK: type: QUERY +Plan optimized by CBO. + +Vertex dependency in root stage +Reducer 11 <- Map 10 (SIMPLE_EDGE), Map 16 (SIMPLE_EDGE) +Reducer 12 <- Reducer 11 (SIMPLE_EDGE) +Reducer 14 <- Map 13 (SIMPLE_EDGE), Map 16 (SIMPLE_EDGE) +Reducer 15 <- Reducer 14 (SIMPLE_EDGE) +Reducer 17 <- Map 16 (SIMPLE_EDGE), Map 19 (SIMPLE_EDGE) +Reducer 18 <- Reducer 17 (SIMPLE_EDGE) +Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE) +Reducer 3 <- Map 9 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) +Reducer 4 <- Reducer 12 (ONE_TO_ONE_EDGE), Reducer 15 (ONE_TO_ONE_EDGE), Reducer 3 (SIMPLE_EDGE) +Reducer 5 <- Reducer 18 (ONE_TO_ONE_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_74] + Limit [LIM_73] (rows=100 width=88) + Number of rows:100 + Select Operator [SEL_72] (rows=383325119 width=88) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10","_col11","_col12","_col13"] + <-Reducer 6 [SIMPLE_EDGE] + SHUFFLE [RS_71] + Select Operator [SEL_70] (rows=383325119 width=88) + Output:["_col0","_col1","_col2","_col3","_col4","_col6","_col8","_col10","_col12"] + Group By Operator [GBY_69] (rows=383325119 width=88) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"],aggregations:["count(VALUE._col0)"],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3, KEY._col4, KEY._col5, KEY._col6, KEY._col7 + <-Reducer 5 [SIMPLE_EDGE] + SHUFFLE [RS_68] + PartitionCols:_col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7 + Group By Operator [GBY_67] (rows=766650239 width=88) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"],aggregations:["count()"],keys:_col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13 + Select Operator [SEL_66] (rows=766650239 width=88) + Output:["_col6","_col7","_col8","_col9","_col10","_col11","_col12","_col13"] + Filter Operator [FIL_65] (rows=766650239 width=88) + predicate:(_col16 is not null or _col18 is not null) + Merge Join Operator [MERGEJOIN_112] (rows=766650239 width=88) + Conds:RS_62._col0=RS_63._col0(Left Outer),Output:["_col6","_col7","_col8","_col9","_col10","_col11","_col12","_col13","_col16","_col18"] + <-Reducer 18 [ONE_TO_ONE_EDGE] + FORWARD [RS_63] + PartitionCols:_col0 + Select Operator [SEL_61] (rows=158394413 width=135) + Output:["_col0","_col1"] + Group By Operator [GBY_60] (rows=158394413 width=135) + Output:["_col0"],keys:KEY._col0 + <-Reducer 17 [SIMPLE_EDGE] + SHUFFLE [RS_59] + PartitionCols:_col0 + Group By Operator [GBY_58] (rows=316788826 width=135) + Output:["_col0"],keys:_col1 + Merge Join Operator [MERGEJOIN_110] (rows=316788826 width=135) + Conds:RS_54._col0=RS_55._col0(Inner),Output:["_col1"] + <-Map 16 [SIMPLE_EDGE] + SHUFFLE [RS_55] + PartitionCols:_col0 + Select Operator [SEL_28] (rows=4058 width=1119) + Output:["_col0"] + Filter Operator [FIL_103] (rows=4058 width=1119) + predicate:((d_year = 2002) and d_date_sk is not null and d_moy BETWEEN 4 AND 7) + TableScan [TS_26] (rows=73049 width=1119) + default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_year","d_moy"] + <-Map 19 [SIMPLE_EDGE] + SHUFFLE [RS_54] + PartitionCols:_col0 + Select Operator [SEL_50] (rows=287989836 width=135) + Output:["_col0","_col1"] + Filter Operator [FIL_104] (rows=287989836 width=135) + predicate:(cs_ship_customer_sk is not null and cs_sold_date_sk is not null) + TableScan [TS_48] (rows=287989836 width=135) + default@catalog_sales,catalog_sales,Tbl:COMPLETE,Col:NONE,Output:["cs_sold_date_sk","cs_ship_customer_sk"] + <-Reducer 4 [SIMPLE_EDGE] + SHUFFLE [RS_62] + PartitionCols:_col0 + Select Operator [SEL_47] (rows=696954748 width=88) + Output:["_col0","_col6","_col7","_col8","_col9","_col10","_col11","_col12","_col13","_col16"] + Merge Join Operator [MERGEJOIN_111] (rows=696954748 width=88) + Conds:RS_43._col0=RS_44._col0(Left Outer),RS_43._col0=RS_45._col0(Inner),Output:["_col0","_col6","_col7","_col8","_col9","_col10","_col11","_col12","_col13","_col15"] + <-Reducer 12 [ONE_TO_ONE_EDGE] + FORWARD [RS_44] + PartitionCols:_col0 + Select Operator [SEL_22] (rows=79201469 width=135) + Output:["_col0","_col1"] + Group By Operator [GBY_21] (rows=79201469 width=135) + Output:["_col0"],keys:KEY._col0 + <-Reducer 11 [SIMPLE_EDGE] + SHUFFLE [RS_20] + PartitionCols:_col0 + Group By Operator [GBY_19] (rows=158402938 width=135) + Output:["_col0"],keys:_col1 + Merge Join Operator [MERGEJOIN_108] (rows=158402938 width=135) + Conds:RS_15._col0=RS_16._col0(Inner),Output:["_col1"] + <-Map 16 [SIMPLE_EDGE] + SHUFFLE [RS_16] + PartitionCols:_col0 + Please refer to the previous Select Operator [SEL_28] + <-Map 10 [SIMPLE_EDGE] + SHUFFLE [RS_15] + PartitionCols:_col0 + Select Operator [SEL_11] (rows=144002668 width=135) + Output:["_col0","_col1"] + Filter Operator [FIL_100] (rows=144002668 width=135) + predicate:(ws_bill_customer_sk is not null and ws_sold_date_sk is not null) + TableScan [TS_9] (rows=144002668 width=135) + default@web_sales,web_sales,Tbl:COMPLETE,Col:NONE,Output:["ws_sold_date_sk","ws_bill_customer_sk"] + <-Reducer 15 [ONE_TO_ONE_EDGE] + FORWARD [RS_45] + PartitionCols:_col0 + Group By Operator [GBY_35] (rows=316797606 width=88) + Output:["_col0"],keys:KEY._col0 + <-Reducer 14 [SIMPLE_EDGE] + SHUFFLE [RS_34] + PartitionCols:_col0 + Group By Operator [GBY_33] (rows=633595212 width=88) + Output:["_col0"],keys:_col1 + Merge Join Operator [MERGEJOIN_109] (rows=633595212 width=88) + Conds:RS_29._col0=RS_30._col0(Inner),Output:["_col1"] + <-Map 16 [SIMPLE_EDGE] + SHUFFLE [RS_30] + PartitionCols:_col0 + Please refer to the previous Select Operator [SEL_28] + <-Map 13 [SIMPLE_EDGE] + SHUFFLE [RS_29] + PartitionCols:_col0 + Select Operator [SEL_25] (rows=575995635 width=88) + Output:["_col0","_col1"] + Filter Operator [FIL_102] (rows=575995635 width=88) + predicate:(ss_customer_sk is not null and ss_sold_date_sk is not null) + TableScan [TS_23] (rows=575995635 width=88) + default@store_sales,store_sales,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_customer_sk"] + <-Reducer 3 [SIMPLE_EDGE] + SHUFFLE [RS_43] + PartitionCols:_col0 + Merge Join Operator [MERGEJOIN_107] (rows=96800003 width=860) + Conds:RS_40._col1=RS_41._col0(Inner),Output:["_col0","_col6","_col7","_col8","_col9","_col10","_col11","_col12","_col13"] + <-Map 9 [SIMPLE_EDGE] + SHUFFLE [RS_41] + PartitionCols:_col0 + Select Operator [SEL_8] (rows=1861800 width=385) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"] + Filter Operator [FIL_99] (rows=1861800 width=385) + predicate:cd_demo_sk is not null + TableScan [TS_6] (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","cd_purchase_estimate","cd_credit_rating","cd_dep_count","cd_dep_employed_count","cd_dep_college_count"] + <-Reducer 2 [SIMPLE_EDGE] + SHUFFLE [RS_40] + PartitionCols:_col1 + Merge Join Operator [MERGEJOIN_106] (rows=88000001 width=860) + Conds:RS_37._col2=RS_38._col0(Inner),Output:["_col0","_col1"] + <-Map 1 [SIMPLE_EDGE] + SHUFFLE [RS_37] + PartitionCols:_col2 + Select Operator [SEL_2] (rows=80000000 width=860) + Output:["_col0","_col1","_col2"] + Filter Operator [FIL_97] (rows=80000000 width=860) + predicate:(c_current_addr_sk is not null and c_current_cdemo_sk is not null) + TableScan [TS_0] (rows=80000000 width=860) + default@customer,c,Tbl:COMPLETE,Col:NONE,Output:["c_customer_sk","c_current_cdemo_sk","c_current_addr_sk"] + <-Map 8 [SIMPLE_EDGE] + SHUFFLE [RS_38] + PartitionCols:_col0 + Select Operator [SEL_5] (rows=20000000 width=1014) + Output:["_col0"] + Filter Operator [FIL_98] (rows=20000000 width=1014) + predicate:((ca_county) IN ('Walker County', 'Richland County', 'Gaines County', 'Douglas County', 'Dona Ana County') and ca_address_sk is not null) + TableScan [TS_3] (rows=40000000 width=1014) + default@customer_address,ca,Tbl:COMPLETE,Col:NONE,Output:["ca_address_sk","ca_county"] +
