http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/query58.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/query58.q.out b/ql/src/test/results/clientpositive/perf/query58.q.out deleted file mode 100644 index d5ef23b..0000000 --- a/ql/src/test/results/clientpositive/perf/query58.q.out +++ /dev/null @@ -1,440 +0,0 @@ -Warning: Shuffle Join MERGEJOIN[265][tables = [$hdt$_4, $hdt$_5]] in Stage 'Reducer 20' is a cross product -Warning: Shuffle Join MERGEJOIN[269][tables = [$hdt$_5, $hdt$_6]] in Stage 'Reducer 24' is a cross product -Warning: Shuffle Join MERGEJOIN[273][tables = [$hdt$_6, $hdt$_7]] in Stage 'Reducer 28' is a cross product -PREHOOK: query: explain -with ss_items as - (select i_item_id item_id - ,sum(ss_ext_sales_price) ss_item_rev - from store_sales - ,item - ,date_dim - where ss_item_sk = i_item_sk - and d_date in (select d_date - from date_dim - where d_week_seq = (select d_week_seq - from date_dim - where d_date = '1998-02-19')) - and ss_sold_date_sk = d_date_sk - group by i_item_id), - cs_items as - (select i_item_id item_id - ,sum(cs_ext_sales_price) cs_item_rev - from catalog_sales - ,item - ,date_dim - where cs_item_sk = i_item_sk - and d_date in (select d_date - from date_dim - where d_week_seq = (select d_week_seq - from date_dim - where d_date = '1998-02-19')) - and cs_sold_date_sk = d_date_sk - group by i_item_id), - ws_items as - (select i_item_id item_id - ,sum(ws_ext_sales_price) ws_item_rev - from web_sales - ,item - ,date_dim - where ws_item_sk = i_item_sk - and d_date in (select d_date - from date_dim - where d_week_seq =(select d_week_seq - from date_dim - where d_date = '1998-02-19')) - and ws_sold_date_sk = d_date_sk - group by i_item_id) - select ss_items.item_id - ,ss_item_rev - ,ss_item_rev/(ss_item_rev+cs_item_rev+ws_item_rev)/3 * 100 ss_dev - ,cs_item_rev - ,cs_item_rev/(ss_item_rev+cs_item_rev+ws_item_rev)/3 * 100 cs_dev - ,ws_item_rev - ,ws_item_rev/(ss_item_rev+cs_item_rev+ws_item_rev)/3 * 100 ws_dev - ,(ss_item_rev+cs_item_rev+ws_item_rev)/3 average - from ss_items,cs_items,ws_items - where ss_items.item_id=cs_items.item_id - and ss_items.item_id=ws_items.item_id - and ss_item_rev between 0.9 * cs_item_rev and 1.1 * cs_item_rev - and ss_item_rev between 0.9 * ws_item_rev and 1.1 * ws_item_rev - and cs_item_rev between 0.9 * ss_item_rev and 1.1 * ss_item_rev - and cs_item_rev between 0.9 * ws_item_rev and 1.1 * ws_item_rev - and ws_item_rev between 0.9 * ss_item_rev and 1.1 * ss_item_rev - and ws_item_rev between 0.9 * cs_item_rev and 1.1 * cs_item_rev - order by item_id - ,ss_item_rev - limit 100 -PREHOOK: type: QUERY -POSTHOOK: query: explain -with ss_items as - (select i_item_id item_id - ,sum(ss_ext_sales_price) ss_item_rev - from store_sales - ,item - ,date_dim - where ss_item_sk = i_item_sk - and d_date in (select d_date - from date_dim - where d_week_seq = (select d_week_seq - from date_dim - where d_date = '1998-02-19')) - and ss_sold_date_sk = d_date_sk - group by i_item_id), - cs_items as - (select i_item_id item_id - ,sum(cs_ext_sales_price) cs_item_rev - from catalog_sales - ,item - ,date_dim - where cs_item_sk = i_item_sk - and d_date in (select d_date - from date_dim - where d_week_seq = (select d_week_seq - from date_dim - where d_date = '1998-02-19')) - and cs_sold_date_sk = d_date_sk - group by i_item_id), - ws_items as - (select i_item_id item_id - ,sum(ws_ext_sales_price) ws_item_rev - from web_sales - ,item - ,date_dim - where ws_item_sk = i_item_sk - and d_date in (select d_date - from date_dim - where d_week_seq =(select d_week_seq - from date_dim - where d_date = '1998-02-19')) - and ws_sold_date_sk = d_date_sk - group by i_item_id) - select ss_items.item_id - ,ss_item_rev - ,ss_item_rev/(ss_item_rev+cs_item_rev+ws_item_rev)/3 * 100 ss_dev - ,cs_item_rev - ,cs_item_rev/(ss_item_rev+cs_item_rev+ws_item_rev)/3 * 100 cs_dev - ,ws_item_rev - ,ws_item_rev/(ss_item_rev+cs_item_rev+ws_item_rev)/3 * 100 ws_dev - ,(ss_item_rev+cs_item_rev+ws_item_rev)/3 average - from ss_items,cs_items,ws_items - where ss_items.item_id=cs_items.item_id - and ss_items.item_id=ws_items.item_id - and ss_item_rev between 0.9 * cs_item_rev and 1.1 * cs_item_rev - and ss_item_rev between 0.9 * ws_item_rev and 1.1 * ws_item_rev - and cs_item_rev between 0.9 * ss_item_rev and 1.1 * ss_item_rev - and cs_item_rev between 0.9 * ws_item_rev and 1.1 * ws_item_rev - and ws_item_rev between 0.9 * ss_item_rev and 1.1 * ss_item_rev - and ws_item_rev between 0.9 * cs_item_rev and 1.1 * cs_item_rev - order by item_id - ,ss_item_rev - limit 100 -POSTHOOK: type: QUERY -Plan optimized by CBO. - -Vertex dependency in root stage -Reducer 10 <- Reducer 9 (SIMPLE_EDGE) -Reducer 11 <- Map 33 (SIMPLE_EDGE), Map 7 (SIMPLE_EDGE) -Reducer 12 <- Reducer 11 (SIMPLE_EDGE), Reducer 17 (SIMPLE_EDGE) -Reducer 13 <- Reducer 12 (SIMPLE_EDGE) -Reducer 15 <- Map 14 (SIMPLE_EDGE), Reducer 22 (ONE_TO_ONE_EDGE) -Reducer 16 <- Map 14 (SIMPLE_EDGE), Reducer 26 (ONE_TO_ONE_EDGE) -Reducer 17 <- Map 14 (SIMPLE_EDGE), Reducer 30 (ONE_TO_ONE_EDGE) -Reducer 19 <- Map 18 (CUSTOM_SIMPLE_EDGE) -Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 7 (SIMPLE_EDGE) -Reducer 20 <- Map 31 (CUSTOM_SIMPLE_EDGE), Reducer 19 (CUSTOM_SIMPLE_EDGE) -Reducer 21 <- Map 31 (SIMPLE_EDGE), Reducer 20 (SIMPLE_EDGE) -Reducer 22 <- Reducer 21 (SIMPLE_EDGE) -Reducer 23 <- Map 18 (CUSTOM_SIMPLE_EDGE) -Reducer 24 <- Map 31 (CUSTOM_SIMPLE_EDGE), Reducer 23 (CUSTOM_SIMPLE_EDGE) -Reducer 25 <- Map 31 (SIMPLE_EDGE), Reducer 24 (SIMPLE_EDGE) -Reducer 26 <- Reducer 25 (SIMPLE_EDGE) -Reducer 27 <- Map 18 (CUSTOM_SIMPLE_EDGE) -Reducer 28 <- Map 31 (CUSTOM_SIMPLE_EDGE), Reducer 27 (CUSTOM_SIMPLE_EDGE) -Reducer 29 <- Map 31 (SIMPLE_EDGE), Reducer 28 (SIMPLE_EDGE) -Reducer 3 <- Reducer 15 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) -Reducer 30 <- Reducer 29 (SIMPLE_EDGE) -Reducer 4 <- Reducer 3 (SIMPLE_EDGE) -Reducer 5 <- Reducer 10 (ONE_TO_ONE_EDGE), Reducer 13 (ONE_TO_ONE_EDGE), Reducer 4 (ONE_TO_ONE_EDGE) -Reducer 6 <- Reducer 5 (SIMPLE_EDGE) -Reducer 8 <- Map 32 (SIMPLE_EDGE), Map 7 (SIMPLE_EDGE) -Reducer 9 <- Reducer 16 (SIMPLE_EDGE), Reducer 8 (SIMPLE_EDGE) - -Stage-0 - Fetch Operator - limit:100 - Stage-1 - Reducer 6 - File Output Operator [FS_164] - Limit [LIM_163] (rows=100 width=88) - Number of rows:100 - Select Operator [SEL_162] (rows=1442 width=88) - Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"] - <-Reducer 5 [SIMPLE_EDGE] - SHUFFLE [RS_161] - Select Operator [SEL_160] (rows=1442 width=88) - Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"] - Filter Operator [FIL_154] (rows=1442 width=88) - predicate:(_col1 BETWEEN (0.9 * _col3) AND (1.1 * _col3) and _col1 BETWEEN (0.9 * _col5) AND (1.1 * _col5) and _col3 BETWEEN (0.9 * _col1) AND (1.1 * _col1) and _col3 BETWEEN (0.9 * _col5) AND (1.1 * _col5) and _col5 BETWEEN (0.9 * _col1) AND (1.1 * _col1) and _col5 BETWEEN (0.9 * _col3) AND (1.1 * _col3)) - Merge Join Operator [MERGEJOIN_279] (rows=766650239 width=88) - Conds:RS_150._col0=RS_151._col0(Inner),RS_150._col0=RS_152._col0(Inner),Output:["_col0","_col1","_col3","_col5"] - <-Reducer 10 [ONE_TO_ONE_EDGE] - FORWARD [RS_151] - PartitionCols:_col0 - Group By Operator [GBY_98] (rows=348477374 width=88) - Output:["_col0","_col1"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0 - <-Reducer 9 [SIMPLE_EDGE] - SHUFFLE [RS_97] - PartitionCols:_col0 - Group By Operator [GBY_96] (rows=696954748 width=88) - Output:["_col0","_col1"],aggregations:["sum(_col2)"],keys:_col4 - Merge Join Operator [MERGEJOIN_277] (rows=696954748 width=88) - Conds:RS_92._col0=RS_93._col0(Inner),Output:["_col2","_col4"] - <-Reducer 16 [SIMPLE_EDGE] - SHUFFLE [RS_93] - PartitionCols:_col0 - Merge Join Operator [MERGEJOIN_271] (rows=80353 width=1119) - Conds:RS_85._col1=RS_86._col0(Inner),Output:["_col0"] - <-Map 14 [SIMPLE_EDGE] - SHUFFLE [RS_85] - PartitionCols:_col1 - Select Operator [SEL_8] (rows=73049 width=1119) - Output:["_col0","_col1"] - Filter Operator [FIL_248] (rows=73049 width=1119) - predicate:(d_date is not null and d_date_sk is not null) - TableScan [TS_6] (rows=73049 width=1119) - default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_date"] - <-Reducer 26 [ONE_TO_ONE_EDGE] - FORWARD [RS_86] - PartitionCols:_col0 - Group By Operator [GBY_83] (rows=40176 width=1119) - Output:["_col0"],keys:KEY._col0 - <-Reducer 25 [SIMPLE_EDGE] - SHUFFLE [RS_82] - PartitionCols:_col0 - Group By Operator [GBY_81] (rows=80353 width=1119) - Output:["_col0"],keys:_col2 - Merge Join Operator [MERGEJOIN_270] (rows=80353 width=1119) - Conds:RS_77._col1=RS_78._col1(Inner),Output:["_col2"] - <-Map 31 [SIMPLE_EDGE] - SHUFFLE [RS_78] - PartitionCols:_col1 - Select Operator [SEL_73] (rows=73049 width=1119) - Output:["_col0","_col1"] - Filter Operator [FIL_257] (rows=73049 width=1119) - predicate:(d_date is not null and d_week_seq is not null) - TableScan [TS_21] (rows=73049 width=1119) - default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date","d_week_seq"] - <-Reducer 24 [SIMPLE_EDGE] - SHUFFLE [RS_77] - PartitionCols:_col1 - Merge Join Operator [MERGEJOIN_269] (rows=36524 width=1128) - Conds:(Inner),Output:["_col1"] - <-Map 31 [CUSTOM_SIMPLE_EDGE] - SHUFFLE [RS_75] - Select Operator [SEL_70] (rows=36524 width=1119) - Output:["_col0"] - Filter Operator [FIL_256] (rows=36524 width=1119) - predicate:((d_date = '1998-02-19') and d_week_seq is not null) - Please refer to the previous TableScan [TS_21] - <-Reducer 23 [CUSTOM_SIMPLE_EDGE] - PARTITION_ONLY_SHUFFLE [RS_74] - Select Operator [SEL_67] (rows=1 width=8) - Filter Operator [FIL_66] (rows=1 width=8) - predicate:(sq_count_check(_col0) <= 1) - Group By Operator [GBY_64] (rows=1 width=8) - Output:["_col0"],aggregations:["count(VALUE._col0)"] - <-Map 18 [CUSTOM_SIMPLE_EDGE] - PARTITION_ONLY_SHUFFLE [RS_63] - Group By Operator [GBY_12] (rows=1 width=8) - Output:["_col0"],aggregations:["count()"] - Select Operator [SEL_11] (rows=36524 width=1119) - Filter Operator [FIL_249] (rows=36524 width=1119) - predicate:(d_date = '1998-02-19') - TableScan [TS_9] (rows=73049 width=1119) - default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date"] - <-Reducer 8 [SIMPLE_EDGE] - SHUFFLE [RS_92] - PartitionCols:_col0 - Merge Join Operator [MERGEJOIN_268] (rows=633595212 width=88) - Conds:RS_89._col1=RS_90._col0(Inner),Output:["_col0","_col2","_col4"] - <-Map 7 [SIMPLE_EDGE] - SHUFFLE [RS_90] - PartitionCols:_col0 - Select Operator [SEL_5] (rows=462000 width=1436) - Output:["_col0","_col1"] - Filter Operator [FIL_247] (rows=462000 width=1436) - predicate:(i_item_id is not null and i_item_sk is not null) - TableScan [TS_3] (rows=462000 width=1436) - default@item,item,Tbl:COMPLETE,Col:NONE,Output:["i_item_sk","i_item_id"] - <-Map 32 [SIMPLE_EDGE] - SHUFFLE [RS_89] - PartitionCols:_col1 - Select Operator [SEL_52] (rows=575995635 width=88) - Output:["_col0","_col1","_col2"] - Filter Operator [FIL_252] (rows=575995635 width=88) - predicate:(ss_item_sk is not null and ss_sold_date_sk is not null) - TableScan [TS_50] (rows=575995635 width=88) - default@store_sales,store_sales,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_item_sk","ss_ext_sales_price"] - <-Reducer 13 [ONE_TO_ONE_EDGE] - FORWARD [RS_152] - PartitionCols:_col0 - Group By Operator [GBY_148] (rows=87121617 width=135) - Output:["_col0","_col1"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0 - <-Reducer 12 [SIMPLE_EDGE] - SHUFFLE [RS_147] - PartitionCols:_col0 - Group By Operator [GBY_146] (rows=174243235 width=135) - Output:["_col0","_col1"],aggregations:["sum(_col2)"],keys:_col4 - Merge Join Operator [MERGEJOIN_278] (rows=174243235 width=135) - Conds:RS_142._col0=RS_143._col0(Inner),Output:["_col2","_col4"] - <-Reducer 11 [SIMPLE_EDGE] - SHUFFLE [RS_142] - PartitionCols:_col0 - Merge Join Operator [MERGEJOIN_272] (rows=158402938 width=135) - Conds:RS_139._col1=RS_140._col0(Inner),Output:["_col0","_col2","_col4"] - <-Map 7 [SIMPLE_EDGE] - SHUFFLE [RS_140] - PartitionCols:_col0 - Please refer to the previous Select Operator [SEL_5] - <-Map 33 [SIMPLE_EDGE] - SHUFFLE [RS_139] - PartitionCols:_col1 - Select Operator [SEL_102] (rows=144002668 width=135) - Output:["_col0","_col1","_col2"] - Filter Operator [FIL_258] (rows=144002668 width=135) - predicate:(ws_item_sk is not null and ws_sold_date_sk is not null) - TableScan [TS_100] (rows=144002668 width=135) - default@web_sales,web_sales,Tbl:COMPLETE,Col:NONE,Output:["ws_sold_date_sk","ws_item_sk","ws_ext_sales_price"] - <-Reducer 17 [SIMPLE_EDGE] - SHUFFLE [RS_143] - PartitionCols:_col0 - Merge Join Operator [MERGEJOIN_275] (rows=80353 width=1119) - Conds:RS_135._col1=RS_136._col0(Inner),Output:["_col0"] - <-Map 14 [SIMPLE_EDGE] - SHUFFLE [RS_135] - PartitionCols:_col1 - Please refer to the previous Select Operator [SEL_8] - <-Reducer 30 [ONE_TO_ONE_EDGE] - FORWARD [RS_136] - PartitionCols:_col0 - Group By Operator [GBY_133] (rows=40176 width=1119) - Output:["_col0"],keys:KEY._col0 - <-Reducer 29 [SIMPLE_EDGE] - SHUFFLE [RS_132] - PartitionCols:_col0 - Group By Operator [GBY_131] (rows=80353 width=1119) - Output:["_col0"],keys:_col2 - Merge Join Operator [MERGEJOIN_274] (rows=80353 width=1119) - Conds:RS_127._col1=RS_128._col1(Inner),Output:["_col2"] - <-Map 31 [SIMPLE_EDGE] - SHUFFLE [RS_128] - PartitionCols:_col1 - Select Operator [SEL_123] (rows=73049 width=1119) - Output:["_col0","_col1"] - Filter Operator [FIL_263] (rows=73049 width=1119) - predicate:(d_date is not null and d_week_seq is not null) - Please refer to the previous TableScan [TS_21] - <-Reducer 28 [SIMPLE_EDGE] - SHUFFLE [RS_127] - PartitionCols:_col1 - Merge Join Operator [MERGEJOIN_273] (rows=36524 width=1128) - Conds:(Inner),Output:["_col1"] - <-Map 31 [CUSTOM_SIMPLE_EDGE] - SHUFFLE [RS_125] - Select Operator [SEL_120] (rows=36524 width=1119) - Output:["_col0"] - Filter Operator [FIL_262] (rows=36524 width=1119) - predicate:((d_date = '1998-02-19') and d_week_seq is not null) - Please refer to the previous TableScan [TS_21] - <-Reducer 27 [CUSTOM_SIMPLE_EDGE] - PARTITION_ONLY_SHUFFLE [RS_124] - Select Operator [SEL_117] (rows=1 width=8) - Filter Operator [FIL_116] (rows=1 width=8) - predicate:(sq_count_check(_col0) <= 1) - Group By Operator [GBY_114] (rows=1 width=8) - Output:["_col0"],aggregations:["count(VALUE._col0)"] - <-Map 18 [CUSTOM_SIMPLE_EDGE] - PARTITION_ONLY_SHUFFLE [RS_113] - Please refer to the previous Group By Operator [GBY_12] - <-Reducer 4 [ONE_TO_ONE_EDGE] - FORWARD [RS_150] - PartitionCols:_col0 - Group By Operator [GBY_48] (rows=174233858 width=135) - Output:["_col0","_col1"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0 - <-Reducer 3 [SIMPLE_EDGE] - SHUFFLE [RS_47] - PartitionCols:_col0 - Group By Operator [GBY_46] (rows=348467716 width=135) - Output:["_col0","_col1"],aggregations:["sum(_col2)"],keys:_col4 - Merge Join Operator [MERGEJOIN_276] (rows=348467716 width=135) - Conds:RS_42._col0=RS_43._col0(Inner),Output:["_col2","_col4"] - <-Reducer 15 [SIMPLE_EDGE] - SHUFFLE [RS_43] - PartitionCols:_col0 - Merge Join Operator [MERGEJOIN_267] (rows=80353 width=1119) - Conds:RS_35._col1=RS_36._col0(Inner),Output:["_col0"] - <-Map 14 [SIMPLE_EDGE] - SHUFFLE [RS_35] - PartitionCols:_col1 - Please refer to the previous Select Operator [SEL_8] - <-Reducer 22 [ONE_TO_ONE_EDGE] - FORWARD [RS_36] - PartitionCols:_col0 - Group By Operator [GBY_33] (rows=40176 width=1119) - Output:["_col0"],keys:KEY._col0 - <-Reducer 21 [SIMPLE_EDGE] - SHUFFLE [RS_32] - PartitionCols:_col0 - Group By Operator [GBY_31] (rows=80353 width=1119) - Output:["_col0"],keys:_col2 - Merge Join Operator [MERGEJOIN_266] (rows=80353 width=1119) - Conds:RS_27._col1=RS_28._col1(Inner),Output:["_col2"] - <-Map 31 [SIMPLE_EDGE] - SHUFFLE [RS_28] - PartitionCols:_col1 - Select Operator [SEL_23] (rows=73049 width=1119) - Output:["_col0","_col1"] - Filter Operator [FIL_251] (rows=73049 width=1119) - predicate:(d_date is not null and d_week_seq is not null) - Please refer to the previous TableScan [TS_21] - <-Reducer 20 [SIMPLE_EDGE] - SHUFFLE [RS_27] - PartitionCols:_col1 - Merge Join Operator [MERGEJOIN_265] (rows=36524 width=1128) - Conds:(Inner),Output:["_col1"] - <-Map 31 [CUSTOM_SIMPLE_EDGE] - SHUFFLE [RS_25] - Select Operator [SEL_20] (rows=36524 width=1119) - Output:["_col0"] - Filter Operator [FIL_250] (rows=36524 width=1119) - predicate:((d_date = '1998-02-19') and d_week_seq is not null) - Please refer to the previous TableScan [TS_21] - <-Reducer 19 [CUSTOM_SIMPLE_EDGE] - PARTITION_ONLY_SHUFFLE [RS_24] - Select Operator [SEL_17] (rows=1 width=8) - Filter Operator [FIL_16] (rows=1 width=8) - predicate:(sq_count_check(_col0) <= 1) - Group By Operator [GBY_14] (rows=1 width=8) - Output:["_col0"],aggregations:["count(VALUE._col0)"] - <-Map 18 [CUSTOM_SIMPLE_EDGE] - PARTITION_ONLY_SHUFFLE [RS_13] - Please refer to the previous Group By Operator [GBY_12] - <-Reducer 2 [SIMPLE_EDGE] - SHUFFLE [RS_42] - PartitionCols:_col0 - Merge Join Operator [MERGEJOIN_264] (rows=316788826 width=135) - Conds:RS_39._col1=RS_40._col0(Inner),Output:["_col0","_col2","_col4"] - <-Map 7 [SIMPLE_EDGE] - SHUFFLE [RS_40] - PartitionCols:_col0 - Please refer to the previous Select Operator [SEL_5] - <-Map 1 [SIMPLE_EDGE] - SHUFFLE [RS_39] - PartitionCols:_col1 - Select Operator [SEL_2] (rows=287989836 width=135) - Output:["_col0","_col1","_col2"] - Filter Operator [FIL_246] (rows=287989836 width=135) - predicate:(cs_item_sk is not null and cs_sold_date_sk is not null) - TableScan [TS_0] (rows=287989836 width=135) - default@catalog_sales,catalog_sales,Tbl:COMPLETE,Col:NONE,Output:["cs_sold_date_sk","cs_item_sk","cs_ext_sales_price"] -
http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/query59.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/query59.q.out b/ql/src/test/results/clientpositive/perf/query59.q.out deleted file mode 100644 index 37c2cc0..0000000 --- a/ql/src/test/results/clientpositive/perf/query59.q.out +++ /dev/null @@ -1,230 +0,0 @@ -PREHOOK: query: explain -with wss as - (select d_week_seq, - ss_store_sk, - sum(case when (d_day_name='Sunday') then ss_sales_price else null end) sun_sales, - sum(case when (d_day_name='Monday') then ss_sales_price else null end) mon_sales, - sum(case when (d_day_name='Tuesday') then ss_sales_price else null end) tue_sales, - sum(case when (d_day_name='Wednesday') then ss_sales_price else null end) wed_sales, - sum(case when (d_day_name='Thursday') then ss_sales_price else null end) thu_sales, - sum(case when (d_day_name='Friday') then ss_sales_price else null end) fri_sales, - sum(case when (d_day_name='Saturday') then ss_sales_price else null end) sat_sales - from store_sales,date_dim - where d_date_sk = ss_sold_date_sk - group by d_week_seq,ss_store_sk - ) - select s_store_name1,s_store_id1,d_week_seq1 - ,sun_sales1/sun_sales2,mon_sales1/mon_sales2 - ,tue_sales1/tue_sales1,wed_sales1/wed_sales2,thu_sales1/thu_sales2 - ,fri_sales1/fri_sales2,sat_sales1/sat_sales2 - from - (select s_store_name s_store_name1,wss.d_week_seq d_week_seq1 - ,s_store_id s_store_id1,sun_sales sun_sales1 - ,mon_sales mon_sales1,tue_sales tue_sales1 - ,wed_sales wed_sales1,thu_sales thu_sales1 - ,fri_sales fri_sales1,sat_sales sat_sales1 - from wss,store,date_dim d - where d.d_week_seq = wss.d_week_seq and - ss_store_sk = s_store_sk and - d_month_seq between 1185 and 1185 + 11) y, - (select s_store_name s_store_name2,wss.d_week_seq d_week_seq2 - ,s_store_id s_store_id2,sun_sales sun_sales2 - ,mon_sales mon_sales2,tue_sales tue_sales2 - ,wed_sales wed_sales2,thu_sales thu_sales2 - ,fri_sales fri_sales2,sat_sales sat_sales2 - from wss,store,date_dim d - where d.d_week_seq = wss.d_week_seq and - ss_store_sk = s_store_sk and - d_month_seq between 1185+ 12 and 1185 + 23) x - where s_store_id1=s_store_id2 - and d_week_seq1=d_week_seq2-52 - order by s_store_name1,s_store_id1,d_week_seq1 -limit 100 -PREHOOK: type: QUERY -POSTHOOK: query: explain -with wss as - (select d_week_seq, - ss_store_sk, - sum(case when (d_day_name='Sunday') then ss_sales_price else null end) sun_sales, - sum(case when (d_day_name='Monday') then ss_sales_price else null end) mon_sales, - sum(case when (d_day_name='Tuesday') then ss_sales_price else null end) tue_sales, - sum(case when (d_day_name='Wednesday') then ss_sales_price else null end) wed_sales, - sum(case when (d_day_name='Thursday') then ss_sales_price else null end) thu_sales, - sum(case when (d_day_name='Friday') then ss_sales_price else null end) fri_sales, - sum(case when (d_day_name='Saturday') then ss_sales_price else null end) sat_sales - from store_sales,date_dim - where d_date_sk = ss_sold_date_sk - group by d_week_seq,ss_store_sk - ) - select s_store_name1,s_store_id1,d_week_seq1 - ,sun_sales1/sun_sales2,mon_sales1/mon_sales2 - ,tue_sales1/tue_sales1,wed_sales1/wed_sales2,thu_sales1/thu_sales2 - ,fri_sales1/fri_sales2,sat_sales1/sat_sales2 - from - (select s_store_name s_store_name1,wss.d_week_seq d_week_seq1 - ,s_store_id s_store_id1,sun_sales sun_sales1 - ,mon_sales mon_sales1,tue_sales tue_sales1 - ,wed_sales wed_sales1,thu_sales thu_sales1 - ,fri_sales fri_sales1,sat_sales sat_sales1 - from wss,store,date_dim d - where d.d_week_seq = wss.d_week_seq and - ss_store_sk = s_store_sk and - d_month_seq between 1185 and 1185 + 11) y, - (select s_store_name s_store_name2,wss.d_week_seq d_week_seq2 - ,s_store_id s_store_id2,sun_sales sun_sales2 - ,mon_sales mon_sales2,tue_sales tue_sales2 - ,wed_sales wed_sales2,thu_sales thu_sales2 - ,fri_sales fri_sales2,sat_sales sat_sales2 - from wss,store,date_dim d - where d.d_week_seq = wss.d_week_seq and - ss_store_sk = s_store_sk and - d_month_seq between 1185+ 12 and 1185 + 23) x - where s_store_id1=s_store_id2 - and d_week_seq1=d_week_seq2-52 - order by s_store_name1,s_store_id1,d_week_seq1 -limit 100 -POSTHOOK: type: QUERY -Plan optimized by CBO. - -Vertex dependency in root stage -Reducer 10 <- Map 13 (SIMPLE_EDGE), Reducer 9 (SIMPLE_EDGE) -Reducer 11 <- Map 15 (SIMPLE_EDGE), Reducer 10 (SIMPLE_EDGE) -Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 12 (SIMPLE_EDGE) -Reducer 3 <- Reducer 2 (SIMPLE_EDGE) -Reducer 4 <- Map 13 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE) -Reducer 5 <- Map 14 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE) -Reducer 6 <- Reducer 11 (SIMPLE_EDGE), Reducer 5 (SIMPLE_EDGE) -Reducer 7 <- Reducer 6 (SIMPLE_EDGE) -Reducer 8 <- Map 1 (SIMPLE_EDGE), Map 12 (SIMPLE_EDGE) -Reducer 9 <- Reducer 8 (SIMPLE_EDGE) - -Stage-0 - Fetch Operator - limit:100 - Stage-1 - Reducer 7 - File Output Operator [FS_63] - Limit [LIM_62] (rows=100 width=88) - Number of rows:100 - Select Operator [SEL_61] (rows=421657640 width=88) - Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9"] - <-Reducer 6 [SIMPLE_EDGE] - SHUFFLE [RS_60] - Select Operator [SEL_59] (rows=421657640 width=88) - Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9"] - Merge Join Operator [MERGEJOIN_104] (rows=421657640 width=88) - Conds:RS_56._col2, _col1=RS_57._col1, (_col0 - 52)(Inner),Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col12","_col13","_col14","_col15","_col16","_col17"] - <-Reducer 11 [SIMPLE_EDGE] - SHUFFLE [RS_57] - PartitionCols:_col1, (_col0 - 52) - Select Operator [SEL_55] (rows=383325119 width=88) - Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"] - Merge Join Operator [MERGEJOIN_103] (rows=383325119 width=88) - Conds:RS_52._col1=RS_53._col0(Inner),Output:["_col0","_col2","_col3","_col4","_col5","_col6","_col7","_col11"] - <-Map 15 [SIMPLE_EDGE] - SHUFFLE [RS_53] - PartitionCols:_col0 - Select Operator [SEL_48] (rows=1704 width=1910) - Output:["_col0","_col1"] - Filter Operator [FIL_97] (rows=1704 width=1910) - predicate:(s_store_id is not null and s_store_sk is not null) - TableScan [TS_46] (rows=1704 width=1910) - default@store,store,Tbl:COMPLETE,Col:NONE,Output:["s_store_sk","s_store_id"] - <-Reducer 10 [SIMPLE_EDGE] - SHUFFLE [RS_52] - PartitionCols:_col1 - Merge Join Operator [MERGEJOIN_102] (rows=348477374 width=88) - Conds:RS_49._col0=RS_50._col1(Inner),Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"] - <-Map 13 [SIMPLE_EDGE] - SHUFFLE [RS_50] - PartitionCols:_col1 - Select Operator [SEL_45] (rows=8116 width=1119) - Output:["_col1"] - Filter Operator [FIL_96] (rows=8116 width=1119) - predicate:(d_month_seq BETWEEN 1197 AND 1208 and d_week_seq is not null) - TableScan [TS_15] (rows=73049 width=1119) - default@date_dim,d,Tbl:COMPLETE,Col:NONE,Output:["d_month_seq","d_week_seq"] - <-Reducer 9 [SIMPLE_EDGE] - SHUFFLE [RS_49] - PartitionCols:_col0 - Group By Operator [GBY_41] (rows=316797606 width=88) - Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)","sum(VALUE._col3)","sum(VALUE._col4)","sum(VALUE._col5)"],keys:KEY._col0, KEY._col1 - <-Reducer 8 [SIMPLE_EDGE] - SHUFFLE [RS_40] - PartitionCols:_col0, _col1 - Group By Operator [GBY_39] (rows=633595212 width=88) - Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7"],aggregations:["sum(_col2)","sum(_col3)","sum(_col5)","sum(_col6)","sum(_col7)","sum(_col8)"],keys:_col0, _col1 - Select Operator [SEL_37] (rows=633595212 width=88) - Output:["_col0","_col1","_col2","_col3","_col5","_col6","_col7","_col8"] - Merge Join Operator [MERGEJOIN_101] (rows=633595212 width=88) - Conds:RS_34._col0=RS_35._col0(Inner),Output:["_col1","_col2","_col4","_col5"] - <-Map 1 [SIMPLE_EDGE] - SHUFFLE [RS_34] - PartitionCols:_col0 - Select Operator [SEL_2] (rows=575995635 width=88) - Output:["_col0","_col1","_col2"] - Filter Operator [FIL_90] (rows=575995635 width=88) - predicate:(ss_sold_date_sk is not null and ss_store_sk is not null) - TableScan [TS_0] (rows=575995635 width=88) - default@store_sales,store_sales,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_store_sk","ss_sales_price"] - <-Map 12 [SIMPLE_EDGE] - SHUFFLE [RS_35] - PartitionCols:_col0 - Select Operator [SEL_5] (rows=73049 width=1119) - Output:["_col0","_col1","_col2"] - Filter Operator [FIL_91] (rows=73049 width=1119) - predicate:(d_date_sk is not null and d_week_seq is not null) - TableScan [TS_3] (rows=73049 width=1119) - default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_week_seq","d_day_name"] - <-Reducer 5 [SIMPLE_EDGE] - SHUFFLE [RS_56] - PartitionCols:_col2, _col1 - Select Operator [SEL_27] (rows=383325119 width=88) - Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9"] - Merge Join Operator [MERGEJOIN_100] (rows=383325119 width=88) - Conds:RS_24._col1=RS_25._col0(Inner),Output:["_col0","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col12","_col13"] - <-Map 14 [SIMPLE_EDGE] - SHUFFLE [RS_25] - PartitionCols:_col0 - Select Operator [SEL_20] (rows=1704 width=1910) - Output:["_col0","_col1","_col2"] - Filter Operator [FIL_93] (rows=1704 width=1910) - predicate:(s_store_id is not null and s_store_sk is not null) - TableScan [TS_18] (rows=1704 width=1910) - default@store,store,Tbl:COMPLETE,Col:NONE,Output:["s_store_sk","s_store_id","s_store_name"] - <-Reducer 4 [SIMPLE_EDGE] - SHUFFLE [RS_24] - PartitionCols:_col1 - Merge Join Operator [MERGEJOIN_99] (rows=348477374 width=88) - Conds:RS_21._col0=RS_22._col1(Inner),Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"] - <-Map 13 [SIMPLE_EDGE] - SHUFFLE [RS_22] - PartitionCols:_col1 - Select Operator [SEL_17] (rows=8116 width=1119) - Output:["_col1"] - Filter Operator [FIL_92] (rows=8116 width=1119) - predicate:(d_month_seq BETWEEN 1185 AND 1196 and d_week_seq is not null) - Please refer to the previous TableScan [TS_15] - <-Reducer 3 [SIMPLE_EDGE] - SHUFFLE [RS_21] - PartitionCols:_col0 - Group By Operator [GBY_13] (rows=316797606 width=88) - Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"],aggregations:["sum(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)","sum(VALUE._col3)","sum(VALUE._col4)","sum(VALUE._col5)","sum(VALUE._col6)"],keys:KEY._col0, KEY._col1 - <-Reducer 2 [SIMPLE_EDGE] - SHUFFLE [RS_12] - PartitionCols:_col0, _col1 - Group By Operator [GBY_11] (rows=633595212 width=88) - Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"],aggregations:["sum(_col2)","sum(_col3)","sum(_col4)","sum(_col5)","sum(_col6)","sum(_col7)","sum(_col8)"],keys:_col0, _col1 - Select Operator [SEL_9] (rows=633595212 width=88) - Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"] - Merge Join Operator [MERGEJOIN_98] (rows=633595212 width=88) - Conds:RS_6._col0=RS_7._col0(Inner),Output:["_col1","_col2","_col4","_col5"] - <-Map 1 [SIMPLE_EDGE] - SHUFFLE [RS_6] - PartitionCols:_col0 - Please refer to the previous Select Operator [SEL_2] - <-Map 12 [SIMPLE_EDGE] - SHUFFLE [RS_7] - PartitionCols:_col0 - Please refer to the previous Select Operator [SEL_5] - http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/query6.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/query6.q.out b/ql/src/test/results/clientpositive/perf/query6.q.out deleted file mode 100644 index f1e4758..0000000 --- a/ql/src/test/results/clientpositive/perf/query6.q.out +++ /dev/null @@ -1,226 +0,0 @@ -Warning: Shuffle Join MERGEJOIN[111][tables = [$hdt$_5, $hdt$_6]] in Stage 'Reducer 12' is a cross product -PREHOOK: query: explain -select a.ca_state state, count(*) cnt - from customer_address a - ,customer c - ,store_sales s - ,date_dim d - ,item i - where a.ca_address_sk = c.c_current_addr_sk - and c.c_customer_sk = s.ss_customer_sk - and s.ss_sold_date_sk = d.d_date_sk - and s.ss_item_sk = i.i_item_sk - and d.d_month_seq = - (select distinct (d_month_seq) - from date_dim - where d_year = 2000 - and d_moy = 2 ) - and i.i_current_price > 1.2 * - (select avg(j.i_current_price) - from item j - where j.i_category = i.i_category) - group by a.ca_state - having count(*) >= 10 - order by cnt - limit 100 -PREHOOK: type: QUERY -POSTHOOK: query: explain -select a.ca_state state, count(*) cnt - from customer_address a - ,customer c - ,store_sales s - ,date_dim d - ,item i - where a.ca_address_sk = c.c_current_addr_sk - and c.c_customer_sk = s.ss_customer_sk - and s.ss_sold_date_sk = d.d_date_sk - and s.ss_item_sk = i.i_item_sk - and d.d_month_seq = - (select distinct (d_month_seq) - from date_dim - where d_year = 2000 - and d_moy = 2 ) - and i.i_current_price > 1.2 * - (select avg(j.i_current_price) - from item j - where j.i_category = i.i_category) - group by a.ca_state - having count(*) >= 10 - order by cnt - limit 100 -POSTHOOK: type: QUERY -Plan optimized by CBO. - -Vertex dependency in root stage -Reducer 10 <- Map 8 (SIMPLE_EDGE) -Reducer 11 <- Reducer 10 (CUSTOM_SIMPLE_EDGE) -Reducer 12 <- Reducer 11 (CUSTOM_SIMPLE_EDGE), Reducer 19 (CUSTOM_SIMPLE_EDGE) -Reducer 13 <- Map 20 (SIMPLE_EDGE), Reducer 12 (SIMPLE_EDGE) -Reducer 16 <- Map 15 (SIMPLE_EDGE), Map 17 (SIMPLE_EDGE) -Reducer 19 <- Map 18 (SIMPLE_EDGE) -Reducer 2 <- Map 1 (SIMPLE_EDGE), Reducer 9 (ONE_TO_ONE_EDGE) -Reducer 3 <- Map 14 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) -Reducer 4 <- Reducer 16 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE) -Reducer 5 <- Reducer 13 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE) -Reducer 6 <- Reducer 5 (SIMPLE_EDGE) -Reducer 7 <- Reducer 6 (SIMPLE_EDGE) -Reducer 9 <- Map 8 (SIMPLE_EDGE) - -Stage-0 - Fetch Operator - limit:100 - Stage-1 - Reducer 7 - File Output Operator [FS_76] - Limit [LIM_75] (rows=100 width=88) - Number of rows:100 - Select Operator [SEL_74] (rows=127775039 width=88) - Output:["_col0","_col1"] - <-Reducer 6 [SIMPLE_EDGE] - SHUFFLE [RS_73] - Filter Operator [FIL_71] (rows=127775039 width=88) - predicate:(_col1 >= 10) - Group By Operator [GBY_70] (rows=383325119 width=88) - Output:["_col0","_col1"],aggregations:["count(VALUE._col0)"],keys:KEY._col0 - <-Reducer 5 [SIMPLE_EDGE] - SHUFFLE [RS_69] - PartitionCols:_col0 - Group By Operator [GBY_68] (rows=766650239 width=88) - Output:["_col0","_col1"],aggregations:["count()"],keys:_col9 - Merge Join Operator [MERGEJOIN_114] (rows=766650239 width=88) - Conds:RS_64._col4=RS_65._col0(Inner),Output:["_col9"] - <-Reducer 13 [SIMPLE_EDGE] - SHUFFLE [RS_65] - PartitionCols:_col0 - Select Operator [SEL_54] (rows=169400 width=1436) - Output:["_col0"] - Filter Operator [FIL_53] (rows=169400 width=1436) - predicate:(_col5 > (1.2 * CASE WHEN (_col1 is null) THEN (null) ELSE (_col0) END)) - Merge Join Operator [MERGEJOIN_112] (rows=508200 width=1436) - Conds:RS_50._col2=RS_51._col2(Inner),Output:["_col0","_col1","_col4","_col5"] - <-Map 20 [SIMPLE_EDGE] - SHUFFLE [RS_51] - PartitionCols:_col2 - Select Operator [SEL_46] (rows=462000 width=1436) - Output:["_col0","_col1","_col2"] - Filter Operator [FIL_107] (rows=462000 width=1436) - predicate:i_item_sk is not null - TableScan [TS_44] (rows=462000 width=1436) - default@item,i,Tbl:COMPLETE,Col:NONE,Output:["i_item_sk","i_current_price","i_category"] - <-Reducer 12 [SIMPLE_EDGE] - SHUFFLE [RS_50] - PartitionCols:_col2 - Merge Join Operator [MERGEJOIN_111] (rows=231000 width=1445) - Conds:(Inner),Output:["_col0","_col1","_col2"] - <-Reducer 11 [CUSTOM_SIMPLE_EDGE] - PARTITION_ONLY_SHUFFLE [RS_48] - Select Operator [SEL_43] (rows=1 width=8) - Filter Operator [FIL_42] (rows=1 width=8) - predicate:(sq_count_check(_col0) <= 1) - Group By Operator [GBY_40] (rows=1 width=8) - Output:["_col0"],aggregations:["count(VALUE._col0)"] - <-Reducer 10 [CUSTOM_SIMPLE_EDGE] - PARTITION_ONLY_SHUFFLE [RS_39] - Group By Operator [GBY_38] (rows=1 width=8) - Output:["_col0"],aggregations:["count()"] - Select Operator [SEL_36] (rows=9131 width=1119) - Group By Operator [GBY_35] (rows=9131 width=1119) - Output:["_col0"],keys:KEY._col0 - <-Map 8 [SIMPLE_EDGE] - SHUFFLE [RS_34] - PartitionCols:_col0 - Group By Operator [GBY_33] (rows=18262 width=1119) - Output:["_col0"],keys:d_month_seq - Select Operator [SEL_32] (rows=18262 width=1119) - Output:["d_month_seq"] - Filter Operator [FIL_106] (rows=18262 width=1119) - predicate:((d_moy = 2) and (d_year = 2000)) - TableScan [TS_3] (rows=73049 width=1119) - default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_month_seq","d_year","d_moy"] - <-Reducer 19 [CUSTOM_SIMPLE_EDGE] - PARTITION_ONLY_SHUFFLE [RS_47] - Select Operator [SEL_29] (rows=231000 width=1436) - Output:["_col0","_col1","_col2"] - Group By Operator [GBY_28] (rows=231000 width=1436) - Output:["_col0","_col1"],aggregations:["avg(VALUE._col0)"],keys:KEY._col0 - <-Map 18 [SIMPLE_EDGE] - SHUFFLE [RS_27] - PartitionCols:_col0 - Group By Operator [GBY_26] (rows=462000 width=1436) - Output:["_col0","_col1"],aggregations:["avg(i_current_price)"],keys:i_category - Filter Operator [FIL_105] (rows=462000 width=1436) - predicate:i_category is not null - TableScan [TS_23] (rows=462000 width=1436) - default@item,j,Tbl:COMPLETE,Col:NONE,Output:["i_current_price","i_category"] - <-Reducer 4 [SIMPLE_EDGE] - SHUFFLE [RS_64] - PartitionCols:_col4 - Merge Join Operator [MERGEJOIN_113] (rows=696954748 width=88) - Conds:RS_61._col5=RS_62._col0(Inner),Output:["_col4","_col9"] - <-Reducer 16 [SIMPLE_EDGE] - SHUFFLE [RS_62] - PartitionCols:_col0 - Merge Join Operator [MERGEJOIN_110] (rows=88000001 width=860) - Conds:RS_19._col1=RS_20._col0(Inner),Output:["_col0","_col3"] - <-Map 15 [SIMPLE_EDGE] - SHUFFLE [RS_19] - PartitionCols:_col1 - Select Operator [SEL_15] (rows=80000000 width=860) - Output:["_col0","_col1"] - Filter Operator [FIL_103] (rows=80000000 width=860) - predicate:(c_current_addr_sk is not null and c_customer_sk is not null) - TableScan [TS_13] (rows=80000000 width=860) - default@customer,c,Tbl:COMPLETE,Col:NONE,Output:["c_customer_sk","c_current_addr_sk"] - <-Map 17 [SIMPLE_EDGE] - SHUFFLE [RS_20] - PartitionCols:_col0 - Select Operator [SEL_18] (rows=40000000 width=1014) - Output:["_col0","_col1"] - Filter Operator [FIL_104] (rows=40000000 width=1014) - predicate:ca_address_sk is not null - TableScan [TS_16] (rows=40000000 width=1014) - default@customer_address,a,Tbl:COMPLETE,Col:NONE,Output:["ca_address_sk","ca_state"] - <-Reducer 3 [SIMPLE_EDGE] - SHUFFLE [RS_61] - PartitionCols:_col5 - Merge Join Operator [MERGEJOIN_109] (rows=633595212 width=88) - Conds:RS_58._col0=RS_59._col0(Inner),Output:["_col4","_col5"] - <-Map 14 [SIMPLE_EDGE] - SHUFFLE [RS_59] - PartitionCols:_col0 - Select Operator [SEL_12] (rows=575995635 width=88) - Output:["_col0","_col1","_col2"] - Filter Operator [FIL_102] (rows=575995635 width=88) - predicate:(ss_customer_sk is not null and ss_item_sk is not null and ss_sold_date_sk is not null) - TableScan [TS_10] (rows=575995635 width=88) - default@store_sales,s,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_item_sk","ss_customer_sk"] - <-Reducer 2 [SIMPLE_EDGE] - SHUFFLE [RS_58] - PartitionCols:_col0 - Merge Join Operator [MERGEJOIN_108] (rows=80353 width=1119) - Conds:RS_55._col1=RS_56._col0(Inner),Output:["_col0"] - <-Map 1 [SIMPLE_EDGE] - SHUFFLE [RS_55] - PartitionCols:_col1 - Select Operator [SEL_2] (rows=73049 width=1119) - Output:["_col0","_col1"] - Filter Operator [FIL_100] (rows=73049 width=1119) - predicate:(d_date_sk is not null and d_month_seq is not null) - TableScan [TS_0] (rows=73049 width=1119) - default@date_dim,d,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_month_seq"] - <-Reducer 9 [ONE_TO_ONE_EDGE] - FORWARD [RS_56] - PartitionCols:_col0 - Group By Operator [GBY_8] (rows=9131 width=1119) - Output:["_col0"],keys:KEY._col0 - <-Map 8 [SIMPLE_EDGE] - SHUFFLE [RS_7] - PartitionCols:_col0 - Group By Operator [GBY_6] (rows=18262 width=1119) - Output:["_col0"],keys:d_month_seq - Select Operator [SEL_5] (rows=18262 width=1119) - Output:["d_month_seq"] - Filter Operator [FIL_101] (rows=18262 width=1119) - predicate:((d_moy = 2) and (d_year = 2000) and d_month_seq is not null) - Please refer to the previous TableScan [TS_3] - http://git-wip-us.apache.org/repos/asf/hive/blob/9244fdc7/ql/src/test/results/clientpositive/perf/query60.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/query60.q.out b/ql/src/test/results/clientpositive/perf/query60.q.out deleted file mode 100644 index 84a9ada..0000000 --- a/ql/src/test/results/clientpositive/perf/query60.q.out +++ /dev/null @@ -1,399 +0,0 @@ -PREHOOK: query: explain -with ss as ( - select - i_item_id,sum(ss_ext_sales_price) total_sales - from - store_sales, - date_dim, - customer_address, - item - where - i_item_id in (select - i_item_id -from - item -where i_category in ('Children')) - and ss_item_sk = i_item_sk - and ss_sold_date_sk = d_date_sk - and d_year = 1999 - and d_moy = 9 - and ss_addr_sk = ca_address_sk - and ca_gmt_offset = -6 - group by i_item_id), - cs as ( - select - i_item_id,sum(cs_ext_sales_price) total_sales - from - catalog_sales, - date_dim, - customer_address, - item - where - i_item_id in (select - i_item_id -from - item -where i_category in ('Children')) - and cs_item_sk = i_item_sk - and cs_sold_date_sk = d_date_sk - and d_year = 1999 - and d_moy = 9 - and cs_bill_addr_sk = ca_address_sk - and ca_gmt_offset = -6 - group by i_item_id), - ws as ( - select - i_item_id,sum(ws_ext_sales_price) total_sales - from - web_sales, - date_dim, - customer_address, - item - where - i_item_id in (select - i_item_id -from - item -where i_category in ('Children')) - and ws_item_sk = i_item_sk - and ws_sold_date_sk = d_date_sk - and d_year = 1999 - and d_moy = 9 - and ws_bill_addr_sk = ca_address_sk - and ca_gmt_offset = -6 - group by i_item_id) - select - i_item_id -,sum(total_sales) total_sales - from (select * from ss - union all - select * from cs - union all - select * from ws) tmp1 - group by i_item_id - order by i_item_id - ,total_sales - limit 100 -PREHOOK: type: QUERY -POSTHOOK: query: explain -with ss as ( - select - i_item_id,sum(ss_ext_sales_price) total_sales - from - store_sales, - date_dim, - customer_address, - item - where - i_item_id in (select - i_item_id -from - item -where i_category in ('Children')) - and ss_item_sk = i_item_sk - and ss_sold_date_sk = d_date_sk - and d_year = 1999 - and d_moy = 9 - and ss_addr_sk = ca_address_sk - and ca_gmt_offset = -6 - group by i_item_id), - cs as ( - select - i_item_id,sum(cs_ext_sales_price) total_sales - from - catalog_sales, - date_dim, - customer_address, - item - where - i_item_id in (select - i_item_id -from - item -where i_category in ('Children')) - and cs_item_sk = i_item_sk - and cs_sold_date_sk = d_date_sk - and d_year = 1999 - and d_moy = 9 - and cs_bill_addr_sk = ca_address_sk - and ca_gmt_offset = -6 - group by i_item_id), - ws as ( - select - i_item_id,sum(ws_ext_sales_price) total_sales - from - web_sales, - date_dim, - customer_address, - item - where - i_item_id in (select - i_item_id -from - item -where i_category in ('Children')) - and ws_item_sk = i_item_sk - and ws_sold_date_sk = d_date_sk - and d_year = 1999 - and d_moy = 9 - and ws_bill_addr_sk = ca_address_sk - and ca_gmt_offset = -6 - group by i_item_id) - select - i_item_id -,sum(total_sales) total_sales - from (select * from ss - union all - select * from cs - union all - select * from ws) tmp1 - group by i_item_id - order by i_item_id - ,total_sales - limit 100 -POSTHOOK: type: QUERY -Plan optimized by CBO. - -Vertex dependency in root stage -Reducer 10 <- Reducer 9 (SIMPLE_EDGE), Union 5 (CONTAINS) -Reducer 11 <- Map 1 (SIMPLE_EDGE), Reducer 17 (ONE_TO_ONE_EDGE) -Reducer 12 <- Reducer 11 (SIMPLE_EDGE), Reducer 25 (SIMPLE_EDGE) -Reducer 13 <- Reducer 12 (SIMPLE_EDGE), Union 5 (CONTAINS) -Reducer 15 <- Map 14 (SIMPLE_EDGE) -Reducer 16 <- Map 14 (SIMPLE_EDGE) -Reducer 17 <- Map 14 (SIMPLE_EDGE) -Reducer 19 <- Map 18 (SIMPLE_EDGE), Map 21 (SIMPLE_EDGE) -Reducer 2 <- Map 1 (SIMPLE_EDGE), Reducer 15 (ONE_TO_ONE_EDGE) -Reducer 20 <- Map 26 (SIMPLE_EDGE), Reducer 19 (SIMPLE_EDGE) -Reducer 22 <- Map 21 (SIMPLE_EDGE), Map 27 (SIMPLE_EDGE) -Reducer 23 <- Map 26 (SIMPLE_EDGE), Reducer 22 (SIMPLE_EDGE) -Reducer 24 <- Map 21 (SIMPLE_EDGE), Map 28 (SIMPLE_EDGE) -Reducer 25 <- Map 26 (SIMPLE_EDGE), Reducer 24 (SIMPLE_EDGE) -Reducer 3 <- Reducer 2 (SIMPLE_EDGE), Reducer 20 (SIMPLE_EDGE) -Reducer 4 <- Reducer 3 (SIMPLE_EDGE), Union 5 (CONTAINS) -Reducer 6 <- Union 5 (SIMPLE_EDGE) -Reducer 7 <- Reducer 6 (SIMPLE_EDGE) -Reducer 8 <- Map 1 (SIMPLE_EDGE), Reducer 16 (ONE_TO_ONE_EDGE) -Reducer 9 <- Reducer 23 (SIMPLE_EDGE), Reducer 8 (SIMPLE_EDGE) - -Stage-0 - Fetch Operator - limit:100 - Stage-1 - Reducer 7 - File Output Operator [FS_121] - Limit [LIM_120] (rows=100 width=108) - Number of rows:100 - Select Operator [SEL_119] (rows=335408073 width=108) - Output:["_col0","_col1"] - <-Reducer 6 [SIMPLE_EDGE] - SHUFFLE [RS_118] - Group By Operator [GBY_116] (rows=335408073 width=108) - Output:["_col0","_col1"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0 - <-Union 5 [SIMPLE_EDGE] - <-Reducer 10 [CONTAINS] - Reduce Output Operator [RS_115] - PartitionCols:_col0 - Group By Operator [GBY_114] (rows=670816147 width=108) - Output:["_col0","_col1"],aggregations:["sum(_col1)"],keys:_col0 - Group By Operator [GBY_72] (rows=191657247 width=135) - Output:["_col0","_col1"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0 - <-Reducer 9 [SIMPLE_EDGE] - SHUFFLE [RS_71] - PartitionCols:_col0 - Group By Operator [GBY_70] (rows=383314495 width=135) - Output:["_col0","_col1"],aggregations:["sum(_col8)"],keys:_col1 - Merge Join Operator [MERGEJOIN_183] (rows=383314495 width=135) - Conds:RS_66._col0=RS_67._col4(Inner),Output:["_col1","_col8"] - <-Reducer 23 [SIMPLE_EDGE] - SHUFFLE [RS_67] - PartitionCols:_col4 - Select Operator [SEL_62] (rows=348467716 width=135) - Output:["_col4","_col5"] - Merge Join Operator [MERGEJOIN_178] (rows=348467716 width=135) - Conds:RS_59._col1=RS_60._col0(Inner),Output:["_col2","_col3"] - <-Map 26 [SIMPLE_EDGE] - SHUFFLE [RS_60] - PartitionCols:_col0 - Select Operator [SEL_18] (rows=20000000 width=1014) - Output:["_col0"] - Filter Operator [FIL_162] (rows=20000000 width=1014) - predicate:((ca_gmt_offset = -6) and ca_address_sk is not null) - TableScan [TS_16] (rows=40000000 width=1014) - default@customer_address,customer_address,Tbl:COMPLETE,Col:NONE,Output:["ca_address_sk","ca_gmt_offset"] - <-Reducer 22 [SIMPLE_EDGE] - SHUFFLE [RS_59] - PartitionCols:_col1 - Merge Join Operator [MERGEJOIN_177] (rows=316788826 width=135) - Conds:RS_56._col0=RS_57._col0(Inner),Output:["_col1","_col2","_col3"] - <-Map 21 [SIMPLE_EDGE] - SHUFFLE [RS_57] - PartitionCols:_col0 - Select Operator [SEL_15] (rows=18262 width=1119) - Output:["_col0"] - Filter Operator [FIL_161] (rows=18262 width=1119) - predicate:((d_moy = 9) and (d_year = 1999) and d_date_sk is not null) - TableScan [TS_13] (rows=73049 width=1119) - default@date_dim,date_dim,Tbl:COMPLETE,Col:NONE,Output:["d_date_sk","d_year","d_moy"] - <-Map 27 [SIMPLE_EDGE] - SHUFFLE [RS_56] - PartitionCols:_col0 - Select Operator [SEL_49] (rows=287989836 width=135) - Output:["_col0","_col1","_col2","_col3"] - Filter Operator [FIL_165] (rows=287989836 width=135) - predicate:(cs_bill_addr_sk is not null and cs_item_sk is not null and cs_sold_date_sk is not null) - TableScan [TS_47] (rows=287989836 width=135) - default@catalog_sales,catalog_sales,Tbl:COMPLETE,Col:NONE,Output:["cs_sold_date_sk","cs_bill_addr_sk","cs_item_sk","cs_ext_sales_price"] - <-Reducer 8 [SIMPLE_EDGE] - SHUFFLE [RS_66] - PartitionCols:_col0 - Merge Join Operator [MERGEJOIN_176] (rows=508200 width=1436) - Conds:RS_63._col1=RS_64._col0(Inner),Output:["_col0","_col1"] - <-Map 1 [SIMPLE_EDGE] - SHUFFLE [RS_63] - PartitionCols:_col1 - Select Operator [SEL_2] (rows=462000 width=1436) - Output:["_col0","_col1"] - Filter Operator [FIL_158] (rows=462000 width=1436) - predicate:(i_item_id is not null and i_item_sk is not null) - TableScan [TS_0] (rows=462000 width=1436) - default@item,item,Tbl:COMPLETE,Col:NONE,Output:["i_item_sk","i_item_id"] - <-Reducer 16 [ONE_TO_ONE_EDGE] - FORWARD [RS_64] - PartitionCols:_col0 - Group By Operator [GBY_45] (rows=115500 width=1436) - Output:["_col0"],keys:KEY._col0 - <-Map 14 [SIMPLE_EDGE] - SHUFFLE [RS_44] - PartitionCols:_col0 - Group By Operator [GBY_6] (rows=231000 width=1436) - Output:["_col0"],keys:i_item_id - Select Operator [SEL_5] (rows=231000 width=1436) - Output:["i_item_id"] - Filter Operator [FIL_159] (rows=231000 width=1436) - predicate:((i_category) IN ('Children') and i_item_id is not null) - TableScan [TS_3] (rows=462000 width=1436) - default@item,item,Tbl:COMPLETE,Col:NONE,Output:["i_item_id","i_category"] - <-Reducer 13 [CONTAINS] - Reduce Output Operator [RS_115] - PartitionCols:_col0 - Group By Operator [GBY_114] (rows=670816147 width=108) - Output:["_col0","_col1"],aggregations:["sum(_col1)"],keys:_col0 - Group By Operator [GBY_110] (rows=95833781 width=135) - Output:["_col0","_col1"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0 - <-Reducer 12 [SIMPLE_EDGE] - SHUFFLE [RS_109] - PartitionCols:_col0 - Group By Operator [GBY_108] (rows=191667562 width=135) - Output:["_col0","_col1"],aggregations:["sum(_col8)"],keys:_col1 - Merge Join Operator [MERGEJOIN_184] (rows=191667562 width=135) - Conds:RS_104._col0=RS_105._col3(Inner),Output:["_col1","_col8"] - <-Reducer 11 [SIMPLE_EDGE] - SHUFFLE [RS_104] - PartitionCols:_col0 - Merge Join Operator [MERGEJOIN_179] (rows=508200 width=1436) - Conds:RS_101._col1=RS_102._col0(Inner),Output:["_col0","_col1"] - <-Map 1 [SIMPLE_EDGE] - SHUFFLE [RS_101] - PartitionCols:_col1 - Please refer to the previous Select Operator [SEL_2] - <-Reducer 17 [ONE_TO_ONE_EDGE] - FORWARD [RS_102] - PartitionCols:_col0 - Group By Operator [GBY_83] (rows=115500 width=1436) - Output:["_col0"],keys:KEY._col0 - <-Map 14 [SIMPLE_EDGE] - SHUFFLE [RS_82] - PartitionCols:_col0 - Please refer to the previous Group By Operator [GBY_6] - <-Reducer 25 [SIMPLE_EDGE] - SHUFFLE [RS_105] - PartitionCols:_col3 - Select Operator [SEL_100] (rows=174243235 width=135) - Output:["_col3","_col5"] - Merge Join Operator [MERGEJOIN_181] (rows=174243235 width=135) - Conds:RS_97._col2=RS_98._col0(Inner),Output:["_col1","_col3"] - <-Map 26 [SIMPLE_EDGE] - SHUFFLE [RS_98] - PartitionCols:_col0 - Please refer to the previous Select Operator [SEL_18] - <-Reducer 24 [SIMPLE_EDGE] - SHUFFLE [RS_97] - PartitionCols:_col2 - Merge Join Operator [MERGEJOIN_180] (rows=158402938 width=135) - Conds:RS_94._col0=RS_95._col0(Inner),Output:["_col1","_col2","_col3"] - <-Map 21 [SIMPLE_EDGE] - SHUFFLE [RS_95] - PartitionCols:_col0 - Please refer to the previous Select Operator [SEL_15] - <-Map 28 [SIMPLE_EDGE] - SHUFFLE [RS_94] - PartitionCols:_col0 - Select Operator [SEL_87] (rows=144002668 width=135) - Output:["_col0","_col1","_col2","_col3"] - Filter Operator [FIL_170] (rows=144002668 width=135) - predicate:(ws_bill_addr_sk is not null and ws_item_sk is not null and ws_sold_date_sk is not null) - TableScan [TS_85] (rows=144002668 width=135) - default@web_sales,web_sales,Tbl:COMPLETE,Col:NONE,Output:["ws_sold_date_sk","ws_item_sk","ws_bill_addr_sk","ws_ext_sales_price"] - <-Reducer 4 [CONTAINS] - Reduce Output Operator [RS_115] - PartitionCols:_col0 - Group By Operator [GBY_114] (rows=670816147 width=108) - Output:["_col0","_col1"],aggregations:["sum(_col1)"],keys:_col0 - Group By Operator [GBY_35] (rows=383325119 width=88) - Output:["_col0","_col1"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0 - <-Reducer 3 [SIMPLE_EDGE] - SHUFFLE [RS_34] - PartitionCols:_col0 - Group By Operator [GBY_33] (rows=766650239 width=88) - Output:["_col0","_col1"],aggregations:["sum(_col8)"],keys:_col1 - Merge Join Operator [MERGEJOIN_182] (rows=766650239 width=88) - Conds:RS_29._col0=RS_30._col3(Inner),Output:["_col1","_col8"] - <-Reducer 2 [SIMPLE_EDGE] - SHUFFLE [RS_29] - PartitionCols:_col0 - Merge Join Operator [MERGEJOIN_173] (rows=508200 width=1436) - Conds:RS_26._col1=RS_27._col0(Inner),Output:["_col0","_col1"] - <-Map 1 [SIMPLE_EDGE] - SHUFFLE [RS_26] - PartitionCols:_col1 - Please refer to the previous Select Operator [SEL_2] - <-Reducer 15 [ONE_TO_ONE_EDGE] - FORWARD [RS_27] - PartitionCols:_col0 - Group By Operator [GBY_8] (rows=115500 width=1436) - Output:["_col0"],keys:KEY._col0 - <-Map 14 [SIMPLE_EDGE] - SHUFFLE [RS_7] - PartitionCols:_col0 - Please refer to the previous Group By Operator [GBY_6] - <-Reducer 20 [SIMPLE_EDGE] - SHUFFLE [RS_30] - PartitionCols:_col3 - Select Operator [SEL_25] (rows=696954748 width=88) - Output:["_col3","_col5"] - Merge Join Operator [MERGEJOIN_175] (rows=696954748 width=88) - Conds:RS_22._col2=RS_23._col0(Inner),Output:["_col1","_col3"] - <-Map 26 [SIMPLE_EDGE] - SHUFFLE [RS_23] - PartitionCols:_col0 - Please refer to the previous Select Operator [SEL_18] - <-Reducer 19 [SIMPLE_EDGE] - SHUFFLE [RS_22] - PartitionCols:_col2 - Merge Join Operator [MERGEJOIN_174] (rows=633595212 width=88) - Conds:RS_19._col0=RS_20._col0(Inner),Output:["_col1","_col2","_col3"] - <-Map 21 [SIMPLE_EDGE] - SHUFFLE [RS_20] - PartitionCols:_col0 - Please refer to the previous Select Operator [SEL_15] - <-Map 18 [SIMPLE_EDGE] - SHUFFLE [RS_19] - PartitionCols:_col0 - Select Operator [SEL_12] (rows=575995635 width=88) - Output:["_col0","_col1","_col2","_col3"] - Filter Operator [FIL_160] (rows=575995635 width=88) - predicate:(ss_addr_sk is not null and ss_item_sk is not null and ss_sold_date_sk is not null) - TableScan [TS_10] (rows=575995635 width=88) - default@store_sales,store_sales,Tbl:COMPLETE,Col:NONE,Output:["ss_sold_date_sk","ss_item_sk","ss_addr_sk","ss_ext_sales_price"] -
