planner, executor: extend ordered-input optimization to general window functions
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
Follow-up to https://github.com/pingcap/tidb/pull/67696.
### Background
`#67696` adds the first demo path for `StreamWindow` and proves that TiDB can reuse an existing `PARTITION BY + ORDER BY` property from the child plan instead of always falling back to `Sort + Window`.
That PR should be treated as the starting point, not the final scope.
### Problem
The follow-up optimization target should be all eligible window functions, not a single demo shape.
There are really two related planner/executor capabilities here:
1. `StreamWindow` can reuse child-provided order when the child already satisfies the required `PARTITION BY + ORDER BY` property.
2. Regular `Window` planning can also reuse index order when the child path already provides the needed order, instead of forcing an extra sort.
Today the demo path in `#67696` is intentionally narrow, but the long-term optimization direction should be broader:
- all eligible window functions should be considered for ordered-input execution
- all eligible window functions should benefit when an index already provides the required order
- the planner should not artificially limit this optimization space to one specific window function shape
### Expected enhancement
Extend the follow-up implementation so that ordered-input window planning is a general optimization for eligible window functions.
Concretely, the enhancement should cover cases such as:
- ranking functions like `rank()` / `dense_rank()` on an index that already matches `partition by + order by`
- aggregate-style windows like `sum()` / `count()` / `avg()` when the required order is already available
- value functions such as `first_value()` / `last_value()` when the existing ordered-input execution contract is satisfied
- index-ordered inner plans in `IndexJoin` for general window cases, as long as the stream-window execution contract is satisfied
### Example SQL shapes
With an index like `idx_ab(a, b)`, the following queries should be considered part of the target enhancement space:
```sql
select a, b, rank() over(partition by a order by b) as rk from t;
select a, b, dense_rank() over(partition by a order by b) as dr from t;
select a, b, sum(c) over(partition by a order by b rows between unbounded preceding and current row) as running_sum from t;
select a, b, first_value(c) over(partition by a order by b rows between unbounded preceding and current row) as fv from t;
```
And for index join inner plans:
```sql
select /*+ INL_JOIN(tmp) */ *
from t1
join (
select a, b, rank() over(partition by a order by b) as rk from t2
) tmp on t1.a = tmp.a;
```
### Why this matters
- avoids unnecessary `Sort` operators when the order is already available from the access path
- broadens the usefulness of ordered-input window execution beyond the first demo path
- makes planner behavior align better with the actual optimization direction: window operators in general should benefit from index order when possible
- provides a cleaner base for future executor improvements on top of the same ordered-input property reuse
### Suggested scope
- relax the current demo restriction into a general ordered-input admission rule for eligible window functions
- add planner/executor coverage showing multiple window-function categories reusing index order
- document clearly that the optimization target is general window-function support, not a single demo case
Suggested labels: `type/enhancement`, `sig/planner`, `component/test`
Contributor guide
Assessment
This issue has not been assessed yet.