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

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