Re: Todo: Teach planner to evaluate multiple windows in the optimal order

On 08/01/23 03:56, David Rowley wrote:

> (your email client still seems broken)

I am looking at this again, will be changing client for here onward.

> You might need to have another loop before the foreach loop that loops
> backwards through the WindowClauses and remembers the index of the
> WindowClause which has pathkeys contained in the query's ORDER BY
> pathkeys then apply the optimisation from that point in the main
> foreach loop. Also, if the condition within the foreach loop which
> checks when we want to apply this optimisation is going to be run > 1
> time, then you should probably have boolean variable that's set
> before the loop which saves if we're going to try to apply the
> optimisation. That'll save from having to check things like if the
> query has a LIMIT clause multiple times.

Thanks, this should do the trick.

> a) looks like the best plan to me. What's the point of pushing the
> sort below the WindowAgg in this case? The point of this optimisation
> is to reduce the number of sorts not to push them as deep into the
> plan as possible. We should only be pushing them down when it can
> reduce the number of sorts. There's no reduction in the number of
> sorts in the above plan.

Yes, you are right, not in this case. I actually mentioned wrong case here,

real problematic case is:

EXPLAIN (COSTS OFF)
SELECT empno,
depname,
min(salary) OVER (PARTITION BY depname ORDER BY empno) depminsalary,
sum(salary) OVER (PARTITION BY depname) depsalary
FROM empsalary
ORDER BY depname, empno, enroll_date;
QUERY PLAN
-------------------------------------------------------------------
Incremental Sort
Sort Key: depname, empno, enroll_date
Presorted Key: depname, empno
-> WindowAgg
-> WindowAgg
-> Incremental Sort
Sort Key: depname, empno
Presorted Key: depname
-> Index Scan using depname_idx on empsalary
(9 rows)

Here, it could have sorted on depname, empno, enroll_date.

Again, as I mentioned before, this is implementation issue. We shouldn't be

skipping optimization if pre-sorted keys are present.

--
Regards,
Ankit Kumar Pandey