pingcap / pingcap/tidb

planner, executor: extend ordered-input optimization to general window functions

Open
#67,989 0 comments 0 reactions 0 assignees View on GitHub
component/test sig/planner type/enhancement
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.