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

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(at)gmail(dot)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
>
>