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
