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 +
