yiqijiu commented on PR #5556:
URL: https://github.com/apache/hive/pull/5556#issuecomment-2509630350
@okumin @ngsg
Hello, I finally managed to mock the issue. For example, with this statement
`create table explain_multiple_ptf_big_table
(
key1 string,
value_str1 string,
key3 string
);
create table explain_multiple_ptf_big_table2
(
key21 string,
value_str21 string,
key23 string
);
alter table explain_multiple_ptf_big_table
update statistics set('numRows' = '9223372036854775',
'rawDataSize' = '9223372036854775807');
alter table explain_multiple_ptf_big_table2
update statistics set('numRows' = '9223372036854775',
'rawDataSize' = '9223372036854775807');
explain
select *,row_number() over (partition by key order by key2 desc) rn,
row_number() over (partition by key2 order by key desc) rn2,
max(value_str) over (partition by key2 order by key desc) max1,
max(value_str) over (partition by key order by key2 desc) max3,
min(value_str) over (partition by key2 order by key desc) min1,
min(value_str) over (partition by key order by key2 desc) min3,
last_value(value_str) over (partition by key) lv,
first_value(value_str) over (partition by key2) fv,
max(value_str) over (partition by key) fv21
from (select key1 key, value_str1 value_str, key3 key2 from
explain_multiple_ptf_big_table
union all select key21 key, value_str21 value_str, key23 key2 from
explain_multiple_ptf_big_table2) a1
;`
it produces the following result.
`+----------------------------------------------------+
| Explain |
+----------------------------------------------------+
| Plan optimized by CBO. |
| |
| Vertex dependency in root stage |
| Map 1 <- Union 2 (CONTAINS) |
| Map 8 <- Union 2 (CONTAINS) |
| Reducer 3 <- Union 2 (SIMPLE_EDGE) |
| Reducer 4 <- Reducer 3 (SIMPLE_EDGE) |
| Reducer 5 <- Reducer 4 (SIMPLE_EDGE) |
| Reducer 6 <- Reducer 5 (SIMPLE_EDGE) |
| Reducer 7 <- Reducer 6 (SIMPLE_EDGE) |
| |
| Stage-0 |
| Fetch Operator |
| limit:-1 |
| Stage-1 |
| Reducer 7 |
| File Output Operator [FS_26] |
| Select Operator [SEL_24] (rows=18446744073709550 width=-475) |
|
Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10","_col11"]
|
| PTF Operator [PTF_23] (rows=18446744073709550 width=-475) |
| Function
definitions:[{},{"name:":"windowingtablefunction","order by:":"_col8 ASC NULLS
FIRST","partition by:":"_col8"}] |
| Select Operator [SEL_22] (rows=18446744073709550 width=-475) |
|
Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10"]
|
| <-Reducer 6 [SIMPLE_EDGE] |
| SHUFFLE [RS_21] |
| PartitionCols:_col7 |
| Select Operator [SEL_20] (rows=18446744073709550
width=-475) |
|
Output:["first_value_window_7","_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9"]
|
| PTF Operator [PTF_19] (rows=18446744073709550
width=-475) |
| Function
definitions:[{},{"name:":"windowingtablefunction","order by:":"_col9 ASC NULLS
FIRST","partition by:":"_col9"}] |
| Select Operator [SEL_18] (rows=18446744073709550
width=-475) |
|
Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9"]
|
| <-Reducer 5 [SIMPLE_EDGE] |
| SHUFFLE [RS_17] |
| PartitionCols:_col8 |
| Select Operator [SEL_16] (rows=18446744073709550
width=-475) |
|
Output:["first_value_window_6","_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"]
|
| PTF Operator [PTF_15] (rows=18446744073709550
width=-475) |
| Function
definitions:[{},{"name:":"windowingtablefunction","order by:":"_col6 DESC NULLS
LAST","partition by:":"_col6"}] |
| Select Operator [SEL_14]
(rows=18446744073709550 width=-475) |
|
Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"]
|
| <-Reducer 4 [SIMPLE_EDGE] |
| SHUFFLE [RS_13] |
| PartitionCols:_col3 |
| Select Operator [SEL_12]
(rows=18446744073709550 width=-475) |
|
Output:["row_number_window_1","max_window_2","min_window_4","_col0","_col1","_col2","_col3","_col4","_col5"]
|
| PTF Operator [PTF_11]
(rows=18446744073709550 width=-475) |
| Function
definitions:[{},{"name:":"windowingtablefunction","order by:":"_col3 DESC NULLS
FIRST","partition by:":"_col5"}] |
| Select Operator [SEL_10]
(rows=18446744073709550 width=-475) |
|
Output:["_col0","_col1","_col2","_col3","_col4","_col5"] |
| <-Reducer 3 [SIMPLE_EDGE] |
| SHUFFLE [RS_9] |
| PartitionCols:_col2 |
| Select Operator [SEL_8]
(rows=18446744073709550 width=-475) |
|
Output:["row_number_window_0","max_window_3","min_window_5","_col0","_col1","_col2"]
|
| PTF Operator [PTF_7]
(rows=18446744073709550 width=-475) |
| Function
definitions:[{},{"name:":"windowingtablefunction","order by:":"_col2 DESC NULLS
FIRST","partition by:":"_col0"}] |
| Select Operator [SEL_6]
(rows=18446744073709550 width=-475) |
|
Output:["_col0","_col1","_col2"] |
| <-Union 2 [SIMPLE_EDGE] |
| <-Map 1 [CONTAINS]
vectorized |
| Reduce Output Operator
[RS_36] |
| PartitionCols:_col0 |
| Select Operator [SEL_35]
(rows=9223372036854775 width=524) |
|
Output:["_col0","_col1","_col2"] |
| TableScan [TS_27]
(rows=9223372036854775 width=524) |
|
default@explain_multiple_ptf_big_table,explain_multiple_ptf_big_table,Tbl:COMPLETE,Col:NONE,Output:["key1","value_str1","key3"]
|
| <-Map 8 [CONTAINS]
vectorized |
| Reduce Output Operator
[RS_43] |
| PartitionCols:_col0 |
| Select Operator [SEL_42]
(rows=9223372036854775 width=524) |
|
Output:["_col0","_col1","_col2"] |
| TableScan [TS_31]
(rows=9223372036854775 width=524) |
|
default@explain_multiple_ptf_big_table2,explain_multiple_ptf_big_table2,Tbl:COMPLETE,Col:NONE,Output:["key21","value_str21","key23"]
|
| |
+----------------------------------------------------+`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
