Repository: hive Updated Branches: refs/heads/master c9f56052c -> 9244fdc7e
http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/tez/query95.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/query95.q.out b/ql/src/test/results/clientpositive/perf/tez/query95.q.out new file mode 100644 index 0000000..dd3e344 --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/query95.q.out @@ -0,0 +1,231 @@ +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/tez/query96.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/query96.q.out b/ql/src/test/results/clientpositive/perf/tez/query96.q.out new file mode 100644 index 0000000..313fc7e --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/query96.q.out @@ -0,0 +1,108 @@ +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/tez/query97.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/query97.q.out b/ql/src/test/results/clientpositive/perf/tez/query97.q.out new file mode 100644 index 0000000..0388e61 --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/query97.q.out @@ -0,0 +1,132 @@ +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/tez/query98.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/query98.q.out b/ql/src/test/results/clientpositive/perf/tez/query98.q.out new file mode 100644 index 0000000..da534cd --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/query98.q.out @@ -0,0 +1,134 @@ +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/tez/query99.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/query99.q.out b/ql/src/test/results/clientpositive/perf/tez/query99.q.out new file mode 100644 index 0000000..db6bd04 --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/query99.q.out @@ -0,0 +1,164 @@ +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"] +
