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