From: Tom Lane <tgl@sss.pgh.pa.us>
To: Christopher Inokuchi <cinokuchi@gmail.com>
Cc: Greg Sabino Mullane <htamfids@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Window Functions with identical PARTITION BY and ORDER BY clauses evaluated separately
Date: Fri, 07 Mar 2025 19:05:51 -0500
Message-ID: <1147307.1741392351@sss.pgh.pa.us> (raw)
In-Reply-To: <CABde6B40mE4me17sKLGZ6MspZ4YiaSsK8S4FunP13c+QNT+fKA@mail.gmail.com>
References: <CABde6B5va2wMsnM79u_x=n9KUgfKQje_pbLROEBmA9Ru5XWidw@mail.gmail.com>
<CAKAnmmK2EkwdfT24LUp4JBOfxdB5tT_oqdyFKC7P-_opF7js1g@mail.gmail.com>
<CABde6B40mE4me17sKLGZ6MspZ4YiaSsK8S4FunP13c+QNT+fKA@mail.gmail.com>
Christopher Inokuchi <cinokuchi@gmail.com> writes:
> Was it really not intentional that the docs explicitly name PARTITION BY
> and ORDER BY rather than the entire window_definition? If I understand
> correctly, only those two clauses control which records are hit and in what
> order.
Yeah, it's intentional, and in fact required by the SQL standard.
However, you're misinterpreting what the guarantee is. The spec
requirement is that window functions sharing PARTITION BY and
ORDER BY all be evaluated on the same concrete ordering of the
data, ie there can't be any re-sorting between them. And that's
what we implement. We do use a separate WindowAgg node for
each distinguishable window specification, but you'll notice
there is not a Sort step between them unless the query involves
entirely-incompatible PARTITION/ORDER BY specs.
Perhaps the wording in section 7.2.5 could be improved; I agree
that "evaluated in one pass" is capable of being read in more
than one way, and it's not clear that it's referring to sorts.
Do you have any suggestions for clearer wording?
It's possible that we could restructure things so that window
functions having distinct frame clauses were nonetheless done
in one WindowAgg node. But it would complicate the code and
it's far from obvious to me that it'd buy much in speed.
Optimizing the sort steps is where most of the potential win lies.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, cinokuchi@gmail.com, htamfids@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Window Functions with identical PARTITION BY and ORDER BY clauses evaluated separately
In-Reply-To: <1147307.1741392351@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox