From 69d7de5ea2a7fbb1aa272b8b7b32017ea053160e Mon Sep 17 00:00:00 2001 From: "David G. Johnston" Date: Fri, 7 Mar 2025 20:07:35 -0700 Subject: [PATCH] doc: more thoroughly explain window function processing. --- doc/src/sgml/queries.sgml | 98 +++++++++++++++++++++++++++++++++++---- 1 file changed, 88 insertions(+), 10 deletions(-) diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml index 372cce1a48..c59dfaa3cb 100644 --- a/doc/src/sgml/queries.sgml +++ b/doc/src/sgml/queries.sgml @@ -1460,25 +1460,103 @@ GROUP BY GROUPING SETS ( - 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. Therefore they will see the same sort ordering, + The following is a query using multiple window functions to provide + a concrete example to refer to when explaining the execution behaviors + of such a query. The result is shown for reference while + the EXPLAIN output is needed for the subsequent discussion. + A full understanding of explain output is not necessary here; the pertinent + details are described below. + + +WITH vals (i,j) AS ( VALUES ('A',1), ('B',2),('B',3) ) +SELECT *, + COUNT(i) OVER (PARTITION BY i) AS count_i_noframe, + COUNT(j) OVER (PARTITION BY j) AS count_j, + SUM(j) OVER (PARTITION BY j) AS sum_j, + COUNT(i) OVER ( + PARTITION BY i + RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW + ) AS count_i_frame +FROM vals; + + i | j | count_i_noframe | count_j | sum_j | count_i_frame +---+---+-----------------+---------+-------+--------------- + A | 1 | 1 | 1 | 1 | 1 + B | 2 | 2 | 1 | 2 | 2 + B | 3 | 2 | 1 | 3 | 2 + + QUERY PLAN +--------------------------------------------------------- + WindowAgg + -> WindowAgg + -> Sort + Sort Key: "*VALUES*".column1 + -> WindowAgg + -> Sort + Sort Key: "*VALUES*".column2 + -> Values Scan on "*VALUES*" + + + + + + In the explain output example above the bottom-most node is the unsorted data-producing + values scan which adds columns i and j to the output. + (This would be replaced with group by results or a normal join and filter for most real queries.) + Next, in order to add columns count_j and sum_j to the output, + the data is sorted on j to match their shared partition specification. + Next, the data is re-sorted using i so that count_i_noframe + can be added; and lastly the same ordered data is traversed again to add count_i_frame to the result. + (Written ordering assumed but they could be swapped.) + + + + As seen in the explain output, the presence of window functions will result + in a stack of WindowAgg nodes with either an inital data producer plus explicit + sort node (see example) or a leaf node that produces ordered results + (e.g., an Index Only Scan). + Additional sort nodes (see example) may be added if there are multiple window + functions each specifying different orderings. + Each window function is associated with one WindowAgg node during planning, + though the same WindowAgg node may be used for multiple window functions. + (This is why there is only a single WindowAgg for column2/partition by j: + the sum_j and count_j columns are sharing.) + In particular, de-duplication of WindowAgg nodes happens during parsing and + planning (the later taking into account any frame clause optimizations actual + window functions may allow). Note that while there is an implicit default window + frame for any over clause that does not specify one, for purposes of uniqueness the + absence of a frame clause and one specifying an equivalent frame explicitly are + considered different. + (This is why there are still two WindowAgg nodes for the two window + functions specifying partition by i.) + + + + When multiple window functions are used the SQL standard requires that all + order-equivalent invocations see the same ordering of peer rows. + In practice this means that WindowAgg nodes corresponding to + specifications containing the same + PARTITION BY and ORDER BY + clauses in their window definitions will appear together in the stack, + without any intervening sort node. Thereby guaranteeing they will see the same sort ordering, even if the ORDER BY does not uniquely determine an ordering. However, no guarantees are made about the evaluation of functions having different PARTITION BY or ORDER BY specifications. (In such cases a sort step is typically required between the passes of window function evaluations, and the sort is not guaranteed to preserve ordering of rows that its ORDER BY sees as equivalent.) + However, the PostgreSQL planner does attempt to determine + whether, given two ORDER BY specifications, one is a prefix of the other, + in which case both window functions will see the ordering required for the more specific one. - Currently, window functions always require presorted data, and so the - query output will be ordered according to one or another of the window - functions' PARTITION BY/ORDER BY clauses. - It is not recommended to rely on this, however. Use an explicit - top-level ORDER BY clause if you want to be sure the - results are sorted in a particular way. + Currently, a query without an order by clause, but containing one or more order-specifying + window functions (i.e., e.g., not count(*) over ()), will still end + up producing sorted output since the WindowAgg node will be the the + row-producing node and it usually outputs sorted data. It is not recommended to rely on this, + however. Use an explicit top-level ORDER BY clause if you want to be sure + the results are sorted in a particular way. -- 2.34.1