Re: Window Functions with identical PARTITION BY and ORDER BY clauses evaluated separately - Mailing list pgsql-bugs

Those are different windows. See:

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:

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)