You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by se...@apache.org on 2017/10/03 22:55:26 UTC
[20/50] [abbrv] hive git commit: HIVE-17543: Enable PerfCliDriver for
HoS (Sahil Takiar, reviewed by Peter Vary) (addendum)
http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/tez/query11.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/query11.q.out b/ql/src/test/results/clientpositive/perf/tez/query11.q.out
new file mode 100644
index 0000000..69ced26
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/query11.q.out
@@ -0,0 +1,351 @@
+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
+ ,c_preferred_cust_flag
+ ,c_birth_country customer_birth_country
+ ,c_login customer_login
+ ,c_email_address customer_email_address
+ ,d_year dyear
+ ,sum(ss_ext_list_price-ss_ext_discount_amt) 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
+ group by c_customer_id
+ ,c_first_name
+ ,c_last_name
+ ,d_year
+ ,c_preferred_cust_flag
+ ,c_birth_country
+ ,c_login
+ ,c_email_address
+ ,d_year
+ union all
+ select c_customer_id customer_id
+ ,c_first_name customer_first_name
+ ,c_last_name customer_last_name
+ ,c_preferred_cust_flag
+ ,c_birth_country customer_birth_country
+ ,c_login customer_login
+ ,c_email_address customer_email_address
+ ,d_year dyear
+ ,sum(ws_ext_list_price-ws_ext_discount_amt) 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
+ group by c_customer_id
+ ,c_first_name
+ ,c_last_name
+ ,c_preferred_cust_flag
+ ,c_birth_country
+ ,c_login
+ ,c_email_address
+ ,d_year
+ )
+ select t_s_secyear.c_preferred_cust_flag
+ 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.dyear = 2001
+ and t_s_secyear.dyear = 2001+1
+ and t_w_firstyear.dyear = 2001
+ and t_w_secyear.dyear = 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 t_s_secyear.c_preferred_cust_flag
+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
+ ,c_preferred_cust_flag
+ ,c_birth_country customer_birth_country
+ ,c_login customer_login
+ ,c_email_address customer_email_address
+ ,d_year dyear
+ ,sum(ss_ext_list_price-ss_ext_discount_amt) 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
+ group by c_customer_id
+ ,c_first_name
+ ,c_last_name
+ ,d_year
+ ,c_preferred_cust_flag
+ ,c_birth_country
+ ,c_login
+ ,c_email_address
+ ,d_year
+ union all
+ select c_customer_id customer_id
+ ,c_first_name customer_first_name
+ ,c_last_name customer_last_name
+ ,c_preferred_cust_flag
+ ,c_birth_country customer_birth_country
+ ,c_login customer_login
+ ,c_email_address customer_email_address
+ ,d_year dyear
+ ,sum(ws_ext_list_price-ws_ext_discount_amt) 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
+ group by c_customer_id
+ ,c_first_name
+ ,c_last_name
+ ,c_preferred_cust_flag
+ ,c_birth_country
+ ,c_login
+ ,c_email_address
+ ,d_year
+ )
+ select t_s_secyear.c_preferred_cust_flag
+ 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.dyear = 2001
+ and t_s_secyear.dyear = 2001+1
+ and t_w_firstyear.dyear = 2001
+ and t_w_secyear.dyear = 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 t_s_secyear.c_preferred_cust_flag
+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_96]
+ Limit [LIM_95] (rows=100 width=88)
+ Number of rows:100
+ Select Operator [SEL_94] (rows=574987679 width=88)
+ Output:["_col0"]
+ <-Reducer 5 [SIMPLE_EDGE]
+ SHUFFLE [RS_93]
+ Select Operator [SEL_92] (rows=574987679 width=88)
+ Output:["_col0"]
+ Filter Operator [FIL_91] (rows=574987679 width=88)
+ predicate:CASE WHEN ((_col14 > 0)) THEN (CASE WHEN ((_col22 > 0)) THEN (((_col7 / _col22) > (_col30 / _col14))) ELSE ((null > (_col30 / _col14))) END) ELSE (CASE WHEN ((_col22 > 0)) THEN (((_col7 / _col22) > null)) ELSE (null) END) END
+ Merge Join Operator [MERGEJOIN_175] (rows=1149975359 width=88)
+ Conds:RS_86._col0=RS_87._col0(Inner),RS_87._col0=RS_88._col0(Inner),RS_87._col0=RS_89._col0(Inner),Output:["_col7","_col14","_col22","_col26","_col30"]
+ <-Reducer 13 [SIMPLE_EDGE]
+ SHUFFLE [RS_89]
+ PartitionCols:_col0
+ Select Operator [SEL_85] (rows=348477374 width=88)
+ Output:["_col0","_col3","_col7"]
+ Group By Operator [GBY_84] (rows=348477374 width=88)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3, KEY._col4, KEY._col5, KEY._col6
+ <-Reducer 12 [SIMPLE_EDGE]
+ SHUFFLE [RS_83]
+ PartitionCols:_col0, _col1, _col2, _col3, _col4, _col5, _col6
+ Group By Operator [GBY_82] (rows=696954748 width=88)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"],aggregations:["sum(_col7)"],keys:_col0, _col1, _col2, _col3, _col4, _col5, _col6
+ Select Operator [SEL_80] (rows=696954748 width=88)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
+ Merge Join Operator [MERGEJOIN_174] (rows=696954748 width=88)
+ Conds:RS_77._col1=RS_78._col0(Inner),Output:["_col2","_col3","_col7","_col8","_col9","_col10","_col11","_col12","_col13"]
+ <-Map 18 [SIMPLE_EDGE]
+ SHUFFLE [RS_78]
+ PartitionCols:_col0
+ Select Operator [SEL_73] (rows=80000000 width=860)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
+ Filter Operator [FIL_164] (rows=80000000 width=860)
+ predicate:(c_customer_id is not null and c_customer_sk is not null)
+ TableScan [TS_71] (rows=80000000 width=860)
+ default@customer,customer,Tbl:COMPLETE,Col:NONE,Output:["c_customer_sk","c_customer_id","c_first_name","c_last_name","c_preferred_cust_flag","c_birth_country","c_login","c_email_address"]
+ <-Reducer 11 [SIMPLE_EDGE]
+ SHUFFLE [RS_77]
+ PartitionCols:_col1
+ Merge Join Operator [MERGEJOIN_173] (rows=633595212 width=88)
+ Conds:RS_74._col0=RS_75._col0(Inner),Output:["_col1","_col2","_col3"]
+ <-Map 17 [SIMPLE_EDGE]
+ SHUFFLE [RS_75]
+ PartitionCols:_col0
+ Select Operator [SEL_70] (rows=36524 width=1119)
+ Output:["_col0"]
+ Filter Operator [FIL_163] (rows=36524 width=1119)
+ predicate:((d_year = 2002) and d_date_sk is not null)
+ TableScan [TS_68] (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_74]
+ PartitionCols:_col0
+ Select Operator [SEL_67] (rows=575995635 width=88)
+ Output:["_col0","_col1","_col2","_col3"]
+ Filter Operator [FIL_162] (rows=575995635 width=88)
+ predicate:(ss_customer_sk is not null and ss_sold_date_sk is not null)
+ TableScan [TS_65] (rows=575995635 width=88)
+ default@store_sales,store_sales,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_customer_sk","ss_ext_discount_amt","ss_ext_list_price"]
+ <-Reducer 16 [SIMPLE_EDGE]
+ SHUFFLE [RS_87]
+ PartitionCols:_col0
+ Select Operator [SEL_42] (rows=116159124 width=88)
+ Output:["_col0","_col6"]
+ Filter Operator [FIL_41] (rows=116159124 width=88)
+ predicate:(_col7 > 0)
+ Select Operator [SEL_166] (rows=348477374 width=88)
+ Output:["_col0","_col7"]
+ Group By Operator [GBY_40] (rows=348477374 width=88)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3, KEY._col4, KEY._col5, KEY._col6
+ <-Reducer 15 [SIMPLE_EDGE]
+ SHUFFLE [RS_39]
+ PartitionCols:_col0, _col1, _col2, _col3, _col4, _col5, _col6
+ Group By Operator [GBY_38] (rows=696954748 width=88)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"],aggregations:["sum(_col7)"],keys:_col0, _col1, _col2, _col3, _col4, _col5, _col6
+ Select Operator [SEL_36] (rows=696954748 width=88)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
+ Merge Join Operator [MERGEJOIN_170] (rows=696954748 width=88)
+ Conds:RS_33._col1=RS_34._col0(Inner),Output:["_col2","_col3","_col7","_col8","_col9","_col10","_col11","_col12","_col13"]
+ <-Map 18 [SIMPLE_EDGE]
+ SHUFFLE [RS_34]
+ PartitionCols:_col0
+ Please refer to the previous Select Operator [SEL_73]
+ <-Reducer 14 [SIMPLE_EDGE]
+ SHUFFLE [RS_33]
+ PartitionCols:_col1
+ Merge Join Operator [MERGEJOIN_169] (rows=633595212 width=88)
+ Conds:RS_30._col0=RS_31._col0(Inner),Output:["_col1","_col2","_col3"]
+ <-Map 17 [SIMPLE_EDGE]
+ SHUFFLE [RS_31]
+ PartitionCols:_col0
+ Select Operator [SEL_26] (rows=36524 width=1119)
+ Output:["_col0"]
+ Filter Operator [FIL_157] (rows=36524 width=1119)
+ predicate:((d_year = 2001) and d_date_sk is not null)
+ Please refer to the previous TableScan [TS_68]
+ <-Map 10 [SIMPLE_EDGE]
+ SHUFFLE [RS_30]
+ PartitionCols:_col0
+ Please refer to the previous Select Operator [SEL_67]
+ <-Reducer 4 [SIMPLE_EDGE]
+ SHUFFLE [RS_86]
+ PartitionCols:_col0
+ Select Operator [SEL_20] (rows=87121617 width=135)
+ Output:["_col0","_col7"]
+ Group By Operator [GBY_19] (rows=87121617 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3, KEY._col4, KEY._col5, KEY._col6
+ <-Reducer 3 [SIMPLE_EDGE]
+ SHUFFLE [RS_18]
+ PartitionCols:_col0, _col1, _col2, _col3, _col4, _col5, _col6
+ Group By Operator [GBY_17] (rows=174243235 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"],aggregations:["sum(_col7)"],keys:_col0, _col1, _col2, _col3, _col4, _col5, _col6
+ Select Operator [SEL_15] (rows=174243235 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
+ Merge Join Operator [MERGEJOIN_168] (rows=174243235 width=135)
+ Conds:RS_12._col1=RS_13._col0(Inner),Output:["_col2","_col3","_col7","_col8","_col9","_col10","_col11","_col12","_col13"]
+ <-Map 18 [SIMPLE_EDGE]
+ SHUFFLE [RS_13]
+ PartitionCols:_col0
+ Please refer to the previous Select Operator [SEL_73]
+ <-Reducer 2 [SIMPLE_EDGE]
+ SHUFFLE [RS_12]
+ PartitionCols:_col1
+ Merge Join Operator [MERGEJOIN_167] (rows=158402938 width=135)
+ Conds:RS_9._col0=RS_10._col0(Inner),Output:["_col1","_col2","_col3"]
+ <-Map 17 [SIMPLE_EDGE]
+ SHUFFLE [RS_10]
+ PartitionCols:_col0
+ Please refer to the previous Select Operator [SEL_70]
+ <-Map 1 [SIMPLE_EDGE]
+ SHUFFLE [RS_9]
+ PartitionCols:_col0
+ Select Operator [SEL_2] (rows=144002668 width=135)
+ Output:["_col0","_col1","_col2","_col3"]
+ Filter Operator [FIL_153] (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_ext_discount_amt","ws_ext_list_price"]
+ <-Reducer 9 [SIMPLE_EDGE]
+ SHUFFLE [RS_88]
+ PartitionCols:_col0
+ Filter Operator [FIL_63] (rows=29040539 width=135)
+ predicate:(_col7 > 0)
+ Select Operator [SEL_165] (rows=87121617 width=135)
+ Output:["_col0","_col7"]
+ Group By Operator [GBY_62] (rows=87121617 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3, KEY._col4, KEY._col5, KEY._col6
+ <-Reducer 8 [SIMPLE_EDGE]
+ SHUFFLE [RS_61]
+ PartitionCols:_col0, _col1, _col2, _col3, _col4, _col5, _col6
+ Group By Operator [GBY_60] (rows=174243235 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"],aggregations:["sum(_col7)"],keys:_col0, _col1, _col2, _col3, _col4, _col5, _col6
+ Select Operator [SEL_58] (rows=174243235 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
+ Merge Join Operator [MERGEJOIN_172] (rows=174243235 width=135)
+ Conds:RS_55._col1=RS_56._col0(Inner),Output:["_col2","_col3","_col7","_col8","_col9","_col10","_col11","_col12","_col13"]
+ <-Map 18 [SIMPLE_EDGE]
+ SHUFFLE [RS_56]
+ PartitionCols:_col0
+ Please refer to the previous Select Operator [SEL_73]
+ <-Reducer 7 [SIMPLE_EDGE]
+ SHUFFLE [RS_55]
+ PartitionCols:_col1
+ Merge Join Operator [MERGEJOIN_171] (rows=158402938 width=135)
+ Conds:RS_52._col0=RS_53._col0(Inner),Output:["_col1","_col2","_col3"]
+ <-Map 17 [SIMPLE_EDGE]
+ SHUFFLE [RS_53]
+ PartitionCols:_col0
+ Select Operator [SEL_48] (rows=36524 width=1119)
+ Output:["_col0"]
+ Filter Operator [FIL_160] (rows=36524 width=1119)
+ predicate:((d_year = 2001) and d_date_sk is not null)
+ Please refer to the previous TableScan [TS_68]
+ <-Map 1 [SIMPLE_EDGE]
+ SHUFFLE [RS_52]
+ PartitionCols:_col0
+ Please refer to the previous Select Operator [SEL_2]
+
http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/tez/query12.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/query12.q.out b/ql/src/test/results/clientpositive/perf/tez/query12.q.out
new file mode 100644
index 0000000..0506eca
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/query12.q.out
@@ -0,0 +1,138 @@
+PREHOOK: query: explain
+select i_item_desc
+ ,i_category
+ ,i_class
+ ,i_current_price
+ ,sum(ws_ext_sales_price) as itemrevenue
+ ,sum(ws_ext_sales_price)*100/sum(sum(ws_ext_sales_price)) over
+ (partition by i_class) as revenueratio
+from
+ web_sales
+ ,item
+ ,date_dim
+where
+ ws_item_sk = i_item_sk
+ and i_category in ('Jewelry', 'Sports', 'Books')
+ and ws_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
+limit 100
+PREHOOK: type: QUERY
+POSTHOOK: query: explain
+select i_item_desc
+ ,i_category
+ ,i_class
+ ,i_current_price
+ ,sum(ws_ext_sales_price) as itemrevenue
+ ,sum(ws_ext_sales_price)*100/sum(sum(ws_ext_sales_price)) over
+ (partition by i_class) as revenueratio
+from
+ web_sales
+ ,item
+ ,date_dim
+where
+ ws_item_sk = i_item_sk
+ and i_category in ('Jewelry', 'Sports', 'Books')
+ and ws_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
+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 <- 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_29]
+ Limit [LIM_27] (rows=100 width=135)
+ Number of rows:100
+ Select Operator [SEL_26] (rows=87121617 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ <-Reducer 5 [SIMPLE_EDGE]
+ SHUFFLE [RS_25]
+ Select Operator [SEL_23] (rows=87121617 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
+ PTF Operator [PTF_22] (rows=87121617 width=135)
+ Function definitions:[{},{"name:":"windowingtablefunction","order by:":"_col3 ASC NULLS FIRST","partition by:":"_col3"}]
+ Select Operator [SEL_21] (rows=87121617 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ <-Reducer 4 [SIMPLE_EDGE]
+ SHUFFLE [RS_20]
+ PartitionCols:_col3
+ Select Operator [SEL_19] (rows=87121617 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ Group By Operator [GBY_18] (rows=87121617 width=135)
+ 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=174243235 width=135)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col2)"],keys:_col10, _col9, _col6, _col7, _col8
+ Merge Join Operator [MERGEJOIN_39] (rows=174243235 width=135)
+ 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_37] (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_38] (rows=158402938 width=135)
+ 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=144002668 width=135)
+ Output:["_col0","_col1","_col2"]
+ Filter Operator [FIL_35] (rows=144002668 width=135)
+ predicate:(ws_item_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_item_sk","ws_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_36] (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/query13.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/query13.q.out b/ql/src/test/results/clientpositive/perf/tez/query13.q.out
new file mode 100644
index 0000000..7cb5449
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/query13.q.out
@@ -0,0 +1,205 @@
+PREHOOK: query: explain
+select avg(ss_quantity)
+ ,avg(ss_ext_sales_price)
+ ,avg(ss_ext_wholesale_cost)
+ ,sum(ss_ext_wholesale_cost)
+ from store_sales
+ ,store
+ ,customer_demographics
+ ,household_demographics
+ ,customer_address
+ ,date_dim
+ where s_store_sk = ss_store_sk
+ and ss_sold_date_sk = d_date_sk and d_year = 2001
+ and((ss_hdemo_sk=hd_demo_sk
+ and cd_demo_sk = ss_cdemo_sk
+ and cd_marital_status = 'M'
+ and cd_education_status = '4 yr Degree'
+ and ss_sales_price between 100.00 and 150.00
+ and hd_dep_count = 3
+ )or
+ (ss_hdemo_sk=hd_demo_sk
+ and cd_demo_sk = ss_cdemo_sk
+ and cd_marital_status = 'D'
+ and cd_education_status = 'Primary'
+ and ss_sales_price between 50.00 and 100.00
+ and hd_dep_count = 1
+ ) or
+ (ss_hdemo_sk=hd_demo_sk
+ and cd_demo_sk = ss_cdemo_sk
+ and cd_marital_status = 'U'
+ and cd_education_status = 'Advanced Degree'
+ and ss_sales_price between 150.00 and 200.00
+ and hd_dep_count = 1
+ ))
+ and((ss_addr_sk = ca_address_sk
+ and ca_country = 'United States'
+ and ca_state in ('KY', 'GA', 'NM')
+ and ss_net_profit between 100 and 200
+ ) or
+ (ss_addr_sk = ca_address_sk
+ and ca_country = 'United States'
+ and ca_state in ('MT', 'OR', 'IN')
+ and ss_net_profit between 150 and 300
+ ) or
+ (ss_addr_sk = ca_address_sk
+ and ca_country = 'United States'
+ and ca_state in ('WI', 'MO', 'WV')
+ and ss_net_profit between 50 and 250
+ ))
+PREHOOK: type: QUERY
+POSTHOOK: query: explain
+select avg(ss_quantity)
+ ,avg(ss_ext_sales_price)
+ ,avg(ss_ext_wholesale_cost)
+ ,sum(ss_ext_wholesale_cost)
+ from store_sales
+ ,store
+ ,customer_demographics
+ ,household_demographics
+ ,customer_address
+ ,date_dim
+ where s_store_sk = ss_store_sk
+ and ss_sold_date_sk = d_date_sk and d_year = 2001
+ and((ss_hdemo_sk=hd_demo_sk
+ and cd_demo_sk = ss_cdemo_sk
+ and cd_marital_status = 'M'
+ and cd_education_status = '4 yr Degree'
+ and ss_sales_price between 100.00 and 150.00
+ and hd_dep_count = 3
+ )or
+ (ss_hdemo_sk=hd_demo_sk
+ and cd_demo_sk = ss_cdemo_sk
+ and cd_marital_status = 'D'
+ and cd_education_status = 'Primary'
+ and ss_sales_price between 50.00 and 100.00
+ and hd_dep_count = 1
+ ) or
+ (ss_hdemo_sk=hd_demo_sk
+ and cd_demo_sk = ss_cdemo_sk
+ and cd_marital_status = 'U'
+ and cd_education_status = 'Advanced Degree'
+ and ss_sales_price between 150.00 and 200.00
+ and hd_dep_count = 1
+ ))
+ and((ss_addr_sk = ca_address_sk
+ and ca_country = 'United States'
+ and ca_state in ('KY', 'GA', 'NM')
+ and ss_net_profit between 100 and 200
+ ) or
+ (ss_addr_sk = ca_address_sk
+ and ca_country = 'United States'
+ and ca_state in ('MT', 'OR', 'IN')
+ and ss_net_profit between 150 and 300
+ ) or
+ (ss_addr_sk = ca_address_sk
+ and ca_country = 'United States'
+ and ca_state in ('WI', 'MO', 'WV')
+ and ss_net_profit between 50 and 250
+ ))
+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 <- Map 12 (SIMPLE_EDGE), Reducer 5 (SIMPLE_EDGE)
+Reducer 7 <- Reducer 6 (CUSTOM_SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:-1
+ Stage-1
+ Reducer 7
+ File Output Operator [FS_40]
+ Group By Operator [GBY_38] (rows=1 width=764)
+ Output:["_col0","_col1","_col2","_col3"],aggregations:["avg(VALUE._col0)","avg(VALUE._col1)","avg(VALUE._col2)","sum(VALUE._col3)"]
+ <-Reducer 6 [CUSTOM_SIMPLE_EDGE]
+ PARTITION_ONLY_SHUFFLE [RS_37]
+ Group By Operator [GBY_36] (rows=1 width=764)
+ Output:["_col0","_col1","_col2","_col3"],aggregations:["avg(_col6)","avg(_col8)","avg(_col9)","sum(_col9)"]
+ Select Operator [SEL_35] (rows=715776 width=88)
+ Output:["_col6","_col8","_col9"]
+ Filter Operator [FIL_34] (rows=715776 width=88)
+ predicate:(((_col19 = 'D') and (_col20 = 'Primary') and _col7 BETWEEN 50 AND 100 and (_col14 = 1)) or ((_col19 = 'M') and (_col20 = '4 yr Degree') and _col7 BETWEEN 100 AND 150 and (_col14 = 3)) or ((_col19 = 'U') and (_col20 = 'Advanced Degree') and _col7 BETWEEN 150 AND 200 and (_col14 = 1)))
+ Merge Join Operator [MERGEJOIN_71] (rows=17178642 width=88)
+ Conds:RS_31._col2=RS_32._col0(Inner),Output:["_col6","_col7","_col8","_col9","_col14","_col19","_col20"]
+ <-Map 12 [SIMPLE_EDGE]
+ SHUFFLE [RS_32]
+ PartitionCols:_col0
+ Select Operator [SEL_17] (rows=1861800 width=385)
+ Output:["_col0","_col1","_col2"]
+ Filter Operator [FIL_66] (rows=1861800 width=385)
+ predicate:(((cd_education_status = '4 yr Degree') or (cd_education_status = 'Primary') or (cd_education_status = 'Advanced Degree')) and ((cd_marital_status = 'M') or (cd_marital_status = 'D') or (cd_marital_status = 'U')) and cd_demo_sk is not null)
+ TableScan [TS_15] (rows=1861800 width=385)
+ default@customer_demographics,customer_demographics,Tbl:COMPLETE,Col:NONE,Output:["cd_demo_sk","cd_marital_status","cd_education_status"]
+ <-Reducer 5 [SIMPLE_EDGE]
+ SHUFFLE [RS_31]
+ PartitionCols:_col2
+ Filter Operator [FIL_30] (rows=15616947 width=88)
+ predicate:(((_col16) IN ('KY', 'GA', 'NM') and _col10 BETWEEN 100 AND 200) or ((_col16) IN ('MT', 'OR', 'IN') and _col10 BETWEEN 150 AND 300) or ((_col16) IN ('WI', 'MO', 'WV') and _col10 BETWEEN 50 AND 250))
+ Merge Join Operator [MERGEJOIN_70] (rows=93701693 width=88)
+ Conds:RS_27._col4=RS_28._col0(Inner),Output:["_col2","_col6","_col7","_col8","_col9","_col10","_col14","_col16"]
+ <-Map 11 [SIMPLE_EDGE]
+ SHUFFLE [RS_28]
+ PartitionCols:_col0
+ Select Operator [SEL_14] (rows=10000000 width=1014)
+ Output:["_col0","_col1"]
+ Filter Operator [FIL_65] (rows=10000000 width=1014)
+ predicate:((ca_country = 'United States') and (ca_state) IN ('KY', 'GA', 'NM', 'MT', 'OR', 'IN', 'WI', 'MO', 'WV') and ca_address_sk is not null)
+ TableScan [TS_12] (rows=40000000 width=1014)
+ default@customer_address,customer_address,Tbl:COMPLETE,Col:NONE,Output:["ca_address_sk","ca_state","ca_country"]
+ <-Reducer 4 [SIMPLE_EDGE]
+ SHUFFLE [RS_27]
+ PartitionCols:_col4
+ Merge Join Operator [MERGEJOIN_69] (rows=85183356 width=88)
+ Conds:RS_24._col3=RS_25._col0(Inner),Output:["_col2","_col4","_col6","_col7","_col8","_col9","_col10","_col14"]
+ <-Map 10 [SIMPLE_EDGE]
+ SHUFFLE [RS_25]
+ PartitionCols:_col0
+ Select Operator [SEL_11] (rows=7200 width=107)
+ Output:["_col0","_col1"]
+ Filter Operator [FIL_64] (rows=7200 width=107)
+ predicate:(((hd_dep_count = 3) or (hd_dep_count = 1)) 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_dep_count"]
+ <-Reducer 3 [SIMPLE_EDGE]
+ SHUFFLE [RS_24]
+ PartitionCols:_col3
+ Merge Join Operator [MERGEJOIN_68] (rows=77439413 width=88)
+ Conds:RS_21._col1=RS_22._col0(Inner),Output:["_col2","_col3","_col4","_col6","_col7","_col8","_col9","_col10"]
+ <-Map 9 [SIMPLE_EDGE]
+ SHUFFLE [RS_22]
+ PartitionCols:_col0
+ Select Operator [SEL_8] (rows=36524 width=1119)
+ Output:["_col0"]
+ Filter Operator [FIL_63] (rows=36524 width=1119)
+ predicate:((d_year = 2001) 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_21]
+ PartitionCols:_col1
+ Merge Join Operator [MERGEJOIN_67] (rows=70399465 width=88)
+ Conds:RS_18._col0=RS_19._col4(Inner),Output:["_col1","_col2","_col3","_col4","_col6","_col7","_col8","_col9","_col10"]
+ <-Map 1 [SIMPLE_EDGE]
+ SHUFFLE [RS_18]
+ PartitionCols:_col0
+ Select Operator [SEL_2] (rows=1704 width=1910)
+ Output:["_col0"]
+ Filter Operator [FIL_61] (rows=1704 width=1910)
+ predicate:s_store_sk is not null
+ TableScan [TS_0] (rows=1704 width=1910)
+ default@store,store,Tbl:COMPLETE,Col:NONE,Output:["s_store_sk"]
+ <-Map 8 [SIMPLE_EDGE]
+ SHUFFLE [RS_19]
+ PartitionCols:_col4
+ Select Operator [SEL_5] (rows=63999513 width=88)
+ Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9"]
+ Filter Operator [FIL_62] (rows=63999513 width=88)
+ predicate:((ss_net_profit BETWEEN 100 AND 200 or ss_net_profit BETWEEN 150 AND 300 or ss_net_profit BETWEEN 50 AND 250) and (ss_sales_price BETWEEN 100 AND 150 or ss_sales_price BETWEEN 50 AND 100 or ss_sales_price BETWEEN 150 AND 200) and ss_addr_sk is not null and ss_cdemo_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_cdemo_sk","ss_hdemo_sk","ss_addr_sk","ss_store_sk","ss_quantity","ss_sales_price","ss_ext_sales_price","ss_ext_wholesale_cost","ss_net_profit"]
+