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

On Mon, 9 Jan 2023 at 05:06, Vik Fearing <vik(at)postgresfriends(dot)org> wrote:
> +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

> Why aren't min() and sum() calculated on the same WindowAgg run?

We'd need to have an ORDER BY per WindowFunc rather than per
WindowClause to do that. The problem is when there is no ORDER BY,
all rows are peers.

Likely there likely are a bunch more optimisations we could do in that
area. I think all the builtin window functions (not aggregates being
used as window functions) don't care about peer rows, so it may be
possible to merge the WindowClauses when the WIndowClause being merged
only has window functions that don't care about peer rows. Not for
this patch though.

David