http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/query51.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query51.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query51.q.out new file mode 100644 index 0000000..9862559 --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query51.q.out @@ -0,0 +1,222 @@ +PREHOOK: query: explain +WITH web_v1 as ( +select + ws_item_sk item_sk, d_date, + sum(sum(ws_sales_price)) + over (partition by ws_item_sk order by d_date rows between unbounded preceding and current row) cume_sales +from web_sales + ,date_dim +where ws_sold_date_sk=d_date_sk + and d_month_seq between 1212 and 1212+11 + and ws_item_sk is not NULL +group by ws_item_sk, d_date), +store_v1 as ( +select + ss_item_sk item_sk, d_date, + sum(sum(ss_sales_price)) + over (partition by ss_item_sk order by d_date rows between unbounded preceding and current row) cume_sales +from store_sales + ,date_dim +where ss_sold_date_sk=d_date_sk + and d_month_seq between 1212 and 1212+11 + and ss_item_sk is not NULL +group by ss_item_sk, d_date) + select * +from (select item_sk + ,d_date + ,web_sales + ,store_sales + ,max(web_sales) + over (partition by item_sk order by d_date rows between unbounded preceding and current row) web_cumulative + ,max(store_sales) + over (partition by item_sk order by d_date rows between unbounded preceding and current row) store_cumulative + from (select case when web.item_sk is not null then web.item_sk else store.item_sk end item_sk + ,case when web.d_date is not null then web.d_date else store.d_date end d_date + ,web.cume_sales web_sales + ,store.cume_sales store_sales + from web_v1 web full outer join store_v1 store on (web.item_sk = store.item_sk + and web.d_date = store.d_date) + )x )y +where web_cumulative > store_cumulative +order by item_sk + ,d_date +limit 100 +PREHOOK: type: QUERY +PREHOOK: Input: default@date_dim +PREHOOK: Input: default@store_sales +PREHOOK: Input: default@web_sales +PREHOOK: Output: hdfs://### HDFS PATH ### +POSTHOOK: query: explain +WITH web_v1 as ( +select + ws_item_sk item_sk, d_date, + sum(sum(ws_sales_price)) + over (partition by ws_item_sk order by d_date rows between unbounded preceding and current row) cume_sales +from web_sales + ,date_dim +where ws_sold_date_sk=d_date_sk + and d_month_seq between 1212 and 1212+11 + and ws_item_sk is not NULL +group by ws_item_sk, d_date), +store_v1 as ( +select + ss_item_sk item_sk, d_date, + sum(sum(ss_sales_price)) + over (partition by ss_item_sk order by d_date rows between unbounded preceding and current row) cume_sales +from store_sales + ,date_dim +where ss_sold_date_sk=d_date_sk + and d_month_seq between 1212 and 1212+11 + and ss_item_sk is not NULL +group by ss_item_sk, d_date) + select * +from (select item_sk + ,d_date + ,web_sales + ,store_sales + ,max(web_sales) + over (partition by item_sk order by d_date rows between unbounded preceding and current row) web_cumulative + ,max(store_sales) + over (partition by item_sk order by d_date rows between unbounded preceding and current row) store_cumulative + from (select case when web.item_sk is not null then web.item_sk else store.item_sk end item_sk + ,case when web.d_date is not null then web.d_date else store.d_date end d_date + ,web.cume_sales web_sales + ,store.cume_sales store_sales + from web_v1 web full outer join store_v1 store on (web.item_sk = store.item_sk + and web.d_date = store.d_date) + )x )y +where web_cumulative > store_cumulative +order by item_sk + ,d_date +limit 100 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@date_dim +POSTHOOK: Input: default@store_sales +POSTHOOK: Input: default@web_sales +POSTHOOK: Output: hdfs://### HDFS PATH ### +Plan optimized by CBO. + +Vertex dependency in root stage +Map 1 <- Reducer 8 (BROADCAST_EDGE) +Map 12 <- Reducer 11 (BROADCAST_EDGE) +Reducer 10 <- Reducer 9 (SIMPLE_EDGE) +Reducer 11 <- Map 7 (CUSTOM_SIMPLE_EDGE) +Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 7 (SIMPLE_EDGE) +Reducer 3 <- Reducer 2 (SIMPLE_EDGE) +Reducer 4 <- Reducer 10 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE) +Reducer 5 <- Reducer 4 (SIMPLE_EDGE) +Reducer 6 <- Reducer 5 (SIMPLE_EDGE) +Reducer 8 <- Map 7 (CUSTOM_SIMPLE_EDGE) +Reducer 9 <- Map 12 (SIMPLE_EDGE), Map 7 (SIMPLE_EDGE) + +Stage-0 + Fetch Operator + limit:100 + Stage-1 + Reducer 6 vectorized + File Output Operator [FS_117] + Limit [LIM_116] (rows=100 width=636) + Number of rows:100 + Select Operator [SEL_115] (rows=363803676 width=636) + Output:["_col0","_col1","_col2","_col3","_col4","_col5"] + <-Reducer 5 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_114] + Select Operator [SEL_113] (rows=363803676 width=636) + Output:["_col0","_col1","_col2","_col3","_col4","_col5"] + Filter Operator [FIL_112] (rows=363803676 width=420) + predicate:(max_window_0 > max_window_1) + PTF Operator [PTF_111] (rows=1091411029 width=420) + Function definitions:[{},{"name:":"windowingtablefunction","order by:":"CASE WHEN (_col4 is not null) THEN (_col4) ELSE (_col1) END ASC NULLS LAST","partition by:":"CASE WHEN (_col3 is not null) THEN (_col3) ELSE (_col0) END"}] + Select Operator [SEL_110] (rows=1091411029 width=420) + Output:["_col0","_col1","_col2","_col3","_col4","_col5"] + <-Reducer 4 [SIMPLE_EDGE] + SHUFFLE [RS_43] + PartitionCols:CASE WHEN (_col3 is not null) THEN (_col3) ELSE (_col0) END + Merge Join Operator [MERGEJOIN_87] (rows=1091411029 width=420) + Conds:RS_40._col0, _col1=RS_41._col0, _col1(Outer),Output:["_col0","_col1","_col2","_col3","_col4","_col5"] + <-Reducer 10 [SIMPLE_EDGE] + SHUFFLE [RS_41] + PartitionCols:_col0, _col1 + Select Operator [SEL_37] (rows=3442937 width=210) + Output:["_col0","_col1","_col2"] + PTF Operator [PTF_36] (rows=3442937 width=210) + Function definitions:[{},{"name:":"windowingtablefunction","order by:":"_col1 ASC NULLS LAST","partition by:":"_col0"}] + Group By Operator [GBY_32] (rows=3442937 width=210) + Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1 + <-Reducer 9 [SIMPLE_EDGE] + SHUFFLE [RS_31] + PartitionCols:_col0 + Group By Operator [GBY_30] (rows=24992810 width=210) + Output:["_col0","_col1","_col2"],aggregations:["sum(_col2)"],keys:_col1, _col4 + Merge Join Operator [MERGEJOIN_86] (rows=24992810 width=209) + Conds:RS_108._col0=RS_92._col0(Inner),Output:["_col1","_col2","_col4"] + <-Map 7 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_92] + PartitionCols:_col0 + Select Operator [SEL_89] (rows=317 width=98) + Output:["_col0","_col1"] + Filter Operator [FIL_88] (rows=317 width=102) + predicate:d_month_seq BETWEEN 1212 AND 1223 + TableScan [TS_3] (rows=73049 width=102) + default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_date","d_month_seq"] + <-Map 12 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_108] + PartitionCols:_col0 + Select Operator [SEL_107] (rows=143966864 width=119) + Output:["_col0","_col1","_col2"] + Filter Operator [FIL_106] (rows=143966864 width=119) + predicate:((ws_sold_date_sk BETWEEN DynamicValue(RS_27_date_dim_d_date_sk_min) AND DynamicValue(RS_27_date_dim_d_date_sk_max) and in_bloom_filter(ws_sold_date_sk, DynamicValue(RS_27_date_dim_d_date_sk_bloom_filter))) and ws_sold_date_sk is not null) + TableScan [TS_20] (rows=144002668 width=119) + default@web_sales,web_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ws_sold_date_sk","ws_item_sk","ws_sales_price"] + <-Reducer 11 [BROADCAST_EDGE] vectorized + BROADCAST [RS_105] + Group By Operator [GBY_104] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 7 [CUSTOM_SIMPLE_EDGE] vectorized + SHUFFLE [RS_97] + Group By Operator [GBY_95] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_93] (rows=317 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_89] + <-Reducer 3 [SIMPLE_EDGE] + SHUFFLE [RS_40] + PartitionCols:_col0, _col1 + Select Operator [SEL_17] (rows=3442937 width=210) + Output:["_col0","_col1","_col2"] + PTF Operator [PTF_16] (rows=3442937 width=210) + Function definitions:[{},{"name:":"windowingtablefunction","order by:":"_col1 ASC NULLS LAST","partition by:":"_col0"}] + Group By Operator [GBY_12] (rows=3442937 width=210) + Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1 + <-Reducer 2 [SIMPLE_EDGE] + SHUFFLE [RS_11] + PartitionCols:_col0 + Group By Operator [GBY_10] (rows=95493908 width=210) + Output:["_col0","_col1","_col2"],aggregations:["sum(_col2)"],keys:_col1, _col4 + Merge Join Operator [MERGEJOIN_85] (rows=95493908 width=180) + Conds:RS_102._col0=RS_90._col0(Inner),Output:["_col1","_col2","_col4"] + <-Map 7 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_90] + PartitionCols:_col0 + Please refer to the previous Select Operator [SEL_89] + <-Map 1 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_102] + PartitionCols:_col0 + Select Operator [SEL_101] (rows=550076554 width=114) + Output:["_col0","_col1","_col2"] + Filter Operator [FIL_100] (rows=550076554 width=114) + 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) + TableScan [TS_0] (rows=575995635 width=114) + default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_sold_date_sk","ss_item_sk","ss_sales_price"] + <-Reducer 8 [BROADCAST_EDGE] vectorized + BROADCAST [RS_99] + Group By Operator [GBY_98] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 7 [CUSTOM_SIMPLE_EDGE] vectorized + SHUFFLE [RS_96] + Group By Operator [GBY_94] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_91] (rows=317 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_89] +
http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/query52.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query52.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query52.q.out new file mode 100644 index 0000000..72f9151 --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query52.q.out @@ -0,0 +1,139 @@ +PREHOOK: query: explain +select dt.d_year + ,item.i_brand_id brand_id + ,item.i_brand brand + ,sum(ss_ext_sales_price) ext_price + from date_dim dt + ,store_sales + ,item + where dt.d_date_sk = store_sales.ss_sold_date_sk + and store_sales.ss_item_sk = item.i_item_sk + and item.i_manager_id = 1 + and dt.d_moy=12 + and dt.d_year=1998 + group by dt.d_year + ,item.i_brand + ,item.i_brand_id + order by dt.d_year + ,ext_price desc + ,brand_id +limit 100 +PREHOOK: type: QUERY +PREHOOK: Input: default@date_dim +PREHOOK: Input: default@item +PREHOOK: Input: default@store_sales +PREHOOK: Output: hdfs://### HDFS PATH ### +POSTHOOK: query: explain +select dt.d_year + ,item.i_brand_id brand_id + ,item.i_brand brand + ,sum(ss_ext_sales_price) ext_price + from date_dim dt + ,store_sales + ,item + where dt.d_date_sk = store_sales.ss_sold_date_sk + and store_sales.ss_item_sk = item.i_item_sk + and item.i_manager_id = 1 + and dt.d_moy=12 + and dt.d_year=1998 + group by dt.d_year + ,item.i_brand + ,item.i_brand_id + order by dt.d_year + ,ext_price desc + ,brand_id +limit 100 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@date_dim +POSTHOOK: Input: default@item +POSTHOOK: Input: default@store_sales +POSTHOOK: Output: hdfs://### HDFS PATH ### +Plan optimized by CBO. + +Vertex dependency in root stage +Map 1 <- Reducer 7 (BROADCAST_EDGE), Reducer 9 (BROADCAST_EDGE) +Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 6 (SIMPLE_EDGE) +Reducer 3 <- Map 8 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) +Reducer 4 <- Reducer 3 (SIMPLE_EDGE) +Reducer 5 <- Reducer 4 (SIMPLE_EDGE) +Reducer 7 <- Map 6 (CUSTOM_SIMPLE_EDGE) +Reducer 9 <- Map 8 (CUSTOM_SIMPLE_EDGE) + +Stage-0 + Fetch Operator + limit:-1 + Stage-1 + Reducer 5 vectorized + File Output Operator [FS_79] + Select Operator [SEL_78] (rows=100 width=220) + Output:["_col0","_col1","_col2","_col3"] + Limit [LIM_77] (rows=100 width=216) + Number of rows:100 + Select Operator [SEL_76] (rows=7333 width=216) + Output:["_col0","_col1","_col2"] + <-Reducer 4 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_75] + Group By Operator [GBY_74] (rows=7333 width=216) + Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1 + <-Reducer 3 [SIMPLE_EDGE] + SHUFFLE [RS_17] + PartitionCols:_col0, _col1 + Group By Operator [GBY_16] (rows=7333 width=216) + Output:["_col0","_col1","_col2"],aggregations:["sum(_col2)"],keys:_col5, _col6 + Merge Join Operator [MERGEJOIN_54] (rows=2301098 width=104) + Conds:RS_12._col1=RS_65._col0(Inner),Output:["_col2","_col5","_col6"] + <-Map 8 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_65] + PartitionCols:_col0 + Select Operator [SEL_64] (rows=7333 width=107) + Output:["_col0","_col1","_col2"] + Filter Operator [FIL_63] (rows=7333 width=111) + predicate:(i_manager_id = 1) + TableScan [TS_6] (rows=462000 width=111) + default@item,item,Tbl:COMPLETE,Col:COMPLETE,Output:["i_item_sk","i_brand_id","i_brand","i_manager_id"] + <-Reducer 2 [SIMPLE_EDGE] + SHUFFLE [RS_12] + PartitionCols:_col1 + Merge Join Operator [MERGEJOIN_53] (rows=15062131 width=4) + Conds:RS_73._col0=RS_57._col0(Inner),Output:["_col1","_col2"] + <-Map 6 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_57] + PartitionCols:_col0 + Select Operator [SEL_56] (rows=50 width=4) + Output:["_col0"] + Filter Operator [FIL_55] (rows=50 width=12) + predicate:((d_moy = 12) and (d_year = 1998)) + TableScan [TS_3] (rows=73049 width=12) + default@date_dim,dt,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_year","d_moy"] + <-Map 1 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_73] + PartitionCols:_col0 + Select Operator [SEL_72] (rows=550076554 width=114) + Output:["_col0","_col1","_col2"] + Filter Operator [FIL_71] (rows=550076554 width=114) + predicate:((ss_item_sk BETWEEN DynamicValue(RS_13_item_i_item_sk_min) AND DynamicValue(RS_13_item_i_item_sk_max) and in_bloom_filter(ss_item_sk, DynamicValue(RS_13_item_i_item_sk_bloom_filter))) and (ss_sold_date_sk BETWEEN DynamicValue(RS_10_dt_d_date_sk_min) AND DynamicValue(RS_10_dt_d_date_sk_max) and in_bloom_filter(ss_sold_date_sk, DynamicValue(RS_10_dt_d_date_sk_bloom_filter))) and ss_sold_date_sk is not null) + TableScan [TS_0] (rows=575995635 width=114) + default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_sold_date_sk","ss_item_sk","ss_ext_sales_price"] + <-Reducer 7 [BROADCAST_EDGE] vectorized + BROADCAST [RS_62] + Group By Operator [GBY_61] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 6 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_60] + Group By Operator [GBY_59] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_58] (rows=50 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_56] + <-Reducer 9 [BROADCAST_EDGE] vectorized + BROADCAST [RS_70] + Group By Operator [GBY_69] (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_68] + Group By Operator [GBY_67] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_66] (rows=7333 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_64] + http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/query53.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query53.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query53.q.out new file mode 100644 index 0000000..27adc6e --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query53.q.out @@ -0,0 +1,161 @@ +PREHOOK: query: explain +select * from +(select i_manufact_id, +sum(ss_sales_price) sum_sales, +avg(sum(ss_sales_price)) over (partition by i_manufact_id) avg_quarterly_sales +from item, store_sales, date_dim, store +where ss_item_sk = i_item_sk and +ss_sold_date_sk = d_date_sk and +ss_store_sk = s_store_sk and +d_month_seq in (1212,1212+1,1212+2,1212+3,1212+4,1212+5,1212+6,1212+7,1212+8,1212+9,1212+10,1212+11) and +((i_category in ('Books','Children','Electronics') and +i_class in ('personal','portable','reference','self-help') and +i_brand in ('scholaramalgamalg #14','scholaramalgamalg #7', + 'exportiunivamalg #9','scholaramalgamalg #9')) +or(i_category in ('Women','Music','Men') and +i_class in ('accessories','classical','fragrances','pants') and +i_brand in ('amalgimporto #1','edu packscholar #1','exportiimporto #1', + 'importoamalg #1'))) +group by i_manufact_id, d_qoy ) tmp1 +where case when avg_quarterly_sales > 0 + then abs (sum_sales - avg_quarterly_sales)/ avg_quarterly_sales + else null end > 0.1 +order by avg_quarterly_sales, + sum_sales, + i_manufact_id +limit 100 +PREHOOK: type: QUERY +PREHOOK: Input: default@date_dim +PREHOOK: Input: default@item +PREHOOK: Input: default@store +PREHOOK: Input: default@store_sales +PREHOOK: Output: hdfs://### HDFS PATH ### +POSTHOOK: query: explain +select * from +(select i_manufact_id, +sum(ss_sales_price) sum_sales, +avg(sum(ss_sales_price)) over (partition by i_manufact_id) avg_quarterly_sales +from item, store_sales, date_dim, store +where ss_item_sk = i_item_sk and +ss_sold_date_sk = d_date_sk and +ss_store_sk = s_store_sk and +d_month_seq in (1212,1212+1,1212+2,1212+3,1212+4,1212+5,1212+6,1212+7,1212+8,1212+9,1212+10,1212+11) and +((i_category in ('Books','Children','Electronics') and +i_class in ('personal','portable','reference','self-help') and +i_brand in ('scholaramalgamalg #14','scholaramalgamalg #7', + 'exportiunivamalg #9','scholaramalgamalg #9')) +or(i_category in ('Women','Music','Men') and +i_class in ('accessories','classical','fragrances','pants') and +i_brand in ('amalgimporto #1','edu packscholar #1','exportiimporto #1', + 'importoamalg #1'))) +group by i_manufact_id, d_qoy ) tmp1 +where case when avg_quarterly_sales > 0 + then abs (sum_sales - avg_quarterly_sales)/ avg_quarterly_sales + else null end > 0.1 +order by avg_quarterly_sales, + sum_sales, + i_manufact_id +limit 100 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@date_dim +POSTHOOK: Input: default@item +POSTHOOK: Input: default@store +POSTHOOK: Input: default@store_sales +POSTHOOK: Output: hdfs://### HDFS PATH ### +Plan optimized by CBO. + +Vertex dependency in root stage +Map 1 <- Reducer 7 (BROADCAST_EDGE), Reducer 9 (BROADCAST_EDGE) +Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 6 (SIMPLE_EDGE) +Reducer 3 <- Map 8 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) +Reducer 4 <- Reducer 3 (SIMPLE_EDGE) +Reducer 5 <- Reducer 4 (SIMPLE_EDGE) +Reducer 7 <- Map 6 (CUSTOM_SIMPLE_EDGE) +Reducer 9 <- Map 8 (CUSTOM_SIMPLE_EDGE) + +Stage-0 + Fetch Operator + limit:100 + Stage-1 + Reducer 5 vectorized + File Output Operator [FS_86] + Limit [LIM_85] (rows=30 width=228) + Number of rows:100 + Select Operator [SEL_84] (rows=30 width=228) + Output:["_col0","_col1","_col2"] + <-Reducer 4 [SIMPLE_EDGE] + SHUFFLE [RS_27] + Select Operator [SEL_24] (rows=30 width=228) + Output:["_col0","_col1","_col2"] + Filter Operator [FIL_36] (rows=30 width=228) + predicate:CASE WHEN ((avg_window_0 > 0)) THEN (((abs((_col2 - avg_window_0)) / avg_window_0) > 0.1)) ELSE (null) END + Select Operator [SEL_23] (rows=60 width=116) + Output:["avg_window_0","_col0","_col2"] + PTF Operator [PTF_22] (rows=60 width=116) + Function definitions:[{},{"name:":"windowingtablefunction","order by:":"_col0 ASC NULLS FIRST","partition by:":"_col0"}] + Select Operator [SEL_19] (rows=60 width=116) + Output:["_col0","_col2"] + Group By Operator [GBY_18] (rows=60 width=120) + Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1 + <-Reducer 3 [SIMPLE_EDGE] + SHUFFLE [RS_17] + PartitionCols:_col0 + Group By Operator [GBY_16] (rows=60 width=120) + Output:["_col0","_col1","_col2"],aggregations:["sum(_col2)"],keys:_col4, _col6 + Merge Join Operator [MERGEJOIN_62] (rows=129200 width=8) + Conds:RS_12._col0=RS_73._col0(Inner),Output:["_col2","_col4","_col6"] + <-Map 8 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_73] + PartitionCols:_col0 + Select Operator [SEL_72] (rows=317 width=8) + Output:["_col0","_col1"] + Filter Operator [FIL_71] (rows=317 width=12) + predicate:(d_month_seq) IN (1212, 1213, 1214, 1215, 1216, 1217, 1218, 1219, 1220, 1221, 1222, 1223) + TableScan [TS_6] (rows=73049 width=12) + default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_month_seq","d_qoy"] + <-Reducer 2 [SIMPLE_EDGE] + SHUFFLE [RS_12] + PartitionCols:_col0 + Merge Join Operator [MERGEJOIN_61] (rows=744232 width=4) + Conds:RS_81._col1=RS_65._col0(Inner),Output:["_col0","_col2","_col4"] + <-Map 6 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_65] + PartitionCols:_col0 + Select Operator [SEL_64] (rows=68 width=8) + Output:["_col0","_col1"] + Filter Operator [FIL_63] (rows=68 width=290) + predicate:((((i_category) IN ('Books', 'Children', 'Electronics') and (i_class) IN ('personal', 'portable', 'reference', 'self-help') and (i_brand) IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9')) or ((i_category) IN ('Women', 'Music', 'Men') and (i_class) IN ('accessories', 'classical', 'fragrances', 'pants') and (i_brand) IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1'))) and (i_brand) IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9', 'amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1') and (i_category) IN ('Books', 'Children', 'Electronics', 'Women', 'Music', 'Men') and (i_class) IN ('personal', 'portable', 'reference', 'self-help', 'accessories', 'classical', 'fragrances', 'pants')) + TableScan [TS_3] (rows=462000 width=289) + default@item,item,Tbl:COMPLETE,Col:COMPLETE,Output:["i_item_sk","i_brand","i_class","i_category","i_manufact_id"] + <-Map 1 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_81] + PartitionCols:_col1 + Select Operator [SEL_80] (rows=525329897 width=114) + Output:["_col0","_col1","_col2"] + Filter Operator [FIL_79] (rows=525329897 width=118) + predicate:((ss_item_sk BETWEEN DynamicValue(RS_10_item_i_item_sk_min) AND DynamicValue(RS_10_item_i_item_sk_max) and in_bloom_filter(ss_item_sk, DynamicValue(RS_10_item_i_item_sk_bloom_filter))) and (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 and ss_store_sk is not null) + TableScan [TS_0] (rows=575995635 width=118) + default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_sold_date_sk","ss_item_sk","ss_store_sk","ss_sales_price"] + <-Reducer 7 [BROADCAST_EDGE] vectorized + BROADCAST [RS_70] + Group By Operator [GBY_69] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 6 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_68] + Group By Operator [GBY_67] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_66] (rows=68 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_64] + <-Reducer 9 [BROADCAST_EDGE] vectorized + BROADCAST [RS_78] + Group By Operator [GBY_77] (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_76] + Group By Operator [GBY_75] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_74] (rows=317 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_72] + http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/query54.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query54.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query54.q.out new file mode 100644 index 0000000..0210163 --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query54.q.out @@ -0,0 +1,439 @@ +Warning: Shuffle Join MERGEJOIN[270][tables = [$hdt$_0, $hdt$_1, $hdt$_2, $hdt$_3]] in Stage 'Reducer 4' is a cross product +Warning: Shuffle Join MERGEJOIN[271][tables = [$hdt$_0, $hdt$_1, $hdt$_2, $hdt$_3, $hdt$_4]] in Stage 'Reducer 5' is a cross product +Warning: Shuffle Join MERGEJOIN[269][tables = [$hdt$_1, $hdt$_2]] in Stage 'Reducer 32' is a cross product +Warning: Shuffle Join MERGEJOIN[272][tables = [$hdt$_1, $hdt$_2, $hdt$_0]] in Stage 'Reducer 6' is a cross product +PREHOOK: query: explain +with my_customers as ( + select distinct c_customer_sk + , c_current_addr_sk + from + ( select cs_sold_date_sk sold_date_sk, + cs_bill_customer_sk customer_sk, + cs_item_sk item_sk + from catalog_sales + union all + select ws_sold_date_sk sold_date_sk, + ws_bill_customer_sk customer_sk, + ws_item_sk item_sk + from web_sales + ) cs_or_ws_sales, + item, + date_dim, + customer + where sold_date_sk = d_date_sk + and item_sk = i_item_sk + and i_category = 'Jewelry' + and i_class = 'consignment' + and c_customer_sk = cs_or_ws_sales.customer_sk + and d_moy = 3 + and d_year = 1999 + ) + , my_revenue as ( + select c_customer_sk, + sum(ss_ext_sales_price) as revenue + from my_customers, + store_sales, + customer_address, + store, + date_dim + where c_current_addr_sk = ca_address_sk + and ca_county = s_county + and ca_state = s_state + and ss_sold_date_sk = d_date_sk + and c_customer_sk = ss_customer_sk + and d_month_seq between (select distinct d_month_seq+1 + from date_dim where d_year = 1999 and d_moy = 3) + and (select distinct d_month_seq+3 + from date_dim where d_year = 1999 and d_moy = 3) + group by c_customer_sk + ) + , segments as + (select cast((revenue/50) as int) as segment + from my_revenue + ) + select segment, count(*) as num_customers, segment*50 as segment_base + from segments + group by segment + order by segment, num_customers + limit 100 +PREHOOK: type: QUERY +PREHOOK: Input: default@catalog_sales +PREHOOK: Input: default@customer +PREHOOK: Input: default@customer_address +PREHOOK: Input: default@date_dim +PREHOOK: Input: default@item +PREHOOK: Input: default@store +PREHOOK: Input: default@store_sales +PREHOOK: Input: default@web_sales +PREHOOK: Output: hdfs://### HDFS PATH ### +POSTHOOK: query: explain +with my_customers as ( + select distinct c_customer_sk + , c_current_addr_sk + from + ( select cs_sold_date_sk sold_date_sk, + cs_bill_customer_sk customer_sk, + cs_item_sk item_sk + from catalog_sales + union all + select ws_sold_date_sk sold_date_sk, + ws_bill_customer_sk customer_sk, + ws_item_sk item_sk + from web_sales + ) cs_or_ws_sales, + item, + date_dim, + customer + where sold_date_sk = d_date_sk + and item_sk = i_item_sk + and i_category = 'Jewelry' + and i_class = 'consignment' + and c_customer_sk = cs_or_ws_sales.customer_sk + and d_moy = 3 + and d_year = 1999 + ) + , my_revenue as ( + select c_customer_sk, + sum(ss_ext_sales_price) as revenue + from my_customers, + store_sales, + customer_address, + store, + date_dim + where c_current_addr_sk = ca_address_sk + and ca_county = s_county + and ca_state = s_state + and ss_sold_date_sk = d_date_sk + and c_customer_sk = ss_customer_sk + and d_month_seq between (select distinct d_month_seq+1 + from date_dim where d_year = 1999 and d_moy = 3) + and (select distinct d_month_seq+3 + from date_dim where d_year = 1999 and d_moy = 3) + group by c_customer_sk + ) + , segments as + (select cast((revenue/50) as int) as segment + from my_revenue + ) + select segment, count(*) as num_customers, segment*50 as segment_base + from segments + group by segment + order by segment, num_customers + limit 100 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@catalog_sales +POSTHOOK: Input: default@customer +POSTHOOK: Input: default@customer_address +POSTHOOK: Input: default@date_dim +POSTHOOK: Input: default@item +POSTHOOK: Input: default@store +POSTHOOK: Input: default@store_sales +POSTHOOK: Input: default@web_sales +POSTHOOK: Output: hdfs://### HDFS PATH ### +Plan optimized by CBO. + +Vertex dependency in root stage +Map 1 <- Reducer 14 (BROADCAST_EDGE) +Map 16 <- Reducer 24 (BROADCAST_EDGE), Reducer 26 (BROADCAST_EDGE), Union 17 (CONTAINS) +Map 22 <- Reducer 24 (BROADCAST_EDGE), Reducer 26 (BROADCAST_EDGE), Union 17 (CONTAINS) +Reducer 12 <- Map 11 (SIMPLE_EDGE), Map 15 (SIMPLE_EDGE) +Reducer 13 <- Reducer 12 (SIMPLE_EDGE), Reducer 21 (SIMPLE_EDGE) +Reducer 14 <- Reducer 13 (CUSTOM_SIMPLE_EDGE) +Reducer 18 <- Map 23 (SIMPLE_EDGE), Union 17 (SIMPLE_EDGE) +Reducer 19 <- Map 25 (SIMPLE_EDGE), Reducer 18 (SIMPLE_EDGE) +Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 10 (SIMPLE_EDGE) +Reducer 20 <- Map 27 (SIMPLE_EDGE), Reducer 19 (SIMPLE_EDGE) +Reducer 21 <- Reducer 20 (SIMPLE_EDGE) +Reducer 24 <- Map 23 (CUSTOM_SIMPLE_EDGE) +Reducer 26 <- Map 25 (CUSTOM_SIMPLE_EDGE) +Reducer 29 <- Map 28 (SIMPLE_EDGE) +Reducer 3 <- Reducer 13 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) +Reducer 30 <- Reducer 29 (CUSTOM_SIMPLE_EDGE) +Reducer 31 <- Map 28 (SIMPLE_EDGE) +Reducer 32 <- Reducer 31 (CUSTOM_SIMPLE_EDGE), Reducer 34 (CUSTOM_SIMPLE_EDGE) +Reducer 33 <- Map 28 (SIMPLE_EDGE) +Reducer 34 <- Reducer 33 (CUSTOM_SIMPLE_EDGE) +Reducer 4 <- Reducer 3 (CUSTOM_SIMPLE_EDGE), Reducer 30 (CUSTOM_SIMPLE_EDGE) +Reducer 5 <- Reducer 29 (CUSTOM_SIMPLE_EDGE), Reducer 4 (CUSTOM_SIMPLE_EDGE) +Reducer 6 <- Reducer 32 (CUSTOM_SIMPLE_EDGE), Reducer 5 (CUSTOM_SIMPLE_EDGE) +Reducer 7 <- Reducer 6 (SIMPLE_EDGE) +Reducer 8 <- Reducer 7 (SIMPLE_EDGE) +Reducer 9 <- Reducer 8 (SIMPLE_EDGE) + +Stage-0 + Fetch Operator + limit:100 + Stage-1 + Reducer 9 vectorized + File Output Operator [FS_352] + Limit [LIM_351] (rows=1 width=16) + Number of rows:100 + Select Operator [SEL_350] (rows=1 width=16) + Output:["_col0","_col1","_col2"] + <-Reducer 8 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_349] + Select Operator [SEL_348] (rows=1 width=16) + Output:["_col0","_col1","_col2"] + Group By Operator [GBY_347] (rows=1 width=12) + Output:["_col0","_col1"],aggregations:["count(VALUE._col0)"],keys:KEY._col0 + <-Reducer 7 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_346] + PartitionCols:_col0 + Group By Operator [GBY_345] (rows=1 width=12) + Output:["_col0","_col1"],aggregations:["count()"],keys:_col0 + Select Operator [SEL_344] (rows=1 width=116) + Output:["_col0"] + Group By Operator [GBY_343] (rows=1 width=116) + Output:["_col0","_col1"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0 + <-Reducer 6 [SIMPLE_EDGE] + SHUFFLE [RS_118] + PartitionCols:_col0 + Group By Operator [GBY_117] (rows=312 width=116) + Output:["_col0","_col1"],aggregations:["sum(_col1)"],keys:_col0 + Select Operator [SEL_116] (rows=624257222 width=127) + Output:["_col0","_col1"] + Filter Operator [FIL_115] (rows=624257222 width=127) + predicate:_col2 BETWEEN _col3 AND _col4 + Select Operator [SEL_114] (rows=5618315000 width=127) + Output:["_col0","_col1","_col2","_col3","_col4"] + Merge Join Operator [MERGEJOIN_272] (rows=5618315000 width=127) + Conds:(Inner),Output:["_col0","_col2","_col6","_col13","_col15"] + <-Reducer 32 [CUSTOM_SIMPLE_EDGE] + PARTITION_ONLY_SHUFFLE [RS_111] + Merge Join Operator [MERGEJOIN_269] (rows=25 width=4) + Conds:(Right Outer),Output:["_col0"] + <-Reducer 31 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_334] + Group By Operator [GBY_333] (rows=25 width=4) + Output:["_col0"],keys:KEY._col0 + <-Map 28 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_322] + PartitionCols:_col0 + Group By Operator [GBY_319] (rows=25 width=4) + Output:["_col0"],keys:_col0 + Select Operator [SEL_316] (rows=50 width=12) + Output:["_col0"] + Filter Operator [FIL_314] (rows=50 width=12) + predicate:((d_moy = 3) and (d_year = 1999)) + TableScan [TS_72] (rows=73049 width=12) + default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_month_seq","d_year","d_moy"] + <-Reducer 34 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_342] + Select Operator [SEL_341] (rows=1 width=8) + Filter Operator [FIL_340] (rows=1 width=8) + predicate:(sq_count_check(_col0) <= 1) + Group By Operator [GBY_339] (rows=1 width=8) + Output:["_col0"],aggregations:["count(VALUE._col0)"] + <-Reducer 33 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_338] + Group By Operator [GBY_337] (rows=1 width=8) + Output:["_col0"],aggregations:["count()"] + Select Operator [SEL_336] (rows=25 width=4) + Group By Operator [GBY_335] (rows=25 width=4) + Output:["_col0"],keys:KEY._col0 + <-Map 28 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_323] + PartitionCols:_col0 + Group By Operator [GBY_320] (rows=25 width=4) + Output:["_col0"],keys:_col0 + Select Operator [SEL_317] (rows=50 width=12) + Output:["_col0"] + Please refer to the previous Filter Operator [FIL_314] + <-Reducer 5 [CUSTOM_SIMPLE_EDGE] + PARTITION_ONLY_SHUFFLE [RS_112] + Select Operator [SEL_107] (rows=224732600 width=119) + Output:["_col0","_col4","_col11","_col13"] + Merge Join Operator [MERGEJOIN_271] (rows=224732600 width=119) + Conds:(Left Outer),Output:["_col2","_col4","_col10","_col13"] + <-Reducer 29 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_326] + Group By Operator [GBY_324] (rows=25 width=4) + Output:["_col0"],keys:KEY._col0 + <-Map 28 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_321] + PartitionCols:_col0 + Group By Operator [GBY_318] (rows=25 width=4) + Output:["_col0"],keys:_col0 + Select Operator [SEL_315] (rows=50 width=12) + Output:["_col0"] + Please refer to the previous Filter Operator [FIL_314] + <-Reducer 4 [CUSTOM_SIMPLE_EDGE] + PARTITION_ONLY_SHUFFLE [RS_104] + Merge Join Operator [MERGEJOIN_270] (rows=8989304 width=8) + Conds:(Inner),Output:["_col2","_col4","_col10"] + <-Reducer 3 [CUSTOM_SIMPLE_EDGE] + PARTITION_ONLY_SHUFFLE [RS_101] + Merge Join Operator [MERGEJOIN_268] (rows=8989304 width=8) + Conds:RS_98._col1=RS_99._col5(Inner),Output:["_col2","_col4","_col10"] + <-Reducer 13 [SIMPLE_EDGE] + SHUFFLE [RS_99] + PartitionCols:_col5 + Merge Join Operator [MERGEJOIN_267] (rows=55046 width=4) + Conds:RS_68._col0=RS_306._col1(Inner),Output:["_col5"] + <-Reducer 12 [SIMPLE_EDGE] + SHUFFLE [RS_68] + PartitionCols:_col0 + Merge Join Operator [MERGEJOIN_263] (rows=39720279 width=4) + Conds:RS_285._col1, _col2=RS_288._col0, _col1(Inner),Output:["_col0"] + <-Map 11 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_285] + PartitionCols:_col1, _col2 + Select Operator [SEL_284] (rows=40000000 width=188) + Output:["_col0","_col1","_col2"] + Filter Operator [FIL_283] (rows=40000000 width=188) + predicate:(ca_county is not null and ca_state is not null) + TableScan [TS_28] (rows=40000000 width=188) + default@customer_address,customer_address,Tbl:COMPLETE,Col:COMPLETE,Output:["ca_address_sk","ca_county","ca_state"] + <-Map 15 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_288] + PartitionCols:_col0, _col1 + Select Operator [SEL_287] (rows=1704 width=184) + Output:["_col0","_col1"] + Filter Operator [FIL_286] (rows=1704 width=184) + predicate:(s_county is not null and s_state is not null) + TableScan [TS_31] (rows=1704 width=184) + default@store,store,Tbl:COMPLETE,Col:COMPLETE,Output:["s_county","s_state"] + <-Reducer 21 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_306] + PartitionCols:_col1 + Select Operator [SEL_305] (rows=55046 width=8) + Output:["_col0","_col1"] + Group By Operator [GBY_304] (rows=55046 width=8) + Output:["_col0","_col1"],keys:KEY._col0, KEY._col1 + <-Reducer 20 [SIMPLE_EDGE] + SHUFFLE [RS_62] + PartitionCols:_col0, _col1 + Group By Operator [GBY_61] (rows=55046 width=8) + Output:["_col0","_col1"],keys:_col6, _col5 + Merge Join Operator [MERGEJOIN_266] (rows=110092 width=8) + Conds:RS_57._col1=RS_303._col0(Inner),Output:["_col5","_col6"] + <-Map 27 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_303] + PartitionCols:_col0 + Select Operator [SEL_302] (rows=80000000 width=8) + Output:["_col0","_col1"] + Filter Operator [FIL_301] (rows=80000000 width=8) + predicate:c_current_addr_sk is not null + TableScan [TS_48] (rows=80000000 width=8) + default@customer,customer,Tbl:COMPLETE,Col:COMPLETE,Output:["c_customer_sk","c_current_addr_sk"] + <-Reducer 19 [SIMPLE_EDGE] + SHUFFLE [RS_57] + PartitionCols:_col1 + Merge Join Operator [MERGEJOIN_265] (rows=110092 width=0) + Conds:RS_54._col2=RS_297._col0(Inner),Output:["_col1"] + <-Map 25 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_297] + PartitionCols:_col0 + Select Operator [SEL_296] (rows=453 width=4) + Output:["_col0"] + Filter Operator [FIL_295] (rows=453 width=186) + predicate:((i_category = 'Jewelry') and (i_class = 'consignment')) + TableScan [TS_45] (rows=462000 width=186) + default@item,item,Tbl:COMPLETE,Col:COMPLETE,Output:["i_item_sk","i_class","i_category"] + <-Reducer 18 [SIMPLE_EDGE] + SHUFFLE [RS_54] + PartitionCols:_col2 + Merge Join Operator [MERGEJOIN_264] (rows=11665117 width=7) + Conds:Union 17._col0=RS_291._col0(Inner),Output:["_col1","_col2"] + <-Map 23 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_291] + PartitionCols:_col0 + Select Operator [SEL_290] (rows=50 width=4) + Output:["_col0"] + Filter Operator [FIL_289] (rows=50 width=12) + predicate:((d_moy = 3) and (d_year = 1999)) + TableScan [TS_42] (rows=73049 width=12) + default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_year","d_moy"] + <-Union 17 [SIMPLE_EDGE] + <-Map 16 [CONTAINS] vectorized + Reduce Output Operator [RS_361] + PartitionCols:_col0 + Select Operator [SEL_360] (rows=285117831 width=11) + Output:["_col0","_col1","_col2"] + Filter Operator [FIL_359] (rows=285117831 width=11) + predicate:((cs_item_sk BETWEEN DynamicValue(RS_55_item_i_item_sk_min) AND DynamicValue(RS_55_item_i_item_sk_max) and in_bloom_filter(cs_item_sk, DynamicValue(RS_55_item_i_item_sk_bloom_filter))) and (cs_sold_date_sk BETWEEN DynamicValue(RS_52_date_dim_d_date_sk_min) AND DynamicValue(RS_52_date_dim_d_date_sk_max) and in_bloom_filter(cs_sold_date_sk, DynamicValue(RS_52_date_dim_d_date_sk_bloom_filter))) and cs_bill_customer_sk is not null and cs_sold_date_sk is not null) + TableScan [TS_273] (rows=287989836 width=11) + Output:["cs_sold_date_sk","cs_bill_customer_sk","cs_item_sk"] + <-Reducer 24 [BROADCAST_EDGE] vectorized + BROADCAST [RS_354] + Group By Operator [GBY_353] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 23 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_294] + Group By Operator [GBY_293] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_292] (rows=50 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_290] + <-Reducer 26 [BROADCAST_EDGE] vectorized + BROADCAST [RS_357] + Group By Operator [GBY_356] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 25 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_300] + Group By Operator [GBY_299] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_298] (rows=453 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_296] + <-Map 22 [CONTAINS] vectorized + Reduce Output Operator [RS_364] + PartitionCols:_col0 + Select Operator [SEL_363] (rows=143930993 width=11) + Output:["_col0","_col1","_col2"] + Filter Operator [FIL_362] (rows=143930993 width=11) + predicate:((ws_item_sk BETWEEN DynamicValue(RS_55_item_i_item_sk_min) AND DynamicValue(RS_55_item_i_item_sk_max) and in_bloom_filter(ws_item_sk, DynamicValue(RS_55_item_i_item_sk_bloom_filter))) and (ws_sold_date_sk BETWEEN DynamicValue(RS_52_date_dim_d_date_sk_min) AND DynamicValue(RS_52_date_dim_d_date_sk_max) and in_bloom_filter(ws_sold_date_sk, DynamicValue(RS_52_date_dim_d_date_sk_bloom_filter))) and ws_bill_customer_sk is not null and ws_sold_date_sk is not null) + TableScan [TS_278] (rows=144002668 width=11) + Output:["ws_sold_date_sk","ws_item_sk","ws_bill_customer_sk"] + <-Reducer 24 [BROADCAST_EDGE] vectorized + BROADCAST [RS_355] + Please refer to the previous Group By Operator [GBY_353] + <-Reducer 26 [BROADCAST_EDGE] vectorized + BROADCAST [RS_358] + Please refer to the previous Group By Operator [GBY_356] + <-Reducer 2 [SIMPLE_EDGE] + SHUFFLE [RS_98] + PartitionCols:_col1 + Merge Join Operator [MERGEJOIN_262] (rows=525327388 width=114) + Conds:RS_311._col0=RS_313._col0(Inner),Output:["_col1","_col2","_col4"] + <-Map 1 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_311] + PartitionCols:_col0 + Select Operator [SEL_310] (rows=525327388 width=114) + Output:["_col0","_col1","_col2"] + Filter Operator [FIL_309] (rows=525327388 width=114) + predicate:((ss_customer_sk BETWEEN DynamicValue(RS_99_customer_c_customer_sk_min) AND DynamicValue(RS_99_customer_c_customer_sk_max) and in_bloom_filter(ss_customer_sk, DynamicValue(RS_99_customer_c_customer_sk_bloom_filter))) and ss_customer_sk is not null and ss_sold_date_sk is not null) + TableScan [TS_23] (rows=575995635 width=114) + default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_sold_date_sk","ss_customer_sk","ss_ext_sales_price"] + <-Reducer 14 [BROADCAST_EDGE] vectorized + BROADCAST [RS_308] + Group By Operator [GBY_307] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Reducer 13 [CUSTOM_SIMPLE_EDGE] + SHUFFLE [RS_182] + Group By Operator [GBY_181] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_180] (rows=55046 width=8) + Output:["_col0"] + Please refer to the previous Merge Join Operator [MERGEJOIN_267] + <-Map 10 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_313] + PartitionCols:_col0 + Select Operator [SEL_312] (rows=73049 width=8) + Output:["_col0","_col1"] + TableScan [TS_26] (rows=73049 width=8) + default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_month_seq"] + <-Reducer 30 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_332] + Select Operator [SEL_331] (rows=1 width=8) + Filter Operator [FIL_330] (rows=1 width=8) + predicate:(sq_count_check(_col0) <= 1) + Group By Operator [GBY_329] (rows=1 width=8) + Output:["_col0"],aggregations:["count(VALUE._col0)"] + <-Reducer 29 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_328] + Group By Operator [GBY_327] (rows=1 width=8) + Output:["_col0"],aggregations:["count()"] + Select Operator [SEL_325] (rows=25 width=4) + Please refer to the previous Group By Operator [GBY_324] + http://git-wip-us.apache.org/repos/asf/hive/blob/b8299551/ql/src/test/results/clientpositive/perf/tez/constraints/query55.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/query55.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/query55.q.out new file mode 100644 index 0000000..eb6b84f --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/tez/constraints/query55.q.out @@ -0,0 +1,123 @@ +PREHOOK: query: explain +select i_brand_id brand_id, i_brand brand, + sum(ss_ext_sales_price) ext_price + from date_dim, store_sales, item + where d_date_sk = ss_sold_date_sk + and ss_item_sk = i_item_sk + and i_manager_id=36 + and d_moy=12 + and d_year=2001 + group by i_brand, i_brand_id + order by ext_price desc, i_brand_id +limit 100 +PREHOOK: type: QUERY +PREHOOK: Input: default@date_dim +PREHOOK: Input: default@item +PREHOOK: Input: default@store_sales +PREHOOK: Output: hdfs://### HDFS PATH ### +POSTHOOK: query: explain +select i_brand_id brand_id, i_brand brand, + sum(ss_ext_sales_price) ext_price + from date_dim, store_sales, item + where d_date_sk = ss_sold_date_sk + and ss_item_sk = i_item_sk + and i_manager_id=36 + and d_moy=12 + and d_year=2001 + group by i_brand, i_brand_id + order by ext_price desc, i_brand_id +limit 100 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@date_dim +POSTHOOK: Input: default@item +POSTHOOK: Input: default@store_sales +POSTHOOK: Output: hdfs://### HDFS PATH ### +Plan optimized by CBO. + +Vertex dependency in root stage +Map 1 <- Reducer 7 (BROADCAST_EDGE), Reducer 9 (BROADCAST_EDGE) +Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 6 (SIMPLE_EDGE) +Reducer 3 <- Map 8 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) +Reducer 4 <- Reducer 3 (SIMPLE_EDGE) +Reducer 5 <- Reducer 4 (SIMPLE_EDGE) +Reducer 7 <- Map 6 (CUSTOM_SIMPLE_EDGE) +Reducer 9 <- Map 8 (CUSTOM_SIMPLE_EDGE) + +Stage-0 + Fetch Operator + limit:-1 + Stage-1 + Reducer 5 vectorized + File Output Operator [FS_79] + Limit [LIM_78] (rows=100 width=220) + Number of rows:100 + Select Operator [SEL_77] (rows=7333 width=220) + Output:["_col0","_col1","_col2"] + <-Reducer 4 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_76] + Select Operator [SEL_75] (rows=7333 width=220) + Output:["_col1","_col2","_col3"] + Group By Operator [GBY_74] (rows=7333 width=216) + Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1 + <-Reducer 3 [SIMPLE_EDGE] + SHUFFLE [RS_17] + PartitionCols:_col0, _col1 + Group By Operator [GBY_16] (rows=7333 width=216) + Output:["_col0","_col1","_col2"],aggregations:["sum(_col2)"],keys:_col5, _col6 + Merge Join Operator [MERGEJOIN_54] (rows=2301098 width=104) + Conds:RS_12._col1=RS_65._col0(Inner),Output:["_col2","_col5","_col6"] + <-Map 8 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_65] + PartitionCols:_col0 + Select Operator [SEL_64] (rows=7333 width=107) + Output:["_col0","_col1","_col2"] + Filter Operator [FIL_63] (rows=7333 width=111) + predicate:(i_manager_id = 36) + TableScan [TS_6] (rows=462000 width=111) + default@item,item,Tbl:COMPLETE,Col:COMPLETE,Output:["i_item_sk","i_brand_id","i_brand","i_manager_id"] + <-Reducer 2 [SIMPLE_EDGE] + SHUFFLE [RS_12] + PartitionCols:_col1 + Merge Join Operator [MERGEJOIN_53] (rows=15062131 width=4) + Conds:RS_73._col0=RS_57._col0(Inner),Output:["_col1","_col2"] + <-Map 6 [SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_57] + PartitionCols:_col0 + Select Operator [SEL_56] (rows=50 width=4) + Output:["_col0"] + Filter Operator [FIL_55] (rows=50 width=12) + predicate:((d_moy = 12) and (d_year = 2001)) + TableScan [TS_3] (rows=73049 width=12) + default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_year","d_moy"] + <-Map 1 [SIMPLE_EDGE] vectorized + SHUFFLE [RS_73] + PartitionCols:_col0 + Select Operator [SEL_72] (rows=550076554 width=114) + Output:["_col0","_col1","_col2"] + Filter Operator [FIL_71] (rows=550076554 width=114) + predicate:((ss_item_sk BETWEEN DynamicValue(RS_13_item_i_item_sk_min) AND DynamicValue(RS_13_item_i_item_sk_max) and in_bloom_filter(ss_item_sk, DynamicValue(RS_13_item_i_item_sk_bloom_filter))) and (ss_sold_date_sk BETWEEN DynamicValue(RS_10_date_dim_d_date_sk_min) AND DynamicValue(RS_10_date_dim_d_date_sk_max) and in_bloom_filter(ss_sold_date_sk, DynamicValue(RS_10_date_dim_d_date_sk_bloom_filter))) and ss_sold_date_sk is not null) + TableScan [TS_0] (rows=575995635 width=114) + default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_sold_date_sk","ss_item_sk","ss_ext_sales_price"] + <-Reducer 7 [BROADCAST_EDGE] vectorized + BROADCAST [RS_62] + Group By Operator [GBY_61] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] + <-Map 6 [CUSTOM_SIMPLE_EDGE] vectorized + PARTITION_ONLY_SHUFFLE [RS_60] + Group By Operator [GBY_59] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_58] (rows=50 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_56] + <-Reducer 9 [BROADCAST_EDGE] vectorized + BROADCAST [RS_70] + Group By Operator [GBY_69] (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_68] + Group By Operator [GBY_67] (rows=1 width=12) + Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] + Select Operator [SEL_66] (rows=7333 width=4) + Output:["_col0"] + Please refer to the previous Select Operator [SEL_64] +
