On 08/01/23 21:36, Vik Fearing wrote:
On 1/8/23 11:21, Ankit Kumar Pandey wrote:
Please find attached patch with addressed issues mentioned before.
I am curious about this plan:
+-- ORDER BY's in multiple Window functions can be combined into one
+-- if they are subset of QUERY's ORDER BY
+EXPLAIN (COSTS OFF)
+SELECT empno,
+ depname,
+ min(salary) OVER (PARTITION BY depname ORDER BY empno) depminsalary,
+ sum(salary) OVER (PARTITION BY depname) depsalary,
+ count(*) OVER (ORDER BY enroll_date DESC) c
+FROM empsalary
+ORDER BY depname, empno, enroll_date;
+ QUERY PLAN
+------------------------------------------------------
+ WindowAgg
+ -> WindowAgg
+ -> Sort
+ Sort Key: depname, empno, enroll_date
+ -> WindowAgg
+ -> Sort
+ Sort Key: enroll_date DESC
+ -> Seq Scan on empsalary
+(8 rows)
+
Why aren't min() and sum() calculated on the same WindowAgg run?
Isn't that exactly what is happening here? First count() with sort on
enroll_date is run and
then min() and sum()?
Only difference between this and plan generated by master(given below)
is a sort in the end.
QUERY PLAN
------------------------------------------------------------
Incremental Sort
Sort Key: depname, empno, enroll_date
Presorted Key: depname, empno
-> WindowAgg
-> WindowAgg
-> Sort
Sort Key: depname, empno
-> WindowAgg
-> Sort
Sort Key: enroll_date DESC
-> Seq Scan on empsalary
Let me know if I am missing anything. Thanks.
--
Regards,
Ankit Kumar Pandey
