Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options

On Sun, 23 Oct 2022 at 03:03, Vik Fearing <vik(at)postgresfriends(dot)org> wrote:
> Shouldn't it be able to detect that these two windows are the same and
> only do one WindowAgg pass?
>
>
> explain (verbose, costs off)
> select row_number() over w1,
> lag(amname) over w2
> from pg_am
> window w1 as (order by amname),
> w2 as (w1 rows unbounded preceding)
> ;

Good thinking. I think the patch should also optimise that case. It
requires re-doing a similar de-duplication phase the same as what's
done in transformWindowFuncCall(). I've added code to do that in the
attached version.

This got me wondering if the support function, instead of returning
some more optimal versions of the frameOptions, I wondered if it
should just return which aspects of the WindowClause it does not care
about. For example,

SELECT row_number() over (), lag(relname) over (order by relname)
from pg_class;

could, in theory, have row_number() reuse the WindowAgg for lag. Here
because the WindowClause for row_number() has an empty ORDER BY
clause, I believe it could just reuse the lag's WindowClause. It
wouldn't be able to do that if row_number() had an ORDER BY, or if
row_number() were some other WindowFunc that cared about peer rows.
I'm currently thinking this might not be worth the trouble as it seems
a bit unlikely that someone would use row_number() and not care about
the ORDER BY. However, maybe the row_number() could reuse some other
WindowClause with a more strict ordering. My current thoughts are that
this feels a bit too unlikely to apply in enough cases for it to be
worthwhile. I just thought I'd mention it for the sake of the
archives.

David

Thanks for taking it for a spin.

David