pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedWindow Functions with identical PARTITION BY and ORDER BY clauses evaluated separately
11+ messages / 4 participants
[nested] [flat]
Commits from this thread:
2025-03-10 9f87e2593f 0f27bd14e4 778f9dd0d2 2db974e40d e7d29d1fac 6841118216 Doc: improve description of window function processing.
* Window Functions with identical PARTITION BY and ORDER BY clauses evaluated separately
@ 2025-03-07 06:02 Christopher Inokuchi <cinokuchi@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Christopher Inokuchi @ 2025-03-07 06:02 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Relevant documentation:
https://www.postgresql.org/docs/9.4/queries-table-expressions.html#QUERIES-WINDOW
"When multiple window functions are used, all the window functions having
syntactically equivalent PARTITION BY and ORDER BY clauses in their window
definitions are guaranteed to be evaluated in a single pass over the data."
PostgreSQL version:
"PostgreSQL 17.4 on x86_64-windows, compiled by msvc-19.42.34436, 64-bit"
Machine information:
Windows server 2016
kernel version 10.0.14393.7783
12.00 GiB memory
4 cores
Reproduction (my_table_contents.csv attached to email as zip file):
- CREATE TABLE my_table (champid SMALLINT, champmastery INT);
- COPY my_table FROM 'path\to\my_table_contents.csv' WITH (FORMAT CSV);
- CREATE INDEX my_idx ON my_table (champid, champmastery);
- SELECT
SUM(CAST(champmastery AS BIGINT)) OVER (
PARTITION BY champid
ORDER BY champmastery ASC
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS sumx,
COUNT(1) OVER (
PARTITION BY champid
ORDER BY champmastery ASC
RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
) AS sampledensity
FROM my_table;
I apologize for the email spacing. It may cause issues with copy paste.
Expected result: Given both window functions in the above SELECT query have
identical PARTITION BY and ORDER BY clauses, the execution plan should have
a single "Window Aggregation" operation.
Actual result: The execution plan generated for the above query has two
"Window Aggregation" operations
[image: image.png]
Attachments:
image.png (image/png, 28.5K) download
view image
* Re: Window Functions with identical PARTITION BY and ORDER BY clauses evaluated separately
@ 2025-03-07 14:23 Greg Sabino Mullane <htamfids@gmail.com>
parent: Christopher Inokuchi <cinokuchi@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Greg Sabino Mullane @ 2025-03-07 14:23 UTC (permalink / raw)
To: Christopher Inokuchi <cinokuchi@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
Those are different windows. See:
https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
Because the (optional) frame_clause is part of the window_definition, it
does seem like a minor documentation bug as we ought to mention that the
frame (if it exists) needs to be equivalent too. Here's a better link to
where we state that:
https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-WINDOW
Here's a simplified example:
greg=# explain select count(*) over (partition by oid rows between 1000
preceding and 1000 following),
count(*) over (partition by oid rows between 1000 preceding and 1000
following) from pg_class;
QUERY PLAN
-------------------------------------------------------------------------------------------------
WindowAgg (cost=0.28..60.87 rows=791 width=20)
-> Index Only Scan using pg_class_oid_index on pg_class
(cost=0.28..49.00 rows=791 width=4)
(2 rows)
greg=# explain select count(*) over (partition by oid rows between 1000
preceding and 1000 following),
count(*) over (partition by oid rows between 1000 preceding and 9999
following) from pg_class;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
WindowAgg (cost=0.28..72.73 rows=791 width=20)
-> WindowAgg (cost=0.28..60.87 rows=791 width=12)
-> Index Only Scan using pg_class_oid_index on pg_class
(cost=0.28..49.00 rows=791 width=4)
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Window Functions with identical PARTITION BY and ORDER BY clauses evaluated separately
@ 2025-03-07 20:53 Christopher Inokuchi <cinokuchi@gmail.com>
parent: Greg Sabino Mullane <htamfids@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Christopher Inokuchi @ 2025-03-07 20:53 UTC (permalink / raw)
To: Greg Sabino Mullane <htamfids@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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.
Sorry for the impudence, but I was rather excited for the potential
performance gain when I saw the doc excerpt. I appreciate the time taken to
respond to my query.
Thank you,
Christopher Inokuchi
On Fri, Mar 7, 2025 at 6:24 AM Greg Sabino Mullane <htamfids@gmail.com>
wrote:
> Those are different windows. See:
>
>
> https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
>
> Because the (optional) frame_clause is part of the window_definition, it
> does seem like a minor documentation bug as we ought to mention that the
> frame (if it exists) needs to be equivalent too. Here's a better link to
> where we state that:
>
>
> https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-WINDOW
>
> Here's a simplified example:
>
> greg=# explain select count(*) over (partition by oid rows between 1000
> preceding and 1000 following),
> count(*) over (partition by oid rows between 1000 preceding and 1000
> following) from pg_class;
> QUERY PLAN
>
> -------------------------------------------------------------------------------------------------
> WindowAgg (cost=0.28..60.87 rows=791 width=20)
> -> Index Only Scan using pg_class_oid_index on pg_class
> (cost=0.28..49.00 rows=791 width=4)
> (2 rows)
>
> greg=# explain select count(*) over (partition by oid rows between 1000
> preceding and 1000 following),
> count(*) over (partition by oid rows between 1000 preceding and 9999
> following) from pg_class;
> QUERY PLAN
>
> -------------------------------------------------------------------------------------------------------
> WindowAgg (cost=0.28..72.73 rows=791 width=20)
> -> WindowAgg (cost=0.28..60.87 rows=791 width=12)
> -> Index Only Scan using pg_class_oid_index on pg_class
> (cost=0.28..49.00 rows=791 width=4)
>
> Cheers,
> Greg
>
> --
> Crunchy Data - https://www.crunchydata.com
> Enterprise Postgres Software Products & Tech Support
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Window Functions with identical PARTITION BY and ORDER BY clauses evaluated separately
@ 2025-03-08 00:05 Tom Lane <tgl@sss.pgh.pa.us>
parent: Christopher Inokuchi <cinokuchi@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Tom Lane @ 2025-03-08 00:05 UTC (permalink / raw)
To: Christopher Inokuchi <cinokuchi@gmail.com>; +Cc: Greg Sabino Mullane <htamfids@gmail.com>; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Window Functions with identical PARTITION BY and ORDER BY clauses evaluated separately
@ 2025-03-08 00:30 Christopher Inokuchi <cinokuchi@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 11+ messages in thread
From: Christopher Inokuchi @ 2025-03-08 00:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Greg Sabino Mullane <htamfids@gmail.com>; pgsql-bugs@lists.postgresql.org
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@sss.pgh.pa.us> wrote:
> 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
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Window Functions with identical PARTITION BY and ORDER BY clauses evaluated separately
@ 2025-03-08 03:17 David G. Johnston <david.g.johnston@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 11+ messages in thread
From: David G. Johnston @ 2025-03-08 03:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christopher Inokuchi <cinokuchi@gmail.com>; Greg Sabino Mullane <htamfids@gmail.com>; pgsql-bugs@lists.postgresql.org
On Fri, Mar 7, 2025 at 5:05 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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?
>
We seem to do quite a few things that we don't tell the user about. The
attached patch describes those things and adds an example demonstrating
their effects via an explain; which is the only way you can construct an
example for this material.
Considered a draft pending feedback to either throw it out in favor of a
one-word/one-line fix or support for going into this amount of detail.
David J.
Attachments:
v0-draft-0001-doc-more-thoroughly-explain-window-function-processing.patch (text/x-patch, 6.8K) download
From 69d7de5ea2a7fbb1aa272b8b7b32017ea053160e Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <David.G.Johnston@Gmail.com>
Date: Fri, 7 Mar 2025 20:07:35 -0700
Subject: [PATCH] doc: more thoroughly explain window function processing.
---
doc/src/sgml/queries.sgml | 98 +++++++++++++++++++++++++++++++++++----
1 file changed, 88 insertions(+), 10 deletions(-)
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
index 372cce1a48..c59dfaa3cb 100644
--- a/doc/src/sgml/queries.sgml
+++ b/doc/src/sgml/queries.sgml
@@ -1460,25 +1460,103 @@ GROUP BY GROUPING SETS (
</para>
<para>
- When multiple window functions are used, all the window functions having
- syntactically equivalent <literal>PARTITION BY</literal> and <literal>ORDER BY</literal>
- clauses in their window definitions are guaranteed to be evaluated in a
- single pass over the data. Therefore they will see the same sort ordering,
+ The following is a query using multiple window functions to provide
+ a concrete example to refer to when explaining the execution behaviors
+ of such a query. The result is shown for reference while
+ the <command>EXPLAIN</command> output is needed for the subsequent discussion.
+ A full understanding of explain output is not necessary here; the pertinent
+ details are described below.
+
+<programlisting>
+WITH vals (i,j) AS ( VALUES ('A',1), ('B',2),('B',3) )
+SELECT *,
+ COUNT(i) OVER (PARTITION BY i) AS count_i_noframe,
+ COUNT(j) OVER (PARTITION BY j) AS count_j,
+ SUM(j) OVER (PARTITION BY j) AS sum_j,
+ COUNT(i) OVER (
+ PARTITION BY i
+ RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+ ) AS count_i_frame
+FROM vals;
+
+ i | j | count_i_noframe | count_j | sum_j | count_i_frame
+---+---+-----------------+---------+-------+---------------
+ A | 1 | 1 | 1 | 1 | 1
+ B | 2 | 2 | 1 | 2 | 2
+ B | 3 | 2 | 1 | 3 | 2
+
+ QUERY PLAN
+---------------------------------------------------------
+ WindowAgg
+ -> WindowAgg
+ -> Sort
+ Sort Key: "*VALUES*".column1
+ -> WindowAgg
+ -> Sort
+ Sort Key: "*VALUES*".column2
+ -> Values Scan on "*VALUES*"
+</programlisting>
+
+ </para>
+
+ <para>
+ In the explain output example above the bottom-most node is the unsorted data-producing
+ values scan which adds columns <literal>i</literal> and <literal>j</literal> to the output.
+ (This would be replaced with group by results or a normal join and filter for most real queries.)
+ Next, in order to add columns <literal>count_j</literal> and <literal>sum_j</literal> to the output,
+ the data is sorted on <literal>j</literal> to match their shared partition specification.
+ Next, the data is re-sorted using <literal>i</literal> so that <literal>count_i_noframe</literal>
+ can be added; and lastly the same ordered data is traversed again to add <literal>count_i_frame</literal> to the result.
+ (Written ordering assumed but they could be swapped.)
+ </para>
+
+ <para>
+ As seen in the explain output, the presence of window functions will result
+ in a stack of <literal>WindowAgg</literal> nodes with either an inital data producer plus explicit
+ sort node (see example) or a leaf node that produces ordered results
+ (e.g., an Index Only Scan).
+ Additional sort nodes (see example) may be added if there are multiple window
+ functions each specifying different orderings.
+ Each window function is associated with one <literal>WindowAgg</literal> node during planning,
+ though the same <literal>WindowAgg</literal> node may be used for multiple window functions.
+ (This is why there is only a single <literal>WindowAgg</literal> for column2/partition by j:
+ the <literal>sum_j</literal> and <literal>count_j</literal> columns are sharing.)
+ In particular, de-duplication of <literal>WindowAgg</literal> nodes happens during parsing and
+ planning (the later taking into account any frame clause optimizations actual
+ window functions may allow). Note that while there is an implicit default window
+ frame for any over clause that does not specify one, for purposes of uniqueness the
+ absence of a frame clause and one specifying an equivalent frame explicitly are
+ considered different.
+ (This is why there are still two <literal>WindowAgg</literal> nodes for the two window
+ functions specifying <literal>partition by i</literal>.)
+ </para>
+
+ <para>
+ When multiple window functions are used the SQL standard requires that all
+ order-equivalent invocations see the same ordering of peer rows.
+ In practice this means that <literal>WindowAgg</literal> nodes corresponding to
+ specifications containing the same
+ <literal>PARTITION BY</literal> and <literal>ORDER BY</literal>
+ clauses in their window definitions will appear together in the stack,
+ without any intervening sort node. Thereby guaranteeing they will see the same sort ordering,
even if the <literal>ORDER BY</literal> does not uniquely determine an ordering.
However, no guarantees are made about the evaluation of functions having
different <literal>PARTITION BY</literal> or <literal>ORDER BY</literal> specifications.
(In such cases a sort step is typically required between the passes of
window function evaluations, and the sort is not guaranteed to preserve
ordering of rows that its <literal>ORDER BY</literal> sees as equivalent.)
+ However, the <productname>PostgreSQL</productname> planner does attempt to determine
+ whether, given two <literal>ORDER BY</literal> specifications, one is a prefix of the other,
+ in which case both window functions will see the ordering required for the more specific one.
</para>
<para>
- Currently, window functions always require presorted data, and so the
- query output will be ordered according to one or another of the window
- functions' <literal>PARTITION BY</literal>/<literal>ORDER BY</literal> clauses.
- It is not recommended to rely on this, however. Use an explicit
- top-level <literal>ORDER BY</literal> clause if you want to be sure the
- results are sorted in a particular way.
+ Currently, a query without an order by clause, but containing one or more order-specifying
+ window functions (i.e., e.g., not <literal>count(*) over ()</literal>), will still end
+ up producing sorted output since the <literal>WindowAgg</literal> node will be the the
+ row-producing node and it usually outputs sorted data. It is not recommended to rely on this,
+ however. Use an explicit top-level <literal>ORDER BY</literal> clause if you want to be sure
+ the results are sorted in a particular way.
</para>
</sect2>
</sect1>
--
2.34.1
* Re: Window Functions with identical PARTITION BY and ORDER BY clauses evaluated separately
@ 2025-03-08 19:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2025-03-08 19:50 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Christopher Inokuchi <cinokuchi@gmail.com>; Greg Sabino Mullane <htamfids@gmail.com>; pgsql-bugs@lists.postgresql.org
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Fri, Mar 7, 2025 at 5:05 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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?
> We seem to do quite a few things that we don't tell the user about. The
> attached patch describes those things and adds an example demonstrating
> their effects via an explain; which is the only way you can construct an
> example for this material.
> Considered a draft pending feedback to either throw it out in favor of a
> one-word/one-line fix or support for going into this amount of detail.
Meh. This is detail that wasn't asked for and doesn't belong in this
section anyway. (If we did want to write something like this, chapter
14 Performance Tips would be a more plausible home I think. That'd
at least remove the problem of forward-referencing EXPLAIN output.)
For a shorter fix, maybe replace the first two sentences
When multiple window functions are used, all the window functions
having syntactically equivalent PARTITION BY and ORDER BY clauses
in their window definitions are guaranteed to be evaluated in a
single pass over the data. Therefore they will see the same sort
ordering, even if the ORDER BY does not uniquely determine an
ordering.
with
When multiple window functions are used, all the window functions
having syntactically equivalent PARTITION BY and ORDER BY clauses
in their window definitions are guaranteed to see the same
ordering of the input rows, even if the ORDER BY does not uniquely
determine the ordering.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Window Functions with identical PARTITION BY and ORDER BY clauses evaluated separately
@ 2025-03-09 01:07 David G. Johnston <david.g.johnston@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: David G. Johnston @ 2025-03-09 01:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christopher Inokuchi <cinokuchi@gmail.com>; Greg Sabino Mullane <htamfids@gmail.com>; pgsql-bugs@lists.postgresql.org
On Sat, Mar 8, 2025 at 12:50 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > On Fri, Mar 7, 2025 at 5:05 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> 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?
>
> > We seem to do quite a few things that we don't tell the user about. The
> > attached patch describes those things and adds an example demonstrating
> > their effects via an explain; which is the only way you can construct an
> > example for this material.
> > Considered a draft pending feedback to either throw it out in favor of a
> > one-word/one-line fix or support for going into this amount of detail.
>
> Meh. This is detail that wasn't asked for and doesn't belong in this
> section anyway. (If we did want to write something like this, chapter
> 14 Performance Tips would be a more plausible home I think. That'd
> at least remove the problem of forward-referencing EXPLAIN output.)
>
Thank you for the input. I figured that would be the response and am fine
with it. I took a look at Chapter 14 and agree it could fit there but that
just brought up more thoughts. I'll start a separate thread for all that.
> For a shorter fix
>
> When multiple window functions are used, all the window functions
> having syntactically equivalent PARTITION BY and ORDER BY clauses
> in their window definitions are guaranteed to see the same
> ordering of the input rows, even if the ORDER BY does not uniquely
> determine the ordering.
>
WFM, the key point is removing the problematic wording and I do find this
reads better in the end.
However, I'm having trouble understanding the purpose of the word
"syntactically" here. Or even using the "clauses" at all. Why not:
"When multiple window functions are used, all the window functions having
the same partitioning and ordering expressions in their window definitions
are guaranteed to see the same ordering of the input rows, even if
the ordering is not deterministic."
David J.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Window Functions with identical PARTITION BY and ORDER BY clauses evaluated separately
@ 2025-03-09 17:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2025-03-09 17:27 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Christopher Inokuchi <cinokuchi@gmail.com>; Greg Sabino Mullane <htamfids@gmail.com>; pgsql-bugs@lists.postgresql.org
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sat, Mar 8, 2025 at 12:50 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> For a shorter fix
>> When multiple window functions are used, all the window functions
>> having syntactically equivalent PARTITION BY and ORDER BY clauses
>> in their window definitions are guaranteed to see the same
>> ordering of the input rows, even if the ORDER BY does not uniquely
>> determine the ordering.
> WFM, the key point is removing the problematic wording and I do find this
> reads better in the end.
> However, I'm having trouble understanding the purpose of the word
> "syntactically" here. Or even using the "clauses" at all. Why not:
> "When multiple window functions are used, all the window functions having
> the same partitioning and ordering expressions in their window definitions
> are guaranteed to see the same ordering of the input rows, even if
> the ordering is not deterministic."
Sure, we can lose "syntactically" --- it's probably not even strictly
correct anyway, given that what we really look for is equal() parsed
expression trees. By the same token though, I don't love "same"
here, because what is "same"? In particular, in your phrasing
it's not clear whether "PARTITION BY foo" and "ORDER BY foo"
are considered equivalent for this purpose. So let's go with
my wording less the "syntactically".
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Window Functions with identical PARTITION BY and ORDER BY clauses evaluated separately
@ 2025-03-09 19:48 David G. Johnston <david.g.johnston@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: David G. Johnston @ 2025-03-09 19:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christopher Inokuchi <cinokuchi@gmail.com>; Greg Sabino Mullane <htamfids@gmail.com>; pgsql-bugs@lists.postgresql.org
On Sun, Mar 9, 2025 at 10:27 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > On Sat, Mar 8, 2025 at 12:50 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> For a shorter fix
> >> When multiple window functions are used, all the window functions
> >> having syntactically equivalent PARTITION BY and ORDER BY clauses
> >> in their window definitions are guaranteed to see the same
> >> ordering of the input rows, even if the ORDER BY does not uniquely
> >> determine the ordering.
>
> So let's go with
> my wording less the "syntactically".
>
>
+1
David J.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Window Functions with identical PARTITION BY and ORDER BY clauses evaluated separately
@ 2025-03-10 14:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Tom Lane @ 2025-03-10 14:23 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Christopher Inokuchi <cinokuchi@gmail.com>; Greg Sabino Mullane <htamfids@gmail.com>; pgsql-bugs@lists.postgresql.org
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sun, Mar 9, 2025 at 10:27 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> So let's go with my wording less the "syntactically".
> +1
Done that way.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2025-03-10 14:23 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-07 06:02 Window Functions with identical PARTITION BY and ORDER BY clauses evaluated separately Christopher Inokuchi <cinokuchi@gmail.com>
2025-03-07 14:23 ` Greg Sabino Mullane <htamfids@gmail.com>
2025-03-07 20:53 ` Christopher Inokuchi <cinokuchi@gmail.com>
2025-03-08 00:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-03-08 00:30 ` Christopher Inokuchi <cinokuchi@gmail.com>
2025-03-08 03:17 ` David G. Johnston <david.g.johnston@gmail.com>
2025-03-08 19:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-03-09 01:07 ` David G. Johnston <david.g.johnston@gmail.com>
2025-03-09 17:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-03-09 19:48 ` David G. Johnston <david.g.johnston@gmail.com>
2025-03-10 14:23 ` Tom Lane <tgl@sss.pgh.pa.us>
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