Re: Window Functions with identical PARTITION BY and ORDER BY clauses evaluated separately

I see. Thank you for the response, I appreciate the postgresql team's
patience in humoring my request twice.

> Do you have any suggestions for clearer wording?
I think specifically the word "pass" in "evaluated in one pass" suggests
iteration. Given the intention of the passage is to indicate the number of
times the data is sorted, then I believe it should refer to "sorting" or
"ordering" explicitly. Perhaps "without additional interleaving sort
operations" (though the parenthetical at the end of the paragraph would
become repetitive so maybe not).

Sincerely,
Christopher Inokuchi

On Fri, Mar 7, 2025 at 4:05 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Christopher Inokuchi <cinokuchi(at)gmail(dot)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
>