felixwluo opened a new pull request, #67136: URL: https://github.com/apache/doris/pull/67136
### What problem does this PR solve?
Issue Number: close #xxx
Related PR: #xxx
Problem Summary:
For external Hive tables, queries using predicates like
`dt = (select max(dt) from table)` could not use partition pruning
effectively.
The scalar subquery `select max(dt)` was planned as a normal file scan. In a
large online cloud-mode cluster, this caused Doris to scan all partitions and
billions of rows only to compute the latest partition value. The outer scan
also
kept `dt = MAX(dt)` as a join condition instead of a literal partition
predicate,
so it scanned all `dt` partitions before filtering rows.
This patch adds a Nereids rewrite rule for external tables. For simple
no-group-by `MIN/MAX` aggregate queries on a single external list partition
column, Doris now computes the result from partition metadata and rewrites
the
aggregate to a one-row constant relation. This allows predicates such as
`dt = (select max(dt) ...)` to become `dt = 'literal'` before file-scan
partition
pruning, so the outer scan can prune to the target partition.
Explain:
before:
```
MySQL [(none)]> explain select * from hive_catalog.doris_test.orders_source
where dt=(select max(dt) from hive_catalog.doris_test.orders);
+---------------------------------------------------------------------------+
| Explain String(Nereids Planner) |
+---------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS: |
| order_id[#16] |
| user_id[#17] |
| amount[#18] |
| dt[#19] |
| PARTITION: RANDOM |
| |
| HAS_COLO_PLAN_NODE: false |
| |
| VRESULT SINK |
| MYSQL_PROTOCOL |
| |
| 6:VHASH JOIN(87) |
| | join op: INNER JOIN(BROADCAST)[] |
| | equal join conjunct: (dt[#10] = max(dt)[#6]) |
| | cardinality=33 |
| | vec output tuple id: 6 |
| | output tuple id: 6 |
| | vIntermediate tuple ids: 5 |
| | hash output slot ids: 7 8 9 10 |
| | final projections: order_id[#11], user_id[#12], amount[#13], dt[#14] |
| | final project output tuple id: 6 |
| | distribute expr lists: |
| | distribute expr lists: |
| | |
| |----4:VEXCHANGE |
| | offset: 0 |
| | distribute expr lists: |
| | |
| 5:VPluginDrivenScanNode(60) |
| TABLE: hive_catalog.doris_test.orders_source |
| CONNECTOR: hms |
| inputSplitNum=2, totalFileSize=811, scanRanges=2 |
| partition=2/2 |
| cardinality=33, numNodes=1 |
| pushdown agg=NONE |
| |
| PLAN FRAGMENT 1 |
| |
| PARTITION: UNPARTITIONED |
| |
| HAS_COLO_PLAN_NODE: true |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 04 |
| UNPARTITIONED |
| |
| 3:VAGGREGATE (merge finalize)(85) |
| | output: max(partial_max(dt)[#5])[#6] |
| | group by: |
| | sortByGroupKey:false |
| | cardinality=1 |
| | distribute expr lists: |
| | |
| 2:VEXCHANGE |
| offset: 0 |
| distribute expr lists: |
| |
| PLAN FRAGMENT 2 |
| |
| PARTITION: RANDOM |
| |
| HAS_COLO_PLAN_NODE: false |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 02 |
| UNPARTITIONED |
| |
| 1:VAGGREGATE (update serialize)(83) |
| | output: partial_max(dt[#4])[#5] |
| | group by: |
| | sortByGroupKey:false |
| | cardinality=1 |
| | distribute expr lists: |
| | |
| 0:VPluginDrivenScanNode(72) |
| TABLE: hive_catalog.doris_test.orders |
| CONNECTOR: hms |
| inputSplitNum=2, totalFileSize=1018, scanRanges=2 |
| partition=2/2 |
| cardinality=42, numNodes=1 |
| pushdown agg=NONE |
| final projections: dt[#3] |
| final project output tuple id: 1 |
| |
| |
| |
| ========== STATISTICS ========== |
| planned with unknown column statistics |
+---------------------------------------------------------------------------+
90 rows in set (0.93 sec)
```
now:
```
MySQL [(none)]> explain select * from hive_catalog.doris_test.orders_source
where dt=(select max(dt) from hive_catalog.doris_test.orders)
-> ;
+-----------------------------------------------------------------------+
| Explain String(Nereids Planner) |
+-----------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS: |
| order_id[#10] |
| user_id[#11] |
| amount[#12] |
| dt[#13] |
| PARTITION: RANDOM |
| |
| HAS_COLO_PLAN_NODE: false |
| |
| VRESULT SINK |
| MYSQL_PROTOCOL |
| |
| 3:VNESTED LOOP JOIN(76) |
| | join op: INNER JOIN() |
| | cardinality=16 |
| | vIntermediate tuple ids: 2 |
| | materialized slot ids: 5 6 7 8 |
| | final projections: order_id[#5], user_id[#6], amount[#7], dt[#8] |
| | final project output tuple id: 3 |
| | distribute expr lists: |
| | distribute expr lists: |
| | |
| |----1:VEXCHANGE |
| | offset: 0 |
| | distribute expr lists: |
| | |
| 2:VPluginDrivenScanNode(62) |
| TABLE: hive_catalog.doris_test.orders_source |
| CONNECTOR: hms |
| PREDICATES: (dt[#4] = '2026-08-26') |
| inputSplitNum=1, totalFileSize=390, scanRanges=1 |
| partition=1/2 |
| cardinality=33, numNodes=1 |
| pushdown agg=NONE |
| |
| PLAN FRAGMENT 1 |
| |
| PARTITION: UNPARTITIONED |
| |
| HAS_COLO_PLAN_NODE: false |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 01 |
| UNPARTITIONED |
| |
| 0:VUNION(64) |
| constant exprs: |
| '2026-08-26' |
| |
| |
| |
| ========== STATISTICS ========== |
| planned with unknown column statistics |
+-----------------------------------------------------------------------+
54 rows in set (0.03 sec)
```
### Release note
Fix slow queries on external Hive partitioned tables when filtering by
`partition_col = (select max(partition_col) from table)`.
None
### Check List (For Author)
- Test <!-- At least one of them must be included. -->
- [ ] Regression test
- [ ] Unit Test
- [ ] Manual test (add detailed scripts or steps below)
- [ ] No need to test or manual test. Explain why:
- [ ] This is a refactor/code format and no logic has been changed.
- [ ] Previous test can cover this change.
- [ ] No code files have been changed.
- [ ] Other reason <!-- Add your reason? -->
- Behavior changed:
- [ ] No.
- [ ] Yes. <!-- Explain the behavior change -->
- Does this need documentation?
- [ ] No.
- [ ] Yes. <!-- Add document PR link here. eg:
https://github.com/apache/doris-website/pull/1214 -->
### Check List (For Reviewer who merge this PR)
- [ ] Confirm the release note
- [ ] Confirm test cases
- [ ] Confirm document
- [ ] Add branch pick label <!-- Add branch pick label that this PR should
merge into -->
--
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]
