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:29 UTC
[02/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/results/clientpositive/perf/query93.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query93.q.out b/ql/src/test/results/clientpositive/perf/query93.q.out
new file mode 100644
index 0000000..37396d9
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/query93.q.out
@@ -0,0 +1,117 @@
+PREHOOK: query: 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
+PREHOOK: type: QUERY
+POSTHOOK: query: 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
+POSTHOOK: type: QUERY
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 6 (SIMPLE_EDGE)
+Reducer 3 <- Map 7 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
+Reducer 4 <- Reducer 3 (SIMPLE_EDGE)
+Reducer 5 <- Reducer 4 (SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:100
+ Stage-1
+ Reducer 5
+ File Output Operator [FS_27]
+ compressed:false
+ Statistics:Num rows: 19 Data size: 3858 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_26]
+ Number of rows:100
+ Statistics:Num rows: 19 Data size: 3858 Basic stats: COMPLETE Column stats: NONE
+ Select Operator [SEL_25]
+ | outputColumnNames:["_col0","_col1"]
+ | Statistics:Num rows: 19 Data size: 3858 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 4 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_24]
+ key expressions:_col1 (type: decimal(28,2)), _col0 (type: int)
+ sort order:++
+ Statistics:Num rows: 19 Data size: 3858 Basic stats: COMPLETE Column stats: NONE
+ Group By Operator [GBY_22]
+ | aggregations:["sum(VALUE._col0)"]
+ | keys:KEY._col0 (type: int)
+ | outputColumnNames:["_col0","_col1"]
+ | Statistics:Num rows: 19 Data size: 3858 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 3 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_21]
+ key expressions:_col0 (type: int)
+ Map-reduce partition columns:_col0 (type: int)
+ sort order:+
+ Statistics:Num rows: 39 Data size: 7920 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col1 (type: decimal(28,2))
+ Group By Operator [GBY_20]
+ aggregations:["sum(_col1)"]
+ keys:_col0 (type: int)
+ outputColumnNames:["_col0","_col1"]
+ Statistics:Num rows: 39 Data size: 7920 Basic stats: COMPLETE Column stats: NONE
+ Select Operator [SEL_18]
+ outputColumnNames:["_col0","_col1"]
+ Statistics:Num rows: 39 Data size: 7920 Basic stats: COMPLETE Column stats: NONE
+ Merge Join Operator [MERGEJOIN_37]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col6 (type: int)","1":"_col0 (type: int)"}
+ | outputColumnNames:["_col1","_col3","_col4","_col8"]
+ | Statistics:Num rows: 39 Data size: 7920 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 7 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_16]
+ | key expressions:_col0 (type: int)
+ | Map-reduce partition columns:_col0 (type: int)
+ | sort order:+
+ | Statistics:Num rows: 36 Data size: 7200 Basic stats: COMPLETE Column stats: NONE
+ | Select Operator [SEL_7]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 36 Data size: 7200 Basic stats: COMPLETE Column stats: NONE
+ | Filter Operator [FIL_35]
+ | predicate:((r_reason_desc = 'Did not like the warranty') and r_reason_sk is not null) (type: boolean)
+ | Statistics:Num rows: 36 Data size: 7200 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_5]
+ | alias:reason
+ | Statistics:Num rows: 72 Data size: 14400 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 2 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_14]
+ key expressions:_col6 (type: int)
+ Map-reduce partition columns:_col6 (type: int)
+ sort order:+
+ Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ value expressions:_col1 (type: int), _col3 (type: int), _col4 (type: decimal(7,2)), _col8 (type: int)
+ Merge Join Operator [MERGEJOIN_36]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col0 (type: int), _col2 (type: int)","1":"_col0 (type: int), _col2 (type: int)"}
+ | outputColumnNames:["_col1","_col3","_col4","_col6","_col8"]
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ |<-Map 1 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_9]
+ | key expressions:_col0 (type: int), _col2 (type: int)
+ | Map-reduce partition columns:_col0 (type: int), _col2 (type: int)
+ | sort order:++
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | value expressions:_col1 (type: int), _col3 (type: int), _col4 (type: decimal(7,2))
+ | Select Operator [SEL_1]
+ | outputColumnNames:["_col0","_col1","_col2","_col3","_col4"]
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | Filter Operator [FIL_33]
+ | predicate:(ss_item_sk is not null and ss_ticket_number 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 6 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_11]
+ key expressions:_col0 (type: int), _col2 (type: int)
+ Map-reduce partition columns:_col0 (type: int), _col2 (type: int)
+ sort order:++
+ Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ value expressions:_col1 (type: int), _col3 (type: int)
+ Select Operator [SEL_4]
+ outputColumnNames:["_col0","_col1","_col2","_col3"]
+ Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ Filter Operator [FIL_34]
+ predicate:((sr_reason_sk is not null and sr_item_sk is not null) and sr_ticket_number is not null) (type: boolean)
+ Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ TableScan [TS_2]
+ alias:store_returns
+ Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+
http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/results/clientpositive/perf/query94.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query94.q.out b/ql/src/test/results/clientpositive/perf/query94.q.out
new file mode 100644
index 0000000..46acdb1
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/query94.q.out
@@ -0,0 +1,228 @@
+PREHOOK: query: 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
+PREHOOK: type: QUERY
+POSTHOOK: query: 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
+POSTHOOK: type: QUERY
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Reducer 9 (SIMPLE_EDGE)
+Reducer 3 <- Map 11 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
+Reducer 4 <- Map 12 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE)
+Reducer 5 <- Map 13 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE)
+Reducer 6 <- Map 14 (SIMPLE_EDGE), Reducer 5 (SIMPLE_EDGE)
+Reducer 7 <- Reducer 6 (SIMPLE_EDGE)
+Reducer 9 <- Map 10 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:100
+ Stage-1
+ Reducer 7
+ File Output Operator [FS_59]
+ 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"}
+ Limit [LIM_58]
+ Number of rows:100
+ Statistics:Num rows: 1 Data size: 344 Basic stats: COMPLETE Column stats: NONE
+ Group By Operator [GBY_56]
+ | aggregations:["count(DISTINCT KEY._col0:0._col0)","sum(VALUE._col1)","sum(VALUE._col2)"]
+ | outputColumnNames:["_col0","_col1","_col2"]
+ | Statistics:Num rows: 1 Data size: 344 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 6 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_55]
+ key expressions:_col0 (type: int)
+ sort order:+
+ Statistics:Num rows: 14641000 Data size: 14858857641 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col2 (type: decimal(17,2)), _col3 (type: decimal(17,2))
+ Group By Operator [GBY_54]
+ aggregations:["count(DISTINCT _col3)","sum(_col4)","sum(_col5)"]
+ keys:_col3 (type: int)
+ outputColumnNames:["_col0","_col1","_col2","_col3"]
+ Statistics:Num rows: 14641000 Data size: 14858857641 Basic stats: COMPLETE Column stats: NONE
+ Select Operator [SEL_53]
+ outputColumnNames:["_col3","_col4","_col5"]
+ Statistics:Num rows: 14641000 Data size: 14858857641 Basic stats: COMPLETE Column stats: NONE
+ Filter Operator [FIL_81]
+ predicate:_col12 is null (type: boolean)
+ Statistics:Num rows: 14641000 Data size: 14858857641 Basic stats: COMPLETE Column stats: NONE
+ Merge Join Operator [MERGEJOIN_95]
+ | condition map:[{"":"Left Outer Join0 to 1"}]
+ | keys:{"0":"_col3 (type: int)","1":"_col0 (type: int)"}
+ | outputColumnNames:["_col3","_col4","_col5","_col12"]
+ | Statistics:Num rows: 29282000 Data size: 29717715282 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 14 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_50]
+ | key expressions:_col0 (type: int)
+ | Map-reduce partition columns:_col0 (type: int)
+ | sort order:+
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | Select Operator [SEL_26]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | TableScan [TS_25]
+ | alias:wr1
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ |<-Reducer 5 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_49]
+ key expressions:_col3 (type: int)
+ Map-reduce partition columns:_col3 (type: int)
+ sort order:+
+ Statistics:Num rows: 26620000 Data size: 27016104217 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col4 (type: decimal(7,2)), _col5 (type: decimal(7,2))
+ Merge Join Operator [MERGEJOIN_94]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col0 (type: int)","1":"_col0 (type: int)"}
+ | outputColumnNames:["_col3","_col4","_col5"]
+ | Statistics:Num rows: 26620000 Data size: 27016104217 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 13 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_47]
+ | 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_24]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 36524 Data size: 40870356 Basic stats: COMPLETE Column stats: NONE
+ | Filter Operator [FIL_88]
+ | predicate:(d_date BETWEEN '1999-05-01' AND '1999-07-01' and d_date_sk is not null) (type: boolean)
+ | Statistics:Num rows: 36524 Data size: 40870356 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_22]
+ | alias:d
+ | Statistics:Num rows: 73049 Data size: 81741831 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 4 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_45]
+ key expressions:_col0 (type: int)
+ Map-reduce partition columns:_col0 (type: int)
+ sort order:+
+ Statistics:Num rows: 24200000 Data size: 24560094211 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col3 (type: int), _col4 (type: decimal(7,2)), _col5 (type: decimal(7,2))
+ Merge Join Operator [MERGEJOIN_93]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col2 (type: int)","1":"_col0 (type: int)"}
+ | outputColumnNames:["_col0","_col3","_col4","_col5"]
+ | Statistics:Num rows: 24200000 Data size: 24560094211 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 12 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_42]
+ | key expressions:_col0 (type: int)
+ | Map-reduce partition columns:_col0 (type: int)
+ | sort order:+
+ | Statistics:Num rows: 42 Data size: 77704 Basic stats: COMPLETE Column stats: NONE
+ | Select Operator [SEL_21]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 42 Data size: 77704 Basic stats: COMPLETE Column stats: NONE
+ | Filter Operator [FIL_87]
+ | predicate:((web_company_name = 'pri') and web_site_sk is not null) (type: boolean)
+ | Statistics:Num rows: 42 Data size: 77704 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_19]
+ | alias:s
+ | Statistics:Num rows: 84 Data size: 155408 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 3 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_40]
+ key expressions:_col2 (type: int)
+ Map-reduce partition columns:_col2 (type: int)
+ sort order:+
+ Statistics:Num rows: 22000000 Data size: 22327357890 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col0 (type: int), _col3 (type: int), _col4 (type: decimal(7,2)), _col5 (type: decimal(7,2))
+ Merge Join Operator [MERGEJOIN_92]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col1 (type: int)","1":"_col0 (type: int)"}
+ | outputColumnNames:["_col0","_col2","_col3","_col4","_col5"]
+ | Statistics:Num rows: 22000000 Data size: 22327357890 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 11 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_37]
+ | 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
+ | Select Operator [SEL_18]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 20000000 Data size: 20297597642 Basic stats: COMPLETE Column stats: NONE
+ | Filter Operator [FIL_86]
+ | predicate:((ca_state = 'TX') and ca_address_sk is not null) (type: boolean)
+ | Statistics:Num rows: 20000000 Data size: 20297597642 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_16]
+ | alias:ca
+ | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 2 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_35]
+ 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: int), _col3 (type: int), _col4 (type: decimal(7,2)), _col5 (type: decimal(7,2))
+ Merge Join Operator [MERGEJOIN_91]
+ | condition map:[{"":"Left Semi Join 0 to 1"}]
+ | keys:{"0":"_col3 (type: int)","1":"_col0 (type: int)"}
+ | outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ |<-Map 1 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_30]
+ | key expressions:_col3 (type: int)
+ | Map-reduce partition columns:_col3 (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), _col4 (type: decimal(7,2)), _col5 (type: decimal(7,2))
+ | Select Operator [SEL_2]
+ | outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | Filter Operator [FIL_82]
+ | predicate:(((ws_ship_addr_sk is not null and ws_web_site_sk is not null) and ws_ship_date_sk is not null) and ws_order_number is not null) (type: boolean)
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | TableScan [TS_0]
+ | alias:ws1
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ |<-Reducer 9 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_32]
+ key expressions:_col0 (type: int)
+ Map-reduce partition columns:_col0 (type: int)
+ sort order:+
+ Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ Group By Operator [GBY_28]
+ keys:_col0 (type: int)
+ outputColumnNames:["_col0"]
+ Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ Select Operator [SEL_15]
+ outputColumnNames:["_col0"]
+ Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ Filter Operator [FIL_83]
+ predicate:(_col0 <> _col2) (type: boolean)
+ Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ Merge Join Operator [MERGEJOIN_90]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col1 (type: int)","1":"_col1 (type: int)"}
+ | outputColumnNames:["_col0","_col1","_col2"]
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ |<-Map 10 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_12]
+ | 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)
+ | Select Operator [SEL_8]
+ | outputColumnNames:["_col0","_col1"]
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | Filter Operator [FIL_85]
+ | predicate:ws_order_number is not null (type: boolean)
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | TableScan [TS_6]
+ | alias:ws1
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ |<-Map 8 [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)
+ Select Operator [SEL_5]
+ outputColumnNames:["_col0","_col1"]
+ Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ Filter Operator [FIL_84]
+ predicate:ws_order_number is not null (type: boolean)
+ Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ TableScan [TS_3]
+ alias:ws1
+ Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+
http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/results/clientpositive/perf/query95.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query95.q.out b/ql/src/test/results/clientpositive/perf/query95.q.out
new file mode 100644
index 0000000..03b0c5c
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/query95.q.out
@@ -0,0 +1,275 @@
+PREHOOK: query: 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 '2002-05-01' and '2002-06-30' and ca.ca_state = 'GA' and s.web_company_name = 'pri'
+PREHOOK: type: QUERY
+POSTHOOK: query: 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 '2002-05-01' and '2002-06-30' and ca.ca_state = 'GA' and s.web_company_name = 'pri'
+POSTHOOK: type: QUERY
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Reducer 11 <- Map 10 (SIMPLE_EDGE), Reducer 13 (SIMPLE_EDGE)
+Reducer 13 <- Map 12 (SIMPLE_EDGE), Map 14 (SIMPLE_EDGE)
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Reducer 11 (SIMPLE_EDGE), Reducer 8 (SIMPLE_EDGE)
+Reducer 3 <- Map 15 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
+Reducer 4 <- Map 16 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE)
+Reducer 5 <- Map 17 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE)
+Reducer 6 <- Reducer 5 (SIMPLE_EDGE)
+Reducer 8 <- Map 7 (SIMPLE_EDGE), Map 9 (SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:-1
+ Stage-1
+ Reducer 6
+ File Output Operator [FS_78]
+ 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_76]
+ | aggregations:["count(DISTINCT KEY._col0:0._col0)","sum(VALUE._col1)","sum(VALUE._col2)"]
+ | outputColumnNames:["_col0","_col1","_col2"]
+ | Statistics:Num rows: 1 Data size: 344 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 5 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_75]
+ key expressions:_col0 (type: int)
+ sort order:+
+ Statistics:Num rows: 26620000 Data size: 27016104217 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col2 (type: decimal(17,2)), _col3 (type: decimal(17,2))
+ Group By Operator [GBY_74]
+ aggregations:["count(DISTINCT _col3)","sum(_col4)","sum(_col5)"]
+ keys:_col3 (type: int)
+ outputColumnNames:["_col0","_col1","_col2","_col3"]
+ Statistics:Num rows: 26620000 Data size: 27016104217 Basic stats: COMPLETE Column stats: NONE
+ Merge Join Operator [MERGEJOIN_139]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col0 (type: int)","1":"_col0 (type: int)"}
+ | outputColumnNames:["_col3","_col4","_col5"]
+ | Statistics:Num rows: 26620000 Data size: 27016104217 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 17 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_71]
+ | 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_46]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 36524 Data size: 40870356 Basic stats: COMPLETE Column stats: NONE
+ | Filter Operator [FIL_132]
+ | predicate:(d_date BETWEEN '2002-05-01' AND '2002-06-30' and d_date_sk is not null) (type: boolean)
+ | Statistics:Num rows: 36524 Data size: 40870356 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_44]
+ | alias:d
+ | Statistics:Num rows: 73049 Data size: 81741831 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 4 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_69]
+ key expressions:_col0 (type: int)
+ Map-reduce partition columns:_col0 (type: int)
+ sort order:+
+ Statistics:Num rows: 24200000 Data size: 24560094211 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col3 (type: int), _col4 (type: decimal(7,2)), _col5 (type: decimal(7,2))
+ Merge Join Operator [MERGEJOIN_138]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col2 (type: int)","1":"_col0 (type: int)"}
+ | outputColumnNames:["_col0","_col3","_col4","_col5"]
+ | Statistics:Num rows: 24200000 Data size: 24560094211 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 16 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_66]
+ | key expressions:_col0 (type: int)
+ | Map-reduce partition columns:_col0 (type: int)
+ | sort order:+
+ | Statistics:Num rows: 42 Data size: 77704 Basic stats: COMPLETE Column stats: NONE
+ | Select Operator [SEL_43]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 42 Data size: 77704 Basic stats: COMPLETE Column stats: NONE
+ | Filter Operator [FIL_131]
+ | predicate:((web_company_name = 'pri') and web_site_sk is not null) (type: boolean)
+ | Statistics:Num rows: 42 Data size: 77704 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_41]
+ | alias:s
+ | Statistics:Num rows: 84 Data size: 155408 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 3 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_64]
+ key expressions:_col2 (type: int)
+ Map-reduce partition columns:_col2 (type: int)
+ sort order:+
+ Statistics:Num rows: 22000000 Data size: 22327357890 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col0 (type: int), _col3 (type: int), _col4 (type: decimal(7,2)), _col5 (type: decimal(7,2))
+ Merge Join Operator [MERGEJOIN_137]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col1 (type: int)","1":"_col0 (type: int)"}
+ | outputColumnNames:["_col0","_col2","_col3","_col4","_col5"]
+ | Statistics:Num rows: 22000000 Data size: 22327357890 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 15 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_61]
+ | 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
+ | Select Operator [SEL_40]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 20000000 Data size: 20297597642 Basic stats: COMPLETE Column stats: NONE
+ | Filter Operator [FIL_130]
+ | predicate:((ca_state = 'GA') and ca_address_sk is not null) (type: boolean)
+ | Statistics:Num rows: 20000000 Data size: 20297597642 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_38]
+ | alias:ca
+ | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 2 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_59]
+ key expressions:_col1 (type: int)
+ Map-reduce partition columns:_col1 (type: int)
+ sort order:+
+ Statistics:Num rows: 2 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ value expressions:_col0 (type: int), _col2 (type: int), _col3 (type: int), _col4 (type: decimal(7,2)), _col5 (type: decimal(7,2))
+ Merge Join Operator [MERGEJOIN_136]
+ | condition map:[{"":"Left Semi Join 0 to 1"},{"":"Left Semi Join 0 to 2"}]
+ | keys:{"0":"_col3 (type: int)","1":"_col0 (type: int)","2":"_col0 (type: int)"}
+ | outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ | Statistics:Num rows: 2 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ |<-Map 1 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_52]
+ | key expressions:_col3 (type: int)
+ | Map-reduce partition columns:_col3 (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), _col4 (type: decimal(7,2)), _col5 (type: decimal(7,2))
+ | Select Operator [SEL_2]
+ | outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5"]
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | Filter Operator [FIL_122]
+ | predicate:(((ws_ship_addr_sk is not null and ws_web_site_sk is not null) and ws_ship_date_sk is not null) and ws_order_number is not null) (type: boolean)
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | TableScan [TS_0]
+ | alias:ws1
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ |<-Reducer 11 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_56]
+ | key expressions:_col0 (type: int)
+ | Map-reduce partition columns:_col0 (type: int)
+ | sort order:+
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | Group By Operator [GBY_50]
+ | keys:_col0 (type: int)
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | Merge Join Operator [MERGEJOIN_135]
+ | | condition map:[{"":"Inner Join 0 to 1"}]
+ | | keys:{"0":"_col0 (type: int)","1":"_col0 (type: int)"}
+ | | outputColumnNames:["_col0"]
+ | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | |<-Map 10 [SIMPLE_EDGE]
+ | | Reduce Output Operator [RS_33]
+ | | key expressions:_col0 (type: int)
+ | | Map-reduce partition columns:_col0 (type: int)
+ | | sort order:+
+ | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | | Select Operator [SEL_18]
+ | | outputColumnNames:["_col0"]
+ | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | | Filter Operator [FIL_126]
+ | | predicate:wr_order_number is not null (type: boolean)
+ | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | | TableScan [TS_16]
+ | | alias:wr
+ | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | |<-Reducer 13 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_35]
+ | key expressions:_col0 (type: int)
+ | Map-reduce partition columns:_col0 (type: int)
+ | sort order:+
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | Select Operator [SEL_31]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | Filter Operator [FIL_127]
+ | predicate:(_col0 <> _col2) (type: boolean)
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | Merge Join Operator [MERGEJOIN_134]
+ | | condition map:[{"":"Inner Join 0 to 1"}]
+ | | keys:{"0":"_col1 (type: int)","1":"_col1 (type: int)"}
+ | | outputColumnNames:["_col0","_col1","_col2"]
+ | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | |<-Map 12 [SIMPLE_EDGE]
+ | | Reduce Output Operator [RS_26]
+ | | 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)
+ | | Select Operator [SEL_21]
+ | | outputColumnNames:["_col0","_col1"]
+ | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | | Filter Operator [FIL_128]
+ | | predicate:ws_order_number is not null (type: boolean)
+ | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | | TableScan [TS_19]
+ | | alias:ws1
+ | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | |<-Map 14 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_28]
+ | 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)
+ | Select Operator [SEL_24]
+ | outputColumnNames:["_col0","_col1"]
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | Filter Operator [FIL_129]
+ | predicate:ws_order_number is not null (type: boolean)
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | TableScan [TS_22]
+ | alias:ws1
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ |<-Reducer 8 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_54]
+ key expressions:_col0 (type: int)
+ Map-reduce partition columns:_col0 (type: int)
+ sort order:+
+ Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ Group By Operator [GBY_48]
+ keys:_col0 (type: int)
+ outputColumnNames:["_col0"]
+ Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ Select Operator [SEL_15]
+ outputColumnNames:["_col0"]
+ Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ Filter Operator [FIL_123]
+ predicate:(_col0 <> _col2) (type: boolean)
+ Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ Merge Join Operator [MERGEJOIN_133]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col1 (type: int)","1":"_col1 (type: int)"}
+ | outputColumnNames:["_col0","_col1","_col2"]
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ |<-Map 7 [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)
+ | Select Operator [SEL_5]
+ | outputColumnNames:["_col0","_col1"]
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | Filter Operator [FIL_124]
+ | predicate:ws_order_number is not null (type: boolean)
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | TableScan [TS_3]
+ | alias:ws1
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ |<-Map 9 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_12]
+ 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)
+ Select Operator [SEL_8]
+ outputColumnNames:["_col0","_col1"]
+ Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ Filter Operator [FIL_125]
+ predicate:ws_order_number is not null (type: boolean)
+ Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ TableScan [TS_6]
+ alias:ws1
+ Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+
http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/results/clientpositive/perf/query96.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query96.q.out b/ql/src/test/results/clientpositive/perf/query96.q.out
new file mode 100644
index 0000000..6290ff0
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/query96.q.out
@@ -0,0 +1,135 @@
+PREHOOK: query: 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
+PREHOOK: type: QUERY
+POSTHOOK: query: 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
+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_35]
+ compressed:false
+ Statistics:Num rows: 1 Data size: 8 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_34]
+ Number of rows:100
+ Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ Select Operator [SEL_33]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 5 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_32]
+ key expressions:_col0 (type: bigint)
+ sort order:+
+ Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ Group By Operator [GBY_30]
+ | aggregations:["count(VALUE._col0)"]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 4 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_29]
+ sort order:
+ Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col0 (type: bigint)
+ Group By Operator [GBY_28]
+ aggregations:["count()"]
+ outputColumnNames:["_col0"]
+ Statistics:Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
+ Merge Join Operator [MERGEJOIN_51]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col2 (type: int)","1":"_col0 (type: int)"}
+ | Statistics:Num rows: 17424 Data size: 8206704 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 9 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_25]
+ | key expressions:_col0 (type: int)
+ | Map-reduce partition columns:_col0 (type: int)
+ | sort order:+
+ | Statistics:Num rows: 852 Data size: 1628138 Basic stats: COMPLETE Column stats: NONE
+ | Select Operator [SEL_11]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 852 Data size: 1628138 Basic stats: COMPLETE Column stats: NONE
+ | Filter Operator [FIL_48]
+ | predicate:((s_store_name = 'ese') and s_store_sk is not null) (type: boolean)
+ | Statistics:Num rows: 852 Data size: 1628138 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_9]
+ | alias:store
+ | Statistics:Num rows: 1704 Data size: 3256276 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: 15840 Data size: 7460640 Basic stats: COMPLETE Column stats: NONE
+ Merge Join Operator [MERGEJOIN_50]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col0 (type: int)","1":"_col0 (type: int)"}
+ | outputColumnNames:["_col2"]
+ | Statistics:Num rows: 15840 Data size: 7460640 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 8 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_20]
+ | key expressions:_col0 (type: int)
+ | Map-reduce partition columns:_col0 (type: int)
+ | sort order:+
+ | Statistics:Num rows: 14400 Data size: 6782400 Basic stats: COMPLETE Column stats: NONE
+ | Select Operator [SEL_8]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 14400 Data size: 6782400 Basic stats: COMPLETE Column stats: NONE
+ | Filter Operator [FIL_47]
+ | predicate:(((t_minute >= 30) and (t_hour = 8)) and t_time_sk is not null) (type: boolean)
+ | Statistics:Num rows: 14400 Data size: 6782400 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_6]
+ | alias:time_dim
+ | Statistics:Num rows: 86400 Data size: 40694400 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 2 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_18]
+ key expressions:_col0 (type: int)
+ Map-reduce partition columns:_col0 (type: int)
+ sort order:+
+ Statistics:Num rows: 3960 Data size: 423720 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col2 (type: int)
+ Merge Join Operator [MERGEJOIN_49]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col1 (type: int)","1":"_col0 (type: int)"}
+ | outputColumnNames:["_col0","_col2"]
+ | Statistics:Num rows: 3960 Data size: 423720 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 1 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_13]
+ | 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: int)
+ | Select Operator [SEL_2]
+ | outputColumnNames:["_col0","_col1","_col2"]
+ | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | Filter Operator [FIL_45]
+ | predicate:((ss_hdemo_sk is not null and ss_sold_time_sk is not null) and ss_store_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 7 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_15]
+ key expressions:_col0 (type: int)
+ Map-reduce partition columns:_col0 (type: int)
+ sort order:+
+ Statistics:Num rows: 3600 Data size: 385200 Basic stats: COMPLETE Column stats: NONE
+ Select Operator [SEL_5]
+ outputColumnNames:["_col0"]
+ Statistics:Num rows: 3600 Data size: 385200 Basic stats: COMPLETE Column stats: NONE
+ Filter Operator [FIL_46]
+ predicate:((hd_dep_count = 5) and hd_demo_sk is not null) (type: boolean)
+ Statistics:Num rows: 3600 Data size: 385200 Basic stats: COMPLETE Column stats: NONE
+ TableScan [TS_3]
+ alias:household_demographics
+ Statistics:Num rows: 7200 Data size: 770400 Basic stats: COMPLETE Column stats: NONE
+
http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/results/clientpositive/perf/query97.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/query97.q.out b/ql/src/test/results/clientpositive/perf/query97.q.out
new file mode 100644
index 0000000..3e5d3bb
--- /dev/null
+++ b/ql/src/test/results/clientpositive/perf/query97.q.out
@@ -0,0 +1,163 @@
+PREHOOK: query: 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
+PREHOOK: type: QUERY
+POSTHOOK: query: 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
+POSTHOOK: type: QUERY
+Plan optimized by CBO.
+
+Vertex dependency in root stage
+Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 6 (SIMPLE_EDGE)
+Reducer 3 <- Reducer 2 (SIMPLE_EDGE)
+Reducer 4 <- Reducer 3 (SIMPLE_EDGE), Reducer 9 (SIMPLE_EDGE)
+Reducer 5 <- Reducer 4 (SIMPLE_EDGE)
+Reducer 8 <- Map 10 (SIMPLE_EDGE), Map 7 (SIMPLE_EDGE)
+Reducer 9 <- Reducer 8 (SIMPLE_EDGE)
+
+Stage-0
+ Fetch Operator
+ limit:100
+ Stage-1
+ Reducer 5
+ File Output Operator [FS_42]
+ compressed:false
+ Statistics:Num rows: 1 Data size: 24 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_41]
+ Number of rows:100
+ Statistics:Num rows: 1 Data size: 24 Basic stats: COMPLETE Column stats: NONE
+ Group By Operator [GBY_39]
+ | aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)"]
+ | outputColumnNames:["_col0","_col1","_col2"]
+ | Statistics:Num rows: 1 Data size: 24 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 4 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_38]
+ sort order:
+ Statistics:Num rows: 1 Data size: 24 Basic stats: COMPLETE Column stats: NONE
+ value expressions:_col0 (type: bigint), _col1 (type: bigint), _col2 (type: bigint)
+ Group By Operator [GBY_37]
+ aggregations:["sum(_col0)","sum(_col1)","sum(_col2)"]
+ outputColumnNames:["_col0","_col1","_col2"]
+ Statistics:Num rows: 1 Data size: 24 Basic stats: COMPLETE Column stats: NONE
+ Select Operator [SEL_35]
+ outputColumnNames:["_col0","_col1","_col2"]
+ Statistics:Num rows: 22096 Data size: 24726566 Basic stats: COMPLETE Column stats: NONE
+ Merge Join Operator [MERGEJOIN_53]
+ | condition map:[{"":"Outer Join 0 to 1"}]
+ | keys:{"0":"_col0 (type: int), _col1 (type: int)","1":"_col0 (type: int), _col1 (type: int)"}
+ | outputColumnNames:["_col0","_col2"]
+ | Statistics:Num rows: 22096 Data size: 24726566 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 3 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_32]
+ | key expressions:_col0 (type: int), _col1 (type: int)
+ | Map-reduce partition columns:_col0 (type: int), _col1 (type: int)
+ | sort order:++
+ | Statistics:Num rows: 20088 Data size: 22478696 Basic stats: COMPLETE Column stats: NONE
+ | Select Operator [SEL_15]
+ | outputColumnNames:["_col0","_col1"]
+ | Statistics:Num rows: 20088 Data size: 22478696 Basic stats: COMPLETE Column stats: NONE
+ | Group By Operator [GBY_14]
+ | | keys:KEY._col0 (type: int), KEY._col1 (type: int)
+ | | outputColumnNames:["_col0","_col1"]
+ | | Statistics:Num rows: 20088 Data size: 22478696 Basic stats: COMPLETE Column stats: NONE
+ | |<-Reducer 2 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_13]
+ | key expressions:_col0 (type: int), _col1 (type: int)
+ | Map-reduce partition columns:_col0 (type: int), _col1 (type: int)
+ | sort order:++
+ | Statistics:Num rows: 40176 Data size: 44957392 Basic stats: COMPLETE Column stats: NONE
+ | Group By Operator [GBY_12]
+ | keys:_col1 (type: int), _col2 (type: int)
+ | outputColumnNames:["_col0","_col1"]
+ | Statistics:Num rows: 40176 Data size: 44957392 Basic stats: COMPLETE Column stats: NONE
+ | Merge Join Operator [MERGEJOIN_51]
+ | | condition map:[{"":"Inner Join 0 to 1"}]
+ | | keys:{"0":"_col0 (type: int)","1":"_col0 (type: int)"}
+ | | outputColumnNames:["_col1","_col2"]
+ | | Statistics:Num rows: 40176 Data size: 44957392 Basic stats: COMPLETE Column stats: NONE
+ | |<-Map 1 [SIMPLE_EDGE]
+ | | Reduce Output Operator [RS_7]
+ | | key expressions:_col0 (type: int)
+ | | Map-reduce partition columns:_col0 (type: int)
+ | | sort order:+
+ | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | | value expressions:_col1 (type: int), _col2 (type: int)
+ | | Select Operator [SEL_2]
+ | | outputColumnNames:["_col0","_col1","_col2"]
+ | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ | | Filter Operator [FIL_47]
+ | | predicate: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 6 [SIMPLE_EDGE]
+ | Reduce Output Operator [RS_9]
+ | 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_5]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 36524 Data size: 40870356 Basic stats: COMPLETE Column stats: NONE
+ | Filter Operator [FIL_48]
+ | predicate:(d_month_seq BETWEEN 1193 AND 1204 and d_date_sk is not null) (type: boolean)
+ | Statistics:Num rows: 36524 Data size: 40870356 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_3]
+ | alias:date_dim
+ | Statistics:Num rows: 73049 Data size: 81741831 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 9 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_33]
+ key expressions:_col0 (type: int), _col1 (type: int)
+ Map-reduce partition columns:_col0 (type: int), _col1 (type: int)
+ sort order:++
+ Statistics:Num rows: 20088 Data size: 22478696 Basic stats: COMPLETE Column stats: NONE
+ Group By Operator [GBY_30]
+ | keys:KEY._col0 (type: int), KEY._col1 (type: int)
+ | outputColumnNames:["_col0","_col1"]
+ | Statistics:Num rows: 20088 Data size: 22478696 Basic stats: COMPLETE Column stats: NONE
+ |<-Reducer 8 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_29]
+ key expressions:_col0 (type: int), _col1 (type: int)
+ Map-reduce partition columns:_col0 (type: int), _col1 (type: int)
+ sort order:++
+ Statistics:Num rows: 40176 Data size: 44957392 Basic stats: COMPLETE Column stats: NONE
+ Group By Operator [GBY_28]
+ keys:_col1 (type: int), _col2 (type: int)
+ outputColumnNames:["_col0","_col1"]
+ Statistics:Num rows: 40176 Data size: 44957392 Basic stats: COMPLETE Column stats: NONE
+ Merge Join Operator [MERGEJOIN_52]
+ | condition map:[{"":"Inner Join 0 to 1"}]
+ | keys:{"0":"_col0 (type: int)","1":"_col0 (type: int)"}
+ | outputColumnNames:["_col1","_col2"]
+ | Statistics:Num rows: 40176 Data size: 44957392 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: 36524 Data size: 40870356 Basic stats: COMPLETE Column stats: NONE
+ | Select Operator [SEL_21]
+ | outputColumnNames:["_col0"]
+ | Statistics:Num rows: 36524 Data size: 40870356 Basic stats: COMPLETE Column stats: NONE
+ | Filter Operator [FIL_50]
+ | predicate:(d_month_seq BETWEEN 1193 AND 1204 and d_date_sk is not null) (type: boolean)
+ | Statistics:Num rows: 36524 Data size: 40870356 Basic stats: COMPLETE Column stats: NONE
+ | TableScan [TS_19]
+ | alias:date_dim
+ | Statistics:Num rows: 73049 Data size: 81741831 Basic stats: COMPLETE Column stats: NONE
+ |<-Map 7 [SIMPLE_EDGE]
+ Reduce Output Operator [RS_23]
+ key expressions:_col0 (type: int)
+ Map-reduce partition columns:_col0 (type: int)
+ sort order:+
+ Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ value expressions:_col1 (type: int), _col2 (type: int)
+ Select Operator [SEL_18]
+ outputColumnNames:["_col0","_col1","_col2"]
+ Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ Filter Operator [FIL_49]
+ predicate:cs_sold_date_sk is not null (type: boolean)
+ Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+ TableScan [TS_16]
+ alias:catalog_sales
+ Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
+