http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/query77.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query77.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query77.q.out new file mode 100644 index 0000000..78f7c28 --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query77.q.out @@ -0,0 +1,504 @@ +Warning: Shuffle Join MERGEJOIN[225][tables = [$hdt$_0, $hdt$_1]] in Stage 'Reducer 14' is a cross product +PREHOOK: query: explain +with ss as + (select s_store_sk, + sum(ss_ext_sales_price) as sales, + sum(ss_net_profit) as profit + from store_sales, + date_dim, + store + where ss_sold_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and ss_store_sk = s_store_sk + group by s_store_sk) + , + sr as + (select s_store_sk, + sum(sr_return_amt) as returns, + sum(sr_net_loss) as profit_loss + from store_returns, + date_dim, + store + where sr_returned_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and sr_store_sk = s_store_sk + group by s_store_sk), + cs as + (select cs_call_center_sk, + sum(cs_ext_sales_price) as sales, + sum(cs_net_profit) as profit + from catalog_sales, + date_dim + where cs_sold_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + group by cs_call_center_sk + ), + cr as + (select + sum(cr_return_amount) as returns, + sum(cr_net_loss) as profit_loss + from catalog_returns, + date_dim + where cr_returned_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + ), + ws as + ( select wp_web_page_sk, + sum(ws_ext_sales_price) as sales, + sum(ws_net_profit) as profit + from web_sales, + date_dim, + web_page + where ws_sold_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and ws_web_page_sk = wp_web_page_sk + group by wp_web_page_sk), + wr as + (select wp_web_page_sk, + sum(wr_return_amt) as returns, + sum(wr_net_loss) as profit_loss + from web_returns, + date_dim, + web_page + where wr_returned_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and wr_web_page_sk = wp_web_page_sk + group by wp_web_page_sk) + select channel + , id + , sum(sales) as sales + , sum(returns) as returns + , sum(profit) as profit + from + (select 'store channel' as channel + , ss.s_store_sk as id + , sales + , coalesce(returns, 0) as returns + , (profit - coalesce(profit_loss,0)) as profit + from ss left join sr + on ss.s_store_sk = sr.s_store_sk + union all + select 'catalog channel' as channel + , cs_call_center_sk as id + , sales + , returns + , (profit - profit_loss) as profit + from cs + , cr + union all + select 'web channel' as channel + , ws.wp_web_page_sk as id + , sales + , coalesce(returns, 0) returns + , (profit - coalesce(profit_loss,0)) as profit + from ws left join wr + on ws.wp_web_page_sk = wr.wp_web_page_sk + ) x + group by rollup (channel, id) + order by channel + ,id + limit 100 +PREHOOK: type: QUERY +PREHOOK: Input: default@catalog_returns +PREHOOK: Input: default@catalog_sales +PREHOOK: Input: default@date_dim +PREHOOK: Input: default@store +PREHOOK: Input: default@store_returns +PREHOOK: Input: default@store_sales +PREHOOK: Input: default@web_page +PREHOOK: Input: default@web_returns +PREHOOK: Input: default@web_sales +PREHOOK: Output: hdfs://### HDFS PATH ### +POSTHOOK: query: explain +with ss as + (select s_store_sk, + sum(ss_ext_sales_price) as sales, + sum(ss_net_profit) as profit + from store_sales, + date_dim, + store + where ss_sold_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and ss_store_sk = s_store_sk + group by s_store_sk) + , + sr as + (select s_store_sk, + sum(sr_return_amt) as returns, + sum(sr_net_loss) as profit_loss + from store_returns, + date_dim, + store + where sr_returned_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and sr_store_sk = s_store_sk + group by s_store_sk), + cs as + (select cs_call_center_sk, + sum(cs_ext_sales_price) as sales, + sum(cs_net_profit) as profit + from catalog_sales, + date_dim + where cs_sold_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + group by cs_call_center_sk + ), + cr as + (select + sum(cr_return_amount) as returns, + sum(cr_net_loss) as profit_loss + from catalog_returns, + date_dim + where cr_returned_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + ), + ws as + ( select wp_web_page_sk, + sum(ws_ext_sales_price) as sales, + sum(ws_net_profit) as profit + from web_sales, + date_dim, + web_page + where ws_sold_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and ws_web_page_sk = wp_web_page_sk + group by wp_web_page_sk), + wr as + (select wp_web_page_sk, + sum(wr_return_amt) as returns, + sum(wr_net_loss) as profit_loss + from web_returns, + date_dim, + web_page + where wr_returned_date_sk = d_date_sk + and d_date between cast('1998-08-04' as date) + and (cast('1998-08-04' as date) + 30 days) + and wr_web_page_sk = wp_web_page_sk + group by wp_web_page_sk) + select channel + , id + , sum(sales) as sales + , sum(returns) as returns + , sum(profit) as profit + from + (select 'store channel' as channel + , ss.s_store_sk as id + , sales + , coalesce(returns, 0) as returns + , (profit - coalesce(profit_loss,0)) as profit + from ss left join sr + on ss.s_store_sk = sr.s_store_sk + union all + select 'catalog channel' as channel + , cs_call_center_sk as id + , sales + , returns + , (profit - profit_loss) as profit + from cs + , cr + union all + select 'web channel' as channel + , ws.wp_web_page_sk as id + , sales + , coalesce(returns, 0) returns + , (profit - coalesce(profit_loss,0)) as profit + from ws left join wr + on ws.wp_web_page_sk = wr.wp_web_page_sk + ) x + group by rollup (channel, id) + order by channel + ,id + limit 100 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@catalog_returns +POSTHOOK: Input: default@catalog_sales +POSTHOOK: Input: default@date_dim +POSTHOOK: Input: default@store +POSTHOOK: Input: default@store_returns +POSTHOOK: Input: default@store_sales +POSTHOOK: Input: default@web_page +POSTHOOK: Input: default@web_returns +POSTHOOK: Input: default@web_sales +POSTHOOK: Output: hdfs://### HDFS PATH ### +Plan optimized by CBO. + +Vertex dependency in root stage +Map 1 <- Reducer 9 (BROADCAST_EDGE) +Map 25 <- Reducer 15 (BROADCAST_EDGE) +Map 27 <- Reducer 21 (BROADCAST_EDGE) +Reducer 10 <- Map 24 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE) +Reducer 11 <- Reducer 10 (SIMPLE_EDGE) +Reducer 12 <- Map 25 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE) +Reducer 13 <- Reducer 12 (SIMPLE_EDGE) +Reducer 14 <- Reducer 13 (CUSTOM_SIMPLE_EDGE), Reducer 17 (CUSTOM_SIMPLE_EDGE), Union 5 (CONTAINS) +Reducer 15 <- Map 8 (CUSTOM_SIMPLE_EDGE) +Reducer 16 <- Map 26 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE) +Reducer 17 <- Reducer 16 (CUSTOM_SIMPLE_EDGE) +Reducer 18 <- Map 27 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE) +Reducer 19 <- Reducer 18 (SIMPLE_EDGE) +Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE) +Reducer 20 <- Reducer 19 (ONE_TO_ONE_EDGE), Reducer 23 (ONE_TO_ONE_EDGE), Union 5 (CONTAINS) +Reducer 21 <- Map 8 (CUSTOM_SIMPLE_EDGE) +Reducer 22 <- Map 28 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE) +Reducer 23 <- Reducer 22 (SIMPLE_EDGE) +Reducer 3 <- Reducer 2 (SIMPLE_EDGE) +Reducer 4 <- Reducer 11 (ONE_TO_ONE_EDGE), Reducer 3 (ONE_TO_ONE_EDGE), Union 5 (CONTAINS) +Reducer 6 <- Union 5 (SIMPLE_EDGE) +Reducer 7 <- Reducer 6 (SIMPLE_EDGE) +Reducer 9 <- Map 8 (CUSTOM_SIMPLE_EDGE) + +Stage-0 + Fetch Operator + limit:100 + Stage-1 + Reducer 7 vectorized + File Output Operator [FS_271] + Limit [LIM_270] (rows=24 width=437) + Number of rows:100 + Select Operator [SEL_269] (rows=24 width=437) + Output:["_col0","_col1","_col2","_col3","_col4"] + <-Reducer 6 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_268] + Select Operator [SEL_267] (rows=24 width=437) + Output:["_col0","_col1","_col2","_col3","_col4"] + Group By Operator [GBY_266] (rows=24 width=445) + Output:["_col0","_col1","_col3","_col4","_col5"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)"],keys:KEY._col0, KEY._col1, KEY._col2 + <-Union 5 [SIMPLE_EDGE] + <-Reducer 14 [CONTAINS] + Reduce Output Operator [RS_230] + PartitionCols:_col0, _col1, _col2 + Group By Operator [GBY_229] (rows=58 width=445) + Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col2)","sum(_col3)","sum(_col4)"],keys:_col0, _col1, 0L + Top N Key Operator [TNK_228] (rows=39 width=435) + keys:_col0, _col1, 0L,sort order:+++,top n:100 + Select Operator [SEL_226] (rows=2 width=439) + Output:["_col0","_col1","_col2","_col3","_col4"] + Merge Join Operator [MERGEJOIN_225] (rows=2 width=452) + Conds:(Inner),Output:["_col0","_col1","_col2","_col3","_col4"] + <-Reducer 13 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_278] + Group By Operator [GBY_277] (rows=2 width=228) + Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)"],keys:KEY._col0 + <-Reducer 12 [SIMPLE_EDGE] + SHUFFLE [RS_45] + PartitionCols:_col0 + Group By Operator [GBY_44] (rows=56 width=227) + Output:["_col0","_col1","_col2"],aggregations:["sum(_col2)","sum(_col3)"],keys:_col1 + Merge Join Operator [MERGEJOIN_212] (rows=31836679 width=222) + Conds:RS_276._col0=RS_242._col0(Inner),Output:["_col1","_col2","_col3"] + <-Map 8 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_242] + PartitionCols:_col0 + Select Operator [SEL_238] (rows=8116 width=4) + Output:["_col0"] + Filter Operator [FIL_237] (rows=8116 width=98) + predicate:CAST( d_date AS TIMESTAMP) BETWEEN TIMESTAMP'1998-08-04 00:00:00' AND TIMESTAMP'1998-09-03 00:00:00' + TableScan [TS_3] (rows=73049 width=98) + default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_date"] + <-Map 25 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_276] + PartitionCols:_col0 + Select Operator [SEL_275] (rows=286549727 width=231) + Output:["_col0","_col1","_col2","_col3"] + Filter Operator [FIL_274] (rows=286549727 width=231) + predicate:((cs_sold_date_sk BETWEEN DynamicValue(RS_41_date_dim_d_date_sk_min) AND DynamicValue(RS_41_date_dim_d_date_sk_max) and in_bloom_filter(cs_sold_date_sk, DynamicValue(RS_41_date_dim_d_date_sk_bloom_filter))) and cs_sold_date_sk is not null) + TableScan [TS_34] (rows=287989836 width=231) + default@catalog_sales,catalog_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["cs_sold_date_sk","cs_call_center_sk","cs_ext_sales_price","cs_net_profit"] + <-Reducer 15 [BROADCAST_EDGE] vectorized + BROADCAST [RS_273] + Group By Operator [GBY_272] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 8 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_252] + Group By Operator [GBY_249] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_243] (rows=8116 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_238] + <-Reducer 17 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_283] + Group By Operator [GBY_282] (rows=1 width=224) + Output:["_col0","_col1"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)"] + <-Reducer 16 [CUSTOM_SIMPLE_EDGE] + PARTITION_ONLY_SHUFFLE [RS_59] + Group By Operator [GBY_58] (rows=1 width=224) + Output:["_col0","_col1"],aggregations:["sum(_col1)","sum(_col2)"] + Merge Join Operator [MERGEJOIN_213] (rows=3199657 width=183) + Conds:RS_281._col0=RS_244._col0(Inner),Output:["_col1","_col2"] + <-Map 8 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_244] + PartitionCols:_col0 + Please refer to the previous Select Operator [SEL_238] + <-Map 26 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_281] + PartitionCols:_col0 + Select Operator [SEL_280] (rows=28798881 width=223) + Output:["_col0","_col1","_col2"] + Filter Operator [FIL_279] (rows=28798881 width=223) + predicate:cr_returned_date_sk is not null + TableScan [TS_48] (rows=28798881 width=223) + default@catalog_returns,catalog_returns,Tbl:COMPLETE,Col:COMPLETE,Output:["cr_returned_date_sk","cr_return_amount","cr_net_loss"] + <-Reducer 20 [CONTAINS] + Reduce Output Operator [RS_236] + PartitionCols:_col0, _col1, _col2 + Group By Operator [GBY_235] (rows=58 width=445) + Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col2)","sum(_col3)","sum(_col4)"],keys:_col0, _col1, 0L + Top N Key Operator [TNK_234] (rows=39 width=435) + keys:_col0, _col1, 0L,sort order:+++,top n:100 + Select Operator [SEL_232] (rows=25 width=435) + Output:["_col0","_col1","_col2","_col3","_col4"] + Merge Join Operator [MERGEJOIN_231] (rows=25 width=452) + Conds:RS_290._col0=RS_295._col0(Left Outer),Output:["_col0","_col1","_col2","_col4","_col5"] + <-Reducer 19 [ONE_TO_ONE_EDGE] vectorized + FORWARD [RS_290] + PartitionCols:_col0 + Group By Operator [GBY_289] (rows=24 width=228) + Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)"],keys:KEY._col0 + <-Reducer 18 [SIMPLE_EDGE] + SHUFFLE [RS_79] + PartitionCols:_col0 + Group By Operator [GBY_78] (rows=360 width=228) + Output:["_col0","_col1","_col2"],aggregations:["sum(_col1)","sum(_col2)"],keys:_col0 + Select Operator [SEL_76] (rows=15991254 width=227) + Output:["_col0","_col1","_col2"] + Merge Join Operator [MERGEJOIN_214] (rows=15991254 width=227) + Conds:RS_288._col0=RS_245._col0(Inner),Output:["_col1","_col2","_col3"] + <-Map 8 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_245] + PartitionCols:_col0 + Please refer to the previous Select Operator [SEL_238] + <-Map 27 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_288] + PartitionCols:_col0 + Select Operator [SEL_287] (rows=143931136 width=231) + Output:["_col0","_col1","_col2","_col3"] + Filter Operator [FIL_286] (rows=143931136 width=231) + predicate:((ws_sold_date_sk BETWEEN DynamicValue(RS_74_date_dim_d_date_sk_min) AND DynamicValue(RS_74_date_dim_d_date_sk_max) and in_bloom_filter(ws_sold_date_sk, DynamicValue(RS_74_date_dim_d_date_sk_bloom_filter))) and ws_sold_date_sk is not null and ws_web_page_sk is not null) + TableScan [TS_67] (rows=144002668 width=231) + default@web_sales,web_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ws_sold_date_sk","ws_web_page_sk","ws_ext_sales_price","ws_net_profit"] + <-Reducer 21 [BROADCAST_EDGE] vectorized + BROADCAST [RS_285] + Group By Operator [GBY_284] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 8 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_253] + Group By Operator [GBY_250] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_246] (rows=8116 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_238] + <-Reducer 23 [ONE_TO_ONE_EDGE] vectorized + FORWARD [RS_295] + PartitionCols:_col0 + Group By Operator [GBY_294] (rows=24 width=226) + Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)"],keys:KEY._col0 + <-Reducer 22 [SIMPLE_EDGE] + SHUFFLE [RS_94] + PartitionCols:_col0 + Group By Operator [GBY_93] (rows=24 width=226) + Output:["_col0","_col1","_col2"],aggregations:["sum(_col1)","sum(_col2)"],keys:_col0 + Select Operator [SEL_91] (rows=1458758 width=135) + Output:["_col0","_col1","_col2"] + Merge Join Operator [MERGEJOIN_215] (rows=1458758 width=135) + Conds:RS_293._col0=RS_247._col0(Inner),Output:["_col1","_col2","_col3"] + <-Map 8 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_247] + PartitionCols:_col0 + Please refer to the previous Select Operator [SEL_238] + <-Map 28 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_293] + PartitionCols:_col0 + Select Operator [SEL_292] (rows=13129719 width=221) + Output:["_col0","_col1","_col2","_col3"] + Filter Operator [FIL_291] (rows=13129719 width=221) + predicate:(wr_returned_date_sk is not null and wr_web_page_sk is not null) + TableScan [TS_82] (rows=14398467 width=221) + default@web_returns,web_returns,Tbl:COMPLETE,Col:COMPLETE,Output:["wr_returned_date_sk","wr_web_page_sk","wr_return_amt","wr_net_loss"] + <-Reducer 4 [CONTAINS] + Reduce Output Operator [RS_224] + PartitionCols:_col0, _col1, _col2 + Group By Operator [GBY_223] (rows=58 width=445) + Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col2)","sum(_col3)","sum(_col4)"],keys:_col0, _col1, 0L + Top N Key Operator [TNK_222] (rows=39 width=435) + keys:_col0, _col1, 0L,sort order:+++,top n:100 + Select Operator [SEL_220] (rows=12 width=436) + Output:["_col0","_col1","_col2","_col3","_col4"] + Merge Join Operator [MERGEJOIN_219] (rows=12 width=451) + Conds:RS_260._col0=RS_265._col0(Left Outer),Output:["_col0","_col1","_col2","_col4","_col5"] + <-Reducer 11 [ONE_TO_ONE_EDGE] vectorized + FORWARD [RS_265] + PartitionCols:_col0 + Group By Operator [GBY_264] (rows=11 width=226) + Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)"],keys:KEY._col0 + <-Reducer 10 [SIMPLE_EDGE] + SHUFFLE [RS_27] + PartitionCols:_col0 + Group By Operator [GBY_26] (rows=44 width=226) + Output:["_col0","_col1","_col2"],aggregations:["sum(_col1)","sum(_col2)"],keys:_col0 + Select Operator [SEL_24] (rows=5959021 width=156) + Output:["_col0","_col1","_col2"] + Merge Join Operator [MERGEJOIN_211] (rows=5959021 width=156) + Conds:RS_263._col0=RS_241._col0(Inner),Output:["_col1","_col2","_col3"] + <-Map 8 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_241] + PartitionCols:_col0 + Please refer to the previous Select Operator [SEL_238] + <-Map 24 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_263] + PartitionCols:_col0 + Select Operator [SEL_262] (rows=53634860 width=223) + Output:["_col0","_col1","_col2","_col3"] + Filter Operator [FIL_261] (rows=53634860 width=223) + predicate:(sr_returned_date_sk is not null and sr_store_sk is not null) + TableScan [TS_15] (rows=57591150 width=223) + default@store_returns,store_returns,Tbl:COMPLETE,Col:COMPLETE,Output:["sr_returned_date_sk","sr_store_sk","sr_return_amt","sr_net_loss"] + <-Reducer 3 [ONE_TO_ONE_EDGE] vectorized + FORWARD [RS_260] + PartitionCols:_col0 + Group By Operator [GBY_259] (rows=11 width=226) + Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)"],keys:KEY._col0 + <-Reducer 2 [SIMPLE_EDGE] + SHUFFLE [RS_12] + PartitionCols:_col0 + Group By Operator [GBY_11] (rows=341 width=226) + Output:["_col0","_col1","_col2"],aggregations:["sum(_col1)","sum(_col2)"],keys:_col0 + Select Operator [SEL_9] (rows=58365993 width=135) + Output:["_col0","_col1","_col2"] + Merge Join Operator [MERGEJOIN_210] (rows=58365993 width=135) + Conds:RS_258._col0=RS_239._col0(Inner),Output:["_col1","_col2","_col3"] + <-Map 8 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_239] + PartitionCols:_col0 + Please refer to the previous Select Operator [SEL_238] + <-Map 1 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_258] + PartitionCols:_col0 + Select Operator [SEL_257] (rows=525329897 width=221) + Output:["_col0","_col1","_col2","_col3"] + Filter Operator [FIL_256] (rows=525329897 width=221) + predicate:((ss_sold_date_sk BETWEEN DynamicValue(RS_7_date_dim_d_date_sk_min) AND DynamicValue(RS_7_date_dim_d_date_sk_max) and in_bloom_filter(ss_sold_date_sk, DynamicValue(RS_7_date_dim_d_date_sk_bloom_filter))) and ss_sold_date_sk is not null and ss_store_sk is not null) + TableScan [TS_0] (rows=575995635 width=221) + default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_sold_date_sk","ss_store_sk","ss_ext_sales_price","ss_net_profit"] + <-Reducer 9 [BROADCAST_EDGE] vectorized + BROADCAST [RS_255] + Group By Operator [GBY_254] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 8 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_251] + Group By Operator [GBY_248] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_240] (rows=8116 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_238] +
http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/query78.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query78.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query78.q.out new file mode 100644 index 0000000..ef4d6db --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query78.q.out @@ -0,0 +1,345 @@ +PREHOOK: query: explain +with ws as + (select d_year AS ws_sold_year, ws_item_sk, + ws_bill_customer_sk ws_customer_sk, + sum(ws_quantity) ws_qty, + sum(ws_wholesale_cost) ws_wc, + sum(ws_sales_price) ws_sp + from web_sales + left join web_returns on wr_order_number=ws_order_number and ws_item_sk=wr_item_sk + join date_dim on ws_sold_date_sk = d_date_sk + where wr_order_number is null + group by d_year, ws_item_sk, ws_bill_customer_sk + ), +cs as + (select d_year AS cs_sold_year, cs_item_sk, + cs_bill_customer_sk cs_customer_sk, + sum(cs_quantity) cs_qty, + sum(cs_wholesale_cost) cs_wc, + sum(cs_sales_price) cs_sp + from catalog_sales + left join catalog_returns on cr_order_number=cs_order_number and cs_item_sk=cr_item_sk + join date_dim on cs_sold_date_sk = d_date_sk + where cr_order_number is null + group by d_year, cs_item_sk, cs_bill_customer_sk + ), +ss as + (select d_year AS ss_sold_year, ss_item_sk, + ss_customer_sk, + sum(ss_quantity) ss_qty, + sum(ss_wholesale_cost) ss_wc, + sum(ss_sales_price) ss_sp + from store_sales + left join store_returns on sr_ticket_number=ss_ticket_number and ss_item_sk=sr_item_sk + join date_dim on ss_sold_date_sk = d_date_sk + where sr_ticket_number is null + group by d_year, ss_item_sk, ss_customer_sk + ) + select +ss_sold_year, ss_item_sk, ss_customer_sk, +round(ss_qty/(coalesce(ws_qty+cs_qty,1)),2) ratio, +ss_qty store_qty, ss_wc store_wholesale_cost, ss_sp store_sales_price, +coalesce(ws_qty,0)+coalesce(cs_qty,0) other_chan_qty, +coalesce(ws_wc,0)+coalesce(cs_wc,0) other_chan_wholesale_cost, +coalesce(ws_sp,0)+coalesce(cs_sp,0) other_chan_sales_price +from ss +left join ws on (ws_sold_year=ss_sold_year and ws_item_sk=ss_item_sk and ws_customer_sk=ss_customer_sk) +left join cs on (cs_sold_year=ss_sold_year and cs_item_sk=cs_item_sk and cs_customer_sk=ss_customer_sk) +where coalesce(ws_qty,0)>0 and coalesce(cs_qty, 0)>0 and ss_sold_year=2000 +order by + ss_sold_year, ss_item_sk, ss_customer_sk, + ss_qty desc, ss_wc desc, ss_sp desc, + other_chan_qty, + other_chan_wholesale_cost, + other_chan_sales_price, + round(ss_qty/(coalesce(ws_qty+cs_qty,1)),2) +limit 100 +PREHOOK: type: QUERY +PREHOOK: Input: default@catalog_returns +PREHOOK: Input: default@catalog_sales +PREHOOK: Input: default@date_dim +PREHOOK: Input: default@store_returns +PREHOOK: Input: default@store_sales +PREHOOK: Input: default@web_returns +PREHOOK: Input: default@web_sales +PREHOOK: Output: hdfs://### HDFS PATH ### +POSTHOOK: query: explain +with ws as + (select d_year AS ws_sold_year, ws_item_sk, + ws_bill_customer_sk ws_customer_sk, + sum(ws_quantity) ws_qty, + sum(ws_wholesale_cost) ws_wc, + sum(ws_sales_price) ws_sp + from web_sales + left join web_returns on wr_order_number=ws_order_number and ws_item_sk=wr_item_sk + join date_dim on ws_sold_date_sk = d_date_sk + where wr_order_number is null + group by d_year, ws_item_sk, ws_bill_customer_sk + ), +cs as + (select d_year AS cs_sold_year, cs_item_sk, + cs_bill_customer_sk cs_customer_sk, + sum(cs_quantity) cs_qty, + sum(cs_wholesale_cost) cs_wc, + sum(cs_sales_price) cs_sp + from catalog_sales + left join catalog_returns on cr_order_number=cs_order_number and cs_item_sk=cr_item_sk + join date_dim on cs_sold_date_sk = d_date_sk + where cr_order_number is null + group by d_year, cs_item_sk, cs_bill_customer_sk + ), +ss as + (select d_year AS ss_sold_year, ss_item_sk, + ss_customer_sk, + sum(ss_quantity) ss_qty, + sum(ss_wholesale_cost) ss_wc, + sum(ss_sales_price) ss_sp + from store_sales + left join store_returns on sr_ticket_number=ss_ticket_number and ss_item_sk=sr_item_sk + join date_dim on ss_sold_date_sk = d_date_sk + where sr_ticket_number is null + group by d_year, ss_item_sk, ss_customer_sk + ) + select +ss_sold_year, ss_item_sk, ss_customer_sk, +round(ss_qty/(coalesce(ws_qty+cs_qty,1)),2) ratio, +ss_qty store_qty, ss_wc store_wholesale_cost, ss_sp store_sales_price, +coalesce(ws_qty,0)+coalesce(cs_qty,0) other_chan_qty, +coalesce(ws_wc,0)+coalesce(cs_wc,0) other_chan_wholesale_cost, +coalesce(ws_sp,0)+coalesce(cs_sp,0) other_chan_sales_price +from ss +left join ws on (ws_sold_year=ss_sold_year and ws_item_sk=ss_item_sk and ws_customer_sk=ss_customer_sk) +left join cs on (cs_sold_year=ss_sold_year and cs_item_sk=cs_item_sk and cs_customer_sk=ss_customer_sk) +where coalesce(ws_qty,0)>0 and coalesce(cs_qty, 0)>0 and ss_sold_year=2000 +order by + ss_sold_year, ss_item_sk, ss_customer_sk, + ss_qty desc, ss_wc desc, ss_sp desc, + other_chan_qty, + other_chan_wholesale_cost, + other_chan_sales_price, + round(ss_qty/(coalesce(ws_qty+cs_qty,1)),2) +limit 100 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@catalog_returns +POSTHOOK: Input: default@catalog_sales +POSTHOOK: Input: default@date_dim +POSTHOOK: Input: default@store_returns +POSTHOOK: Input: default@store_sales +POSTHOOK: Input: default@web_returns +POSTHOOK: Input: default@web_sales +POSTHOOK: Output: hdfs://### HDFS PATH ### +Plan optimized by CBO. + +Vertex dependency in root stage +Map 14 <- Reducer 7 (BROADCAST_EDGE) +Map 17 <- Reducer 10 (BROADCAST_EDGE) +Map 20 <- Reducer 13 (BROADCAST_EDGE) +Reducer 10 <- Map 1 (CUSTOM_SIMPLE_EDGE) +Reducer 11 <- Map 1 (SIMPLE_EDGE), Reducer 21 (SIMPLE_EDGE) +Reducer 12 <- Reducer 11 (SIMPLE_EDGE) +Reducer 13 <- Map 1 (CUSTOM_SIMPLE_EDGE) +Reducer 15 <- Map 14 (SIMPLE_EDGE), Map 16 (SIMPLE_EDGE) +Reducer 18 <- Map 17 (SIMPLE_EDGE), Map 19 (SIMPLE_EDGE) +Reducer 2 <- Map 1 (SIMPLE_EDGE), Reducer 15 (SIMPLE_EDGE) +Reducer 21 <- Map 20 (SIMPLE_EDGE), Map 22 (SIMPLE_EDGE) +Reducer 3 <- Reducer 2 (SIMPLE_EDGE) +Reducer 4 <- Reducer 3 (ONE_TO_ONE_EDGE), Reducer 9 (ONE_TO_ONE_EDGE) +Reducer 5 <- Reducer 12 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE) +Reducer 6 <- Reducer 5 (SIMPLE_EDGE) +Reducer 7 <- Map 1 (CUSTOM_SIMPLE_EDGE) +Reducer 8 <- Map 1 (SIMPLE_EDGE), Reducer 18 (SIMPLE_EDGE) +Reducer 9 <- Reducer 8 (SIMPLE_EDGE) + +Stage-0 + Fetch Operator + limit:100 + Stage-1 + Reducer 6 vectorized + File Output Operator [FS_238] + Limit [LIM_237] (rows=100 width=484) + Number of rows:100 + Select Operator [SEL_236] (rows=462576000046 width=483) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9"] + <-Reducer 5 [SIMPLE_EDGE] + SHUFFLE [RS_73] + Select Operator [SEL_72] (rows=462576000046 width=719) + Output:["_col0","_col1","_col6","_col7","_col8","_col9","_col10","_col11","_col12"] + Filter Operator [FIL_71] (rows=462576000046 width=702) + predicate:CASE WHEN (_col11 is not null) THEN ((_col11 > 0L)) ELSE (false) END + Merge Join Operator [MERGEJOIN_191] (rows=925152000093 width=702) + Conds:RS_68._col1=RS_235._col0(Left Outer),Output:["_col0","_col1","_col2","_col3","_col4","_col7","_col8","_col9","_col11","_col12","_col13"] + <-Reducer 12 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_235] + PartitionCols:_col0 + Select Operator [SEL_234] (rows=101592102 width=235) + Output:["_col0","_col1","_col2","_col3"] + Group By Operator [GBY_233] (rows=101592102 width=239) + Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)"],keys:KEY._col0, KEY._col1 + <-Reducer 11 [SIMPLE_EDGE] + SHUFFLE [RS_65] + PartitionCols:_col0, _col1 + Group By Operator [GBY_64] (rows=101592102 width=239) + Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(_col4)","sum(_col5)","sum(_col6)"],keys:_col2, _col3 + Merge Join Operator [MERGEJOIN_189] (rows=101592102 width=233) + Conds:RS_198._col0=RS_61._col0(Inner),Output:["_col2","_col3","_col4","_col5","_col6"] + <-Map 1 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_198] + PartitionCols:_col0 + Select Operator [SEL_193] (rows=652 width=4) + Output:["_col0"] + Filter Operator [FIL_192] (rows=652 width=8) + predicate:(d_year = 2000) + TableScan [TS_0] (rows=73049 width=8) + default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_year"] + <-Reducer 21 [SIMPLE_EDGE] + SHUFFLE [RS_61] + PartitionCols:_col0 + Select Operator [SEL_59] (rows=286549727 width=239) + Output:["_col0","_col1","_col2","_col3","_col4","_col5"] + Filter Operator [FIL_58] (rows=286549727 width=240) + predicate:_col8 is null + Merge Join Operator [MERGEJOIN_188] (rows=468719906 width=240) + Conds:RS_230._col2, _col3=RS_232._col0, _col1(Left Outer),Output:["_col0","_col1","_col2","_col4","_col5","_col6","_col8"] + <-Map 20 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_230] + PartitionCols:_col2, _col3 + Select Operator [SEL_229] (rows=286549727 width=242) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"] + Filter Operator [FIL_228] (rows=286549727 width=242) + predicate:((cs_sold_date_sk BETWEEN DynamicValue(RS_60_date_dim_d_date_sk_min) AND DynamicValue(RS_60_date_dim_d_date_sk_max) and in_bloom_filter(cs_sold_date_sk, DynamicValue(RS_60_date_dim_d_date_sk_bloom_filter))) and cs_sold_date_sk is not null) + TableScan [TS_50] (rows=287989836 width=242) + default@catalog_sales,catalog_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["cs_sold_date_sk","cs_bill_customer_sk","cs_item_sk","cs_order_number","cs_quantity","cs_wholesale_cost","cs_sales_price"] + <-Reducer 13 [BROADCAST_EDGE] vectorized + BROADCAST [RS_227] + Group By Operator [GBY_226] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 1 [CUSTOM_SIMPLE_EDGE] vectorized + SHUFFLE [RS_205] + Group By Operator [GBY_202] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_199] (rows=652 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_193] + <-Map 22 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_232] + PartitionCols:_col0, _col1 + Select Operator [SEL_231] (rows=28798881 width=8) + Output:["_col0","_col1"] + TableScan [TS_53] (rows=28798881 width=8) + default@catalog_returns,catalog_returns,Tbl:COMPLETE,Col:COMPLETE,Output:["cr_item_sk","cr_order_number"] + <-Reducer 4 [SIMPLE_EDGE] + SHUFFLE [RS_68] + PartitionCols:_col1 + Filter Operator [FIL_45] (rows=4580227799 width=471) + predicate:CASE WHEN (_col7 is not null) THEN ((_col7 > 0L)) ELSE (false) END + Merge Join Operator [MERGEJOIN_190] (rows=9160455599 width=471) + Conds:RS_215._col1, _col0=RS_225._col1, _col0(Left Outer),Output:["_col0","_col1","_col2","_col3","_col4","_col7","_col8","_col9"] + <-Reducer 3 [ONE_TO_ONE_EDGE] vectorized + FORWARD [RS_215] + PartitionCols:_col1, _col0 + Select Operator [SEL_214] (rows=114214965 width=239) + Output:["_col0","_col1","_col2","_col3","_col4"] + Group By Operator [GBY_213] (rows=114214965 width=239) + Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)"],keys:KEY._col0, KEY._col1 + <-Reducer 2 [SIMPLE_EDGE] + SHUFFLE [RS_18] + PartitionCols:_col0, _col1 + Group By Operator [GBY_17] (rows=114214965 width=239) + Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(_col4)","sum(_col5)","sum(_col6)"],keys:_col3, _col2 + Merge Join Operator [MERGEJOIN_185] (rows=114214965 width=217) + Conds:RS_194._col0=RS_14._col0(Inner),Output:["_col2","_col3","_col4","_col5","_col6"] + <-Map 1 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_194] + PartitionCols:_col0 + Please refer to the previous Select Operator [SEL_193] + <-Reducer 15 [SIMPLE_EDGE] + SHUFFLE [RS_14] + PartitionCols:_col0 + Select Operator [SEL_12] (rows=319876350 width=233) + Output:["_col0","_col1","_col2","_col3","_col4","_col5"] + Filter Operator [FIL_11] (rows=319876350 width=235) + predicate:_col8 is null + Merge Join Operator [MERGEJOIN_184] (rows=883006376 width=235) + Conds:RS_210._col1, _col3=RS_212._col0, _col1(Left Outer),Output:["_col0","_col1","_col2","_col4","_col5","_col6","_col8"] + <-Map 14 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_210] + PartitionCols:_col1, _col3 + Select Operator [SEL_209] (rows=550076554 width=233) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"] + Filter Operator [FIL_208] (rows=550076554 width=233) + predicate:((ss_sold_date_sk BETWEEN DynamicValue(RS_13_date_dim_d_date_sk_min) AND DynamicValue(RS_13_date_dim_d_date_sk_max) and in_bloom_filter(ss_sold_date_sk, DynamicValue(RS_13_date_dim_d_date_sk_bloom_filter))) and ss_sold_date_sk is not null) + TableScan [TS_3] (rows=575995635 width=233) + default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_sold_date_sk","ss_item_sk","ss_customer_sk","ss_ticket_number","ss_quantity","ss_wholesale_cost","ss_sales_price"] + <-Reducer 7 [BROADCAST_EDGE] vectorized + BROADCAST [RS_207] + Group By Operator [GBY_206] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 1 [CUSTOM_SIMPLE_EDGE] vectorized + SHUFFLE [RS_203] + Group By Operator [GBY_200] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_195] (rows=652 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_193] + <-Map 16 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_212] + PartitionCols:_col0, _col1 + Select Operator [SEL_211] (rows=57591150 width=8) + Output:["_col0","_col1"] + TableScan [TS_6] (rows=57591150 width=8) + default@store_returns,store_returns,Tbl:COMPLETE,Col:COMPLETE,Output:["sr_item_sk","sr_ticket_number"] + <-Reducer 9 [ONE_TO_ONE_EDGE] vectorized + FORWARD [RS_225] + PartitionCols:_col1, _col0 + Select Operator [SEL_224] (rows=40539971 width=239) + Output:["_col0","_col1","_col2","_col3","_col4"] + Group By Operator [GBY_223] (rows=40539971 width=239) + Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)"],keys:KEY._col0, KEY._col1 + <-Reducer 8 [SIMPLE_EDGE] + SHUFFLE [RS_39] + PartitionCols:_col0, _col1 + Group By Operator [GBY_38] (rows=40539971 width=239) + Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(_col4)","sum(_col5)","sum(_col6)"],keys:_col3, _col2 + Merge Join Operator [MERGEJOIN_187] (rows=40539971 width=235) + Conds:RS_196._col0=RS_35._col0(Inner),Output:["_col2","_col3","_col4","_col5","_col6"] + <-Map 1 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_196] + PartitionCols:_col0 + Please refer to the previous Select Operator [SEL_193] + <-Reducer 18 [SIMPLE_EDGE] + SHUFFLE [RS_35] + PartitionCols:_col0 + Select Operator [SEL_33] (rows=113538342 width=239) + Output:["_col0","_col1","_col2","_col3","_col4","_col5"] + Filter Operator [FIL_32] (rows=113538342 width=242) + predicate:_col8 is null + Merge Join Operator [MERGEJOIN_186] (rows=254679677 width=242) + Conds:RS_220._col1, _col3=RS_222._col0, _col1(Left Outer),Output:["_col0","_col1","_col2","_col4","_col5","_col6","_col8"] + <-Map 17 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_220] + PartitionCols:_col1, _col3 + Select Operator [SEL_219] (rows=143966864 width=243) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"] + Filter Operator [FIL_218] (rows=143966864 width=243) + predicate:((ws_sold_date_sk BETWEEN DynamicValue(RS_34_date_dim_d_date_sk_min) AND DynamicValue(RS_34_date_dim_d_date_sk_max) and in_bloom_filter(ws_sold_date_sk, DynamicValue(RS_34_date_dim_d_date_sk_bloom_filter))) and ws_sold_date_sk is not null) + TableScan [TS_24] (rows=144002668 width=243) + default@web_sales,web_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ws_sold_date_sk","ws_item_sk","ws_bill_customer_sk","ws_order_number","ws_quantity","ws_wholesale_cost","ws_sales_price"] + <-Reducer 10 [BROADCAST_EDGE] vectorized + BROADCAST [RS_217] + Group By Operator [GBY_216] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 1 [CUSTOM_SIMPLE_EDGE] vectorized + SHUFFLE [RS_204] + Group By Operator [GBY_201] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_197] (rows=652 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_193] + <-Map 19 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_222] + PartitionCols:_col0, _col1 + Select Operator [SEL_221] (rows=14398467 width=8) + Output:["_col0","_col1"] + TableScan [TS_27] (rows=14398467 width=8) + default@web_returns,web_returns,Tbl:COMPLETE,Col:COMPLETE,Output:["wr_item_sk","wr_order_number"] + http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/query79.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query79.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query79.q.out new file mode 100644 index 0000000..fb94428 --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query79.q.out @@ -0,0 +1,175 @@ +PREHOOK: query: explain +select + c_last_name,c_first_name,substr(s_city,1,30),ss_ticket_number,amt,profit + from + (select ss_ticket_number + ,ss_customer_sk + ,store.s_city + ,sum(ss_coupon_amt) amt + ,sum(ss_net_profit) profit + from store_sales,date_dim,store,household_demographics + where store_sales.ss_sold_date_sk = date_dim.d_date_sk + and store_sales.ss_store_sk = store.s_store_sk + and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk + and (household_demographics.hd_dep_count = 8 or household_demographics.hd_vehicle_count > 0) + and date_dim.d_dow = 1 + and date_dim.d_year in (1998,1998+1,1998+2) + and store.s_number_employees between 200 and 295 + group by ss_ticket_number,ss_customer_sk,ss_addr_sk,store.s_city) ms,customer + where ss_customer_sk = c_customer_sk + order by c_last_name,c_first_name,substr(s_city,1,30), profit +limit 100 +PREHOOK: type: QUERY +PREHOOK: Input: default@customer +PREHOOK: Input: default@date_dim +PREHOOK: Input: default@household_demographics +PREHOOK: Input: default@store +PREHOOK: Input: default@store_sales +PREHOOK: Output: hdfs://### HDFS PATH ### +POSTHOOK: query: explain +select + c_last_name,c_first_name,substr(s_city,1,30),ss_ticket_number,amt,profit + from + (select ss_ticket_number + ,ss_customer_sk + ,store.s_city + ,sum(ss_coupon_amt) amt + ,sum(ss_net_profit) profit + from store_sales,date_dim,store,household_demographics + where store_sales.ss_sold_date_sk = date_dim.d_date_sk + and store_sales.ss_store_sk = store.s_store_sk + and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk + and (household_demographics.hd_dep_count = 8 or household_demographics.hd_vehicle_count > 0) + and date_dim.d_dow = 1 + and date_dim.d_year in (1998,1998+1,1998+2) + and store.s_number_employees between 200 and 295 + group by ss_ticket_number,ss_customer_sk,ss_addr_sk,store.s_city) ms,customer + where ss_customer_sk = c_customer_sk + order by c_last_name,c_first_name,substr(s_city,1,30), profit +limit 100 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@customer +POSTHOOK: Input: default@date_dim +POSTHOOK: Input: default@household_demographics +POSTHOOK: Input: default@store +POSTHOOK: Input: default@store_sales +POSTHOOK: Output: hdfs://### HDFS PATH ### +Plan optimized by CBO. + +Vertex dependency in root stage +Map 4 <- Reducer 10 (BROADCAST_EDGE), Reducer 13 (BROADCAST_EDGE) +Reducer 10 <- Map 9 (CUSTOM_SIMPLE_EDGE) +Reducer 13 <- Map 12 (CUSTOM_SIMPLE_EDGE) +Reducer 2 <- Map 1 (SIMPLE_EDGE), Reducer 8 (SIMPLE_EDGE) +Reducer 3 <- Reducer 2 (SIMPLE_EDGE) +Reducer 5 <- Map 4 (SIMPLE_EDGE), Map 9 (SIMPLE_EDGE) +Reducer 6 <- Map 11 (SIMPLE_EDGE), Reducer 5 (SIMPLE_EDGE) +Reducer 7 <- Map 12 (SIMPLE_EDGE), Reducer 6 (SIMPLE_EDGE) +Reducer 8 <- Reducer 7 (SIMPLE_EDGE) + +Stage-0 + Fetch Operator + limit:-1 + Stage-1 + Reducer 3 vectorized + File Output Operator [FS_129] + Limit [LIM_128] (rows=100 width=776) + Number of rows:100 + Select Operator [SEL_127] (rows=43530621 width=776) + Output:["_col0","_col1","_col2","_col3","_col4","_col5"] + <-Reducer 2 [SIMPLE_EDGE] + SHUFFLE [RS_32] + Select Operator [SEL_31] (rows=43530621 width=776) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"] + Merge Join Operator [MERGEJOIN_99] (rows=43530621 width=685) + Conds:RS_101._col0=RS_126._col1(Inner),Output:["_col1","_col2","_col3","_col5","_col6","_col7","_col8"] + <-Map 1 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_101] + PartitionCols:_col0 + Select Operator [SEL_100] (rows=80000000 width=184) + Output:["_col0","_col1","_col2"] + TableScan [TS_0] (rows=80000000 width=184) + default@customer,customer,Tbl:COMPLETE,Col:COMPLETE,Output:["c_customer_sk","c_first_name","c_last_name"] + <-Reducer 8 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_126] + PartitionCols:_col1 + Select Operator [SEL_125] (rows=43530621 width=507) + Output:["_col0","_col1","_col2","_col3","_col4","_col5"] + Group By Operator [GBY_124] (rows=43530621 width=325) + Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)"],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3 + <-Reducer 7 [SIMPLE_EDGE] + SHUFFLE [RS_25] + PartitionCols:_col0, _col1, _col2, _col3 + Group By Operator [GBY_24] (rows=43530621 width=325) + Output:["_col0","_col1","_col2","_col3","_col4","_col5"],aggregations:["sum(_col6)","sum(_col7)"],keys:_col1, _col3, _col5, _col10 + Merge Join Operator [MERGEJOIN_98] (rows=43530621 width=214) + Conds:RS_20._col2=RS_112._col0(Inner),Output:["_col1","_col3","_col5","_col6","_col7","_col10"] + <-Map 12 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_112] + PartitionCols:_col0 + Select Operator [SEL_111] (rows=3055 width=4) + Output:["_col0"] + Filter Operator [FIL_110] (rows=3055 width=12) + predicate:((hd_dep_count = 8) or (hd_vehicle_count > 0)) + TableScan [TS_11] (rows=7200 width=12) + default@household_demographics,household_demographics,Tbl:COMPLETE,Col:COMPLETE,Output:["hd_demo_sk","hd_dep_count","hd_vehicle_count"] + <-Reducer 6 [SIMPLE_EDGE] + SHUFFLE [RS_20] + PartitionCols:_col2 + Merge Join Operator [MERGEJOIN_97] (rows=102592623 width=283) + Conds:RS_17._col4=RS_123._col0(Inner),Output:["_col1","_col2","_col3","_col5","_col6","_col7","_col10"] + <-Map 11 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_123] + PartitionCols:_col0 + Select Operator [SEL_122] (rows=1704 width=97) + Output:["_col0","_col1"] + Filter Operator [FIL_121] (rows=1704 width=100) + predicate:s_number_employees BETWEEN 200 AND 295 + TableScan [TS_8] (rows=1704 width=100) + default@store,store,Tbl:COMPLETE,Col:COMPLETE,Output:["s_store_sk","s_number_employees","s_city"] + <-Reducer 5 [SIMPLE_EDGE] + SHUFFLE [RS_17] + PartitionCols:_col4 + Merge Join Operator [MERGEJOIN_96] (rows=102592623 width=193) + Conds:RS_120._col0=RS_104._col0(Inner),Output:["_col1","_col2","_col3","_col4","_col5","_col6","_col7"] + <-Map 9 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_104] + PartitionCols:_col0 + Select Operator [SEL_103] (rows=391 width=4) + Output:["_col0"] + Filter Operator [FIL_102] (rows=391 width=12) + predicate:((d_dow = 1) and (d_year) IN (1998, 1999, 2000)) + TableScan [TS_5] (rows=73049 width=12) + default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_year","d_dow"] + <-Map 4 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_120] + PartitionCols:_col0 + Select Operator [SEL_119] (rows=479121995 width=237) + Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"] + Filter Operator [FIL_118] (rows=479121995 width=237) + predicate:((ss_hdemo_sk BETWEEN DynamicValue(RS_21_household_demographics_hd_demo_sk_min) AND DynamicValue(RS_21_household_demographics_hd_demo_sk_max) and in_bloom_filter(ss_hdemo_sk, DynamicValue(RS_21_household_demographics_hd_demo_sk_bloom_filter))) and (ss_sold_date_sk BETWEEN DynamicValue(RS_15_date_dim_d_date_sk_min) AND DynamicValue(RS_15_date_dim_d_date_sk_max) and in_bloom_filter(ss_sold_date_sk, DynamicValue(RS_15_date_dim_d_date_sk_bloom_filter))) and ss_customer_sk is not null and ss_hdemo_sk is not null and ss_sold_date_sk is not null and ss_store_sk is not null) + TableScan [TS_2] (rows=575995635 width=237) + default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_sold_date_sk","ss_customer_sk","ss_hdemo_sk","ss_addr_sk","ss_store_sk","ss_ticket_number","ss_coupon_amt","ss_net_profit"] + <-Reducer 10 [BROADCAST_EDGE] vectorized + BROADCAST [RS_109] + Group By Operator [GBY_108] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 9 [CUSTOM_SIMPLE_EDGE] vectorized + SHUFFLE [RS_107] + Group By Operator [GBY_106] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_105] (rows=391 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_103] + <-Reducer 13 [BROADCAST_EDGE] vectorized + BROADCAST [RS_117] + Group By Operator [GBY_116] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 12 [CUSTOM_SIMPLE_EDGE] vectorized + SHUFFLE [RS_115] + Group By Operator [GBY_114] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_113] (rows=3055 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_111] +
