Re: Todo: Teach planner to evaluate multiple windows in the optimal order - Mailing list pgsql-hackers

From David Rowley
Subject Re: Todo: Teach planner to evaluate multiple windows in the optimal order
Date
Msg-id CAApHDvrjzY9NMFNnZ-dcTZVbCgzGi8iyAGVaobMEb=oQbz8m8A@mail.gmail.com
Whole thread Raw
In response to Re: Todo: Teach planner to evaluate multiple windows in the optimal order  (Vik Fearing <vik@postgresfriends.org>)
List pgsql-hackers
On Mon, 9 Jan 2023 at 05:06, Vik Fearing <vik@postgresfriends.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



pgsql-hackers by date:

Previous
From: Justin Pryzby
Date:
Subject: [PATCH] basebackup: support zstd long distance matching
Next
From: Tom Lane
Date:
Subject: Re: on placeholder entries in view rule action query's range table