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:
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:
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)
