You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by ha...@apache.org on 2015/12/15 23:15:52 UTC
[25/27] hive git commit: HIVE-11107 : Support for Performance
regression test suite with TPCDS (Hari Subramaniyan,
reviewed by Ashutosh Chauhan)
http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/queries/clientpositive/perf/query90.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query90.q b/ql/src/test/queries/clientpositive/perf/query90.q
new file mode 100644
index 0000000..515da06
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/perf/query90.q
@@ -0,0 +1,2 @@
+set hive.mapred.mode=nonstrict;
+explain select cast(amc as decimal(15,4))/cast(pmc as decimal(15,4)) am_pm_ratio from ( select count(*) amc from web_sales, household_demographics , time_dim, web_page where ws_sold_time_sk = time_dim.t_time_sk and ws_ship_hdemo_sk = household_demographics.hd_demo_sk and ws_web_page_sk = web_page.wp_web_page_sk and time_dim.t_hour between 6 and 6+1 and household_demographics.hd_dep_count = 8 and web_page.wp_char_count between 5000 and 5200) at, ( select count(*) pmc from web_sales, household_demographics , time_dim, web_page where ws_sold_time_sk = time_dim.t_time_sk and ws_ship_hdemo_sk = household_demographics.hd_demo_sk and ws_web_page_sk = web_page.wp_web_page_sk and time_dim.t_hour between 14 and 14+1 and household_demographics.hd_dep_count = 8 and web_page.wp_char_count between 5000 and 5200) pt order by am_pm_ratio limit 100;
http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/queries/clientpositive/perf/query91.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query91.q b/ql/src/test/queries/clientpositive/perf/query91.q
new file mode 100644
index 0000000..9458c0c
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/perf/query91.q
@@ -0,0 +1,2 @@
+set hive.mapred.mode=nonstrict;
+explain select cc_call_center_id Call_Center, cc_name Call_Center_Name, cc_manager Manager, sum(cr_net_loss) Returns_Loss from call_center, catalog_returns, date_dim, customer, customer_address, customer_demographics, household_demographics where catalog_returns.cr_call_center_sk = call_center.cc_call_center_sk and catalog_returns.cr_returned_date_sk = date_dim.d_date_sk and catalog_returns.cr_returning_customer_sk= customer.c_customer_sk and customer_demographics.cd_demo_sk = customer.c_current_cdemo_sk and household_demographics.hd_demo_sk = customer.c_current_hdemo_sk and customer_address.ca_address_sk = customer.c_current_addr_sk and d_year = 1999 and d_moy = 11 and ( (cd_marital_status = 'M' and cd_education_status = 'Unknown') or(cd_marital_status = 'W' and cd_education_status = 'Advanced Degree')) and hd_buy_potential like '0-500%' and ca_gmt_offset = -7 group by cc_call_center_id,cc_name,cc_manager,cd_marital_status,cd_education_status order by Returns_Loss desc;
http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/queries/clientpositive/perf/query92.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query92.q b/ql/src/test/queries/clientpositive/perf/query92.q
new file mode 100644
index 0000000..625e99f
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/perf/query92.q
@@ -0,0 +1 @@
+explain SELECT sum(case when ssci.customer_sk is not null and csci.customer_sk is null then 1 else 0 end) as store_only, sum(case when ssci.customer_sk is null and csci.customer_sk is not null then 1 else 0 end) as catalog_only, sum(case when ssci.customer_sk is not null and csci.customer_sk is not null then 1 else 0 end) as store_and_catalog FROM (SELECT ss.ss_customer_sk as customer_sk, ss.ss_item_sk as item_sk FROM store_sales ss JOIN date_dim d1 ON (ss.ss_sold_date_sk = d1.d_date_sk) WHERE d1.d_month_seq >= 1206 and d1.d_month_seq <= 1217 GROUP BY ss.ss_customer_sk, ss.ss_item_sk) ssci FULL OUTER JOIN (SELECT cs.cs_bill_customer_sk as customer_sk, cs.cs_item_sk as item_sk FROM catalog_sales cs JOIN date_dim d2 ON (cs.cs_sold_date_sk = d2.d_date_sk) WHERE d2.d_month_seq >= 1206 and d2.d_month_seq <= 1217 GROUP BY cs.cs_bill_customer_sk, cs.cs_item_sk) csci ON (ssci.customer_sk=csci.customer_sk and ssci.item_sk = csci.item_sk);
http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/queries/clientpositive/perf/query93.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query93.q b/ql/src/test/queries/clientpositive/perf/query93.q
new file mode 100644
index 0000000..b60b041
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/perf/query93.q
@@ -0,0 +1 @@
+explain select ss_customer_sk ,sum(act_sales) sumsales from (select ss_item_sk ,ss_ticket_number ,ss_customer_sk ,case when sr_return_quantity is not null then (ss_quantity-sr_return_quantity)*ss_sales_price else (ss_quantity*ss_sales_price) end act_sales from store_sales left outer join store_returns on (store_returns.sr_item_sk = store_sales.ss_item_sk and store_returns.sr_ticket_number = store_sales.ss_ticket_number) ,reason where store_returns.sr_reason_sk = reason.r_reason_sk and r_reason_desc = 'Did not like the warranty') t group by ss_customer_sk order by sumsales, ss_customer_sk limit 100;
http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/queries/clientpositive/perf/query94.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query94.q b/ql/src/test/queries/clientpositive/perf/query94.q
new file mode 100644
index 0000000..f9f4bc1
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/perf/query94.q
@@ -0,0 +1 @@
+explain SELECT count(distinct ws_order_number) as order_count, sum(ws_ext_ship_cost) as total_shipping_cost, sum(ws_net_profit) as total_net_profit FROM web_sales ws1 JOIN customer_address ca ON (ws1.ws_ship_addr_sk = ca.ca_address_sk) JOIN web_site s ON (ws1.ws_web_site_sk = s.web_site_sk) JOIN date_dim d ON (ws1.ws_ship_date_sk = d.d_date_sk) LEFT SEMI JOIN (SELECT ws2.ws_order_number as ws_order_number FROM web_sales ws2 JOIN web_sales ws3 ON (ws2.ws_order_number = ws3.ws_order_number) WHERE ws2.ws_warehouse_sk <> ws3.ws_warehouse_sk ) ws_wh1 ON (ws1.ws_order_number = ws_wh1.ws_order_number) LEFT OUTER JOIN web_returns wr1 ON (ws1.ws_order_number = wr1.wr_order_number) WHERE d.d_date between '1999-05-01' and '1999-07-01' and ca.ca_state = 'TX' and s.web_company_name = 'pri' and wr1.wr_order_number is null limit 100;
http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/queries/clientpositive/perf/query95.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query95.q b/ql/src/test/queries/clientpositive/perf/query95.q
new file mode 100644
index 0000000..fbd2d47
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/perf/query95.q
@@ -0,0 +1 @@
+explain SELECT count(distinct ws1.ws_order_number) as order_count, sum(ws1.ws_ext_ship_cost) as total_shipping_cost, sum(ws1.ws_net_profit) as total_net_profit FROM web_sales ws1 JOIN customer_address ca ON (ws1.ws_ship_addr_sk = ca.ca_address_sk) JOIN web_site s ON (ws1.ws_web_site_sk = s.web_site_sk) JOIN date_dim d ON (ws1.ws_ship_date_sk = d.d_date_sk) LEFT SEMI JOIN (SELECT ws2.ws_order_number as ws_order_number FROM web_sales ws2 JOIN web_sales ws3 ON (ws2.ws_order_number = ws3.ws_order_number) WHERE ws2.ws_warehouse_sk <> ws3.ws_warehouse_sk ) ws_wh1 ON (ws1.ws_order_number = ws_wh1.ws_order_number) LEFT SEMI JOIN (SELECT wr_order_number FROM web_returns wr JOIN (SELECT ws4.ws_order_number as ws_order_number FROM web_sales ws4 JOIN web_sales ws5 ON (ws4.ws_order_number = ws5.ws_order_number) WHERE ws4.ws_warehouse_sk <> ws5.ws_warehouse_sk ) ws_wh2 ON (wr.wr_order_number = ws_wh2.ws_order_number)) tmp1 ON (ws1.ws_order_number = tmp1.wr_order_number) WHERE d.d_date between '20
02-05-01' and '2002-06-30' and ca.ca_state = 'GA' and s.web_company_name = 'pri';
http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/queries/clientpositive/perf/query96.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query96.q b/ql/src/test/queries/clientpositive/perf/query96.q
new file mode 100644
index 0000000..29265ed
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/perf/query96.q
@@ -0,0 +1 @@
+explain select count(*) as c from store_sales ,household_demographics ,time_dim, store where store_sales.ss_sold_time_sk = time_dim.t_time_sk and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk and store_sales.ss_store_sk = store.s_store_sk and time_dim.t_hour = 8 and time_dim.t_minute >= 30 and household_demographics.hd_dep_count = 5 and store.s_store_name = 'ese' order by c limit 100;
http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/queries/clientpositive/perf/query97.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query97.q b/ql/src/test/queries/clientpositive/perf/query97.q
new file mode 100644
index 0000000..4995309
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/perf/query97.q
@@ -0,0 +1 @@
+explain select sum(case when ssci.customer_sk is not null and csci.customer_sk is null then 1 else 0 end) store_only ,sum(case when ssci.customer_sk is null and csci.customer_sk is not null then 1 else 0 end) catalog_only ,sum(case when ssci.customer_sk is not null and csci.customer_sk is not null then 1 else 0 end) store_and_catalog from ( select ss_customer_sk customer_sk ,ss_item_sk item_sk from store_sales JOIN date_dim ON store_sales.ss_sold_date_sk = date_dim.d_date_sk where d_month_seq between 1193 and 1193 + 11 group by ss_customer_sk ,ss_item_sk) ssci full outer join ( select cs_bill_customer_sk customer_sk ,cs_item_sk item_sk from catalog_sales JOIN date_dim ON catalog_sales.cs_sold_date_sk = date_dim.d_date_sk where d_month_seq between 1193 and 1193 + 11 group by cs_bill_customer_sk ,cs_item_sk) csci on (ssci.customer_sk=csci.customer_sk and ssci.item_sk = csci.item_sk) limit 100;
http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/queries/clientpositive/perf/query98.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/perf/query98.q b/ql/src/test/queries/clientpositive/perf/query98.q
new file mode 100644
index 0000000..930b7ef
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/perf/query98.q
@@ -0,0 +1,2 @@
+set hive.mapred.mode=nonstrict;
+explain select i_item_desc ,i_category ,i_class ,i_current_price ,i_item_id ,sum(ss_ext_sales_price) as itemrevenue ,sum(ss_ext_sales_price)*100/sum(sum(ss_ext_sales_price)) over (partition by i_class) as revenueratio from store_sales ,item ,date_dim where store_sales.ss_item_sk = item.i_item_sk and i_category in ('Jewelry', 'Sports', 'Books') and store_sales.ss_sold_date_sk = date_dim.d_date_sk and d_date between cast('2001-01-12' as date) and (cast('2001-02-11' as date)) 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;
http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/results/clientpositive/perf/query12.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query12.q.out b/ql/src/test/results/clientpositive/perf/query12.q.out
new file mode 100644
index 0000000..648d64d
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/query12.q.out
@@ -0,0 +1,129 @@
+PREHOOK: query: explain select i_item_desc ,i_category ,i_class ,i_current_price ,i_item_id ,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 web_sales.ws_item_sk = item.i_item_sk and item.i_category in ('Jewelry', 'Sports', 'Books') and web_sales.ws_sold_date_sk = date_dim.d_date_sk and date_dim.d_date between '2001-01-12' and '2001-02-11' 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 ,i_item_id ,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 web_sales.ws_item_sk = item.i_item_sk and item.i_category in ('Jewelry', 'Sports', 'Books') and web_sales.ws_sold_date_sk = date_dim.d_date_sk and date_dim.d_date between '2001-01-12' and '2001-02-11' 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 not 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:100
+ Stage-1
+ Reducer 6
+ File Output Operator [FS_26]
+ compressed:false
+ Statistics:Num rows: 100 Data size: 143600 Basic stats: COMPLETE Column stats: NONE
+ table:{"input format:":"org.apache.hadoop.mapred.TextInputFormat","output format:":"org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat","serde:":"org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe"}
+ Limit [LIM_25]
+ Number of rows:100
+ Statistics:Num rows: 100 Data size: 143600 Basic stats: COMPLETE Column stats: NONE
+ Select Operator [SEL_24]
+ | outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
+ | Statistics:Num rows: 34938 Data size: 50180683 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 5 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_23]
+ key expressions:_col1 (type: string), _col2 (type: string), _col4 (type: string), _col0 (type: string), _col6 (type: decimal(38,23))
+ sort order:+++++
+ Statistics:Num rows: 34938 Data size: 50180683 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col3 (type: decimal(7,2)), _col5 (type: decimal(17,2))
+ Select Operator [SEL_21]
+ outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"]
+ Statistics:Num rows: 34938 Data size: 50180683 Basic stats: COMPLETE Column stats: NONE
+ PTF Operator [PTF_20]
+ Function definitions:[{"Input definition":{"type:":"WINDOWING"}},{"name:":"windowingtablefunction","order by:":"_col3","partition by:":"_col3"}]
+ Statistics:Num rows: 34938 Data size: 50180683 Basic stats: COMPLETE Column stats: NONE
+ Select Operator [SEL_19]
+ | outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ | Statistics:Num rows: 34938 Data size: 50180683 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 4 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_18]
+ key expressions:_col3 (type: string)
+ Map-reduce partition columns:_col3 (type: string)
+ sort order:+
+ Statistics:Num rows: 34938 Data size: 50180683 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col0 (type: string), _col1 (type: string), _col2 (type: string), _col4 (type: decimal(7,2)), _col5 (type: decimal(17,2))
+ Group By Operator [GBY_17]
+ | aggregations:["sum(VALUE._col0)"]
+ | keys:KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: string), KEY._col3 (type: string), KEY._col4 (type: decimal(7,2))
+ | outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ | Statistics:Num rows: 34938 Data size: 50180683 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 3 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_16]
+ key expressions:_col0 (type: string), _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: decimal(7,2))
+ Map-reduce partition columns:_col0 (type: string), _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: decimal(7,2))
+ sort order:+++++
+ Statistics:Num rows: 69877 Data size: 100362804 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col5 (type: decimal(17,2))
+ Group By Operator [GBY_15]
+ aggregations:["sum(_col23)"]
+ keys:_col38 (type: string), _col41 (type: string), _col49 (type: string), _col47 (type: string), _col42 (type: decimal(7,2))
+ outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ Statistics:Num rows: 69877 Data size: 100362804 Basic stats: COMPLETE Column stats: NONE
+ Select Operator [SEL_14]
+ outputColumnNames:["_col38","_col41","_col49","_col47","_col42","_col23"]
+ Statistics:Num rows: 69877 Data size: 100362804 Basic stats: COMPLETE Column stats: NONE
+ Filter Operator [FIL_32]
+ predicate:((_col3 = _col37) and (_col0 = _col62)) (type: boolean)
+ Statistics:Num rows: 69877 Data size: 100362804 Basic stats: COMPLETE Column stats: NONE
+ Merge Join Operator [MERGEJOIN_37]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col0 (type: int)","1":"d_date_sk (type: int)"}
+ | outputColumnNames:["_col0","_col3","_col23","_col37","_col38","_col41","_col42","_col47","_col49","_col62"]
+ | Statistics:Num rows: 279510 Data size: 401454092 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 8 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_11]
+ | key expressions:d_date_sk (type: int)
+ | Map-reduce partition columns:d_date_sk (type: int)
+ | sort order:+
+ | Statistics:Num rows: 36524 Data size: 40870356 Basic stats: COMPLETE Column stats: NONE
+ | Filter Operator [FIL_35]
+ | predicate:(d_date_sk is not null and d_date BETWEEN '2001-01-12' AND '2001-02-11') (type: boolean)
+ | Statistics:Num rows: 36524 Data size: 40870356 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_2]
+ | alias:date_dim
+ | Statistics:Num rows: 73049 Data size: 81741831 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 2 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_9]
+ key expressions:_col0 (type: int)
+ Map-reduce partition columns:_col0 (type: int)
+ sort order:+
+ Statistics:Num rows: 254100 Data size: 364958258 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col3 (type: int), _col23 (type: decimal(7,2)), _col37 (type: int), _col38 (type: string), _col41 (type: string), _col42 (type: decimal(7,2)), _col47 (type: string), _col49 (type: string)
+ Merge Join Operator [MERGEJOIN_36]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"ws_item_sk (type: int)","1":"i_item_sk (type: int)"}
+ | outputColumnNames:["_col0","_col3","_col23","_col37","_col38","_col41","_col42","_col47","_col49"]
+ | Statistics:Num rows: 254100 Data size: 364958258 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 1 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_4]
+ | key expressions:ws_item_sk (type: int)
+ | Map-reduce partition columns:ws_item_sk (type: int)
+ | sort order:+
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | value expressions:ws_sold_date_sk (type: int), ws_ext_sales_price (type: decimal(7,2))
+ | Filter Operator [FIL_33]
+ | predicate:(ws_item_sk is not null and ws_sold_date_sk is not null) (type: boolean)
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | TableScan [TS_0]
+ | alias:web_sales
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ |<-Map 7 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_6]
+ key expressions:i_item_sk (type: int)
+ Map-reduce partition columns:i_item_sk (type: int)
+ sort order:+
+ Statistics:Num rows: 231000 Data size: 331780228 Basic stats: COMPLETE Column stats: NONE
+ value expressions:i_item_id (type: string), i_item_desc (type: string), i_current_price (type: decimal(7,2)), i_class (type: string), i_category (type: string)
+ Filter Operator [FIL_34]
+ predicate:(i_item_sk is not null and (i_category) IN ('Jewelry', 'Sports', 'Books')) (type: boolean)
+ Statistics:Num rows: 231000 Data size: 331780228 Basic stats: COMPLETE Column stats: NONE
+ TableScan [TS_1]
+ alias:item
+ Statistics:Num rows: 462000 Data size: 663560457 Basic stats: COMPLETE Column stats: NONE
+
http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/results/clientpositive/perf/query13.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query13.q.out b/ql/src/test/results/clientpositive/perf/query13.q.out
new file mode 100644
index 0000000..0e59b0d
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/query13.q.out
@@ -0,0 +1,300 @@
+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 store.s_store_sk = store_sales.ss_store_sk
+AND store_sales.ss_sold_date_sk = date_dim.d_date_sk
+AND date_dim.d_year = 2001
+AND ((
+ store_sales.ss_hdemo_sk=household_demographics.hd_demo_sk
+ AND customer_demographics.cd_demo_sk = store_sales.ss_cdemo_sk
+ AND customer_demographics.cd_marital_status = 'M'
+ AND customer_demographics.cd_education_status = '4 yr Degree'
+ AND store_sales.ss_sales_price BETWEEN 100.00 AND 150.00
+ AND household_demographics.hd_dep_count = 3 )
+ OR (
+ store_sales.ss_hdemo_sk=household_demographics.hd_demo_sk
+ AND customer_demographics.cd_demo_sk = store_sales.ss_cdemo_sk
+ AND customer_demographics.cd_marital_status = 'D'
+ AND customer_demographics.cd_education_status = 'Primary'
+ AND store_sales.ss_sales_price BETWEEN 50.00 AND 100.00
+ AND household_demographics.hd_dep_count = 1 )
+ OR (
+ store_sales.ss_hdemo_sk=household_demographics.hd_demo_sk
+ AND customer_demographics.cd_demo_sk = ss_cdemo_sk
+ AND customer_demographics.cd_marital_status = 'U'
+ AND customer_demographics.cd_education_status = 'Advanced Degree'
+ AND store_sales.ss_sales_price BETWEEN 150.00 AND 200.00
+ AND household_demographics.hd_dep_count = 1 ))
+AND ((
+ store_sales.ss_addr_sk = customer_address.ca_address_sk
+ AND customer_address.ca_country = 'United States'
+ AND customer_address.ca_state IN ('KY',
+ 'GA',
+ 'NM')
+ AND store_sales.ss_net_profit BETWEEN 100 AND 200 )
+ OR (
+ store_sales.ss_addr_sk = customer_address.ca_address_sk
+ AND customer_address.ca_country = 'United States'
+ AND customer_address.ca_state IN ('MT',
+ 'OR',
+ 'IN')
+ AND store_sales.ss_net_profit BETWEEN 150 AND 300 )
+ OR (
+ store_sales.ss_addr_sk = customer_address.ca_address_sk
+ AND customer_address.ca_country = 'United States'
+ AND customer_address.ca_state IN ('WI', 'MO', 'WV')
+ AND store_sales.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 store.s_store_sk = store_sales.ss_store_sk
+AND store_sales.ss_sold_date_sk = date_dim.d_date_sk
+AND date_dim.d_year = 2001
+AND ((
+ store_sales.ss_hdemo_sk=household_demographics.hd_demo_sk
+ AND customer_demographics.cd_demo_sk = store_sales.ss_cdemo_sk
+ AND customer_demographics.cd_marital_status = 'M'
+ AND customer_demographics.cd_education_status = '4 yr Degree'
+ AND store_sales.ss_sales_price BETWEEN 100.00 AND 150.00
+ AND household_demographics.hd_dep_count = 3 )
+ OR (
+ store_sales.ss_hdemo_sk=household_demographics.hd_demo_sk
+ AND customer_demographics.cd_demo_sk = store_sales.ss_cdemo_sk
+ AND customer_demographics.cd_marital_status = 'D'
+ AND customer_demographics.cd_education_status = 'Primary'
+ AND store_sales.ss_sales_price BETWEEN 50.00 AND 100.00
+ AND household_demographics.hd_dep_count = 1 )
+ OR (
+ store_sales.ss_hdemo_sk=household_demographics.hd_demo_sk
+ AND customer_demographics.cd_demo_sk = ss_cdemo_sk
+ AND customer_demographics.cd_marital_status = 'U'
+ AND customer_demographics.cd_education_status = 'Advanced Degree'
+ AND store_sales.ss_sales_price BETWEEN 150.00 AND 200.00
+ AND household_demographics.hd_dep_count = 1 ))
+AND ((
+ store_sales.ss_addr_sk = customer_address.ca_address_sk
+ AND customer_address.ca_country = 'United States'
+ AND customer_address.ca_state IN ('KY',
+ 'GA',
+ 'NM')
+ AND store_sales.ss_net_profit BETWEEN 100 AND 200 )
+ OR (
+ store_sales.ss_addr_sk = customer_address.ca_address_sk
+ AND customer_address.ca_country = 'United States'
+ AND customer_address.ca_state IN ('MT',
+ 'OR',
+ 'IN')
+ AND store_sales.ss_net_profit BETWEEN 150 AND 300 )
+ OR (
+ store_sales.ss_addr_sk = customer_address.ca_address_sk
+ AND customer_address.ca_country = 'United States'
+ AND customer_address.ca_state IN ('WI', 'MO', 'WV')
+ AND store_sales.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 (SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:-1
+ Stage-1
+ Reducer 7
+ File Output Operator [FS_52]
+ compressed:false
+ Statistics:Num rows: 1 Data size: 344 Basic stats: COMPLETE Column stats: NONE
+ table:{"input format:":"org.apache.hadoop.mapred.TextInputFormat","output format:":"org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat","serde:":"org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe"}
+ Group By Operator [GBY_50]
+ | aggregations:["avg(VALUE._col0)","avg(VALUE._col1)","avg(VALUE._col2)","sum(VALUE._col3)"]
+ | outputColumnNames:["_col0","_col1","_col2","_col3"]
+ | Statistics:Num rows: 1 Data size: 344 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 6 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_49]
+ sort order:
+ Statistics:Num rows: 1 Data size: 112 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col0 (type: struct<count:bigint,sum:double,input:int>), _col1 (type: struct<count:bigint,sum:decimal(17,2),input:decimal(7,2)>), _col2 (type: struct<count:bigint,sum:decimal(17,2),input:decimal(7,2)>), _col3 (type: decimal(17,2))
+ Group By Operator [GBY_48]
+ aggregations:["avg(_col5)","avg(_col7)","avg(_col8)","sum(_col8)"]
+ outputColumnNames:["_col0","_col1","_col2","_col3"]
+ Statistics:Num rows: 1 Data size: 112 Basic stats: COMPLETE Column stats: NONE
+ Merge Join Operator [MERGEJOIN_85]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col0 (type: int)","1":"_col0 (type: int)"}
+ | outputColumnNames:["_col5","_col7","_col8"]
+ | Statistics:Num rows: 18150000 Data size: 18420070657 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 12 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_45]
+ | key expressions:_col0 (type: int)
+ | Map-reduce partition columns:_col0 (type: int)
+ | sort order:+
+ | Statistics:Num rows: 36524 Data size: 40870356 Basic stats: COMPLETE Column stats: NONE
+ | Select Operator [SEL_41]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 36524 Data size: 40870356 Basic stats: COMPLETE Column stats: NONE
+ | Filter Operator [FIL_80]
+ | predicate:(d_date_sk is not null and (d_year = 2001)) (type: boolean)
+ | Statistics:Num rows: 36524 Data size: 40870356 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_39]
+ | alias:date_dim
+ | Statistics:Num rows: 73049 Data size: 81741831 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 5 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_43]
+ key expressions:_col0 (type: int)
+ Map-reduce partition columns:_col0 (type: int)
+ sort order:+
+ Statistics:Num rows: 16500000 Data size: 16745518417 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col5 (type: int), _col7 (type: decimal(7,2)), _col8 (type: decimal(7,2))
+ Select Operator [SEL_38]
+ outputColumnNames:["_col0","_col5","_col7","_col8"]
+ Statistics:Num rows: 16500000 Data size: 16745518417 Basic stats: COMPLETE Column stats: NONE
+ Filter Operator [FIL_73]
+ predicate:(((_col17) IN ('KY', 'GA', 'NM') and _col9 BETWEEN 100 AND 200) or ((_col17) IN ('MT', 'OR', 'IN') and _col9 BETWEEN 150 AND 300) or ((_col17) IN ('WI', 'MO', 'WV') and _col9 BETWEEN 50 AND 250)) (type: boolean)
+ Statistics:Num rows: 16500000 Data size: 16745518417 Basic stats: COMPLETE Column stats: NONE
+ Merge Join Operator [MERGEJOIN_84]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col3 (type: int)","1":"_col0 (type: int)"}
+ | outputColumnNames:["_col0","_col5","_col7","_col8","_col9","_col17"]
+ | Statistics:Num rows: 22000000 Data size: 22327357890 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 11 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_35]
+ | key expressions:_col0 (type: int)
+ | Map-reduce partition columns:_col0 (type: int)
+ | sort order:+
+ | Statistics:Num rows: 20000000 Data size: 20297597642 Basic stats: COMPLETE Column stats: NONE
+ | value expressions:_col1 (type: string)
+ | Select Operator [SEL_31]
+ | outputColumnNames:["_col0","_col1"]
+ | Statistics:Num rows: 20000000 Data size: 20297597642 Basic stats: COMPLETE Column stats: NONE
+ | Filter Operator [FIL_79]
+ | predicate:((((ca_state) IN ('KY', 'GA', 'NM') or (ca_state) IN ('MT', 'OR', 'IN') or (ca_state) IN ('WI', 'MO', 'WV')) and (ca_country = 'United States')) and ca_address_sk is not null) (type: boolean)
+ | Statistics:Num rows: 20000000 Data size: 20297597642 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_29]
+ | alias:customer_address
+ | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 4 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_33]
+ key expressions:_col3 (type: int)
+ Map-reduce partition columns:_col3 (type: int)
+ sort order:+
+ Statistics:Num rows: 4491 Data size: 1626526 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col0 (type: int), _col5 (type: int), _col7 (type: decimal(7,2)), _col8 (type: decimal(7,2)), _col9 (type: decimal(7,2))
+ Select Operator [SEL_28]
+ outputColumnNames:["_col0","_col3","_col5","_col7","_col8","_col9"]
+ Statistics:Num rows: 4491 Data size: 1626526 Basic stats: COMPLETE Column stats: NONE
+ Filter Operator [FIL_74]
+ predicate:(((_col12 = 'M') and (_col13 = '4 yr Degree') and _col6 BETWEEN 100.0 AND 150.0 and (_col15 = 3)) or ((_col12 = 'D') and (_col13 = 'Primary') and _col6 BETWEEN 50.0 AND 100.0 and (_col15 = 1)) or ((_col12 = 'U') and (_col13 = 'Advanced Degree') and _col6 BETWEEN 150.0 AND 200.0 and (_col15 = 1))) (type: boolean)
+ Statistics:Num rows: 4491 Data size: 1626526 Basic stats: COMPLETE Column stats: NONE
+ Merge Join Operator [MERGEJOIN_83]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col2 (type: int)","1":"_col0 (type: int)"}
+ | outputColumnNames:["_col0","_col3","_col5","_col6","_col7","_col8","_col9","_col12","_col13","_col15"]
+ | Statistics:Num rows: 23958 Data size: 8676981 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 10 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_25]
+ | key expressions:_col0 (type: int)
+ | Map-reduce partition columns:_col0 (type: int)
+ | sort order:+
+ | Statistics:Num rows: 7200 Data size: 770400 Basic stats: COMPLETE Column stats: NONE
+ | value expressions:_col1 (type: int)
+ | Select Operator [SEL_11]
+ | outputColumnNames:["_col0","_col1"]
+ | Statistics:Num rows: 7200 Data size: 770400 Basic stats: COMPLETE Column stats: NONE
+ | Filter Operator [FIL_78]
+ | predicate:(((hd_dep_count = 3) or (hd_dep_count = 1)) and hd_demo_sk is not null) (type: boolean)
+ | Statistics:Num rows: 7200 Data size: 770400 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_9]
+ | alias:household_demographics
+ | Statistics:Num rows: 7200 Data size: 770400 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 3 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_23]
+ key expressions:_col2 (type: int)
+ Map-reduce partition columns:_col2 (type: int)
+ sort order:+
+ Statistics:Num rows: 21780 Data size: 7888165 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col0 (type: int), _col3 (type: int), _col5 (type: int), _col6 (type: decimal(7,2)), _col7 (type: decimal(7,2)), _col8 (type: decimal(7,2)), _col9 (type: decimal(7,2)), _col12 (type: string), _col13 (type: string)
+ Merge Join Operator [MERGEJOIN_82]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col1 (type: int)","1":"_col0 (type: int)"}
+ | outputColumnNames:["_col0","_col2","_col3","_col5","_col6","_col7","_col8","_col9","_col12","_col13"]
+ | Statistics:Num rows: 21780 Data size: 7888165 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 9 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_20]
+ | key expressions:_col0 (type: int)
+ | Map-reduce partition columns:_col0 (type: int)
+ | sort order:+
+ | Statistics:Num rows: 19800 Data size: 7171059 Basic stats: COMPLETE Column stats: NONE
+ | value expressions:_col1 (type: string), _col2 (type: string)
+ | Select Operator [SEL_8]
+ | outputColumnNames:["_col0","_col1","_col2"]
+ | Statistics:Num rows: 19800 Data size: 7171059 Basic stats: COMPLETE Column stats: NONE
+ | Filter Operator [FIL_77]
+ | predicate:((((cd_marital_status = 'M') or (cd_marital_status = 'D') or (cd_marital_status = 'U')) and ((cd_education_status = '4 yr Degree') or (cd_education_status = 'Primary') or (cd_education_status = 'Advanced Degree'))) and cd_demo_sk is not null) (type: boolean)
+ | Statistics:Num rows: 19800 Data size: 7171059 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_6]
+ | alias:customer_demographics
+ | Statistics:Num rows: 19800 Data size: 7171059 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 2 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_18]
+ key expressions:_col1 (type: int)
+ Map-reduce partition columns:_col1 (type: int)
+ sort order:+
+ Statistics:Num rows: 1874 Data size: 3581903 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col0 (type: int), _col2 (type: int), _col3 (type: int), _col5 (type: int), _col6 (type: decimal(7,2)), _col7 (type: decimal(7,2)), _col8 (type: decimal(7,2)), _col9 (type: decimal(7,2))
+ Merge Join Operator [MERGEJOIN_81]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col4 (type: int)","1":"_col0 (type: int)"}
+ | outputColumnNames:["_col0","_col1","_col2","_col3","_col5","_col6","_col7","_col8","_col9"]
+ | Statistics:Num rows: 1874 Data size: 3581903 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 1 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_13]
+ | key expressions:_col4 (type: int)
+ | Map-reduce partition columns:_col4 (type: int)
+ | sort order:+
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | value expressions:_col0 (type: int), _col1 (type: int), _col2 (type: int), _col3 (type: int), _col5 (type: int), _col6 (type: decimal(7,2)), _col7 (type: decimal(7,2)), _col8 (type: decimal(7,2)), _col9 (type: decimal(7,2))
+ | Select Operator [SEL_2]
+ | outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9"]
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | Filter Operator [FIL_75]
+ | 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_store_sk is not null) and (ss_sales_price BETWEEN 100.0 AND 150.0 or ss_sales_price BETWEEN 50.0 AND 100.0 or ss_sales_price BETWEEN 150.0 AND 200.0)) and ss_cdemo_sk is not null) and ss_hdemo_sk is not null) and ss_addr_sk is not null) and ss_sold_date_sk is not null) (type: boolean)
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | TableScan [TS_0]
+ | alias:store_sales
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ |<-Map 8 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_15]
+ key expressions:_col0 (type: int)
+ Map-reduce partition columns:_col0 (type: int)
+ sort order:+
+ Statistics:Num rows: 1704 Data size: 3256276 Basic stats: COMPLETE Column stats: NONE
+ Select Operator [SEL_5]
+ outputColumnNames:["_col0"]
+ Statistics:Num rows: 1704 Data size: 3256276 Basic stats: COMPLETE Column stats: NONE
+ Filter Operator [FIL_76]
+ predicate:s_store_sk is not null (type: boolean)
+ Statistics:Num rows: 1704 Data size: 3256276 Basic stats: COMPLETE Column stats: NONE
+ TableScan [TS_3]
+ alias:store
+ Statistics:Num rows: 1704 Data size: 3256276 Basic stats: COMPLETE Column stats: NONE
+
http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/results/clientpositive/perf/query15.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query15.q.out b/ql/src/test/results/clientpositive/perf/query15.q.out
new file mode 100644
index 0000000..0ce4e52
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/query15.q.out
@@ -0,0 +1,153 @@
+PREHOOK: query: explain select ca_zip ,sum(cs_sales_price) from catalog_sales ,customer ,customer_address ,date_dim where catalog_sales.cs_bill_customer_sk = customer.c_customer_sk and customer.c_current_addr_sk = customer_address.ca_address_sk and ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475', '85392', '85460', '80348', '81792') or customer_address.ca_state in ('CA','WA','GA') or catalog_sales.cs_sales_price > 500) and catalog_sales.cs_sold_date_sk = date_dim.d_date_sk and date_dim.d_qoy = 2 and date_dim.d_year = 2000 group by ca_zip order by ca_zip limit 100
+PREHOOK: type: QUERY
+POSTHOOK: query: explain select ca_zip ,sum(cs_sales_price) from catalog_sales ,customer ,customer_address ,date_dim where catalog_sales.cs_bill_customer_sk = customer.c_customer_sk and customer.c_current_addr_sk = customer_address.ca_address_sk and ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475', '85392', '85460', '80348', '81792') or customer_address.ca_state in ('CA','WA','GA') or catalog_sales.cs_sales_price > 500) and catalog_sales.cs_sold_date_sk = date_dim.d_date_sk and date_dim.d_qoy = 2 and date_dim.d_year = 2000 group by ca_zip order by ca_zip limit 100
+POSTHOOK: type: QUERY
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 7 (SIMPLE_EDGE)
+Reducer 3 <- Map 8 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
+Reducer 4 <- Map 9 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE)
+Reducer 5 <- Reducer 4 (SIMPLE_EDGE)
+Reducer 6 <- Reducer 5 (SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:100
+ Stage-1
+ Reducer 6
+ File Output Operator [FS_37]
+ compressed:false
+ Statistics:Num rows: 100 Data size: 86000 Basic stats: COMPLETE Column stats: NONE
+ table:{"input format:":"org.apache.hadoop.mapred.TextInputFormat","output format:":"org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat","serde:":"org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe"}
+ Limit [LIM_36]
+ Number of rows:100
+ Statistics:Num rows: 100 Data size: 86000 Basic stats: COMPLETE Column stats: NONE
+ Select Operator [SEL_35]
+ | outputColumnNames:["_col0","_col1"]
+ | Statistics:Num rows: 53240002 Data size: 45787477895 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 5 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_34]
+ key expressions:_col0 (type: string)
+ sort order:+
+ Statistics:Num rows: 53240002 Data size: 45787477895 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col1 (type: decimal(17,2))
+ Group By Operator [GBY_32]
+ | aggregations:["sum(VALUE._col0)"]
+ | keys:KEY._col0 (type: string)
+ | outputColumnNames:["_col0","_col1"]
+ | Statistics:Num rows: 53240002 Data size: 45787477895 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 4 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_31]
+ key expressions:_col0 (type: string)
+ Map-reduce partition columns:_col0 (type: string)
+ sort order:+
+ Statistics:Num rows: 106480005 Data size: 91574956652 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col1 (type: decimal(17,2))
+ Group By Operator [GBY_30]
+ aggregations:["sum(_col2)"]
+ keys:_col7 (type: string)
+ outputColumnNames:["_col0","_col1"]
+ Statistics:Num rows: 106480005 Data size: 91574956652 Basic stats: COMPLETE Column stats: NONE
+ Select Operator [SEL_29]
+ outputColumnNames:["_col7","_col2"]
+ Statistics:Num rows: 106480005 Data size: 91574956652 Basic stats: COMPLETE Column stats: NONE
+ Merge Join Operator [MERGEJOIN_54]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col0 (type: int)","1":"_col0 (type: int)"}
+ | outputColumnNames:["_col2","_col7"]
+ | Statistics:Num rows: 106480005 Data size: 91574956652 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 9 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_27]
+ | key expressions:_col0 (type: int)
+ | Map-reduce partition columns:_col0 (type: int)
+ | sort order:+
+ | Statistics:Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE
+ | Select Operator [SEL_23]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE
+ | Filter Operator [FIL_51]
+ | predicate:(((d_qoy = 2) and (d_year = 2000)) and d_date_sk is not null) (type: boolean)
+ | Statistics:Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_21]
+ | alias:date_dim
+ | Statistics:Num rows: 73049 Data size: 81741831 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 3 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_25]
+ key expressions:_col0 (type: int)
+ Map-reduce partition columns:_col0 (type: int)
+ sort order:+
+ Statistics:Num rows: 96800003 Data size: 83249958789 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col2 (type: decimal(7,2)), _col7 (type: string)
+ Select Operator [SEL_20]
+ outputColumnNames:["_col0","_col2","_col7"]
+ Statistics:Num rows: 96800003 Data size: 83249958789 Basic stats: COMPLETE Column stats: NONE
+ Filter Operator [FIL_47]
+ predicate:((substr(_col7, 1, 5)) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792') or (_col6) IN ('CA', 'WA', 'GA') or (_col2 > 500)) (type: boolean)
+ Statistics:Num rows: 96800003 Data size: 83249958789 Basic stats: COMPLETE Column stats: NONE
+ Merge Join Operator [MERGEJOIN_53]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col4 (type: int)","1":"_col0 (type: int)"}
+ | outputColumnNames:["_col0","_col2","_col6","_col7"]
+ | Statistics:Num rows: 96800003 Data size: 83249958789 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 8 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_17]
+ | key expressions:_col0 (type: int)
+ | Map-reduce partition columns:_col0 (type: int)
+ | sort order:+
+ | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE
+ | value expressions:_col1 (type: string), _col2 (type: string)
+ | Select Operator [SEL_8]
+ | outputColumnNames:["_col0","_col1","_col2"]
+ | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE
+ | Filter Operator [FIL_50]
+ | predicate:ca_address_sk is not null (type: boolean)
+ | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_6]
+ | alias:customer_address
+ | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 2 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_15]
+ key expressions:_col4 (type: int)
+ Map-reduce partition columns:_col4 (type: int)
+ sort order:+
+ Statistics:Num rows: 88000001 Data size: 75681779077 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col0 (type: int), _col2 (type: decimal(7,2))
+ Merge Join Operator [MERGEJOIN_52]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col1 (type: int)","1":"_col0 (type: int)"}
+ | outputColumnNames:["_col0","_col2","_col4"]
+ | Statistics:Num rows: 88000001 Data size: 75681779077 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 1 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_10]
+ | key expressions:_col1 (type: int)
+ | Map-reduce partition columns:_col1 (type: int)
+ | sort order:+
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | value expressions:_col0 (type: int), _col2 (type: decimal(7,2))
+ | Select Operator [SEL_2]
+ | outputColumnNames:["_col0","_col1","_col2"]
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | Filter Operator [FIL_48]
+ | predicate:(cs_bill_customer_sk is not null and cs_sold_date_sk is not null) (type: boolean)
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | TableScan [TS_0]
+ | alias:catalog_sales
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ |<-Map 7 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_12]
+ key expressions:_col0 (type: int)
+ Map-reduce partition columns:_col0 (type: int)
+ sort order:+
+ Statistics:Num rows: 80000000 Data size: 68801615852 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col1 (type: int)
+ Select Operator [SEL_5]
+ outputColumnNames:["_col0","_col1"]
+ Statistics:Num rows: 80000000 Data size: 68801615852 Basic stats: COMPLETE Column stats: NONE
+ Filter Operator [FIL_49]
+ predicate:(c_customer_sk is not null and c_current_addr_sk is not null) (type: boolean)
+ Statistics:Num rows: 80000000 Data size: 68801615852 Basic stats: COMPLETE Column stats: NONE
+ TableScan [TS_3]
+ alias:customer
+ Statistics:Num rows: 80000000 Data size: 68801615852 Basic stats: COMPLETE Column stats: NONE
+