From: | Ankit Kumar Pandey <itsankitkp(at)gmail(dot)com> |
---|---|
To: | David Rowley <dgrowleyml(at)gmail(dot)com> |
Cc: | pghackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Vik Fearing <vik(at)postgresfriends(dot)org> |
Subject: | Re: Todo: Teach planner to evaluate multiple windows in the optimal order |
Date: | 2023-01-08 06:18:05 |
Message-ID: | 2040c902-32b1-46c8-0b26-d3f43a16b9bb@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ankit Kumar Pandey | 2023-01-08 06:29:27 | Re: Todo: Teach planner to evaluate multiple windows in the optimal order |
Previous Message | houzj.fnst@fujitsu.com | 2023-01-08 06:02:46 | RE: Perform streaming logical transactions by background workers and parallel apply |