Push down TopK below Join #11900

Is your feature request related to a problem or challenge?

One case where pushing down sorts is when the sort has a fetch (aka TopK).
This will result in less input to the join, so the join will execute faster / with less memory usage.

Describe the solution you'd like

Push down sorts with fetch for joins is (at least) possible when there is no join filter and:

• When sort expression is based on columns on the left side and type is left join
• When sort expression is based on columns on the right side and type is right join.

We should still keep the final sort on top of the result as not all planned join types will preserve the input ordering.
As we have the TopK as input for join, the resulting. The other optimization rules might remove the final sort if the physical join does in fact preserve the ordering (e.g. right joins and others).

The proposed optimization looks like:

Sort (fetch = 100) -> (projection) -> LeftJoin

Sort (fetch = 100) -> (projection) -> Join(join_type=Left)  -> Sort (fetch = 100)

Describe alternatives you've considered

No response

Additional context

No response