hive-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ser...@apache.org
Subject [20/50] [abbrv] hive git commit: HIVE-17543: Enable PerfCliDriver for HoS (Sahil Takiar, reviewed by Peter Vary) (addendum)
Date Tue, 03 Oct 2017 22:55:26 GMT
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"]
+


Mime
View raw message