Window Functions with identical PARTITION BY and ORDER BY clauses evaluated separately - Mailing list pgsql-bugs
Relevant documentation: https://www.postgresql.org/docs/9.4/queries-table-expressions.html#QUERIES-WINDOW
"When multiple window functions are used, all the window functions having syntactically equivalent PARTITION BY and ORDER BY clauses in their window definitions are guaranteed to be evaluated in a single pass over the data."
Reproduction (my_table_contents.csv attached to email as zip file):
"When multiple window functions are used, all the window functions having syntactically equivalent PARTITION BY and ORDER BY clauses in their window definitions are guaranteed to be evaluated in a single pass over the data."
PostgreSQL version:
"PostgreSQL 17.4 on x86_64-windows, compiled by msvc-19.42.34436, 64-bit"
Machine information:
Windows server 2016kernel version 10.0.14393.778312.00 GiB memory4 cores
Reproduction (my_table_contents.csv attached to email as zip file):
- CREATE TABLE my_table (champid SMALLINT, champmastery INT);
- COPY my_table FROM 'path\to\my_table_contents.csv' WITH (FORMAT CSV);
- CREATE INDEX my_idx ON my_table (champid, champmastery);
- SELECT
SUM(CAST(champmastery AS BIGINT)) OVER (
PARTITION BY champid
ORDER BY champmastery ASC
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS sumx,
COUNT(1) OVER (
PARTITION BY champid
ORDER BY champmastery ASC
RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
) AS sampledensity
FROM my_table;
I apologize for the email spacing. It may cause issues with copy paste.
Expected result: Given both window functions in the above SELECT query have identical PARTITION BY and ORDER BY clauses, the execution plan should have a single "Window Aggregation" operation.
Actual result: The execution plan generated for the above query has two "Window Aggregation" operations
Attachment
pgsql-bugs by date: