hive-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From stak...@apache.org
Subject [05/41] hive git commit: HIVE-17543: Enable PerfCliDriver for HoS (Sahil Takiar, reviewed by Peter Vary) (addendum)
Date Sat, 30 Sep 2017 05:43:09 GMT
http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/tez/query78.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/query78.q.out b/ql/src/test/results/clientpositive/perf/tez/query78.q.out
new file mode 100644
index 0000000..91c244f
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/query78.q.out
@@ -0,0 +1,292 @@
+PREHOOK: query: explain
+with ws as
+  (select d_year AS ws_sold_year, ws_item_sk,
+    ws_bill_customer_sk ws_customer_sk,
+    sum(ws_quantity) ws_qty,
+    sum(ws_wholesale_cost) ws_wc,
+    sum(ws_sales_price) ws_sp
+   from web_sales
+   left join web_returns on wr_order_number=ws_order_number and ws_item_sk=wr_item_sk
+   join date_dim on ws_sold_date_sk = d_date_sk
+   where wr_order_number is null
+   group by d_year, ws_item_sk, ws_bill_customer_sk
+   ),
+cs as
+  (select d_year AS cs_sold_year, cs_item_sk,
+    cs_bill_customer_sk cs_customer_sk,
+    sum(cs_quantity) cs_qty,
+    sum(cs_wholesale_cost) cs_wc,
+    sum(cs_sales_price) cs_sp
+   from catalog_sales
+   left join catalog_returns on cr_order_number=cs_order_number and cs_item_sk=cr_item_sk
+   join date_dim on cs_sold_date_sk = d_date_sk
+   where cr_order_number is null
+   group by d_year, cs_item_sk, cs_bill_customer_sk
+   ),
+ss as
+  (select d_year AS ss_sold_year, ss_item_sk,
+    ss_customer_sk,
+    sum(ss_quantity) ss_qty,
+    sum(ss_wholesale_cost) ss_wc,
+    sum(ss_sales_price) ss_sp
+   from store_sales
+   left join store_returns on sr_ticket_number=ss_ticket_number and ss_item_sk=sr_item_sk
+   join date_dim on ss_sold_date_sk = d_date_sk
+   where sr_ticket_number is null
+   group by d_year, ss_item_sk, ss_customer_sk
+   )
+ select 
+ss_sold_year, ss_item_sk, ss_customer_sk,
+round(ss_qty/(coalesce(ws_qty+cs_qty,1)),2) ratio,
+ss_qty store_qty, ss_wc store_wholesale_cost, ss_sp store_sales_price,
+coalesce(ws_qty,0)+coalesce(cs_qty,0) other_chan_qty,
+coalesce(ws_wc,0)+coalesce(cs_wc,0) other_chan_wholesale_cost,
+coalesce(ws_sp,0)+coalesce(cs_sp,0) other_chan_sales_price
+from ss
+left join ws on (ws_sold_year=ss_sold_year and ws_item_sk=ss_item_sk and ws_customer_sk=ss_customer_sk)
+left join cs on (cs_sold_year=ss_sold_year and cs_item_sk=cs_item_sk and cs_customer_sk=ss_customer_sk)
+where coalesce(ws_qty,0)>0 and coalesce(cs_qty, 0)>0 and ss_sold_year=2000
+order by 
+  ss_sold_year, ss_item_sk, ss_customer_sk,
+  ss_qty desc, ss_wc desc, ss_sp desc,
+  other_chan_qty,
+  other_chan_wholesale_cost,
+  other_chan_sales_price,
+  round(ss_qty/(coalesce(ws_qty+cs_qty,1)),2)
+limit 100
+PREHOOK: type: QUERY
+POSTHOOK: query: explain
+with ws as
+  (select d_year AS ws_sold_year, ws_item_sk,
+    ws_bill_customer_sk ws_customer_sk,
+    sum(ws_quantity) ws_qty,
+    sum(ws_wholesale_cost) ws_wc,
+    sum(ws_sales_price) ws_sp
+   from web_sales
+   left join web_returns on wr_order_number=ws_order_number and ws_item_sk=wr_item_sk
+   join date_dim on ws_sold_date_sk = d_date_sk
+   where wr_order_number is null
+   group by d_year, ws_item_sk, ws_bill_customer_sk
+   ),
+cs as
+  (select d_year AS cs_sold_year, cs_item_sk,
+    cs_bill_customer_sk cs_customer_sk,
+    sum(cs_quantity) cs_qty,
+    sum(cs_wholesale_cost) cs_wc,
+    sum(cs_sales_price) cs_sp
+   from catalog_sales
+   left join catalog_returns on cr_order_number=cs_order_number and cs_item_sk=cr_item_sk
+   join date_dim on cs_sold_date_sk = d_date_sk
+   where cr_order_number is null
+   group by d_year, cs_item_sk, cs_bill_customer_sk
+   ),
+ss as
+  (select d_year AS ss_sold_year, ss_item_sk,
+    ss_customer_sk,
+    sum(ss_quantity) ss_qty,
+    sum(ss_wholesale_cost) ss_wc,
+    sum(ss_sales_price) ss_sp
+   from store_sales
+   left join store_returns on sr_ticket_number=ss_ticket_number and ss_item_sk=sr_item_sk
+   join date_dim on ss_sold_date_sk = d_date_sk
+   where sr_ticket_number is null
+   group by d_year, ss_item_sk, ss_customer_sk
+   )
+ select 
+ss_sold_year, ss_item_sk, ss_customer_sk,
+round(ss_qty/(coalesce(ws_qty+cs_qty,1)),2) ratio,
+ss_qty store_qty, ss_wc store_wholesale_cost, ss_sp store_sales_price,
+coalesce(ws_qty,0)+coalesce(cs_qty,0) other_chan_qty,
+coalesce(ws_wc,0)+coalesce(cs_wc,0) other_chan_wholesale_cost,
+coalesce(ws_sp,0)+coalesce(cs_sp,0) other_chan_sales_price
+from ss
+left join ws on (ws_sold_year=ss_sold_year and ws_item_sk=ss_item_sk and ws_customer_sk=ss_customer_sk)
+left join cs on (cs_sold_year=ss_sold_year and cs_item_sk=cs_item_sk and cs_customer_sk=ss_customer_sk)
+where coalesce(ws_qty,0)>0 and coalesce(cs_qty, 0)>0 and ss_sold_year=2000
+order by 
+  ss_sold_year, ss_item_sk, ss_customer_sk,
+  ss_qty desc, ss_wc desc, ss_sp desc,
+  other_chan_qty,
+  other_chan_wholesale_cost,
+  other_chan_sales_price,
+  round(ss_qty/(coalesce(ws_qty+cs_qty,1)),2)
+limit 100
+POSTHOOK: type: QUERY
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Reducer 10 <- Reducer 9 (SIMPLE_EDGE)
+Reducer 12 <- Map 11 (SIMPLE_EDGE), Map 13 (SIMPLE_EDGE)
+Reducer 15 <- Map 14 (SIMPLE_EDGE), Map 16 (SIMPLE_EDGE)
+Reducer 18 <- Map 17 (SIMPLE_EDGE), Map 19 (SIMPLE_EDGE)
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Reducer 12 (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 10 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE)
+Reducer 6 <- Reducer 5 (SIMPLE_EDGE)
+Reducer 7 <- Map 1 (SIMPLE_EDGE), Reducer 15 (SIMPLE_EDGE)
+Reducer 8 <- Reducer 7 (SIMPLE_EDGE)
+Reducer 9 <- Map 1 (SIMPLE_EDGE), Reducer 18 (SIMPLE_EDGE)
+
+Stage-0
+  Fetch Operator
+    limit:-1
+    Stage-1
+      Reducer 6
+      File Output Operator [FS_78]
+        Select Operator [SEL_77] (rows=100 width=88)
+          Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9"]
+          Limit [LIM_76] (rows=100 width=88)
+            Number of rows:100
+            Select Operator [SEL_75] (rows=23425424 width=88)
+              Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"]
+            <-Reducer 5 [SIMPLE_EDGE]
+              SHUFFLE [RS_74]
+                Select Operator [SEL_73] (rows=23425424 width=88)
+                  Output:["_col0","_col1","_col6","_col7","_col8","_col9","_col10","_col11","_col12"]
+                  Filter Operator [FIL_72] (rows=23425424 width=88)
+                    predicate:(COALESCE(_col11,0) > 0)
+                    Merge Join Operator [MERGEJOIN_113] (rows=70276272 width=88)
+                      Conds:RS_69._col1=RS_70._col0(Left Outer),Output:["_col0","_col1","_col2","_col3","_col4","_col7","_col8","_col9","_col11","_col12","_col13"]
+                    <-Reducer 10 [SIMPLE_EDGE]
+                      SHUFFLE [RS_70]
+                        PartitionCols:_col0
+                        Select Operator [SEL_67] (rows=43558464 width=135)
+                          Output:["_col0","_col1","_col2","_col3"]
+                          Group By Operator [GBY_66] (rows=43558464 width=135)
+                            Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)"],keys:KEY._col0, KEY._col1
+                          <-Reducer 9 [SIMPLE_EDGE]
+                            SHUFFLE [RS_65]
+                              PartitionCols:_col0, _col1
+                              Group By Operator [GBY_64] (rows=87116928 width=135)
+                                Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(_col6)","sum(_col7)","sum(_col8)"],keys:_col3, _col4
+                                Merge Join Operator [MERGEJOIN_111] (rows=87116928 width=135)
+                                  Conds:RS_60._col0=RS_61._col0(Inner),Output:["_col3","_col4","_col6","_col7","_col8"]
+                                <-Map 1 [SIMPLE_EDGE]
+                                  SHUFFLE [RS_60]
+                                    PartitionCols:_col0
+                                    Select Operator [SEL_2] (rows=36524 width=1119)
+                                      Output:["_col0"]
+                                      Filter Operator [FIL_97] (rows=36524 width=1119)
+                                        predicate:((d_year = 2000) and d_date_sk is not null)
+                                        TableScan [TS_0] (rows=73049 width=1119)
+                                          default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_year"]
+                                <-Reducer 18 [SIMPLE_EDGE]
+                                  SHUFFLE [RS_61]
+                                    PartitionCols:_col0
+                                    Select Operator [SEL_59] (rows=79197206 width=135)
+                                      Output:["_col0","_col1","_col2","_col4","_col5","_col6"]
+                                      Filter Operator [FIL_58] (rows=79197206 width=135)
+                                        predicate:_col8 is null
+                                        Merge Join Operator [MERGEJOIN_110] (rows=158394413 width=135)
+                                          Conds:RS_55._col2, _col3=RS_56._col0, _col1(Left Outer),Output:["_col0","_col1","_col2","_col4","_col5","_col6","_col8"]
+                                        <-Map 17 [SIMPLE_EDGE]
+                                          SHUFFLE [RS_55]
+                                            PartitionCols:_col2, _col3
+                                            Select Operator [SEL_52] (rows=143994918 width=135)
+                                              Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
+                                              Filter Operator [FIL_104] (rows=143994918 width=135)
+                                                predicate:((cs_item_sk = cs_item_sk) and cs_sold_date_sk is not null)
+                                                TableScan [TS_50] (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","cs_order_number","cs_quantity","cs_wholesale_cost","cs_sales_price"]
+                                        <-Map 19 [SIMPLE_EDGE]
+                                          SHUFFLE [RS_56]
+                                            PartitionCols:_col0, _col1
+                                            Select Operator [SEL_54] (rows=28798881 width=106)
+                                              Output:["_col0","_col1"]
+                                              TableScan [TS_53] (rows=28798881 width=106)
+                                                default@catalog_returns,catalog_returns,Tbl:COMPLETE,Col:NONE,Output:["cr_item_sk","cr_order_number"]
+                    <-Reducer 4 [SIMPLE_EDGE]
+                      SHUFFLE [RS_69]
+                        PartitionCols:_col1
+                        Filter Operator [FIL_45] (rows=63887519 width=88)
+                          predicate:(COALESCE(_col7,0) > 0)
+                          Merge Join Operator [MERGEJOIN_112] (rows=191662559 width=88)
+                            Conds:RS_42._col1, _col0=RS_43._col1, _col0(Left Outer),Output:["_col0","_col1","_col2","_col3","_col4","_col7","_col8","_col9"]
+                          <-Reducer 3 [ONE_TO_ONE_EDGE]
+                            FORWARD [RS_42]
+                              PartitionCols:_col1, _col0
+                              Select Operator [SEL_20] (rows=174238687 width=88)
+                                Output:["_col0","_col1","_col2","_col3","_col4"]
+                                Group By Operator [GBY_19] (rows=174238687 width=88)
+                                  Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)"],keys:KEY._col0, KEY._col1
+                                <-Reducer 2 [SIMPLE_EDGE]
+                                  SHUFFLE [RS_18]
+                                    PartitionCols:_col0, _col1
+                                    Group By Operator [GBY_17] (rows=348477374 width=88)
+                                      Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(_col6)","sum(_col7)","sum(_col8)"],keys:_col4, _col3
+                                      Merge Join Operator [MERGEJOIN_107] (rows=348477374 width=88)
+                                        Conds:RS_13._col0=RS_14._col0(Inner),Output:["_col3","_col4","_col6","_col7","_col8"]
+                                      <-Map 1 [SIMPLE_EDGE]
+                                        SHUFFLE [RS_13]
+                                          PartitionCols:_col0
+                                           Please refer to the previous Select Operator [SEL_2]
+                                      <-Reducer 12 [SIMPLE_EDGE]
+                                        SHUFFLE [RS_14]
+                                          PartitionCols:_col0
+                                          Select Operator [SEL_12] (rows=316797606 width=88)
+                                            Output:["_col0","_col1","_col2","_col4","_col5","_col6"]
+                                            Filter Operator [FIL_11] (rows=316797606 width=88)
+                                              predicate:_col8 is null
+                                              Merge Join Operator [MERGEJOIN_106] (rows=633595212 width=88)
+                                                Conds:RS_8._col1, _col3=RS_9._col0, _col1(Left Outer),Output:["_col0","_col1","_col2","_col4","_col5","_col6","_col8"]
+                                              <-Map 11 [SIMPLE_EDGE]
+                                                SHUFFLE [RS_8]
+                                                  PartitionCols:_col1, _col3
+                                                  Select Operator [SEL_5] (rows=575995635 width=88)
+                                                    Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
+                                                    Filter Operator [FIL_98] (rows=575995635 width=88)
+                                                      predicate:ss_sold_date_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_item_sk","ss_customer_sk","ss_ticket_number","ss_quantity","ss_wholesale_cost","ss_sales_price"]
+                                              <-Map 13 [SIMPLE_EDGE]
+                                                SHUFFLE [RS_9]
+                                                  PartitionCols:_col0, _col1
+                                                  Select Operator [SEL_7] (rows=57591150 width=77)
+                                                    Output:["_col0","_col1"]
+                                                    TableScan [TS_6] (rows=57591150 width=77)
+                                                      default@store_returns,store_returns,Tbl:COMPLETE,Col:NONE,Output:["sr_item_sk","sr_ticket_number"]
+                          <-Reducer 8 [ONE_TO_ONE_EDGE]
+                            FORWARD [RS_43]
+                              PartitionCols:_col1, _col0
+                              Select Operator [SEL_41] (rows=43560808 width=135)
+                                Output:["_col0","_col1","_col2","_col3","_col4"]
+                                Group By Operator [GBY_40] (rows=43560808 width=135)
+                                  Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)"],keys:KEY._col0, KEY._col1
+                                <-Reducer 7 [SIMPLE_EDGE]
+                                  SHUFFLE [RS_39]
+                                    PartitionCols:_col0, _col1
+                                    Group By Operator [GBY_38] (rows=87121617 width=135)
+                                      Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(_col6)","sum(_col7)","sum(_col8)"],keys:_col4, _col3
+                                      Merge Join Operator [MERGEJOIN_109] (rows=87121617 width=135)
+                                        Conds:RS_34._col0=RS_35._col0(Inner),Output:["_col3","_col4","_col6","_col7","_col8"]
+                                      <-Map 1 [SIMPLE_EDGE]
+                                        SHUFFLE [RS_34]
+                                          PartitionCols:_col0
+                                           Please refer to the previous Select Operator [SEL_2]
+                                      <-Reducer 15 [SIMPLE_EDGE]
+                                        SHUFFLE [RS_35]
+                                          PartitionCols:_col0
+                                          Select Operator [SEL_33] (rows=79201469 width=135)
+                                            Output:["_col0","_col1","_col2","_col4","_col5","_col6"]
+                                            Filter Operator [FIL_32] (rows=79201469 width=135)
+                                              predicate:_col8 is null
+                                              Merge Join Operator [MERGEJOIN_108] (rows=158402938 width=135)
+                                                Conds:RS_29._col1, _col3=RS_30._col0, _col1(Left Outer),Output:["_col0","_col1","_col2","_col4","_col5","_col6","_col8"]
+                                              <-Map 14 [SIMPLE_EDGE]
+                                                SHUFFLE [RS_29]
+                                                  PartitionCols:_col1, _col3
+                                                  Select Operator [SEL_26] (rows=144002668 width=135)
+                                                    Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
+                                                    Filter Operator [FIL_101] (rows=144002668 width=135)
+                                                      predicate:ws_sold_date_sk is not null
+                                                      TableScan [TS_24] (rows=144002668 width=135)
+                                                        default@web_sales,web_sales,Tbl:COMPLETE,Col:NONE,Output:["ws_sold_date_sk","ws_item_sk","ws_bill_customer_sk","ws_order_number","ws_quantity","ws_wholesale_cost","ws_sales_price"]
+                                              <-Map 16 [SIMPLE_EDGE]
+                                                SHUFFLE [RS_30]
+                                                  PartitionCols:_col0, _col1
+                                                  Select Operator [SEL_28] (rows=14398467 width=92)
+                                                    Output:["_col0","_col1"]
+                                                    TableScan [TS_27] (rows=14398467 width=92)
+                                                      default@web_returns,web_returns,Tbl:COMPLETE,Col:NONE,Output:["wr_item_sk","wr_order_number"]
+

http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/tez/query79.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/query79.q.out b/ql/src/test/results/clientpositive/perf/tez/query79.q.out
new file mode 100644
index 0000000..b34a83f
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/query79.q.out
@@ -0,0 +1,140 @@
+PREHOOK: query: explain
+select 
+  c_last_name,c_first_name,substr(s_city,1,30),ss_ticket_number,amt,profit
+  from
+   (select ss_ticket_number
+          ,ss_customer_sk
+          ,store.s_city
+          ,sum(ss_coupon_amt) amt
+          ,sum(ss_net_profit) profit
+    from store_sales,date_dim,store,household_demographics
+    where store_sales.ss_sold_date_sk = date_dim.d_date_sk
+    and store_sales.ss_store_sk = store.s_store_sk  
+    and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
+    and (household_demographics.hd_dep_count = 8 or household_demographics.hd_vehicle_count > 0)
+    and date_dim.d_dow = 1
+    and date_dim.d_year in (1998,1998+1,1998+2) 
+    and store.s_number_employees between 200 and 295
+    group by ss_ticket_number,ss_customer_sk,ss_addr_sk,store.s_city) ms,customer
+    where ss_customer_sk = c_customer_sk
+ order by c_last_name,c_first_name,substr(s_city,1,30), profit
+limit 100
+PREHOOK: type: QUERY
+POSTHOOK: query: explain
+select 
+  c_last_name,c_first_name,substr(s_city,1,30),ss_ticket_number,amt,profit
+  from
+   (select ss_ticket_number
+          ,ss_customer_sk
+          ,store.s_city
+          ,sum(ss_coupon_amt) amt
+          ,sum(ss_net_profit) profit
+    from store_sales,date_dim,store,household_demographics
+    where store_sales.ss_sold_date_sk = date_dim.d_date_sk
+    and store_sales.ss_store_sk = store.s_store_sk  
+    and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
+    and (household_demographics.hd_dep_count = 8 or household_demographics.hd_vehicle_count > 0)
+    and date_dim.d_dow = 1
+    and date_dim.d_year in (1998,1998+1,1998+2) 
+    and store.s_number_employees between 200 and 295
+    group by ss_ticket_number,ss_customer_sk,ss_addr_sk,store.s_city) ms,customer
+    where ss_customer_sk = c_customer_sk
+ order by c_last_name,c_first_name,substr(s_city,1,30), profit
+limit 100
+POSTHOOK: type: QUERY
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Reducer 8 (SIMPLE_EDGE)
+Reducer 3 <- Reducer 2 (SIMPLE_EDGE)
+Reducer 5 <- Map 4 (SIMPLE_EDGE), Map 9 (SIMPLE_EDGE)
+Reducer 6 <- Map 10 (SIMPLE_EDGE), Reducer 5 (SIMPLE_EDGE)
+Reducer 7 <- Map 11 (SIMPLE_EDGE), Reducer 6 (SIMPLE_EDGE)
+Reducer 8 <- Reducer 7 (SIMPLE_EDGE)
+
+Stage-0
+  Fetch Operator
+    limit:-1
+    Stage-1
+      Reducer 3
+      File Output Operator [FS_38]
+        Limit [LIM_36] (rows=100 width=88)
+          Number of rows:100
+          Select Operator [SEL_35] (rows=421657640 width=88)
+            Output:["_col0","_col1","_col2","_col3","_col4","_col5"]
+          <-Reducer 2 [SIMPLE_EDGE]
+            SHUFFLE [RS_34]
+              Select Operator [SEL_33] (rows=421657640 width=88)
+                Output:["_col0","_col1","_col3","_col4","_col5","_col6"]
+                Merge Join Operator [MERGEJOIN_61] (rows=421657640 width=88)
+                  Conds:RS_30._col0=RS_31._col1(Inner),Output:["_col1","_col2","_col3","_col5","_col6","_col7"]
+                <-Map 1 [SIMPLE_EDGE]
+                  SHUFFLE [RS_30]
+                    PartitionCols:_col0
+                    Select Operator [SEL_2] (rows=80000000 width=860)
+                      Output:["_col0","_col1","_col2"]
+                      Filter Operator [FIL_53] (rows=80000000 width=860)
+                        predicate:c_customer_sk is not null
+                        TableScan [TS_0] (rows=80000000 width=860)
+                          default@customer,customer,Tbl:COMPLETE,Col:NONE,Output:["c_customer_sk","c_first_name","c_last_name"]
+                <-Reducer 8 [SIMPLE_EDGE]
+                  SHUFFLE [RS_31]
+                    PartitionCols:_col1
+                    Select Operator [SEL_28] (rows=383325119 width=88)
+                      Output:["_col0","_col1","_col2","_col3","_col4"]
+                      Group By Operator [GBY_27] (rows=383325119 width=88)
+                        Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)"],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3
+                      <-Reducer 7 [SIMPLE_EDGE]
+                        SHUFFLE [RS_26]
+                          PartitionCols:_col0, _col1, _col2, _col3
+                          Group By Operator [GBY_25] (rows=766650239 width=88)
+                            Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col6)","sum(_col7)"],keys:_col1, _col3, _col5, _col13
+                            Merge Join Operator [MERGEJOIN_60] (rows=766650239 width=88)
+                              Conds:RS_21._col2=RS_22._col0(Inner),Output:["_col1","_col3","_col5","_col6","_col7","_col13"]
+                            <-Map 11 [SIMPLE_EDGE]
+                              SHUFFLE [RS_22]
+                                PartitionCols:_col0
+                                Select Operator [SEL_14] (rows=6000 width=107)
+                                  Output:["_col0"]
+                                  Filter Operator [FIL_57] (rows=6000 width=107)
+                                    predicate:(((hd_dep_count = 8) or (hd_vehicle_count > 0)) and hd_demo_sk is not null)
+                                    TableScan [TS_12] (rows=7200 width=107)
+                                      default@household_demographics,household_demographics,Tbl:COMPLETE,Col:NONE,Output:["hd_demo_sk","hd_dep_count","hd_vehicle_count"]
+                            <-Reducer 6 [SIMPLE_EDGE]
+                              SHUFFLE [RS_21]
+                                PartitionCols:_col2
+                                Merge Join Operator [MERGEJOIN_59] (rows=696954748 width=88)
+                                  Conds:RS_18._col4=RS_19._col0(Inner),Output:["_col1","_col2","_col3","_col5","_col6","_col7","_col13"]
+                                <-Map 10 [SIMPLE_EDGE]
+                                  SHUFFLE [RS_19]
+                                    PartitionCols:_col0
+                                    Select Operator [SEL_11] (rows=189 width=1910)
+                                      Output:["_col0","_col2"]
+                                      Filter Operator [FIL_56] (rows=189 width=1910)
+                                        predicate:(s_number_employees BETWEEN 200 AND 295 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_number_employees","s_city"]
+                                <-Reducer 5 [SIMPLE_EDGE]
+                                  SHUFFLE [RS_18]
+                                    PartitionCols:_col4
+                                    Merge Join Operator [MERGEJOIN_58] (rows=633595212 width=88)
+                                      Conds:RS_15._col0=RS_16._col0(Inner),Output:["_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
+                                    <-Map 4 [SIMPLE_EDGE]
+                                      SHUFFLE [RS_15]
+                                        PartitionCols:_col0
+                                        Select Operator [SEL_5] (rows=575995635 width=88)
+                                          Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"]
+                                          Filter Operator [FIL_54] (rows=575995635 width=88)
+                                            predicate:(ss_customer_sk is not null and ss_hdemo_sk is not null and ss_sold_date_sk is not null and ss_store_sk is not null)
+                                            TableScan [TS_3] (rows=575995635 width=88)
+                                              default@store_sales,store_sales,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_customer_sk","ss_hdemo_sk","ss_addr_sk","ss_store_sk","ss_ticket_number","ss_coupon_amt","ss_net_profit"]
+                                    <-Map 9 [SIMPLE_EDGE]
+                                      SHUFFLE [RS_16]
+                                        PartitionCols:_col0
+                                        Select Operator [SEL_8] (rows=18262 width=1119)
+                                          Output:["_col0"]
+                                          Filter Operator [FIL_55] (rows=18262 width=1119)
+                                            predicate:((d_dow = 1) and (d_year) IN (1998, 1999, 2000) 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","d_dow"]
+

http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/tez/query8.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/query8.q.out b/ql/src/test/results/clientpositive/perf/tez/query8.q.out
new file mode 100644
index 0000000..8660fc9
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/query8.q.out
@@ -0,0 +1,358 @@
+PREHOOK: query: explain
+select  s_store_name
+      ,sum(ss_net_profit)
+ from store_sales
+     ,date_dim
+     ,store,
+     (select ca_zip
+     from (
+     (SELECT substr(ca_zip,1,5) ca_zip
+      FROM customer_address
+      WHERE substr(ca_zip,1,5) IN (
+                          '89436','30868','65085','22977','83927','77557',
+                          '58429','40697','80614','10502','32779',
+                          '91137','61265','98294','17921','18427',
+                          '21203','59362','87291','84093','21505',
+                          '17184','10866','67898','25797','28055',
+                          '18377','80332','74535','21757','29742',
+                          '90885','29898','17819','40811','25990',
+                          '47513','89531','91068','10391','18846',
+                          '99223','82637','41368','83658','86199',
+                          '81625','26696','89338','88425','32200',
+                          '81427','19053','77471','36610','99823',
+                          '43276','41249','48584','83550','82276',
+                          '18842','78890','14090','38123','40936',
+                          '34425','19850','43286','80072','79188',
+                          '54191','11395','50497','84861','90733',
+                          '21068','57666','37119','25004','57835',
+                          '70067','62878','95806','19303','18840',
+                          '19124','29785','16737','16022','49613',
+                          '89977','68310','60069','98360','48649',
+                          '39050','41793','25002','27413','39736',
+                          '47208','16515','94808','57648','15009',
+                          '80015','42961','63982','21744','71853',
+                          '81087','67468','34175','64008','20261',
+                          '11201','51799','48043','45645','61163',
+                          '48375','36447','57042','21218','41100',
+                          '89951','22745','35851','83326','61125',
+                          '78298','80752','49858','52940','96976',
+                          '63792','11376','53582','18717','90226',
+                          '50530','94203','99447','27670','96577',
+                          '57856','56372','16165','23427','54561',
+                          '28806','44439','22926','30123','61451',
+                          '92397','56979','92309','70873','13355',
+                          '21801','46346','37562','56458','28286',
+                          '47306','99555','69399','26234','47546',
+                          '49661','88601','35943','39936','25632',
+                          '24611','44166','56648','30379','59785',
+                          '11110','14329','93815','52226','71381',
+                          '13842','25612','63294','14664','21077',
+                          '82626','18799','60915','81020','56447',
+                          '76619','11433','13414','42548','92713',
+                          '70467','30884','47484','16072','38936',
+                          '13036','88376','45539','35901','19506',
+                          '65690','73957','71850','49231','14276',
+                          '20005','18384','76615','11635','38177',
+                          '55607','41369','95447','58581','58149',
+                          '91946','33790','76232','75692','95464',
+                          '22246','51061','56692','53121','77209',
+                          '15482','10688','14868','45907','73520',
+                          '72666','25734','17959','24677','66446',
+                          '94627','53535','15560','41967','69297',
+                          '11929','59403','33283','52232','57350',
+                          '43933','40921','36635','10827','71286',
+                          '19736','80619','25251','95042','15526',
+                          '36496','55854','49124','81980','35375',
+                          '49157','63512','28944','14946','36503',
+                          '54010','18767','23969','43905','66979',
+                          '33113','21286','58471','59080','13395',
+                          '79144','70373','67031','38360','26705',
+                          '50906','52406','26066','73146','15884',
+                          '31897','30045','61068','45550','92454',
+                          '13376','14354','19770','22928','97790',
+                          '50723','46081','30202','14410','20223',
+                          '88500','67298','13261','14172','81410',
+                          '93578','83583','46047','94167','82564',
+                          '21156','15799','86709','37931','74703',
+                          '83103','23054','70470','72008','49247',
+                          '91911','69998','20961','70070','63197',
+                          '54853','88191','91830','49521','19454',
+                          '81450','89091','62378','25683','61869',
+                          '51744','36580','85778','36871','48121',
+                          '28810','83712','45486','67393','26935',
+                          '42393','20132','55349','86057','21309',
+                          '80218','10094','11357','48819','39734',
+                          '40758','30432','21204','29467','30214',
+                          '61024','55307','74621','11622','68908',
+                          '33032','52868','99194','99900','84936',
+                          '69036','99149','45013','32895','59004',
+                          '32322','14933','32936','33562','72550',
+                          '27385','58049','58200','16808','21360',
+                          '32961','18586','79307','15492'))
+     intersect
+     (select ca_zip
+      from (SELECT substr(ca_zip,1,5) ca_zip,count(*) cnt
+            FROM customer_address, customer
+            WHERE ca_address_sk = c_current_addr_sk and
+                  c_preferred_cust_flag='Y'
+            group by ca_zip
+            having count(*) > 10)A1))A2) V1
+ where ss_store_sk = s_store_sk
+  and ss_sold_date_sk = d_date_sk
+  and d_qoy = 1 and d_year = 2002
+  and (substr(s_zip,1,2) = substr(V1.ca_zip,1,2))
+ group by s_store_name
+ order by s_store_name
+ limit 100
+PREHOOK: type: QUERY
+POSTHOOK: query: explain
+select  s_store_name
+      ,sum(ss_net_profit)
+ from store_sales
+     ,date_dim
+     ,store,
+     (select ca_zip
+     from (
+     (SELECT substr(ca_zip,1,5) ca_zip
+      FROM customer_address
+      WHERE substr(ca_zip,1,5) IN (
+                          '89436','30868','65085','22977','83927','77557',
+                          '58429','40697','80614','10502','32779',
+                          '91137','61265','98294','17921','18427',
+                          '21203','59362','87291','84093','21505',
+                          '17184','10866','67898','25797','28055',
+                          '18377','80332','74535','21757','29742',
+                          '90885','29898','17819','40811','25990',
+                          '47513','89531','91068','10391','18846',
+                          '99223','82637','41368','83658','86199',
+                          '81625','26696','89338','88425','32200',
+                          '81427','19053','77471','36610','99823',
+                          '43276','41249','48584','83550','82276',
+                          '18842','78890','14090','38123','40936',
+                          '34425','19850','43286','80072','79188',
+                          '54191','11395','50497','84861','90733',
+                          '21068','57666','37119','25004','57835',
+                          '70067','62878','95806','19303','18840',
+                          '19124','29785','16737','16022','49613',
+                          '89977','68310','60069','98360','48649',
+                          '39050','41793','25002','27413','39736',
+                          '47208','16515','94808','57648','15009',
+                          '80015','42961','63982','21744','71853',
+                          '81087','67468','34175','64008','20261',
+                          '11201','51799','48043','45645','61163',
+                          '48375','36447','57042','21218','41100',
+                          '89951','22745','35851','83326','61125',
+                          '78298','80752','49858','52940','96976',
+                          '63792','11376','53582','18717','90226',
+                          '50530','94203','99447','27670','96577',
+                          '57856','56372','16165','23427','54561',
+                          '28806','44439','22926','30123','61451',
+                          '92397','56979','92309','70873','13355',
+                          '21801','46346','37562','56458','28286',
+                          '47306','99555','69399','26234','47546',
+                          '49661','88601','35943','39936','25632',
+                          '24611','44166','56648','30379','59785',
+                          '11110','14329','93815','52226','71381',
+                          '13842','25612','63294','14664','21077',
+                          '82626','18799','60915','81020','56447',
+                          '76619','11433','13414','42548','92713',
+                          '70467','30884','47484','16072','38936',
+                          '13036','88376','45539','35901','19506',
+                          '65690','73957','71850','49231','14276',
+                          '20005','18384','76615','11635','38177',
+                          '55607','41369','95447','58581','58149',
+                          '91946','33790','76232','75692','95464',
+                          '22246','51061','56692','53121','77209',
+                          '15482','10688','14868','45907','73520',
+                          '72666','25734','17959','24677','66446',
+                          '94627','53535','15560','41967','69297',
+                          '11929','59403','33283','52232','57350',
+                          '43933','40921','36635','10827','71286',
+                          '19736','80619','25251','95042','15526',
+                          '36496','55854','49124','81980','35375',
+                          '49157','63512','28944','14946','36503',
+                          '54010','18767','23969','43905','66979',
+                          '33113','21286','58471','59080','13395',
+                          '79144','70373','67031','38360','26705',
+                          '50906','52406','26066','73146','15884',
+                          '31897','30045','61068','45550','92454',
+                          '13376','14354','19770','22928','97790',
+                          '50723','46081','30202','14410','20223',
+                          '88500','67298','13261','14172','81410',
+                          '93578','83583','46047','94167','82564',
+                          '21156','15799','86709','37931','74703',
+                          '83103','23054','70470','72008','49247',
+                          '91911','69998','20961','70070','63197',
+                          '54853','88191','91830','49521','19454',
+                          '81450','89091','62378','25683','61869',
+                          '51744','36580','85778','36871','48121',
+                          '28810','83712','45486','67393','26935',
+                          '42393','20132','55349','86057','21309',
+                          '80218','10094','11357','48819','39734',
+                          '40758','30432','21204','29467','30214',
+                          '61024','55307','74621','11622','68908',
+                          '33032','52868','99194','99900','84936',
+                          '69036','99149','45013','32895','59004',
+                          '32322','14933','32936','33562','72550',
+                          '27385','58049','58200','16808','21360',
+                          '32961','18586','79307','15492'))
+     intersect
+     (select ca_zip
+      from (SELECT substr(ca_zip,1,5) ca_zip,count(*) cnt
+            FROM customer_address, customer
+            WHERE ca_address_sk = c_current_addr_sk and
+                  c_preferred_cust_flag='Y'
+            group by ca_zip
+            having count(*) > 10)A1))A2) V1
+ where ss_store_sk = s_store_sk
+  and ss_sold_date_sk = d_date_sk
+  and d_qoy = 1 and d_year = 2002
+  and (substr(s_zip,1,2) = substr(V1.ca_zip,1,2))
+ group by s_store_name
+ order by s_store_name
+ limit 100
+POSTHOOK: type: QUERY
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Reducer 10 <- Union 9 (SIMPLE_EDGE)
+Reducer 11 <- Map 17 (SIMPLE_EDGE), Reducer 10 (SIMPLE_EDGE)
+Reducer 13 <- Map 12 (SIMPLE_EDGE), Map 16 (SIMPLE_EDGE)
+Reducer 14 <- Reducer 13 (SIMPLE_EDGE)
+Reducer 15 <- Reducer 14 (SIMPLE_EDGE), Union 9 (CONTAINS)
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 6 (SIMPLE_EDGE)
+Reducer 3 <- Reducer 11 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
+Reducer 4 <- Reducer 3 (SIMPLE_EDGE)
+Reducer 5 <- Reducer 4 (SIMPLE_EDGE)
+Reducer 8 <- Map 7 (SIMPLE_EDGE), Union 9 (CONTAINS)
+
+Stage-0
+  Fetch Operator
+    limit:100
+    Stage-1
+      Reducer 5
+      File Output Operator [FS_63]
+        Limit [LIM_62] (rows=100 width=88)
+          Number of rows:100
+          Select Operator [SEL_61] (rows=348477374 width=88)
+            Output:["_col0","_col1"]
+          <-Reducer 4 [SIMPLE_EDGE]
+            SHUFFLE [RS_60]
+              Group By Operator [GBY_58] (rows=348477374 width=88)
+                Output:["_col0","_col1"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0
+              <-Reducer 3 [SIMPLE_EDGE]
+                SHUFFLE [RS_57]
+                  PartitionCols:_col0
+                  Group By Operator [GBY_56] (rows=696954748 width=88)
+                    Output:["_col0","_col1"],aggregations:["sum(_col2)"],keys:_col8
+                    Merge Join Operator [MERGEJOIN_89] (rows=696954748 width=88)
+                      Conds:RS_52._col1=RS_53._col1(Inner),Output:["_col2","_col8"]
+                    <-Reducer 11 [SIMPLE_EDGE]
+                      SHUFFLE [RS_53]
+                        PartitionCols:_col1
+                        Merge Join Operator [MERGEJOIN_88] (rows=1874 width=1911)
+                          Conds:RS_45.substr(_col0, 1, 2)=RS_46.substr(_col2, 1, 2)(Inner),Output:["_col1","_col2"]
+                        <-Map 17 [SIMPLE_EDGE]
+                          SHUFFLE [RS_46]
+                            PartitionCols:substr(_col2, 1, 2)
+                            Select Operator [SEL_44] (rows=1704 width=1910)
+                              Output:["_col0","_col1","_col2"]
+                              Filter Operator [FIL_85] (rows=1704 width=1910)
+                                predicate:(s_store_sk is not null and substr(s_zip, 1, 2) is not null)
+                                TableScan [TS_42] (rows=1704 width=1910)
+                                  default@store,store,Tbl:COMPLETE,Col:NONE,Output:["s_store_sk","s_store_name","s_zip"]
+                        <-Reducer 10 [SIMPLE_EDGE]
+                          SHUFFLE [RS_45]
+                            PartitionCols:substr(_col0, 1, 2)
+                            Select Operator [SEL_41] (rows=1 width=1014)
+                              Output:["_col0"]
+                              Filter Operator [FIL_40] (rows=1 width=1014)
+                                predicate:(_col1 = 2)
+                                Group By Operator [GBY_39] (rows=6833333 width=1014)
+                                  Output:["_col0","_col1"],aggregations:["count(VALUE._col0)"],keys:KEY._col0
+                                <-Union 9 [SIMPLE_EDGE]
+                                  <-Reducer 15 [CONTAINS]
+                                    Reduce Output Operator [RS_38]
+                                      PartitionCols:_col0
+                                      Group By Operator [GBY_37] (rows=13666666 width=1014)
+                                        Output:["_col0","_col1"],aggregations:["count(_col1)"],keys:_col0
+                                        Group By Operator [GBY_32] (rows=3666666 width=1014)
+                                          Output:["_col0","_col1"],aggregations:["count(VALUE._col0)"],keys:KEY._col0
+                                        <-Reducer 14 [SIMPLE_EDGE]
+                                          SHUFFLE [RS_31]
+                                            PartitionCols:_col0
+                                            Group By Operator [GBY_30] (rows=7333333 width=1014)
+                                              Output:["_col0","_col1"],aggregations:["count()"],keys:_col0
+                                              Select Operator [SEL_28] (rows=7333333 width=1014)
+                                                Output:["_col0"]
+                                                Filter Operator [FIL_27] (rows=7333333 width=1014)
+                                                  predicate:(_col1 > 10)
+                                                  Group By Operator [GBY_26] (rows=22000000 width=1014)
+                                                    Output:["_col0","_col1"],aggregations:["count(VALUE._col0)"],keys:KEY._col0
+                                                  <-Reducer 13 [SIMPLE_EDGE]
+                                                    SHUFFLE [RS_25]
+                                                      PartitionCols:_col0
+                                                      Group By Operator [GBY_24] (rows=44000000 width=1014)
+                                                        Output:["_col0","_col1"],aggregations:["count()"],keys:_col1
+                                                        Merge Join Operator [MERGEJOIN_87] (rows=44000000 width=1014)
+                                                          Conds:RS_20._col0=RS_21._col0(Inner),Output:["_col1"]
+                                                        <-Map 12 [SIMPLE_EDGE]
+                                                          SHUFFLE [RS_20]
+                                                            PartitionCols:_col0
+                                                            Select Operator [SEL_16] (rows=40000000 width=1014)
+                                                              Output:["_col0","_col1"]
+                                                              Filter Operator [FIL_83] (rows=40000000 width=1014)
+                                                                predicate:(ca_address_sk is not null and substr(substr(ca_zip, 1, 5), 1, 2) is not null)
+                                                                TableScan [TS_14] (rows=40000000 width=1014)
+                                                                  default@customer_address,customer_address,Tbl:COMPLETE,Col:NONE,Output:["ca_address_sk","ca_zip"]
+                                                        <-Map 16 [SIMPLE_EDGE]
+                                                          SHUFFLE [RS_21]
+                                                            PartitionCols:_col0
+                                                            Select Operator [SEL_19] (rows=40000000 width=860)
+                                                              Output:["_col0"]
+                                                              Filter Operator [FIL_84] (rows=40000000 width=860)
+                                                                predicate:((c_preferred_cust_flag = 'Y') and c_current_addr_sk is not null)
+                                                                TableScan [TS_17] (rows=80000000 width=860)
+                                                                  default@customer,customer,Tbl:COMPLETE,Col:NONE,Output:["c_current_addr_sk","c_preferred_cust_flag"]
+                                  <-Reducer 8 [CONTAINS]
+                                    Reduce Output Operator [RS_38]
+                                      PartitionCols:_col0
+                                      Group By Operator [GBY_37] (rows=13666666 width=1014)
+                                        Output:["_col0","_col1"],aggregations:["count(_col1)"],keys:_col0
+                                        Group By Operator [GBY_12] (rows=10000000 width=1014)
+                                          Output:["_col0","_col1"],aggregations:["count(VALUE._col0)"],keys:KEY._col0
+                                        <-Map 7 [SIMPLE_EDGE]
+                                          SHUFFLE [RS_11]
+                                            PartitionCols:_col0
+                                            Group By Operator [GBY_10] (rows=20000000 width=1014)
+                                              Output:["_col0","_col1"],aggregations:["count()"],keys:_col0
+                                              Select Operator [SEL_8] (rows=20000000 width=1014)
+                                                Output:["_col0"]
+                                                Filter Operator [FIL_81] (rows=20000000 width=1014)
+                                                  predicate:((substr(ca_zip, 1, 5)) IN ('89436', '30868', '65085', '22977', '83927', '77557', '58429', '40697', '80614', '10502', '32779', '91137', '61265', '98294', '17921', '18427', '21203', '59362', '87291', '84093', '21505', '17184', '10866', '67898', '25797', '28055', '18377', '80332', '74535', '21757', '29742', '90885', '29898', '17819', '40811', '25990', '47513', '89531', '91068', '10391', '18846', '99223', '82637', '41368', '83658', '86199', '81625', '26696', '89338', '88425', '32200', '81427', '19053', '77471', '36610', '99823', '43276', '41249', '48584', '83550', '82276', '18842', '78890', '14090', '38123', '40936', '34425', '19850', '43286', '80072', '79188', '54191', '11395', '50497', '84861', '90733', '21068', '57666', '37119', '25004', '57835', '70067', '62878', '95806', '19303', '18840', '19124', '29785', '16737', '16022', '49613', '89977', '68310', '60069', '98360', '48649', '39050', '41793', '25002', '27413', '39736', 
 '47208', '16515', '94808', '57648', '15009', '80015', '42961', '63982', '21744', '71853', '81087', '67468', '34175', '64008', '20261', '11201', '51799', '48043', '45645', '61163', '48375', '36447', '57042', '21218', '41100', '89951', '22745', '35851', '83326', '61125', '78298', '80752', '49858', '52940', '96976', '63792', '11376', '53582', '18717', '90226', '50530', '94203', '99447', '27670', '96577', '57856', '56372', '16165', '23427', '54561', '28806', '44439', '22926', '30123', '61451', '92397', '56979', '92309', '70873', '13355', '21801', '46346', '37562', '56458', '28286', '47306', '99555', '69399', '26234', '47546', '49661', '88601', '35943', '39936', '25632', '24611', '44166', '56648', '30379', '59785', '11110', '14329', '93815', '52226', '71381', '13842', '25612', '63294', '14664', '21077', '82626', '18799', '60915', '81020', '56447', '76619', '11433', '13414', '42548', '92713', '70467', '30884', '47484', '16072', '38936', '13036', '88376', '45539', '35901', '19506', '65690'
 , '73957', '71850', '49231', '14276', '20005', '18384', '76615', '11635', '38177', '55607', '41369', '95447', '58581', '58149', '91946', '33790', '76232', '75692', '95464', '22246', '51061', '56692', '53121', '77209', '15482', '10688', '14868', '45907', '73520', '72666', '25734', '17959', '24677', '66446', '94627', '53535', '15560', '41967', '69297', '11929', '59403', '33283', '52232', '57350', '43933', '40921', '36635', '10827', '71286', '19736', '80619', '25251', '95042', '15526', '36496', '55854', '49124', '81980', '35375', '49157', '63512', '28944', '14946', '36503', '54010', '18767', '23969', '43905', '66979', '33113', '21286', '58471', '59080', '13395', '79144', '70373', '67031', '38360', '26705', '50906', '52406', '26066', '73146', '15884', '31897', '30045', '61068', '45550', '92454', '13376', '14354', '19770', '22928', '97790', '50723', '46081', '30202', '14410', '20223', '88500', '67298', '13261', '14172', '81410', '93578', '83583', '46047', '94167', '82564', '21156', '1579
 9', '86709', '37931', '74703', '83103', '23054', '70470', '72008', '49247', '91911', '69998', '20961', '70070', '63197', '54853', '88191', '91830', '49521', '19454', '81450', '89091', '62378', '25683', '61869', '51744', '36580', '85778', '36871', '48121', '28810', '83712', '45486', '67393', '26935', '42393', '20132', '55349', '86057', '21309', '80218', '10094', '11357', '48819', '39734', '40758', '30432', '21204', '29467', '30214', '61024', '55307', '74621', '11622', '68908', '33032', '52868', '99194', '99900', '84936', '69036', '99149', '45013', '32895', '59004', '32322', '14933', '32936', '33562', '72550', '27385', '58049', '58200', '16808', '21360', '32961', '18586', '79307', '15492') and substr(substr(ca_zip, 1, 5), 1, 2) is not null)
+                                                  TableScan [TS_6] (rows=40000000 width=1014)
+                                                    default@customer_address,customer_address,Tbl:COMPLETE,Col:NONE,Output:["ca_zip"]
+                    <-Reducer 2 [SIMPLE_EDGE]
+                      SHUFFLE [RS_52]
+                        PartitionCols:_col1
+                        Merge Join Operator [MERGEJOIN_86] (rows=633595212 width=88)
+                          Conds:RS_49._col0=RS_50._col0(Inner),Output:["_col1","_col2"]
+                        <-Map 1 [SIMPLE_EDGE]
+                          SHUFFLE [RS_49]
+                            PartitionCols:_col0
+                            Select Operator [SEL_2] (rows=575995635 width=88)
+                              Output:["_col0","_col1","_col2"]
+                              Filter Operator [FIL_78] (rows=575995635 width=88)
+                                predicate:(ss_sold_date_sk is not null and ss_store_sk is not null)
+                                TableScan [TS_0] (rows=575995635 width=88)
+                                  default@store_sales,store_sales,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_store_sk","ss_net_profit"]
+                        <-Map 6 [SIMPLE_EDGE]
+                          SHUFFLE [RS_50]
+                            PartitionCols:_col0
+                            Select Operator [SEL_5] (rows=18262 width=1119)
+                              Output:["_col0"]
+                              Filter Operator [FIL_79] (rows=18262 width=1119)
+                                predicate:((d_qoy = 1) and (d_year = 2002) and d_date_sk is not null)
+                                TableScan [TS_3] (rows=73049 width=1119)
+                                  default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_year","d_qoy"]
+

http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/tez/query80.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/query80.q.out b/ql/src/test/results/clientpositive/perf/tez/query80.q.out
new file mode 100644
index 0000000..a64cad7
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/tez/query80.q.out
@@ -0,0 +1,478 @@
+PREHOOK: query: explain
+with ssr as
+ (select  s_store_id as store_id,
+          sum(ss_ext_sales_price) as sales,
+          sum(coalesce(sr_return_amt, 0)) as returns,
+          sum(ss_net_profit - coalesce(sr_net_loss, 0)) as profit
+  from store_sales left outer join store_returns on
+         (ss_item_sk = sr_item_sk and ss_ticket_number = sr_ticket_number),
+     date_dim,
+     store,
+     item,
+     promotion
+ where ss_sold_date_sk = d_date_sk
+       and d_date between cast('1998-08-04' as date) 
+                  and (cast('1998-08-04' as date) +  30 days)
+       and ss_store_sk = s_store_sk
+       and ss_item_sk = i_item_sk
+       and i_current_price > 50
+       and ss_promo_sk = p_promo_sk
+       and p_channel_tv = 'N'
+ group by s_store_id)
+ ,
+ csr as
+ (select  cp_catalog_page_id as catalog_page_id,
+          sum(cs_ext_sales_price) as sales,
+          sum(coalesce(cr_return_amount, 0)) as returns,
+          sum(cs_net_profit - coalesce(cr_net_loss, 0)) as profit
+  from catalog_sales left outer join catalog_returns on
+         (cs_item_sk = cr_item_sk and cs_order_number = cr_order_number),
+     date_dim,
+     catalog_page,
+     item,
+     promotion
+ where cs_sold_date_sk = d_date_sk
+       and d_date between cast('1998-08-04' as date)
+                  and (cast('1998-08-04' as date) +  30 days)
+        and cs_catalog_page_sk = cp_catalog_page_sk
+       and cs_item_sk = i_item_sk
+       and i_current_price > 50
+       and cs_promo_sk = p_promo_sk
+       and p_channel_tv = 'N'
+group by cp_catalog_page_id)
+ ,
+ wsr as
+ (select  web_site_id,
+          sum(ws_ext_sales_price) as sales,
+          sum(coalesce(wr_return_amt, 0)) as returns,
+          sum(ws_net_profit - coalesce(wr_net_loss, 0)) as profit
+  from web_sales left outer join web_returns on
+         (ws_item_sk = wr_item_sk and ws_order_number = wr_order_number),
+     date_dim,
+     web_site,
+     item,
+     promotion
+ where ws_sold_date_sk = d_date_sk
+       and d_date between cast('1998-08-04' as date)
+                  and (cast('1998-08-04' as date) +  30 days)
+        and ws_web_site_sk = web_site_sk
+       and ws_item_sk = i_item_sk
+       and i_current_price > 50
+       and ws_promo_sk = p_promo_sk
+       and p_channel_tv = 'N'
+group by web_site_id)
+  select  channel
+        , id
+        , sum(sales) as sales
+        , sum(returns) as returns
+        , sum(profit) as profit
+ from 
+ (select 'store channel' as channel
+        , 'store' || store_id as id
+        , sales
+        , returns
+        , profit
+ from   ssr
+ union all
+ select 'catalog channel' as channel
+        , 'catalog_page' || catalog_page_id as id
+        , sales
+        , returns
+        , profit
+ from  csr
+ union all
+ select 'web channel' as channel
+        , 'web_site' || web_site_id as id
+        , sales
+        , returns
+        , profit
+ from   wsr
+ ) x
+ group by rollup (channel, id)
+ order by channel
+         ,id
+ limit 100
+PREHOOK: type: QUERY
+POSTHOOK: query: explain
+with ssr as
+ (select  s_store_id as store_id,
+          sum(ss_ext_sales_price) as sales,
+          sum(coalesce(sr_return_amt, 0)) as returns,
+          sum(ss_net_profit - coalesce(sr_net_loss, 0)) as profit
+  from store_sales left outer join store_returns on
+         (ss_item_sk = sr_item_sk and ss_ticket_number = sr_ticket_number),
+     date_dim,
+     store,
+     item,
+     promotion
+ where ss_sold_date_sk = d_date_sk
+       and d_date between cast('1998-08-04' as date) 
+                  and (cast('1998-08-04' as date) +  30 days)
+       and ss_store_sk = s_store_sk
+       and ss_item_sk = i_item_sk
+       and i_current_price > 50
+       and ss_promo_sk = p_promo_sk
+       and p_channel_tv = 'N'
+ group by s_store_id)
+ ,
+ csr as
+ (select  cp_catalog_page_id as catalog_page_id,
+          sum(cs_ext_sales_price) as sales,
+          sum(coalesce(cr_return_amount, 0)) as returns,
+          sum(cs_net_profit - coalesce(cr_net_loss, 0)) as profit
+  from catalog_sales left outer join catalog_returns on
+         (cs_item_sk = cr_item_sk and cs_order_number = cr_order_number),
+     date_dim,
+     catalog_page,
+     item,
+     promotion
+ where cs_sold_date_sk = d_date_sk
+       and d_date between cast('1998-08-04' as date)
+                  and (cast('1998-08-04' as date) +  30 days)
+        and cs_catalog_page_sk = cp_catalog_page_sk
+       and cs_item_sk = i_item_sk
+       and i_current_price > 50
+       and cs_promo_sk = p_promo_sk
+       and p_channel_tv = 'N'
+group by cp_catalog_page_id)
+ ,
+ wsr as
+ (select  web_site_id,
+          sum(ws_ext_sales_price) as sales,
+          sum(coalesce(wr_return_amt, 0)) as returns,
+          sum(ws_net_profit - coalesce(wr_net_loss, 0)) as profit
+  from web_sales left outer join web_returns on
+         (ws_item_sk = wr_item_sk and ws_order_number = wr_order_number),
+     date_dim,
+     web_site,
+     item,
+     promotion
+ where ws_sold_date_sk = d_date_sk
+       and d_date between cast('1998-08-04' as date)
+                  and (cast('1998-08-04' as date) +  30 days)
+        and ws_web_site_sk = web_site_sk
+       and ws_item_sk = i_item_sk
+       and i_current_price > 50
+       and ws_promo_sk = p_promo_sk
+       and p_channel_tv = 'N'
+group by web_site_id)
+  select  channel
+        , id
+        , sum(sales) as sales
+        , sum(returns) as returns
+        , sum(profit) as profit
+ from 
+ (select 'store channel' as channel
+        , 'store' || store_id as id
+        , sales
+        , returns
+        , profit
+ from   ssr
+ union all
+ select 'catalog channel' as channel
+        , 'catalog_page' || catalog_page_id as id
+        , sales
+        , returns
+        , profit
+ from  csr
+ union all
+ select 'web channel' as channel
+        , 'web_site' || web_site_id as id
+        , sales
+        , returns
+        , profit
+ from   wsr
+ ) x
+ group by rollup (channel, id)
+ order by channel
+         ,id
+ limit 100
+POSTHOOK: type: QUERY
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Reducer 10 <- Reducer 9 (SIMPLE_EDGE)
+Reducer 13 <- Map 12 (SIMPLE_EDGE), Reducer 27 (SIMPLE_EDGE)
+Reducer 14 <- Map 23 (SIMPLE_EDGE), Reducer 13 (SIMPLE_EDGE)
+Reducer 15 <- Map 24 (SIMPLE_EDGE), Reducer 14 (SIMPLE_EDGE)
+Reducer 16 <- Map 29 (SIMPLE_EDGE), Reducer 15 (SIMPLE_EDGE)
+Reducer 17 <- Reducer 16 (SIMPLE_EDGE), Union 8 (CONTAINS)
+Reducer 18 <- Map 12 (SIMPLE_EDGE), Reducer 31 (SIMPLE_EDGE)
+Reducer 19 <- Map 23 (SIMPLE_EDGE), Reducer 18 (SIMPLE_EDGE)
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 11 (SIMPLE_EDGE)
+Reducer 20 <- Map 24 (SIMPLE_EDGE), Reducer 19 (SIMPLE_EDGE)
+Reducer 21 <- Map 33 (SIMPLE_EDGE), Reducer 20 (SIMPLE_EDGE)
+Reducer 22 <- Reducer 21 (SIMPLE_EDGE), Union 8 (CONTAINS)
+Reducer 27 <- Map 26 (SIMPLE_EDGE), Map 28 (SIMPLE_EDGE)
+Reducer 3 <- Map 12 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
+Reducer 31 <- Map 30 (SIMPLE_EDGE), Map 32 (SIMPLE_EDGE)
+Reducer 4 <- Map 23 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE)
+Reducer 5 <- Map 24 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE)
+Reducer 6 <- Map 25 (SIMPLE_EDGE), Reducer 5 (SIMPLE_EDGE)
+Reducer 7 <- Reducer 6 (SIMPLE_EDGE), Union 8 (CONTAINS)
+Reducer 9 <- Union 8 (SIMPLE_EDGE)
+
+Stage-0
+  Fetch Operator
+    limit:100
+    Stage-1
+      Reducer 10
+      File Output Operator [FS_127]
+        Limit [LIM_126] (rows=100 width=108)
+          Number of rows:100
+          Select Operator [SEL_125] (rows=1217531358 width=108)
+            Output:["_col0","_col1","_col2","_col3","_col4"]
+          <-Reducer 9 [SIMPLE_EDGE]
+            SHUFFLE [RS_124]
+              Select Operator [SEL_123] (rows=1217531358 width=108)
+                Output:["_col0","_col1","_col2","_col3","_col4"]
+                Group By Operator [GBY_122] (rows=1217531358 width=108)
+                  Output:["_col0","_col1","_col3","_col4","_col5"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)"],keys:KEY._col0, KEY._col1, KEY._col2
+                <-Union 8 [SIMPLE_EDGE]
+                  <-Reducer 17 [CONTAINS]
+                    Reduce Output Operator [RS_121]
+                      PartitionCols:_col0, _col1, _col2
+                      Group By Operator [GBY_120] (rows=2435062716 width=108)
+                        Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col2)","sum(_col3)","sum(_col4)"],keys:_col0, _col1, 0
+                        Select Operator [SEL_77] (rows=231905279 width=135)
+                          Output:["_col0","_col1","_col2","_col3","_col4"]
+                          Group By Operator [GBY_76] (rows=231905279 width=135)
+                            Output:["_col0","_col1","_col2","_col3"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)"],keys:KEY._col0
+                          <-Reducer 16 [SIMPLE_EDGE]
+                            SHUFFLE [RS_75]
+                              PartitionCols:_col0
+                              Group By Operator [GBY_74] (rows=463810558 width=135)
+                                Output:["_col0","_col1","_col2","_col3"],aggregations:["sum(_col1)","sum(_col2)","sum(_col3)"],keys:_col0
+                                Select Operator [SEL_72] (rows=463810558 width=135)
+                                  Output:["_col0","_col1","_col2","_col3"]
+                                  Merge Join Operator [MERGEJOIN_212] (rows=463810558 width=135)
+                                    Conds:RS_69._col1=RS_70._col0(Inner),Output:["_col5","_col6","_col9","_col10","_col18"]
+                                  <-Map 29 [SIMPLE_EDGE]
+                                    SHUFFLE [RS_70]
+                                      PartitionCols:_col0
+                                      Select Operator [SEL_56] (rows=46000 width=460)
+                                        Output:["_col0","_col1"]
+                                        Filter Operator [FIL_196] (rows=46000 width=460)
+                                          predicate:cp_catalog_page_sk is not null
+                                          TableScan [TS_54] (rows=46000 width=460)
+                                            default@catalog_page,catalog_page,Tbl:COMPLETE,Col:NONE,Output:["cp_catalog_page_sk","cp_catalog_page_id"]
+                                  <-Reducer 15 [SIMPLE_EDGE]
+                                    SHUFFLE [RS_69]
+                                      PartitionCols:_col1
+                                      Merge Join Operator [MERGEJOIN_211] (rows=421645953 width=135)
+                                        Conds:RS_66._col3=RS_67._col0(Inner),Output:["_col1","_col5","_col6","_col9","_col10"]
+                                      <-Map 24 [SIMPLE_EDGE]
+                                        SHUFFLE [RS_67]
+                                          PartitionCols:_col0
+                                          Select Operator [SEL_14] (rows=1150 width=1179)
+                                            Output:["_col0"]
+                                            Filter Operator [FIL_189] (rows=1150 width=1179)
+                                              predicate:((p_channel_tv = 'N') and p_promo_sk is not null)
+                                              TableScan [TS_12] (rows=2300 width=1179)
+                                                default@promotion,promotion,Tbl:COMPLETE,Col:NONE,Output:["p_promo_sk","p_channel_tv"]
+                                      <-Reducer 14 [SIMPLE_EDGE]
+                                        SHUFFLE [RS_66]
+                                          PartitionCols:_col3
+                                          Merge Join Operator [MERGEJOIN_210] (rows=383314495 width=135)
+                                            Conds:RS_63._col2=RS_64._col0(Inner),Output:["_col1","_col3","_col5","_col6","_col9","_col10"]
+                                          <-Map 23 [SIMPLE_EDGE]
+                                            SHUFFLE [RS_64]
+                                              PartitionCols:_col0
+                                              Select Operator [SEL_11] (rows=154000 width=1436)
+                                                Output:["_col0"]
+                                                Filter Operator [FIL_188] (rows=154000 width=1436)
+                                                  predicate:((i_current_price > 50) and i_item_sk is not null)
+                                                  TableScan [TS_9] (rows=462000 width=1436)
+                                                    default@item,item,Tbl:COMPLETE,Col:NONE,Output:["i_item_sk","i_current_price"]
+                                          <-Reducer 13 [SIMPLE_EDGE]
+                                            SHUFFLE [RS_63]
+                                              PartitionCols:_col2
+                                              Merge Join Operator [MERGEJOIN_209] (rows=348467716 width=135)
+                                                Conds:RS_60._col0=RS_61._col0(Inner),Output:["_col1","_col2","_col3","_col5","_col6","_col9","_col10"]
+                                              <-Map 12 [SIMPLE_EDGE]
+                                                SHUFFLE [RS_61]
+                                                  PartitionCols:_col0
+                                                  Select Operator [SEL_8] (rows=8116 width=1119)
+                                                    Output:["_col0"]
+                                                    Filter Operator [FIL_187] (rows=8116 width=1119)
+                                                      predicate:(CAST( d_date AS TIMESTAMP) BETWEEN 1998-08-04 00:00:00.0 AND 1998-09-03 00:00:00.0 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_date"]
+                                              <-Reducer 27 [SIMPLE_EDGE]
+                                                SHUFFLE [RS_60]
+                                                  PartitionCols:_col0
+                                                  Merge Join Operator [MERGEJOIN_208] (rows=316788826 width=135)
+                                                    Conds:RS_57._col2, _col4=RS_58._col0, _col1(Left Outer),Output:["_col0","_col1","_col2","_col3","_col5","_col6","_col9","_col10"]
+                                                  <-Map 26 [SIMPLE_EDGE]
+                                                    SHUFFLE [RS_57]
+                                                      PartitionCols:_col2, _col4
+                                                      Select Operator [SEL_41] (rows=287989836 width=135)
+                                                        Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
+                                                        Filter Operator [FIL_191] (rows=287989836 width=135)
+                                                          predicate:(cs_catalog_page_sk is not null and cs_item_sk is not null and cs_promo_sk is not null and cs_sold_date_sk is not null)
+                                                          TableScan [TS_39] (rows=287989836 width=135)
+                                                            default@catalog_sales,catalog_sales,Tbl:COMPLETE,Col:NONE,Output:["cs_sold_date_sk","cs_catalog_page_sk","cs_item_sk","cs_promo_sk","cs_order_number","cs_ext_sales_price","cs_net_profit"]
+                                                  <-Map 28 [SIMPLE_EDGE]
+                                                    SHUFFLE [RS_58]
+                                                      PartitionCols:_col0, _col1
+                                                      Select Operator [SEL_44] (rows=28798881 width=106)
+                                                        Output:["_col0","_col1","_col2","_col3"]
+                                                        Filter Operator [FIL_192] (rows=28798881 width=106)
+                                                          predicate:cr_item_sk is not null
+                                                          TableScan [TS_42] (rows=28798881 width=106)
+                                                            default@catalog_returns,catalog_returns,Tbl:COMPLETE,Col:NONE,Output:["cr_item_sk","cr_order_number","cr_return_amount","cr_net_loss"]
+                  <-Reducer 22 [CONTAINS]
+                    Reduce Output Operator [RS_121]
+                      PartitionCols:_col0, _col1, _col2
+                      Group By Operator [GBY_120] (rows=2435062716 width=108)
+                        Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col2)","sum(_col3)","sum(_col4)"],keys:_col0, _col1, 0
+                        Select Operator [SEL_117] (rows=115958879 width=135)
+                          Output:["_col0","_col1","_col2","_col3","_col4"]
+                          Group By Operator [GBY_116] (rows=115958879 width=135)
+                            Output:["_col0","_col1","_col2","_col3"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)"],keys:KEY._col0
+                          <-Reducer 21 [SIMPLE_EDGE]
+                            SHUFFLE [RS_115]
+                              PartitionCols:_col0
+                              Group By Operator [GBY_114] (rows=231917759 width=135)
+                                Output:["_col0","_col1","_col2","_col3"],aggregations:["sum(_col1)","sum(_col2)","sum(_col3)"],keys:_col0
+                                Select Operator [SEL_112] (rows=231917759 width=135)
+                                  Output:["_col0","_col1","_col2","_col3"]
+                                  Merge Join Operator [MERGEJOIN_217] (rows=231917759 width=135)
+                                    Conds:RS_109._col2=RS_110._col0(Inner),Output:["_col5","_col6","_col9","_col10","_col18"]
+                                  <-Map 33 [SIMPLE_EDGE]
+                                    SHUFFLE [RS_110]
+                                      PartitionCols:_col0
+                                      Select Operator [SEL_96] (rows=84 width=1850)
+                                        Output:["_col0","_col1"]
+                                        Filter Operator [FIL_202] (rows=84 width=1850)
+                                          predicate:web_site_sk is not null
+                                          TableScan [TS_94] (rows=84 width=1850)
+                                            default@web_site,web_site,Tbl:COMPLETE,Col:NONE,Output:["web_site_sk","web_site_id"]
+                                  <-Reducer 20 [SIMPLE_EDGE]
+                                    SHUFFLE [RS_109]
+                                      PartitionCols:_col2
+                                      Merge Join Operator [MERGEJOIN_216] (rows=210834322 width=135)
+                                        Conds:RS_106._col3=RS_107._col0(Inner),Output:["_col2","_col5","_col6","_col9","_col10"]
+                                      <-Map 24 [SIMPLE_EDGE]
+                                        SHUFFLE [RS_107]
+                                          PartitionCols:_col0
+                                           Please refer to the previous Select Operator [SEL_14]
+                                      <-Reducer 19 [SIMPLE_EDGE]
+                                        SHUFFLE [RS_106]
+                                          PartitionCols:_col3
+                                          Merge Join Operator [MERGEJOIN_215] (rows=191667562 width=135)
+                                            Conds:RS_103._col1=RS_104._col0(Inner),Output:["_col2","_col3","_col5","_col6","_col9","_col10"]
+                                          <-Map 23 [SIMPLE_EDGE]
+                                            SHUFFLE [RS_104]
+                                              PartitionCols:_col0
+                                               Please refer to the previous Select Operator [SEL_11]
+                                          <-Reducer 18 [SIMPLE_EDGE]
+                                            SHUFFLE [RS_103]
+                                              PartitionCols:_col1
+                                              Merge Join Operator [MERGEJOIN_214] (rows=174243235 width=135)
+                                                Conds:RS_100._col0=RS_101._col0(Inner),Output:["_col1","_col2","_col3","_col5","_col6","_col9","_col10"]
+                                              <-Map 12 [SIMPLE_EDGE]
+                                                SHUFFLE [RS_101]
+                                                  PartitionCols:_col0
+                                                   Please refer to the previous Select Operator [SEL_8]
+                                              <-Reducer 31 [SIMPLE_EDGE]
+                                                SHUFFLE [RS_100]
+                                                  PartitionCols:_col0
+                                                  Merge Join Operator [MERGEJOIN_213] (rows=158402938 width=135)
+                                                    Conds:RS_97._col1, _col4=RS_98._col0, _col1(Left Outer),Output:["_col0","_col1","_col2","_col3","_col5","_col6","_col9","_col10"]
+                                                  <-Map 30 [SIMPLE_EDGE]
+                                                    SHUFFLE [RS_97]
+                                                      PartitionCols:_col1, _col4
+                                                      Select Operator [SEL_81] (rows=144002668 width=135)
+                                                        Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
+                                                        Filter Operator [FIL_197] (rows=144002668 width=135)
+                                                          predicate:(ws_item_sk is not null and ws_promo_sk is not null and ws_sold_date_sk is not null and ws_web_site_sk is not null)
+                                                          TableScan [TS_79] (rows=144002668 width=135)
+                                                            default@web_sales,web_sales,Tbl:COMPLETE,Col:NONE,Output:["ws_sold_date_sk","ws_item_sk","ws_web_site_sk","ws_promo_sk","ws_order_number","ws_ext_sales_price","ws_net_profit"]
+                                                  <-Map 32 [SIMPLE_EDGE]
+                                                    SHUFFLE [RS_98]
+                                                      PartitionCols:_col0, _col1
+                                                      Select Operator [SEL_84] (rows=14398467 width=92)
+                                                        Output:["_col0","_col1","_col2","_col3"]
+                                                        Filter Operator [FIL_198] (rows=14398467 width=92)
+                                                          predicate:wr_item_sk is not null
+                                                          TableScan [TS_82] (rows=14398467 width=92)
+                                                            default@web_returns,web_returns,Tbl:COMPLETE,Col:NONE,Output:["wr_item_sk","wr_order_number","wr_return_amt","wr_net_loss"]
+                  <-Reducer 7 [CONTAINS]
+                    Reduce Output Operator [RS_121]
+                      PartitionCols:_col0, _col1, _col2
+                      Group By Operator [GBY_120] (rows=2435062716 width=108)
+                        Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col2)","sum(_col3)","sum(_col4)"],keys:_col0, _col1, 0
+                        Select Operator [SEL_38] (rows=463823414 width=88)
+                          Output:["_col0","_col1","_col2","_col3","_col4"]
+                          Group By Operator [GBY_37] (rows=463823414 width=88)
+                            Output:["_col0","_col1","_col2","_col3"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)"],keys:KEY._col0
+                          <-Reducer 6 [SIMPLE_EDGE]
+                            SHUFFLE [RS_36]
+                              PartitionCols:_col0
+                              Group By Operator [GBY_35] (rows=927646829 width=88)
+                                Output:["_col0","_col1","_col2","_col3"],aggregations:["sum(_col1)","sum(_col2)","sum(_col3)"],keys:_col0
+                                Select Operator [SEL_33] (rows=927646829 width=88)
+                                  Output:["_col0","_col1","_col2","_col3"]
+                                  Merge Join Operator [MERGEJOIN_207] (rows=927646829 width=88)
+                                    Conds:RS_30._col2=RS_31._col0(Inner),Output:["_col5","_col6","_col9","_col10","_col18"]
+                                  <-Map 25 [SIMPLE_EDGE]
+                                    SHUFFLE [RS_31]
+                                      PartitionCols:_col0
+                                      Select Operator [SEL_17] (rows=1704 width=1910)
+                                        Output:["_col0","_col1"]
+                                        Filter Operator [FIL_190] (rows=1704 width=1910)
+                                          predicate:s_store_sk is not null
+                                          TableScan [TS_15] (rows=1704 width=1910)
+                                            default@store,store,Tbl:COMPLETE,Col:NONE,Output:["s_store_sk","s_store_id"]
+                                  <-Reducer 5 [SIMPLE_EDGE]
+                                    SHUFFLE [RS_30]
+                                      PartitionCols:_col2
+                                      Merge Join Operator [MERGEJOIN_206] (rows=843315281 width=88)
+                                        Conds:RS_27._col3=RS_28._col0(Inner),Output:["_col2","_col5","_col6","_col9","_col10"]
+                                      <-Map 24 [SIMPLE_EDGE]
+                                        SHUFFLE [RS_28]
+                                          PartitionCols:_col0
+                                           Please refer to the previous Select Operator [SEL_14]
+                                      <-Reducer 4 [SIMPLE_EDGE]
+                                        SHUFFLE [RS_27]
+                                          PartitionCols:_col3
+                                          Merge Join Operator [MERGEJOIN_205] (rows=766650239 width=88)
+                                            Conds:RS_24._col1=RS_25._col0(Inner),Output:["_col2","_col3","_col5","_col6","_col9","_col10"]
+                                          <-Map 23 [SIMPLE_EDGE]
+                                            SHUFFLE [RS_25]
+                                              PartitionCols:_col0
+                                               Please refer to the previous Select Operator [SEL_11]
+                                          <-Reducer 3 [SIMPLE_EDGE]
+                                            SHUFFLE [RS_24]
+                                              PartitionCols:_col1
+                                              Merge Join Operator [MERGEJOIN_204] (rows=696954748 width=88)
+                                                Conds:RS_21._col0=RS_22._col0(Inner),Output:["_col1","_col2","_col3","_col5","_col6","_col9","_col10"]
+                                              <-Map 12 [SIMPLE_EDGE]
+                                                SHUFFLE [RS_22]
+                                                  PartitionCols:_col0
+                                                   Please refer to the previous Select Operator [SEL_8]
+                                              <-Reducer 2 [SIMPLE_EDGE]
+                                                SHUFFLE [RS_21]
+                                                  PartitionCols:_col0
+                                                  Merge Join Operator [MERGEJOIN_203] (rows=633595212 width=88)
+                                                    Conds:RS_18._col1, _col4=RS_19._col0, _col1(Left Outer),Output:["_col0","_col1","_col2","_col3","_col5","_col6","_col9","_col10"]
+                                                  <-Map 1 [SIMPLE_EDGE]
+                                                    SHUFFLE [RS_18]
+                                                      PartitionCols:_col1, _col4
+                                                      Select Operator [SEL_2] (rows=575995635 width=88)
+                                                        Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
+                                                        Filter Operator [FIL_185] (rows=575995635 width=88)
+                                                          predicate:(ss_item_sk is not null and ss_promo_sk is not null and ss_sold_date_sk is not null and ss_store_sk is not null)
+                                                          TableScan [TS_0] (rows=575995635 width=88)
+                                                            default@store_sales,store_sales,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_item_sk","ss_store_sk","ss_promo_sk","ss_ticket_number","ss_ext_sales_price","ss_net_profit"]
+                                                  <-Map 11 [SIMPLE_EDGE]
+                                                    SHUFFLE [RS_19]
+                                                      PartitionCols:_col0, _col1
+                                                      Select Operator [SEL_5] (rows=57591150 width=77)
+                                                        Output:["_col0","_col1","_col2","_col3"]
+                                                        Filter Operator [FIL_186] (rows=57591150 width=77)
+                                                          predicate:sr_item_sk is not null
+                                                          TableScan [TS_3] (rows=57591150 width=77)
+                                                            default@store_returns,store_returns,Tbl:COMPLETE,Col:NONE,Output:["sr_item_sk","sr_ticket_number","sr_return_amt","sr_net_loss"]
+


Mime
View raw message