Thread: Printing window function OVER clauses in EXPLAIN
While thinking about the discussion at [1], I got annoyed about
how EXPLAIN still can't print a useful description of window
functions' window clauses (it just emits "OVER (?)"). The
difficulty is that there's no access to the original WindowClause
anymore; else we could re-use the ruleutils.c code that dumps
those. It struck me that we could fix that by making WindowAgg
plan nodes keep the WindowClause as a sub-node, replacing their
current habit of having most of the WindowClause's fields as
loose fields in the WindowAgg node. A little bit later I had
a working patch, as attached. I think this data structure change
is about a wash for performance outside of EXPLAIN. It requires
a few extra indirections during ExecInitWindowAgg, but there's
no change in code used during the plan's execution.
One thing that puzzled me a bit is that many of the outputs
show "ROWS UNBOUNDED PRECEDING" in window functions where that
definitely wasn't in the source query. Eventually I realized
that that comes from window_row_number_support() and cohorts
optimizing the query. While this isn't wrong, I suspect it
will cause a lot of confusion and questions. I wonder if we
should do something to hide the change?
regards, tom lane
[1] https://www.postgresql.org/message-id/flat/CABde6B5va2wMsnM79u_x%3Dn9KUgfKQje_pbLROEBmA9Ru5XWidw%40mail.gmail.com
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index c68119030ab..e79136008c5 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -3968,10 +3968,10 @@ select c2, sum(c2), count(c2) over (partition by c2%2) from ft2 where c2 < 10 gr
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Sort
- Output: c2, (sum(c2)), (count(c2) OVER (?)), ((c2 % 2))
+ Output: c2, (sum(c2)), (count(c2) OVER (PARTITION BY ((c2 % 2)))), ((c2 % 2))
Sort Key: ft2.c2
-> WindowAgg
- Output: c2, (sum(c2)), count(c2) OVER (?), ((c2 % 2))
+ Output: c2, (sum(c2)), count(c2) OVER (PARTITION BY ((c2 % 2))), ((c2 % 2))
-> Sort
Output: c2, ((c2 % 2)), (sum(c2))
Sort Key: ((ft2.c2 % 2))
@@ -4001,10 +4001,10 @@ select c2, array_agg(c2) over (partition by c2%2 order by c2 desc) from ft1 wher
QUERY PLAN
---------------------------------------------------------------------------------------------------
Sort
- Output: c2, (array_agg(c2) OVER (?)), ((c2 % 2))
+ Output: c2, (array_agg(c2) OVER (PARTITION BY ((c2 % 2)) ORDER BY c2 DESC)), ((c2 % 2))
Sort Key: ft1.c2
-> WindowAgg
- Output: c2, array_agg(c2) OVER (?), ((c2 % 2))
+ Output: c2, array_agg(c2) OVER (PARTITION BY ((c2 % 2)) ORDER BY c2 DESC), ((c2 % 2))
-> Sort
Output: c2, ((c2 % 2))
Sort Key: ((ft1.c2 % 2)), ft1.c2 DESC
@@ -4031,13 +4031,13 @@ select c2, array_agg(c2) over (partition by c2%2 order by c2 desc) from ft1 wher
explain (verbose, costs off)
select c2, array_agg(c2) over (partition by c2%2 order by c2 range between current row and unbounded following) from
ft1where c2 < 10 group by c2 order by 1;
- QUERY PLAN
----------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------
Sort
- Output: c2, (array_agg(c2) OVER (?)), ((c2 % 2))
+ Output: c2, (array_agg(c2) OVER (PARTITION BY ((c2 % 2)) ORDER BY c2 RANGE BETWEEN CURRENT ROW AND UNBOUNDED
FOLLOWING)),((c2 % 2))
Sort Key: ft1.c2
-> WindowAgg
- Output: c2, array_agg(c2) OVER (?), ((c2 % 2))
+ Output: c2, array_agg(c2) OVER (PARTITION BY ((c2 % 2)) ORDER BY c2 RANGE BETWEEN CURRENT ROW AND UNBOUNDED
FOLLOWING),((c2 % 2))
-> Sort
Output: c2, ((c2 % 2))
Sort Key: ((ft1.c2 % 2)), ft1.c2
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 9a1acce2b5d..21939e67929 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -2436,7 +2436,7 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
ExprContext *tmpcontext;
WindowStatePerFunc perfunc;
WindowStatePerAgg peragg;
- int frameOptions = node->frameOptions;
+ int frameOptions = node->winclause->frameOptions;
int numfuncs,
wfuncno,
numaggs,
@@ -2615,9 +2615,9 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
AclResult aclresult;
int i;
- if (wfunc->winref != node->winref) /* planner screwed up? */
+ if (wfunc->winref != node->winclause->winref) /* planner screwed up? */
elog(ERROR, "WindowFunc with winref %u assigned to WindowAgg with winref %u",
- wfunc->winref, node->winref);
+ wfunc->winref, node->winclause->winref);
/* Look for a previous duplicate window function */
for (i = 0; i <= wfuncno; i++)
@@ -2709,19 +2709,19 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
winstate->status = WINDOWAGG_RUN;
/* initialize frame bound offset expressions */
- winstate->startOffset = ExecInitExpr((Expr *) node->startOffset,
+ winstate->startOffset = ExecInitExpr((Expr *) node->winclause->startOffset,
(PlanState *) winstate);
- winstate->endOffset = ExecInitExpr((Expr *) node->endOffset,
+ winstate->endOffset = ExecInitExpr((Expr *) node->winclause->endOffset,
(PlanState *) winstate);
/* Lookup in_range support functions if needed */
- if (OidIsValid(node->startInRangeFunc))
- fmgr_info(node->startInRangeFunc, &winstate->startInRangeFunc);
- if (OidIsValid(node->endInRangeFunc))
- fmgr_info(node->endInRangeFunc, &winstate->endInRangeFunc);
- winstate->inRangeColl = node->inRangeColl;
- winstate->inRangeAsc = node->inRangeAsc;
- winstate->inRangeNullsFirst = node->inRangeNullsFirst;
+ if (OidIsValid(node->winclause->startInRangeFunc))
+ fmgr_info(node->winclause->startInRangeFunc, &winstate->startInRangeFunc);
+ if (OidIsValid(node->winclause->endInRangeFunc))
+ fmgr_info(node->winclause->endInRangeFunc, &winstate->endInRangeFunc);
+ winstate->inRangeColl = node->winclause->inRangeColl;
+ winstate->inRangeAsc = node->winclause->inRangeAsc;
+ winstate->inRangeNullsFirst = node->winclause->inRangeNullsFirst;
winstate->all_first = true;
winstate->partition_spooled = false;
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 816a2b2a576..c7004a7611c 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -285,12 +285,9 @@ static Memoize *make_memoize(Plan *lefttree, Oid *hashoperators,
Oid *collations, List *param_exprs,
bool singlerow, bool binary_mode,
uint32 est_entries, Bitmapset *keyparamids);
-static WindowAgg *make_windowagg(List *tlist, Index winref,
+static WindowAgg *make_windowagg(List *tlist, WindowClause *winclause,
int partNumCols, AttrNumber *partColIdx, Oid *partOperators, Oid *partCollations,
int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, Oid *ordCollations,
- int frameOptions, Node *startOffset, Node *endOffset,
- Oid startInRangeFunc, Oid endInRangeFunc,
- Oid inRangeColl, bool inRangeAsc, bool inRangeNullsFirst,
List *runCondition, List *qual, bool topWindow,
Plan *lefttree);
static Group *make_group(List *tlist, List *qual, int numGroupCols,
@@ -2683,7 +2680,7 @@ create_windowagg_plan(PlannerInfo *root, WindowAggPath *best_path)
/* And finally we can make the WindowAgg node */
plan = make_windowagg(tlist,
- wc->winref,
+ wc,
partNumCols,
partColIdx,
partOperators,
@@ -2692,14 +2689,6 @@ create_windowagg_plan(PlannerInfo *root, WindowAggPath *best_path)
ordColIdx,
ordOperators,
ordCollations,
- wc->frameOptions,
- wc->startOffset,
- wc->endOffset,
- wc->startInRangeFunc,
- wc->endInRangeFunc,
- wc->inRangeColl,
- wc->inRangeAsc,
- wc->inRangeNullsFirst,
best_path->runCondition,
best_path->qual,
best_path->topwindow,
@@ -6704,18 +6693,15 @@ make_agg(List *tlist, List *qual,
}
static WindowAgg *
-make_windowagg(List *tlist, Index winref,
+make_windowagg(List *tlist, WindowClause *winclause,
int partNumCols, AttrNumber *partColIdx, Oid *partOperators, Oid *partCollations,
int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, Oid *ordCollations,
- int frameOptions, Node *startOffset, Node *endOffset,
- Oid startInRangeFunc, Oid endInRangeFunc,
- Oid inRangeColl, bool inRangeAsc, bool inRangeNullsFirst,
List *runCondition, List *qual, bool topWindow, Plan *lefttree)
{
WindowAgg *node = makeNode(WindowAgg);
Plan *plan = &node->plan;
- node->winref = winref;
+ node->winclause = winclause;
node->partNumCols = partNumCols;
node->partColIdx = partColIdx;
node->partOperators = partOperators;
@@ -6724,17 +6710,9 @@ make_windowagg(List *tlist, Index winref,
node->ordColIdx = ordColIdx;
node->ordOperators = ordOperators;
node->ordCollations = ordCollations;
- node->frameOptions = frameOptions;
- node->startOffset = startOffset;
- node->endOffset = endOffset;
node->runCondition = runCondition;
/* a duplicate of the above for EXPLAIN */
node->runConditionOrig = runCondition;
- node->startInRangeFunc = startInRangeFunc;
- node->endInRangeFunc = endInRangeFunc;
- node->inRangeColl = inRangeColl;
- node->inRangeAsc = inRangeAsc;
- node->inRangeNullsFirst = inRangeNullsFirst;
node->topWindow = topWindow;
plan->targetlist = tlist;
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 999a5a8ab5a..7da8ff34782 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -989,14 +989,12 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset)
set_upper_references(root, plan, rtoffset);
/*
- * Like Limit node limit/offset expressions, WindowAgg has
- * frame offset expressions, which cannot contain subplan
+ * Like Limit node limit/offset expressions, the window clause
+ * has frame offset expressions, which cannot contain subplan
* variable refs, so fix_scan_expr works for them.
*/
- wplan->startOffset =
- fix_scan_expr(root, wplan->startOffset, rtoffset, 1);
- wplan->endOffset =
- fix_scan_expr(root, wplan->endOffset, rtoffset, 1);
+ wplan->winclause = (WindowClause *)
+ fix_scan_expr(root, (Node *) wplan->winclause, rtoffset, 1);
wplan->runCondition = fix_scan_list(root,
wplan->runCondition,
rtoffset,
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 8230cbea3c3..c460f83ea35 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -2783,9 +2783,7 @@ finalize_plan(PlannerInfo *root, Plan *plan,
break;
case T_WindowAgg:
- finalize_primnode(((WindowAgg *) plan)->startOffset,
- &context);
- finalize_primnode(((WindowAgg *) plan)->endOffset,
+ finalize_primnode((Node *) ((WindowAgg *) plan)->winclause,
&context);
break;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index d11a8a20eea..05b9de12b97 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -11030,30 +11030,51 @@ get_windowfunc_expr_helper(WindowFunc *wfunc, deparse_context *context,
appendStringInfoString(buf, ") OVER ");
- foreach(l, context->windowClause)
+ if (context->windowClause)
{
- WindowClause *wc = (WindowClause *) lfirst(l);
-
- if (wc->winref == wfunc->winref)
+ foreach(l, context->windowClause)
{
- if (wc->name)
- appendStringInfoString(buf, quote_identifier(wc->name));
- else
- get_rule_windowspec(wc, context->targetList, context);
- break;
+ WindowClause *wc = (WindowClause *) lfirst(l);
+
+ if (wc->winref == wfunc->winref)
+ {
+ if (wc->name)
+ appendStringInfoString(buf, quote_identifier(wc->name));
+ else
+ get_rule_windowspec(wc, context->targetList, context);
+ break;
+ }
}
- }
- if (l == NULL)
- {
- if (context->windowClause)
+ if (l == NULL)
elog(ERROR, "could not find window clause for winref %u",
wfunc->winref);
-
+ }
+ else
+ {
/*
- * In EXPLAIN, we don't have window context information available, so
- * we have to settle for this:
+ * In EXPLAIN, search the namespace stack for a matching WindowAgg
+ * node. (Probably it's always the first entry.)
*/
- appendStringInfoString(buf, "(?)");
+ foreach(l, context->namespaces)
+ {
+ deparse_namespace *dpns = (deparse_namespace *) lfirst(l);
+
+ if (dpns->plan && IsA(dpns->plan, WindowAgg))
+ {
+ WindowClause *wc = ((WindowAgg *) dpns->plan)->winclause;
+
+ if (wc->winref == wfunc->winref)
+ {
+ /* Always print the spec, not the window name */
+ get_rule_windowspec(wc, dpns->plan->lefttree->targetlist,
+ context);
+ break;
+ }
+ }
+ }
+ /* This probably can't happen anymore: */
+ if (l == NULL)
+ appendStringInfoString(buf, "(?)");
}
}
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index bf1f25c0dba..8bae356d075 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -1171,13 +1171,14 @@ typedef struct WindowAgg
{
Plan plan;
- /* ID referenced by window functions */
- Index winref;
+ /* WindowClause implemented in this node */
+ /* use struct pointer to avoid including parsenodes.h here */
+ struct WindowClause *winclause;
/* number of columns in partition clause */
int partNumCols;
- /* their indexes in the target list */
+ /* their indexes in the subplan's target list */
AttrNumber *partColIdx pg_node_attr(array_size(partNumCols));
/* equality operators for partition columns */
@@ -1189,7 +1190,7 @@ typedef struct WindowAgg
/* number of columns in ordering clause */
int ordNumCols;
- /* their indexes in the target list */
+ /* their indexes in the subplan's target list */
AttrNumber *ordColIdx pg_node_attr(array_size(ordNumCols));
/* equality operators for ordering columns */
@@ -1198,42 +1199,13 @@ typedef struct WindowAgg
/* collations for ordering columns */
Oid *ordCollations pg_node_attr(array_size(ordNumCols));
- /* frame_clause options, see WindowDef */
- int frameOptions;
-
- /* expression for starting bound, if any */
- Node *startOffset;
-
- /* expression for ending bound, if any */
- Node *endOffset;
-
/* qual to help short-circuit execution */
List *runCondition;
- /* runCondition for display in EXPLAIN */
+ /* original runCondition, used only for display in EXPLAIN */
List *runConditionOrig;
- /* these fields are used with RANGE offset PRECEDING/FOLLOWING: */
-
- /* in_range function for startOffset */
- Oid startInRangeFunc;
-
- /* in_range function for endOffset */
- Oid endInRangeFunc;
-
- /* collation for in_range tests */
- Oid inRangeColl;
-
- /* use ASC sort order for in_range tests? */
- bool inRangeAsc;
-
- /* nulls sort first for in_range tests? */
- bool inRangeNullsFirst;
-
- /*
- * false for all apart from the WindowAgg that's closest to the root of
- * the plan
- */
+ /* true only in the WindowAgg that's closest to the root of the plan */
bool topWindow;
} WindowAgg;
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index d7c9b44605d..5e9dc086a07 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -1433,10 +1433,10 @@ select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum
explain (costs off)
select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum
from gstest2 group by cube (a,b) order by rsum, a, b;
- QUERY PLAN
----------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------
Sort
- Sort Key: (sum((sum(c))) OVER (?)), a, b
+ Sort Key: (sum((sum(c))) OVER (ORDER BY a, b)), a, b
-> WindowAgg
-> Sort
Sort Key: a, b
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 56509540f2a..b35602062fe 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -1125,14 +1125,14 @@ reset role;
explain (costs off, verbose)
select count(*) from tenk1 a where (unique1, two) in
(select unique1, row_number() over() from tenk1 b);
- QUERY PLAN
-----------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
Aggregate
Output: count(*)
-> Hash Right Semi Join
- Hash Cond: ((b.unique1 = a.unique1) AND ((row_number() OVER (?)) = a.two))
+ Hash Cond: ((b.unique1 = a.unique1) AND ((row_number() OVER (ROWS UNBOUNDED PRECEDING)) = a.two))
-> WindowAgg
- Output: b.unique1, row_number() OVER (?)
+ Output: b.unique1, row_number() OVER (ROWS UNBOUNDED PRECEDING)
-> Gather
Output: b.unique1
Workers Planned: 4
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
index 5e664fae084..41d86905dbb 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -1011,10 +1011,10 @@ FROM generate_series(1,5) i;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT JSON_OBJECTAGG(i: ('111' || i)::bytea FORMAT JSON WITH UNIQUE RETURNING text) OVER (PARTITION BY i % 2)
FROM generate_series(1,5) i;
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------
WindowAgg
- Output: JSON_OBJECTAGG(i : (('111'::text || (i)::text))::bytea FORMAT JSON WITH UNIQUE KEYS RETURNING text) OVER
(?),((i % 2))
+ Output: JSON_OBJECTAGG(i : (('111'::text || (i)::text))::bytea FORMAT JSON WITH UNIQUE KEYS RETURNING text) OVER
(PARTITIONBY ((i % 2))), ((i % 2))
-> Sort
Output: ((i % 2)), i
Sort Key: ((i.i % 2))
@@ -1047,10 +1047,10 @@ FROM generate_series(1,5) i;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT JSON_ARRAYAGG(('111' || i)::bytea FORMAT JSON NULL ON NULL RETURNING text) OVER (PARTITION BY i % 2)
FROM generate_series(1,5) i;
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------
WindowAgg
- Output: JSON_ARRAYAGG((('111'::text || (i)::text))::bytea FORMAT JSON NULL ON NULL RETURNING text) OVER (?), ((i %
2))
+ Output: JSON_ARRAYAGG((('111'::text || (i)::text))::bytea FORMAT JSON NULL ON NULL RETURNING text) OVER (PARTITION
BY((i % 2))), ((i % 2))
-> Sort
Output: ((i % 2)), i
Sort Key: ((i.i % 2))
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 23d1463df22..31a971a02c7 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -3731,12 +3731,12 @@ SELECT
count(*) OVER (PARTITION BY depname ORDER BY enroll_date RANGE BETWEEN
CURRENT ROW AND CURRENT ROW) cnt
FROM empsalary;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------
+
QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WindowAgg
- Output: empno, depname, (row_number() OVER (?)), (rank() OVER (?)), count(*) OVER (?), enroll_date
+ Output: empno, depname, (row_number() OVER (PARTITION BY depname ORDER BY enroll_date ROWS UNBOUNDED PRECEDING)),
(rank()OVER (PARTITION BY depname ORDER BY enroll_date ROWS UNBOUNDED PRECEDING)), count(*) OVER (PARTITION BY depname
ORDERBY enroll_date RANGE BETWEEN CURRENT ROW AND CURRENT ROW), enroll_date
-> WindowAgg
- Output: depname, enroll_date, empno, row_number() OVER (?), rank() OVER (?)
+ Output: depname, enroll_date, empno, row_number() OVER (PARTITION BY depname ORDER BY enroll_date ROWS
UNBOUNDEDPRECEDING), rank() OVER (PARTITION BY depname ORDER BY enroll_date ROWS UNBOUNDED PRECEDING)
-> Sort
Output: depname, enroll_date, empno
Sort Key: empsalary.depname, empsalary.enroll_date
@@ -3817,10 +3817,10 @@ SELECT * FROM
row_number() OVER (ORDER BY empno) rn
FROM empsalary) emp
WHERE rn < 3;
- QUERY PLAN
-----------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
WindowAgg
- Run Condition: (row_number() OVER (?) < 3)
+ Run Condition: (row_number() OVER (ORDER BY empsalary.empno ROWS UNBOUNDED PRECEDING) < 3)
-> Sort
Sort Key: empsalary.empno
-> Seq Scan on empsalary
@@ -3868,10 +3868,10 @@ SELECT * FROM
rank() OVER (ORDER BY salary DESC) r
FROM empsalary) emp
WHERE r <= 3;
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
WindowAgg
- Run Condition: (rank() OVER (?) <= 3)
+ Run Condition: (rank() OVER (ORDER BY empsalary.salary DESC ROWS UNBOUNDED PRECEDING) <= 3)
-> Sort
Sort Key: empsalary.salary DESC
-> Seq Scan on empsalary
@@ -3898,12 +3898,12 @@ SELECT * FROM
dense_rank() OVER (ORDER BY salary DESC) dr
FROM empsalary) emp
WHERE dr = 1;
- QUERY PLAN
------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
Subquery Scan on emp
Filter: (emp.dr = 1)
-> WindowAgg
- Run Condition: (dense_rank() OVER (?) <= 1)
+ Run Condition: (dense_rank() OVER (ORDER BY empsalary.salary DESC ROWS UNBOUNDED PRECEDING) <= 1)
-> Sort
Sort Key: empsalary.salary DESC
-> Seq Scan on empsalary
@@ -3928,10 +3928,10 @@ SELECT * FROM
count(*) OVER (ORDER BY salary DESC) c
FROM empsalary) emp
WHERE c <= 3;
- QUERY PLAN
--------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------
WindowAgg
- Run Condition: (count(*) OVER (?) <= 3)
+ Run Condition: (count(*) OVER (ORDER BY empsalary.salary DESC) <= 3)
-> Sort
Sort Key: empsalary.salary DESC
-> Seq Scan on empsalary
@@ -3957,10 +3957,10 @@ SELECT * FROM
count(empno) OVER (ORDER BY salary DESC) c
FROM empsalary) emp
WHERE c <= 3;
- QUERY PLAN
----------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------
WindowAgg
- Run Condition: (count(empsalary.empno) OVER (?) <= 3)
+ Run Condition: (count(empsalary.empno) OVER (ORDER BY empsalary.salary DESC) <= 3)
-> Sort
Sort Key: empsalary.salary DESC
-> Seq Scan on empsalary
@@ -3986,10 +3986,10 @@ SELECT * FROM
count(*) OVER (ORDER BY salary DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) c
FROM empsalary) emp
WHERE c >= 3;
- QUERY PLAN
--------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------
WindowAgg
- Run Condition: (count(*) OVER (?) >= 3)
+ Run Condition: (count(*) OVER (ORDER BY empsalary.salary DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) >=
3)
-> Sort
Sort Key: empsalary.salary DESC
-> Seq Scan on empsalary
@@ -4002,10 +4002,10 @@ SELECT * FROM
count(*) OVER () c
FROM empsalary) emp
WHERE 11 <= c;
- QUERY PLAN
---------------------------------------------
+ QUERY PLAN
+-------------------------------------------
WindowAgg
- Run Condition: (11 <= count(*) OVER (?))
+ Run Condition: (11 <= count(*) OVER ())
-> Seq Scan on empsalary
(3 rows)
@@ -4017,12 +4017,12 @@ SELECT * FROM
dense_rank() OVER (ORDER BY salary DESC) dr
FROM empsalary) emp
WHERE dr = 1;
- QUERY PLAN
------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------
Subquery Scan on emp
Filter: (emp.dr = 1)
-> WindowAgg
- Run Condition: (dense_rank() OVER (?) <= 1)
+ Run Condition: (dense_rank() OVER (ORDER BY empsalary.salary DESC) <= 1)
-> Sort
Sort Key: empsalary.salary DESC
-> Seq Scan on empsalary
@@ -4036,10 +4036,10 @@ SELECT * FROM
row_number() OVER (PARTITION BY depname ORDER BY empno) rn
FROM empsalary) emp
WHERE rn < 3;
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------
WindowAgg
- Run Condition: (row_number() OVER (?) < 3)
+ Run Condition: (row_number() OVER (PARTITION BY empsalary.depname ORDER BY empsalary.empno ROWS UNBOUNDED
PRECEDING)< 3)
-> Sort
Sort Key: empsalary.depname, empsalary.empno
-> Seq Scan on empsalary
@@ -4071,11 +4071,11 @@ SELECT empno, depname FROM
row_number() OVER (PARTITION BY depname ORDER BY empno) rn
FROM empsalary) emp
WHERE rn < 3;
- QUERY PLAN
-------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on emp
-> WindowAgg
- Run Condition: (row_number() OVER (?) < 3)
+ Run Condition: (row_number() OVER (PARTITION BY empsalary.depname ORDER BY empsalary.empno ROWS UNBOUNDED
PRECEDING)< 3)
-> Sort
Sort Key: empsalary.depname, empsalary.empno
-> Seq Scan on empsalary
@@ -4090,10 +4090,10 @@ SELECT * FROM
count(empno) OVER (PARTITION BY depname ORDER BY salary DESC) c
FROM empsalary) emp
WHERE c <= 3;
- QUERY PLAN
-------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------
WindowAgg
- Run Condition: (count(empsalary.empno) OVER (?) <= 3)
+ Run Condition: (count(empsalary.empno) OVER (PARTITION BY empsalary.depname ORDER BY empsalary.salary DESC) <= 3)
-> Sort
Sort Key: empsalary.depname, empsalary.salary DESC
-> Seq Scan on empsalary
@@ -4129,10 +4129,10 @@ SELECT * FROM
count(empno) OVER () c
FROM empsalary) emp
WHERE c = 1;
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------
WindowAgg
- Run Condition: (count(empsalary.empno) OVER (?) = 1)
+ Run Condition: (count(empsalary.empno) OVER () = 1)
-> Seq Scan on empsalary
(3 rows)
@@ -4157,16 +4157,16 @@ SELECT * FROM
ntile(2) OVER (PARTITION BY depname) nt -- w2
FROM empsalary
) e WHERE rn <= 1 AND c1 <= 3 AND nt < 2;
- QUERY PLAN
------------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on e
-> WindowAgg
- Filter: (((row_number() OVER (?)) <= 1) AND ((ntile(2) OVER (?)) < 2))
- Run Condition: (count(empsalary.salary) OVER (?) <= 3)
+ Filter: (((row_number() OVER (PARTITION BY empsalary.depname)) <= 1) AND ((ntile(2) OVER (PARTITION BY
empsalary.depname))< 2))
+ Run Condition: (count(empsalary.salary) OVER (PARTITION BY (((empsalary.depname)::text || ''::text))) <= 3)
-> Sort
Sort Key: (((empsalary.depname)::text || ''::text))
-> WindowAgg
- Run Condition: ((row_number() OVER (?) <= 1) AND (ntile(2) OVER (?) < 2))
+ Run Condition: ((row_number() OVER (PARTITION BY empsalary.depname) <= 1) AND (ntile(2) OVER
(PARTITIONBY empsalary.depname) < 2))
-> Sort
Sort Key: empsalary.depname
-> WindowAgg
@@ -4199,12 +4199,12 @@ SELECT 1 FROM
FROM empsalary e1 LEFT JOIN empsalary e2 ON TRUE
WHERE e1.empno = e2.empno) s
WHERE s.c = 1;
- QUERY PLAN
----------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------
Subquery Scan on s
Filter: (s.c = 1)
-> WindowAgg
- Run Condition: (ntile(e2.salary) OVER (?) <= 1)
+ Run Condition: (ntile(e2.salary) OVER (PARTITION BY e1.depname ROWS UNBOUNDED PRECEDING) <= 1)
-> Sort
Sort Key: e1.depname
-> Merge Join
@@ -4224,12 +4224,12 @@ SELECT 1 FROM
(SELECT ntile(s1.x) OVER () AS c
FROM (SELECT (SELECT 1) AS x) AS s1) s
WHERE s.c = 1;
- QUERY PLAN
------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------
Subquery Scan on s
Filter: (s.c = 1)
-> WindowAgg
- Run Condition: (ntile((InitPlan 1).col1) OVER (?) <= 1)
+ Run Condition: (ntile((InitPlan 1).col1) OVER (ROWS UNBOUNDED PRECEDING) <= 1)
InitPlan 1
-> Result
-> Result
@@ -4389,11 +4389,11 @@ SELECT DISTINCT
min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary
FROM empsalary
ORDER BY depname, enroll_date;
- QUERY PLAN
------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique
-> Incremental Sort
- Sort Key: depname, enroll_date, empno, (sum(salary) OVER (?)), (min(salary) OVER (?))
+ Sort Key: depname, enroll_date, empno, (sum(salary) OVER (PARTITION BY depname ORDER BY empno)), (min(salary)
OVER(PARTITION BY depname ORDER BY enroll_date))
Presorted Key: depname, enroll_date
-> WindowAgg
-> Incremental Sort
@@ -4416,11 +4416,11 @@ SELECT DISTINCT
min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary
FROM empsalary
ORDER BY depname, empno;
- QUERY PLAN
------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique
-> Incremental Sort
- Sort Key: depname, empno, enroll_date, (sum(salary) OVER (?)), (min(salary) OVER (?))
+ Sort Key: depname, empno, enroll_date, (sum(salary) OVER (PARTITION BY depname ORDER BY empno)), (min(salary)
OVER(PARTITION BY depname ORDER BY enroll_date))
Presorted Key: depname, empno
-> WindowAgg
-> Incremental Sort
On Sun, 9 Mar 2025 at 10:39, Tom Lane <tgl@sss.pgh.pa.us> wrote: > One thing that puzzled me a bit is that many of the outputs > show "ROWS UNBOUNDED PRECEDING" in window functions where that > definitely wasn't in the source query. Eventually I realized > that that comes from window_row_number_support() and cohorts > optimizing the query. While this isn't wrong, I suspect it > will cause a lot of confusion and questions. I wonder if we > should do something to hide the change? I suspect it might be more confusing if we were to show the user the original frame options. Isn't EXPLAIN meant to be a window into the plan that's been or would be executed? I think it would be misleading to display something different to what will be executed. Take the following case, for example: create table t1 as select 1 as a from generate_Series(1,1000000); vacuum freeze analyze t1; (btw, the patch is giving me ERROR: bogus varno: -3 with EXPLAIN VERBOSE on this) select a,row_number() over (order by a) from t1 limit 1; Time: 0.246 ms This performs a "ROWS UNBOUNDED PRECEDING" WindowAgg. If we add another WindowFunc with the same frame options: select a,row_number() over (order by a),sum(a) over (order by a) from t1 limit 1; Time: 159.420 ms This one performs a "RANGE UNBOUNDED PRECEDING" WindowAgg. A user might be surprised that the performance drops to this degree just by adding the SUM() aggregate using the same frame options as the row_number(). If we show the honest frame options as decided by the planner, then the performance drop is easier to understand. If too many users are confused with why the frame options aren't what they asked for, then maybe we'll need to document the optimisation. I think the planner does plenty of other things that change what's shown in EXPLAIN. Constant folding is one example. Partition pruning is another. Maybe those two are easier to understand than window agg frame options, however. David
On Sat, Mar 8, 2025 at 6:15 PM David Rowley <dgrowleyml@gmail.com> wrote:
On Sun, 9 Mar 2025 at 10:39, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> One thing that puzzled me a bit is that many of the outputs
> show "ROWS UNBOUNDED PRECEDING" in window functions where that
> definitely wasn't in the source query. Eventually I realized
> that that comes from window_row_number_support() and cohorts
> optimizing the query. While this isn't wrong, I suspect it
> will cause a lot of confusion and questions. I wonder if we
> should do something to hide the change?
I suspect it might be more confusing if we were to show the user the
original frame options. Isn't EXPLAIN meant to be a window into the
plan that's been or would be executed? I think it would be misleading
to display something different to what will be executed.
Looking at this example:
SELECT
empno,
depname,
row_number() OVER (PARTITION BY depname ORDER BY enroll_date) rn,
rank() OVER (PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) rnk,
count(*) OVER (PARTITION BY depname ORDER BY enroll_date RANGE BETWEEN
CURRENT ROW AND CURRENT ROW) cnt
FROM empsalary;
empno,
depname,
row_number() OVER (PARTITION BY depname ORDER BY enroll_date) rn,
rank() OVER (PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) rnk,
count(*) OVER (PARTITION BY depname ORDER BY enroll_date RANGE BETWEEN
CURRENT ROW AND CURRENT ROW) cnt
FROM empsalary;
The new output is:
WindowAgg
Output: empno, depname, (row_number() OVER (PARTITION BY depname ORDER BY enroll_date ROWS UNBOUNDED PRECEDING)), (rank() OVER (PARTITION BY depname ORDER BY enroll_date ROWS UNBOUNDED PRECEDING)), count(*) OVER (PARTITION BY depname ORDER BY enroll_date RANGE BETWEEN CURRENT ROW AND CURRENT ROW), enroll_date
-> WindowAgg
Output: depname, enroll_date, empno, row_number() OVER (PARTITION BY depname ORDER BY enroll_date ROWS UNBOUNDED PRECEDING), rank() OVER (PARTITION BY depname ORDER BY enroll_date ROWS UNBOUNDED PRECEDING)
-> Sort
Output: depname, enroll_date, empno
Sort Key: empsalary.depname, empsalary.enroll_date
-> Seq Scan on pg_temp.empsalary
Output: depname, enroll_date, empno
Output: empno, depname, (row_number() OVER (PARTITION BY depname ORDER BY enroll_date ROWS UNBOUNDED PRECEDING)), (rank() OVER (PARTITION BY depname ORDER BY enroll_date ROWS UNBOUNDED PRECEDING)), count(*) OVER (PARTITION BY depname ORDER BY enroll_date RANGE BETWEEN CURRENT ROW AND CURRENT ROW), enroll_date
-> WindowAgg
Output: depname, enroll_date, empno, row_number() OVER (PARTITION BY depname ORDER BY enroll_date ROWS UNBOUNDED PRECEDING), rank() OVER (PARTITION BY depname ORDER BY enroll_date ROWS UNBOUNDED PRECEDING)
-> Sort
Output: depname, enroll_date, empno
Sort Key: empsalary.depname, empsalary.enroll_date
-> Seq Scan on pg_temp.empsalary
Output: depname, enroll_date, empno
It is kinda annoying that row_number and rank have their entire expression output twice when the computation only happens once. But that is outside the scope; just making an observation. It just becomes even worse when we fill in the details.
As for the optimization, any reason to not just show that it was done? In optimize_window_clauses arrange to save the existing_wc somewhere on the relevant window functions then, in explain, output something like:
-> WindowAgg
Output: depname, enroll_date, empno, row_number() OVER (...), rank() OVER (...)
Reframed: row_number() from (default) RANGE => ROWS
(I'm unsure whether we can write "default" here though, it isn't critical.)
Reframed: rank() from UNBOUNDED FOLLOWING => CURRENT ROW
(I initially put the entire frame clause, without omitting default frame_end, there but then figured it defeated the point. We should only show those elements (type, start, end) that actually are different between the parsed query and what gets executed.)
Which does bring up the point, to what extent should the explain output rely on defaults versus being explicit? We are omitting frame_end of CURRENT ROW generally here.
David J.
Hello
Would it be possible and make sense to use notation of explicit WINDOW
clauses, for cases where multiple window functions invoke identical
window definitions? I'm thinking of something like
explain verbose SELECT
empno,
depname,
row_number() OVER testwin rn,
rank() OVER testwin rnk,
count(*) OVER testwin cnt
FROM empsalary
window testwin as
(PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
for which, with the patch, we'd get this
QUERY PLAN
─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
WindowAgg (cost=74.64..101.29 rows=1070 width=68)
Output: empno, depname, row_number() OVER (PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN UNBOUNDED
PRECEDINGAND UNBOUNDED FOLLOWING), rank() OVER (PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN UNBOUNDED
PRECEDINGAND UNBOUNDED FOLLOWING), count(*) OVER (PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN UNBOUNDED
PRECEDINGAND UNBOUNDED FOLLOWING), enroll_date
-> Sort (cost=74.54..77.21 rows=1070 width=44)
Output: depname, enroll_date, empno
Sort Key: empsalary.depname, empsalary.enroll_date
-> Seq Scan on pg_temp.empsalary (cost=0.00..20.70 rows=1070 width=44)
Output: depname, enroll_date, empno
(7 filas)
which is pretty ugly to read and requires careful tracking to verify
that they're all defined on the same window. Previously, we just get
QUERY PLAN
──────────────────────────────────────────────────────────────────────────────────────────────────
WindowAgg (cost=74.64..101.29 rows=1070 width=68)
Output: empno, depname, row_number() OVER (?), rank() OVER (?), count(*) OVER (?), enroll_date
-> Sort (cost=74.54..77.21 rows=1070 width=44)
Output: depname, enroll_date, empno
Sort Key: empsalary.depname, empsalary.enroll_date
-> Seq Scan on pg_temp.empsalary (cost=0.00..20.70 rows=1070 width=44)
Output: depname, enroll_date, empno
(7 filas)
so it didn't matter.
I'd imagine something like
QUERY PLAN
─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Window testwin AS (PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
WindowAgg (cost=74.64..101.29 rows=1070 width=68)
Output: empno, depname, row_number() OVER testwin, rank() OVER testwin, count(*) OVER testwin, enroll_date
-> Sort (cost=74.54..77.21 rows=1070 width=44)
Output: depname, enroll_date, empno
Sort Key: empsalary.depname, empsalary.enroll_date
-> Seq Scan on pg_temp.empsalary (cost=0.00..20.70 rows=1070 width=44)
Output: depname, enroll_date, empno
(7 filas)
I imagine this working even if the user doesn't explicitly use a WINDOW
clause, if only because it makes the explain easier to read, and it's
much clearer if the user specifies two different window definitions.
So with David Johnston's example, something like
Window window1 AS (PARTITION BY depname ORDER BY enroll_date ROWS UNBOUNDED PRECEDING)
Window window2 AS (PARTITION BY depname ORDER BY enroll_date RANGE BETWEEN CURRENT ROW AND CURRENT ROW)
WindowAgg
Output: empno, depname, (row_number() OVER window1), rank() OVER window1, count(*) OVER window2, enroll_date
-> WindowAgg
Output: depname, enroll_date, empno, row_number() OVER window1, rank() OVER window1
-> Sort
Output: depname, enroll_date, empno
Sort Key: empsalary.depname, empsalary.enroll_date
-> Seq Scan on pg_temp.empsalary
Output: depname, enroll_date, empno
(Hmm, not sure if the Window clauses would be top-level or attached to
each WindowAgg in its own level.)
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
Thou shalt study thy libraries and strive not to reinvent them without
cause, that thy code may be short and readable and thy days pleasant
and productive. (7th Commandment for C Programmers)
=?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@alvh.no-ip.org> writes:
> Would it be possible and make sense to use notation of explicit WINDOW
> clauses, for cases where multiple window functions invoke identical
> window definitions?
There's something to be said for that. We would have to assign
made-up names to windows that didn't have one. But then the
output might look like
WindowAgg (...)
Output: empno, depname, row_number() OVER (window1), rank() OVER (window1), count(*) OVER (window1), enroll_date
Window: window1 = PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING
which is surely a lot nicer than 3x repetitions of the window spec.
After reading David's mail I'd been thinking of something like
WindowAgg (...)
Output: empno, depname, row_number() OVER (...), rank() OVER (...), count(*) OVER (...), enroll_date
Window: PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
which is shorter but vaguer. In particular, if you have more than one
WindowAgg, then with explicit names we'd have something like
WindowAgg (...)
Output: empno, depname, row_number() OVER (window1), rank() OVER (window1), (count(*) OVER (window2)), enroll_date
Window: window1 = PARTITION BY depname ORDER BY enroll_date ROWS UNBOUNDED PRECEDING
WindowAgg (...)
Output: empno, depname, count(*) OVER (window2), enroll_date
Window: window2 = PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING
With "..." that would be confusing as heck to someone who didn't
understand the nuances of the extra parentheses.
> (Hmm, not sure if the Window clauses would be top-level or attached to
> each WindowAgg in its own level.)
IMO the obvious thing is to attach each WindowClause to the WindowAgg
node that implements it.
I'll go try to code this up.
regards, tom lane
I wrote:
> I'll go try to code this up.
OK, here's v2 done like that. I do like this output better.
I backed off the idea of putting the WindowClause as such
into the plan, partly because I didn't feel like debugging
the setrefs.c problem that David discovered upthread.
This way does require a bit more code, but I think it's less
likely to have bugs.
A couple of notes:
* I made the Window: plan annotation come out unconditionally.
We could alternatively print it only in VERBOSE mode, which would
greatly reduce the number of regression test diffs. However, it seems
fairly comparable to the sort keys of a Sort node or the group keys of
a Group node, which we print unconditionally. Also, there are cases
where a higher-level node unconditionally prints a reference to a
window function output, which would mean that that output's reference
to "windowN" would have no referent in the displayed data.
* In passing, I editorialized on the order in which the Run Condition
annotation comes out:
case T_WindowAgg:
+ show_window_def(castNode(WindowAggState, planstate), ancestors, es);
show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
+ show_upper_qual(((WindowAgg *) plan)->runConditionOrig,
+ "Run Condition", planstate, ancestors, es);
if (plan->qual)
show_instrumentation_count("Rows Removed by Filter", 1,
planstate, es);
- show_upper_qual(((WindowAgg *) plan)->runConditionOrig,
- "Run Condition", planstate, ancestors, es);
show_windowagg_info(castNode(WindowAggState, planstate), es);
break;
It seemed quite weird to me to have the Run Condition plan property
come out in the middle of properties that only appear in EXPLAIN
ANALYZE mode. Maybe there's a reason for this other than "people
added new properties at the end", but I don't see it.
regards, tom lane
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index c68119030ab..29e8d7b806f 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -3968,10 +3968,11 @@ select c2, sum(c2), count(c2) over (partition by c2%2) from ft2 where c2 < 10 gr
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Sort
- Output: c2, (sum(c2)), (count(c2) OVER (?)), ((c2 % 2))
+ Output: c2, (sum(c2)), (count(c2) OVER window1), ((c2 % 2))
Sort Key: ft2.c2
-> WindowAgg
- Output: c2, (sum(c2)), count(c2) OVER (?), ((c2 % 2))
+ Output: c2, (sum(c2)), count(c2) OVER window1, ((c2 % 2))
+ Window: window1 AS (PARTITION BY ((ft2.c2 % 2)))
-> Sort
Output: c2, ((c2 % 2)), (sum(c2))
Sort Key: ((ft2.c2 % 2))
@@ -3979,7 +3980,7 @@ select c2, sum(c2), count(c2) over (partition by c2%2) from ft2 where c2 < 10 gr
Output: c2, ((c2 % 2)), (sum(c2))
Relations: Aggregate on (public.ft2)
Remote SQL: SELECT c2, (c2 % 2), sum(c2) FROM "S 1"."T 1" WHERE ((c2 < 10)) GROUP BY 1
-(12 rows)
+(13 rows)
select c2, sum(c2), count(c2) over (partition by c2%2) from ft2 where c2 < 10 group by c2 order by 1;
c2 | sum | count
@@ -4001,10 +4002,11 @@ select c2, array_agg(c2) over (partition by c2%2 order by c2 desc) from ft1 wher
QUERY PLAN
---------------------------------------------------------------------------------------------------
Sort
- Output: c2, (array_agg(c2) OVER (?)), ((c2 % 2))
+ Output: c2, (array_agg(c2) OVER window1), ((c2 % 2))
Sort Key: ft1.c2
-> WindowAgg
- Output: c2, array_agg(c2) OVER (?), ((c2 % 2))
+ Output: c2, array_agg(c2) OVER window1, ((c2 % 2))
+ Window: window1 AS (PARTITION BY ((ft1.c2 % 2)) ORDER BY ft1.c2)
-> Sort
Output: c2, ((c2 % 2))
Sort Key: ((ft1.c2 % 2)), ft1.c2 DESC
@@ -4012,7 +4014,7 @@ select c2, array_agg(c2) over (partition by c2%2 order by c2 desc) from ft1 wher
Output: c2, ((c2 % 2))
Relations: Aggregate on (public.ft1)
Remote SQL: SELECT c2, (c2 % 2) FROM "S 1"."T 1" WHERE ((c2 < 10)) GROUP BY 1
-(12 rows)
+(13 rows)
select c2, array_agg(c2) over (partition by c2%2 order by c2 desc) from ft1 where c2 < 10 group by c2 order by 1;
c2 | array_agg
@@ -4031,13 +4033,14 @@ select c2, array_agg(c2) over (partition by c2%2 order by c2 desc) from ft1 wher
explain (verbose, costs off)
select c2, array_agg(c2) over (partition by c2%2 order by c2 range between current row and unbounded following) from
ft1where c2 < 10 group by c2 order by 1;
- QUERY PLAN
----------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------
Sort
- Output: c2, (array_agg(c2) OVER (?)), ((c2 % 2))
+ Output: c2, (array_agg(c2) OVER window1), ((c2 % 2))
Sort Key: ft1.c2
-> WindowAgg
- Output: c2, array_agg(c2) OVER (?), ((c2 % 2))
+ Output: c2, array_agg(c2) OVER window1, ((c2 % 2))
+ Window: window1 AS (PARTITION BY ((ft1.c2 % 2)) ORDER BY ft1.c2 RANGE BETWEEN CURRENT ROW AND UNBOUNDED
FOLLOWING)
-> Sort
Output: c2, ((c2 % 2))
Sort Key: ((ft1.c2 % 2)), ft1.c2
@@ -4045,7 +4048,7 @@ select c2, array_agg(c2) over (partition by c2%2 order by c2 range between curre
Output: c2, ((c2 % 2))
Relations: Aggregate on (public.ft1)
Remote SQL: SELECT c2, (c2 % 2) FROM "S 1"."T 1" WHERE ((c2 < 10)) GROUP BY 1
-(12 rows)
+(13 rows)
select c2, array_agg(c2) over (partition by c2%2 order by c2 range between current row and unbounded following) from
ft1where c2 < 10 group by c2 order by 1;
c2 | array_agg
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index d8a7232cedb..4d7add40c31 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -107,6 +107,11 @@ static void show_sort_group_keys(PlanState *planstate, const char *qlabel,
List *ancestors, ExplainState *es);
static void show_sortorder_options(StringInfo buf, Node *sortexpr,
Oid sortOperator, Oid collation, bool nullsFirst);
+static void show_window_def(WindowAggState *planstate,
+ List *ancestors, ExplainState *es);
+static void show_window_keys(StringInfo buf, PlanState *planstate,
+ int nkeys, AttrNumber *keycols,
+ List *ancestors, ExplainState *es);
static void show_storage_info(char *maxStorageType, int64 maxSpaceUsed,
ExplainState *es);
static void show_tablesample(TableSampleClause *tsc, PlanState *planstate,
@@ -2329,12 +2334,13 @@ ExplainNode(PlanState *planstate, List *ancestors,
planstate, es);
break;
case T_WindowAgg:
+ show_window_def(castNode(WindowAggState, planstate), ancestors, es);
show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
+ show_upper_qual(((WindowAgg *) plan)->runConditionOrig,
+ "Run Condition", planstate, ancestors, es);
if (plan->qual)
show_instrumentation_count("Rows Removed by Filter", 1,
planstate, es);
- show_upper_qual(((WindowAgg *) plan)->runConditionOrig,
- "Run Condition", planstate, ancestors, es);
show_windowagg_info(castNode(WindowAggState, planstate), es);
break;
case T_Group:
@@ -3003,6 +3009,113 @@ show_sortorder_options(StringInfo buf, Node *sortexpr,
}
}
+/*
+ * Show the window definition for a WindowAgg node.
+ */
+static void
+show_window_def(WindowAggState *planstate, List *ancestors, ExplainState *es)
+{
+ WindowAgg *wagg = (WindowAgg *) planstate->ss.ps.plan;
+ StringInfoData wbuf;
+ bool needspace = false;
+
+ initStringInfo(&wbuf);
+ appendStringInfo(&wbuf, "%s AS (", quote_identifier(wagg->winname));
+
+ /* The key columns refer to the tlist of the child plan */
+ ancestors = lcons(wagg, ancestors);
+ if (wagg->partNumCols > 0)
+ {
+ appendStringInfoString(&wbuf, "PARTITION BY ");
+ show_window_keys(&wbuf, outerPlanState(planstate),
+ wagg->partNumCols, wagg->partColIdx,
+ ancestors, es);
+ needspace = true;
+ }
+ if (wagg->ordNumCols > 0)
+ {
+ if (needspace)
+ appendStringInfoChar(&wbuf, ' ');
+ appendStringInfoString(&wbuf, "ORDER BY ");
+ show_window_keys(&wbuf, outerPlanState(planstate),
+ wagg->ordNumCols, wagg->ordColIdx,
+ ancestors, es);
+ needspace = true;
+ }
+ ancestors = list_delete_first(ancestors);
+ if (wagg->frameOptions & FRAMEOPTION_NONDEFAULT)
+ {
+ List *context;
+ bool useprefix;
+ char *framestr;
+
+ /* Set up deparsing context for possible frame expressions */
+ context = set_deparse_context_plan(es->deparse_cxt,
+ (Plan *) wagg,
+ ancestors);
+ useprefix = (es->rtable_size > 1 || es->verbose);
+ framestr = get_window_frame_options_for_explain(wagg->frameOptions,
+ wagg->startOffset,
+ wagg->endOffset,
+ context,
+ useprefix);
+ if (needspace)
+ appendStringInfoChar(&wbuf, ' ');
+ appendStringInfoString(&wbuf, framestr);
+ pfree(framestr);
+ }
+ appendStringInfoChar(&wbuf, ')');
+ ExplainPropertyText("Window", wbuf.data, es);
+ pfree(wbuf.data);
+}
+
+/*
+ * Append the keys of a window's PARTITION BY or ORDER BY clause to buf.
+ * We can't use show_sort_group_keys for this because that's too opinionated
+ * about how the result will be displayed.
+ * Note that the "planstate" node should be the WindowAgg's child.
+ */
+static void
+show_window_keys(StringInfo buf, PlanState *planstate,
+ int nkeys, AttrNumber *keycols,
+ List *ancestors, ExplainState *es)
+{
+ Plan *plan = planstate->plan;
+ List *context;
+ bool useprefix;
+
+ /* Set up deparsing context */
+ context = set_deparse_context_plan(es->deparse_cxt,
+ plan,
+ ancestors);
+ useprefix = (es->rtable_size > 1 || es->verbose);
+
+ for (int keyno = 0; keyno < nkeys; keyno++)
+ {
+ /* find key expression in tlist */
+ AttrNumber keyresno = keycols[keyno];
+ TargetEntry *target = get_tle_by_resno(plan->targetlist,
+ keyresno);
+ char *exprstr;
+
+ if (!target)
+ elog(ERROR, "no tlist entry for key %d", keyresno);
+ /* Deparse the expression, showing any top-level cast */
+ exprstr = deparse_expression((Node *) target->expr, context,
+ useprefix, true);
+ if (keyno > 0)
+ appendStringInfoString(buf, ", ");
+ appendStringInfoString(buf, exprstr);
+ pfree(exprstr);
+
+ /*
+ * We don't attempt to provide sort order information because
+ * WindowAgg carries equality operators not comparison operators;
+ * compare show_agg_keys.
+ */
+ }
+}
+
/*
* Show information on storage method and maximum memory/disk space used.
*/
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 816a2b2a576..75e2b0b9036 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -285,12 +285,9 @@ static Memoize *make_memoize(Plan *lefttree, Oid *hashoperators,
Oid *collations, List *param_exprs,
bool singlerow, bool binary_mode,
uint32 est_entries, Bitmapset *keyparamids);
-static WindowAgg *make_windowagg(List *tlist, Index winref,
+static WindowAgg *make_windowagg(List *tlist, WindowClause *wc,
int partNumCols, AttrNumber *partColIdx, Oid *partOperators, Oid *partCollations,
int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, Oid *ordCollations,
- int frameOptions, Node *startOffset, Node *endOffset,
- Oid startInRangeFunc, Oid endInRangeFunc,
- Oid inRangeColl, bool inRangeAsc, bool inRangeNullsFirst,
List *runCondition, List *qual, bool topWindow,
Plan *lefttree);
static Group *make_group(List *tlist, List *qual, int numGroupCols,
@@ -2683,7 +2680,7 @@ create_windowagg_plan(PlannerInfo *root, WindowAggPath *best_path)
/* And finally we can make the WindowAgg node */
plan = make_windowagg(tlist,
- wc->winref,
+ wc,
partNumCols,
partColIdx,
partOperators,
@@ -2692,14 +2689,6 @@ create_windowagg_plan(PlannerInfo *root, WindowAggPath *best_path)
ordColIdx,
ordOperators,
ordCollations,
- wc->frameOptions,
- wc->startOffset,
- wc->endOffset,
- wc->startInRangeFunc,
- wc->endInRangeFunc,
- wc->inRangeColl,
- wc->inRangeAsc,
- wc->inRangeNullsFirst,
best_path->runCondition,
best_path->qual,
best_path->topwindow,
@@ -6704,18 +6693,16 @@ make_agg(List *tlist, List *qual,
}
static WindowAgg *
-make_windowagg(List *tlist, Index winref,
+make_windowagg(List *tlist, WindowClause *wc,
int partNumCols, AttrNumber *partColIdx, Oid *partOperators, Oid *partCollations,
int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, Oid *ordCollations,
- int frameOptions, Node *startOffset, Node *endOffset,
- Oid startInRangeFunc, Oid endInRangeFunc,
- Oid inRangeColl, bool inRangeAsc, bool inRangeNullsFirst,
List *runCondition, List *qual, bool topWindow, Plan *lefttree)
{
WindowAgg *node = makeNode(WindowAgg);
Plan *plan = &node->plan;
- node->winref = winref;
+ node->winname = wc->name;
+ node->winref = wc->winref;
node->partNumCols = partNumCols;
node->partColIdx = partColIdx;
node->partOperators = partOperators;
@@ -6724,17 +6711,17 @@ make_windowagg(List *tlist, Index winref,
node->ordColIdx = ordColIdx;
node->ordOperators = ordOperators;
node->ordCollations = ordCollations;
- node->frameOptions = frameOptions;
- node->startOffset = startOffset;
- node->endOffset = endOffset;
+ node->frameOptions = wc->frameOptions;
+ node->startOffset = wc->startOffset;
+ node->endOffset = wc->endOffset;
node->runCondition = runCondition;
/* a duplicate of the above for EXPLAIN */
node->runConditionOrig = runCondition;
- node->startInRangeFunc = startInRangeFunc;
- node->endInRangeFunc = endInRangeFunc;
- node->inRangeColl = inRangeColl;
- node->inRangeAsc = inRangeAsc;
- node->inRangeNullsFirst = inRangeNullsFirst;
+ node->startInRangeFunc = wc->startInRangeFunc;
+ node->endInRangeFunc = wc->endInRangeFunc;
+ node->inRangeColl = wc->inRangeColl;
+ node->inRangeAsc = wc->inRangeAsc;
+ node->inRangeNullsFirst = wc->inRangeNullsFirst;
node->topWindow = topWindow;
plan->targetlist = tlist;
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 36ee6dd43de..87b2d53280c 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -214,6 +214,7 @@ static List *postprocess_setop_tlist(List *new_tlist, List *orig_tlist);
static void optimize_window_clauses(PlannerInfo *root,
WindowFuncLists *wflists);
static List *select_active_windows(PlannerInfo *root, WindowFuncLists *wflists);
+static void name_active_windows(List *activeWindows);
static PathTarget *make_window_input_target(PlannerInfo *root,
PathTarget *final_target,
List *activeWindows);
@@ -1539,7 +1540,11 @@ grouping_planner(PlannerInfo *root, double tuple_fraction,
*/
optimize_window_clauses(root, wflists);
+ /* Extract the list of windows actually in use. */
activeWindows = select_active_windows(root, wflists);
+
+ /* Make sure they all have names, for EXPLAIN's use. */
+ name_active_windows(activeWindows);
}
else
parse->hasWindowFuncs = false;
@@ -5914,6 +5919,52 @@ select_active_windows(PlannerInfo *root, WindowFuncLists *wflists)
return result;
}
+/*
+ * name_active_windows
+ * Ensure all active windows have unique names.
+ *
+ * The parser will have checked that user-assigned window names are unique
+ * within the Query. Here we assign made-up names to any unnamed
+ * WindowClauses for the benefit of EXPLAIN. (We don't want to do this
+ * at parse time, because it'd mess up decompilation of views.)
+ *
+ * activeWindows: result of select_active_windows
+ */
+static void
+name_active_windows(List *activeWindows)
+{
+ int next_n = 1;
+ char newname[32];
+ ListCell *lc;
+
+ foreach(lc, activeWindows)
+ {
+ WindowClause *wc = lfirst_node(WindowClause, lc);
+
+ /* Nothing to do if it has a name already. */
+ if (wc->name)
+ continue;
+
+ /* Identify a name not currently present in the list. */
+ for (;;)
+ {
+ ListCell *lc2;
+
+ snprintf(newname, sizeof(newname), "window%d", next_n++);
+ foreach(lc2, activeWindows)
+ {
+ WindowClause *wc2 = lfirst_node(WindowClause, lc2);
+
+ if (wc2->name && strcmp(wc2->name, newname) == 0)
+ break; /* matched */
+ }
+ if (lc2 == NULL)
+ break; /* reached the end with no match */
+ }
+ wc->name = pstrdup(newname);
+ }
+}
+
/*
* common_prefix_cmp
* QSort comparison function for WindowClauseSortData
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index d11a8a20eea..9e90acedb91 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -441,6 +441,9 @@ static void get_rule_orderby(List *orderList, List *targetList,
static void get_rule_windowclause(Query *query, deparse_context *context);
static void get_rule_windowspec(WindowClause *wc, List *targetList,
deparse_context *context);
+static void get_window_frame_options(int frameOptions,
+ Node *startOffset, Node *endOffset,
+ deparse_context *context);
static char *get_variable(Var *var, int levelsup, bool istoplevel,
deparse_context *context);
static void get_special_variable(Node *node, deparse_context *context,
@@ -6811,45 +6814,64 @@ get_rule_windowspec(WindowClause *wc, List *targetList,
{
if (needspace)
appendStringInfoChar(buf, ' ');
- if (wc->frameOptions & FRAMEOPTION_RANGE)
+ get_window_frame_options(wc->frameOptions,
+ wc->startOffset, wc->endOffset,
+ context);
+ }
+ appendStringInfoChar(buf, ')');
+}
+
+/*
+ * Append the description of a window's framing options to context->buf
+ */
+static void
+get_window_frame_options(int frameOptions,
+ Node *startOffset, Node *endOffset,
+ deparse_context *context)
+{
+ StringInfo buf = context->buf;
+
+ if (frameOptions & FRAMEOPTION_NONDEFAULT)
+ {
+ if (frameOptions & FRAMEOPTION_RANGE)
appendStringInfoString(buf, "RANGE ");
- else if (wc->frameOptions & FRAMEOPTION_ROWS)
+ else if (frameOptions & FRAMEOPTION_ROWS)
appendStringInfoString(buf, "ROWS ");
- else if (wc->frameOptions & FRAMEOPTION_GROUPS)
+ else if (frameOptions & FRAMEOPTION_GROUPS)
appendStringInfoString(buf, "GROUPS ");
else
Assert(false);
- if (wc->frameOptions & FRAMEOPTION_BETWEEN)
+ if (frameOptions & FRAMEOPTION_BETWEEN)
appendStringInfoString(buf, "BETWEEN ");
- if (wc->frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING)
+ if (frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING)
appendStringInfoString(buf, "UNBOUNDED PRECEDING ");
- else if (wc->frameOptions & FRAMEOPTION_START_CURRENT_ROW)
+ else if (frameOptions & FRAMEOPTION_START_CURRENT_ROW)
appendStringInfoString(buf, "CURRENT ROW ");
- else if (wc->frameOptions & FRAMEOPTION_START_OFFSET)
+ else if (frameOptions & FRAMEOPTION_START_OFFSET)
{
- get_rule_expr(wc->startOffset, context, false);
- if (wc->frameOptions & FRAMEOPTION_START_OFFSET_PRECEDING)
+ get_rule_expr(startOffset, context, false);
+ if (frameOptions & FRAMEOPTION_START_OFFSET_PRECEDING)
appendStringInfoString(buf, " PRECEDING ");
- else if (wc->frameOptions & FRAMEOPTION_START_OFFSET_FOLLOWING)
+ else if (frameOptions & FRAMEOPTION_START_OFFSET_FOLLOWING)
appendStringInfoString(buf, " FOLLOWING ");
else
Assert(false);
}
else
Assert(false);
- if (wc->frameOptions & FRAMEOPTION_BETWEEN)
+ if (frameOptions & FRAMEOPTION_BETWEEN)
{
appendStringInfoString(buf, "AND ");
- if (wc->frameOptions & FRAMEOPTION_END_UNBOUNDED_FOLLOWING)
+ if (frameOptions & FRAMEOPTION_END_UNBOUNDED_FOLLOWING)
appendStringInfoString(buf, "UNBOUNDED FOLLOWING ");
- else if (wc->frameOptions & FRAMEOPTION_END_CURRENT_ROW)
+ else if (frameOptions & FRAMEOPTION_END_CURRENT_ROW)
appendStringInfoString(buf, "CURRENT ROW ");
- else if (wc->frameOptions & FRAMEOPTION_END_OFFSET)
+ else if (frameOptions & FRAMEOPTION_END_OFFSET)
{
- get_rule_expr(wc->endOffset, context, false);
- if (wc->frameOptions & FRAMEOPTION_END_OFFSET_PRECEDING)
+ get_rule_expr(endOffset, context, false);
+ if (frameOptions & FRAMEOPTION_END_OFFSET_PRECEDING)
appendStringInfoString(buf, " PRECEDING ");
- else if (wc->frameOptions & FRAMEOPTION_END_OFFSET_FOLLOWING)
+ else if (frameOptions & FRAMEOPTION_END_OFFSET_FOLLOWING)
appendStringInfoString(buf, " FOLLOWING ");
else
Assert(false);
@@ -6857,16 +6879,46 @@ get_rule_windowspec(WindowClause *wc, List *targetList,
else
Assert(false);
}
- if (wc->frameOptions & FRAMEOPTION_EXCLUDE_CURRENT_ROW)
+ if (frameOptions & FRAMEOPTION_EXCLUDE_CURRENT_ROW)
appendStringInfoString(buf, "EXCLUDE CURRENT ROW ");
- else if (wc->frameOptions & FRAMEOPTION_EXCLUDE_GROUP)
+ else if (frameOptions & FRAMEOPTION_EXCLUDE_GROUP)
appendStringInfoString(buf, "EXCLUDE GROUP ");
- else if (wc->frameOptions & FRAMEOPTION_EXCLUDE_TIES)
+ else if (frameOptions & FRAMEOPTION_EXCLUDE_TIES)
appendStringInfoString(buf, "EXCLUDE TIES ");
/* we will now have a trailing space; remove it */
- buf->len--;
+ buf->data[--(buf->len)] = '\0';
}
- appendStringInfoChar(buf, ')');
+}
+
+/*
+ * Return the description of a window's framing options as a palloc'd string
+ */
+char *
+get_window_frame_options_for_explain(int frameOptions,
+ Node *startOffset, Node *endOffset,
+ List *dpcontext, bool forceprefix)
+{
+ StringInfoData buf;
+ deparse_context context;
+
+ initStringInfo(&buf);
+ context.buf = &buf;
+ context.namespaces = dpcontext;
+ context.resultDesc = NULL;
+ context.targetList = NIL;
+ context.windowClause = NIL;
+ context.varprefix = forceprefix;
+ context.prettyFlags = 0;
+ context.wrapColumn = WRAP_COLUMN_DEFAULT;
+ context.indentLevel = 0;
+ context.colNamesVisible = true;
+ context.inGroupBy = false;
+ context.varInOrderBy = false;
+ context.appendparents = NULL;
+
+ get_window_frame_options(frameOptions, startOffset, endOffset, &context);
+
+ return buf.data;
}
/* ----------
@@ -11030,30 +11082,50 @@ get_windowfunc_expr_helper(WindowFunc *wfunc, deparse_context *context,
appendStringInfoString(buf, ") OVER ");
- foreach(l, context->windowClause)
+ if (context->windowClause)
{
- WindowClause *wc = (WindowClause *) lfirst(l);
-
- if (wc->winref == wfunc->winref)
+ /* Query-decompilation case: search the windowClause list */
+ foreach(l, context->windowClause)
{
- if (wc->name)
- appendStringInfoString(buf, quote_identifier(wc->name));
- else
- get_rule_windowspec(wc, context->targetList, context);
- break;
+ WindowClause *wc = (WindowClause *) lfirst(l);
+
+ if (wc->winref == wfunc->winref)
+ {
+ if (wc->name)
+ appendStringInfoString(buf, quote_identifier(wc->name));
+ else
+ get_rule_windowspec(wc, context->targetList, context);
+ break;
+ }
}
- }
- if (l == NULL)
- {
- if (context->windowClause)
+ if (l == NULL)
elog(ERROR, "could not find window clause for winref %u",
wfunc->winref);
-
+ }
+ else
+ {
/*
- * In EXPLAIN, we don't have window context information available, so
- * we have to settle for this:
+ * In EXPLAIN, search the namespace stack for a matching WindowAgg
+ * node (probably it's always the first entry), and print winname.
*/
- appendStringInfoString(buf, "(?)");
+ foreach(l, context->namespaces)
+ {
+ deparse_namespace *dpns = (deparse_namespace *) lfirst(l);
+
+ if (dpns->plan && IsA(dpns->plan, WindowAgg))
+ {
+ WindowAgg *wagg = (WindowAgg *) dpns->plan;
+
+ if (wagg->winref == wfunc->winref)
+ {
+ appendStringInfoString(buf, quote_identifier(wagg->winname));
+ break;
+ }
+ }
+ }
+ if (l == NULL)
+ elog(ERROR, "could not find window clause for winref %u",
+ wfunc->winref);
}
}
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index bf1f25c0dba..22841211f48 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -1171,6 +1171,9 @@ typedef struct WindowAgg
{
Plan plan;
+ /* name of WindowClause implemented by this node */
+ char *winname;
+
/* ID referenced by window functions */
Index winref;
diff --git a/src/include/utils/ruleutils.h b/src/include/utils/ruleutils.h
index aa7a8a3800f..5f2ea2e4d0e 100644
--- a/src/include/utils/ruleutils.h
+++ b/src/include/utils/ruleutils.h
@@ -43,6 +43,11 @@ extern List *set_deparse_context_plan(List *dpcontext,
struct Plan *plan, List *ancestors);
extern List *select_rtable_names_for_explain(List *rtable,
Bitmapset *rels_used);
+extern char *get_window_frame_options_for_explain(int frameOptions,
+ Node *startOffset,
+ Node *endOffset,
+ List *dpcontext,
+ bool forceprefix);
extern char *generate_collation_name(Oid collid);
extern char *generate_opclass_name(Oid opclass);
extern char *get_range_partbound_string(List *bound_datums);
diff --git a/src/test/regress/expected/box.out b/src/test/regress/expected/box.out
index 8c9e9e39355..7a99dc70f70 100644
--- a/src/test/regress/expected/box.out
+++ b/src/test/regress/expected/box.out
@@ -594,12 +594,13 @@ SET enable_bitmapscan = OFF;
EXPLAIN (COSTS OFF)
SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id
FROM quad_box_tbl;
- QUERY PLAN
----------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------
WindowAgg
+ Window: window1 AS (ORDER BY (b <-> '(123,456)'::point) ROWS UNBOUNDED PRECEDING)
-> Index Scan using quad_box_tbl_idx on quad_box_tbl
Order By: (b <-> '(123,456)'::point)
-(3 rows)
+(4 rows)
CREATE TEMP TABLE quad_box_tbl_ord_idx1 AS
SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id
@@ -616,13 +617,14 @@ WHERE seq.id IS NULL OR idx.id IS NULL;
EXPLAIN (COSTS OFF)
SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id
FROM quad_box_tbl WHERE b <@ box '((200,300),(500,600))';
- QUERY PLAN
----------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------
WindowAgg
+ Window: window1 AS (ORDER BY (b <-> '(123,456)'::point) ROWS UNBOUNDED PRECEDING)
-> Index Scan using quad_box_tbl_idx on quad_box_tbl
Index Cond: (b <@ '(500,600),(200,300)'::box)
Order By: (b <-> '(123,456)'::point)
-(4 rows)
+(5 rows)
CREATE TEMP TABLE quad_box_tbl_ord_idx2 AS
SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id
diff --git a/src/test/regress/expected/create_index_spgist.out b/src/test/regress/expected/create_index_spgist.out
index 5c04df9c01b..1cd6aa764ef 100644
--- a/src/test/regress/expected/create_index_spgist.out
+++ b/src/test/regress/expected/create_index_spgist.out
@@ -329,12 +329,13 @@ SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)';
EXPLAIN (COSTS OFF)
SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM quad_point_tbl;
- QUERY PLAN
------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------
WindowAgg
+ Window: window1 AS (ORDER BY (p <-> '(0,0)'::point) ROWS UNBOUNDED PRECEDING)
-> Index Only Scan using sp_quad_ind on quad_point_tbl
Order By: (p <-> '(0,0)'::point)
-(3 rows)
+(4 rows)
CREATE TEMP TABLE quad_point_tbl_ord_idx1 AS
SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
@@ -349,13 +350,14 @@ WHERE seq.dist IS DISTINCT FROM idx.dist;
EXPLAIN (COSTS OFF)
SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
- QUERY PLAN
------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------
WindowAgg
+ Window: window1 AS (ORDER BY (p <-> '(0,0)'::point) ROWS UNBOUNDED PRECEDING)
-> Index Only Scan using sp_quad_ind on quad_point_tbl
Index Cond: (p <@ '(1000,1000),(200,200)'::box)
Order By: (p <-> '(0,0)'::point)
-(4 rows)
+(5 rows)
CREATE TEMP TABLE quad_point_tbl_ord_idx2 AS
SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
@@ -370,13 +372,14 @@ WHERE seq.dist IS DISTINCT FROM idx.dist;
EXPLAIN (COSTS OFF)
SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
FROM quad_point_tbl WHERE p IS NOT NULL;
- QUERY PLAN
------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------
WindowAgg
+ Window: window1 AS (ORDER BY (p <-> '(333,400)'::point) ROWS UNBOUNDED PRECEDING)
-> Index Only Scan using sp_quad_ind on quad_point_tbl
Index Cond: (p IS NOT NULL)
Order By: (p <-> '(333,400)'::point)
-(4 rows)
+(5 rows)
CREATE TEMP TABLE quad_point_tbl_ord_idx3 AS
SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
@@ -496,12 +499,13 @@ SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)';
EXPLAIN (COSTS OFF)
SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM kd_point_tbl;
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------
WindowAgg
+ Window: window1 AS (ORDER BY (p <-> '(0,0)'::point) ROWS UNBOUNDED PRECEDING)
-> Index Only Scan using sp_kd_ind on kd_point_tbl
Order By: (p <-> '(0,0)'::point)
-(3 rows)
+(4 rows)
CREATE TEMP TABLE kd_point_tbl_ord_idx1 AS
SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
@@ -516,13 +520,14 @@ WHERE seq.dist IS DISTINCT FROM idx.dist;
EXPLAIN (COSTS OFF)
SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)';
- QUERY PLAN
----------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------
WindowAgg
+ Window: window1 AS (ORDER BY (p <-> '(0,0)'::point) ROWS UNBOUNDED PRECEDING)
-> Index Only Scan using sp_kd_ind on kd_point_tbl
Index Cond: (p <@ '(1000,1000),(200,200)'::box)
Order By: (p <-> '(0,0)'::point)
-(4 rows)
+(5 rows)
CREATE TEMP TABLE kd_point_tbl_ord_idx2 AS
SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
@@ -537,13 +542,14 @@ WHERE seq.dist IS DISTINCT FROM idx.dist;
EXPLAIN (COSTS OFF)
SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
FROM kd_point_tbl WHERE p IS NOT NULL;
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------
WindowAgg
+ Window: window1 AS (ORDER BY (p <-> '(333,400)'::point) ROWS UNBOUNDED PRECEDING)
-> Index Only Scan using sp_kd_ind on kd_point_tbl
Index Cond: (p IS NOT NULL)
Order By: (p <-> '(333,400)'::point)
-(4 rows)
+(5 rows)
CREATE TEMP TABLE kd_point_tbl_ord_idx3 AS
SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out
index f5d60e50893..ae96e77a52d 100644
--- a/src/test/regress/expected/explain.out
+++ b/src/test/regress/expected/explain.out
@@ -742,11 +742,12 @@ select explain_filter('explain (analyze,buffers off,costs off) select sum(n) ove
explain_filter
----------------------------------------------------------------------------------
WindowAgg (actual time=N.N..N.N rows=N.N loops=N)
+ Window: window1 AS ()
Storage: Memory Maximum Storage: NkB
-> Function Scan on generate_series a (actual time=N.N..N.N rows=N.N loops=N)
Planning Time: N.N ms
Execution Time: N.N ms
-(5 rows)
+(6 rows)
-- Test tuplestore storage usage in Window aggregate (disk case)
set work_mem to 64;
@@ -754,17 +755,19 @@ select explain_filter('explain (analyze,buffers off,costs off) select sum(n) ove
explain_filter
----------------------------------------------------------------------------------
WindowAgg (actual time=N.N..N.N rows=N.N loops=N)
+ Window: window1 AS ()
Storage: Disk Maximum Storage: NkB
-> Function Scan on generate_series a (actual time=N.N..N.N rows=N.N loops=N)
Planning Time: N.N ms
Execution Time: N.N ms
-(5 rows)
+(6 rows)
-- Test tuplestore storage usage in Window aggregate (memory and disk case, final result is disk)
select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over(partition by m) from (SELECT n < 3
asm, n from generate_series(1,2000) a(n))');
explain_filter
----------------------------------------------------------------------------------------
WindowAgg (actual time=N.N..N.N rows=N.N loops=N)
+ Window: window1 AS (PARTITION BY ((a.n < N)))
Storage: Disk Maximum Storage: NkB
-> Sort (actual time=N.N..N.N rows=N.N loops=N)
Sort Key: ((a.n < N))
@@ -772,6 +775,6 @@ select explain_filter('explain (analyze,buffers off,costs off) select sum(n) ove
-> Function Scan on generate_series a (actual time=N.N..N.N rows=N.N loops=N)
Planning Time: N.N ms
Execution Time: N.N ms
-(8 rows)
+(9 rows)
reset work_mem;
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index 7ef05f45be7..cac999b7b8d 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -1427,6 +1427,7 @@ order by t1.a;
Sort
Sort Key: t1.a
-> WindowAgg
+ Window: window1 AS (PARTITION BY t2.a)
-> Sort
Sort Key: t2.a
-> Nested Loop Left Join
@@ -1434,7 +1435,7 @@ order by t1.a;
-> Seq Scan on gtest32 t1
-> Materialize
-> Seq Scan on gtest32 t2
-(10 rows)
+(11 rows)
select sum(t2.b) over (partition by t2.a),
sum(t2.c) over (partition by t2.a),
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index d7c9b44605d..5476ef9644c 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -1433,11 +1433,12 @@ select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum
explain (costs off)
select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum
from gstest2 group by cube (a,b) order by rsum, a, b;
- QUERY PLAN
----------------------------------------------
+ QUERY PLAN
+------------------------------------------------
Sort
- Sort Key: (sum((sum(c))) OVER (?)), a, b
+ Sort Key: (sum((sum(c))) OVER window1), a, b
-> WindowAgg
+ Window: window1 AS (ORDER BY a, b)
-> Sort
Sort Key: a, b
-> MixedAggregate
@@ -1446,7 +1447,7 @@ explain (costs off)
Hash Key: b
Group Key: ()
-> Seq Scan on gstest2
-(11 rows)
+(12 rows)
select a, b, sum(v.x)
from (values (1),(2)) v(x), gstest_data(v.x)
@@ -2427,9 +2428,10 @@ explain (costs off)
select a, b, row_number() over (order by a, b nulls first)
from (values (1, 1), (2, 2)) as t (a, b) where a = b
group by grouping sets((a, b), (a));
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
WindowAgg
+ Window: window1 AS (ORDER BY "*VALUES*".column1, "*VALUES*".column2 ROWS UNBOUNDED PRECEDING)
-> Sort
Sort Key: "*VALUES*".column1, "*VALUES*".column2 NULLS FIRST
-> HashAggregate
@@ -2437,7 +2439,7 @@ group by grouping sets((a, b), (a));
Hash Key: "*VALUES*".column1
-> Values Scan on "*VALUES*"
Filter: (column1 = column2)
-(8 rows)
+(9 rows)
select a, b, row_number() over (order by a, b nulls first)
from (values (1, 1), (2, 2)) as t (a, b) where a = b
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index d95d2395d48..cc22e84eada 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -4606,6 +4606,7 @@ select min(a) over (partition by a order by a) from part_abc where a >= stable_o
Append
-> Subquery Scan on "*SELECT* 1_1"
-> WindowAgg
+ Window: window1 AS (PARTITION BY part_abc.a ORDER BY part_abc.a)
-> Append
Subplans Removed: 1
-> Index Scan using part_abc_2_a_idx on part_abc_2 part_abc_1
@@ -4622,6 +4623,7 @@ select min(a) over (partition by a order by a) from part_abc where a >= stable_o
Filter: (d <= stable_one())
-> Subquery Scan on "*SELECT* 2"
-> WindowAgg
+ Window: window1 AS (PARTITION BY part_abc_5.a ORDER BY part_abc_5.a)
-> Append
Subplans Removed: 1
-> Index Scan using part_abc_2_a_idx on part_abc_2 part_abc_6
@@ -4636,7 +4638,7 @@ select min(a) over (partition by a order by a) from part_abc where a >= stable_o
-> Index Scan using part_abc_3_3_a_idx on part_abc_3_3 part_abc_9
Index Cond: (a >= (stable_one() + 1))
Filter: (d >= stable_one())
-(33 rows)
+(35 rows)
drop view part_abc_view;
drop table part_abc;
diff --git a/src/test/regress/expected/polygon.out b/src/test/regress/expected/polygon.out
index 7a9778e70fd..1a216f6d0a9 100644
--- a/src/test/regress/expected/polygon.out
+++ b/src/test/regress/expected/polygon.out
@@ -283,13 +283,14 @@ SET enable_bitmapscan = OFF;
EXPLAIN (COSTS OFF)
SELECT rank() OVER (ORDER BY p <-> point '123,456') n, p <-> point '123,456' dist, id
FROM quad_poly_tbl WHERE p <@ polygon '((300,300),(400,600),(600,500),(700,200))';
- QUERY PLAN
----------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------
WindowAgg
+ Window: window1 AS (ORDER BY (p <-> '(123,456)'::point) ROWS UNBOUNDED PRECEDING)
-> Index Scan using quad_poly_tbl_idx on quad_poly_tbl
Index Cond: (p <@ '((300,300),(400,600),(600,500),(700,200))'::polygon)
Order By: (p <-> '(123,456)'::point)
-(4 rows)
+(5 rows)
CREATE TEMP TABLE quad_poly_tbl_ord_idx2 AS
SELECT rank() OVER (ORDER BY p <-> point '123,456') n, p <-> point '123,456' dist, id
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 56509540f2a..91792b28e1e 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -1130,9 +1130,10 @@ explain (costs off, verbose)
Aggregate
Output: count(*)
-> Hash Right Semi Join
- Hash Cond: ((b.unique1 = a.unique1) AND ((row_number() OVER (?)) = a.two))
+ Hash Cond: ((b.unique1 = a.unique1) AND ((row_number() OVER window1) = a.two))
-> WindowAgg
- Output: b.unique1, row_number() OVER (?)
+ Output: b.unique1, row_number() OVER window1
+ Window: window1 AS (ROWS UNBOUNDED PRECEDING)
-> Gather
Output: b.unique1
Workers Planned: 4
@@ -1145,7 +1146,7 @@ explain (costs off, verbose)
Workers Planned: 4
-> Parallel Seq Scan on public.tenk1 a
Output: a.unique1, a.two
-(18 rows)
+(19 rows)
-- LIMIT/OFFSET within sub-selects can't be pushed to workers.
explain (costs off)
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
index 5e664fae084..a7279def4e0 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -1011,17 +1011,18 @@ FROM generate_series(1,5) i;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT JSON_OBJECTAGG(i: ('111' || i)::bytea FORMAT JSON WITH UNIQUE RETURNING text) OVER (PARTITION BY i % 2)
FROM generate_series(1,5) i;
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------
WindowAgg
- Output: JSON_OBJECTAGG(i : (('111'::text || (i)::text))::bytea FORMAT JSON WITH UNIQUE KEYS RETURNING text) OVER
(?),((i % 2))
+ Output: JSON_OBJECTAGG(i : (('111'::text || (i)::text))::bytea FORMAT JSON WITH UNIQUE KEYS RETURNING text) OVER
window1,((i % 2))
+ Window: window1 AS (PARTITION BY ((i.i % 2)))
-> Sort
Output: ((i % 2)), i
Sort Key: ((i.i % 2))
-> Function Scan on pg_catalog.generate_series i
Output: (i % 2), i
Function Call: generate_series(1, 5)
-(8 rows)
+(9 rows)
CREATE VIEW json_objectagg_view AS
SELECT JSON_OBJECTAGG(i: ('111' || i)::bytea FORMAT JSON WITH UNIQUE RETURNING text) FILTER (WHERE i > 3)
@@ -1047,17 +1048,18 @@ FROM generate_series(1,5) i;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT JSON_ARRAYAGG(('111' || i)::bytea FORMAT JSON NULL ON NULL RETURNING text) OVER (PARTITION BY i % 2)
FROM generate_series(1,5) i;
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------
WindowAgg
- Output: JSON_ARRAYAGG((('111'::text || (i)::text))::bytea FORMAT JSON NULL ON NULL RETURNING text) OVER (?), ((i %
2))
+ Output: JSON_ARRAYAGG((('111'::text || (i)::text))::bytea FORMAT JSON NULL ON NULL RETURNING text) OVER window1,
((i% 2))
+ Window: window1 AS (PARTITION BY ((i.i % 2)))
-> Sort
Output: ((i % 2)), i
Sort Key: ((i.i % 2))
-> Function Scan on pg_catalog.generate_series i
Output: (i % 2), i
Function Call: generate_series(1, 5)
-(8 rows)
+(9 rows)
CREATE VIEW json_arrayagg_view AS
SELECT JSON_ARRAYAGG(('111' || i)::bytea FORMAT JSON NULL ON NULL RETURNING text) FILTER (WHERE i > 3)
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 23d1463df22..8d35bc01055 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -652,10 +652,11 @@ select first_value(max(x)) over (), y
QUERY PLAN
---------------------------------------------
WindowAgg
+ Window: window1 AS ()
-> HashAggregate
Group Key: (tenk1.ten + tenk1.four)
-> Seq Scan on tenk1
-(4 rows)
+(5 rows)
-- window functions returning pass-by-ref values from different rows
select x, lag(x, 1) over (order by x), lead(x, 3) over (order by x)
@@ -3537,14 +3538,15 @@ explain (costs off)
select f1, sum(f1) over (partition by f1 order by f2
range between 1 preceding and 1 following)
from t1 where f1 = f2;
- QUERY PLAN
----------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------
WindowAgg
+ Window: window1 AS (PARTITION BY f1 ORDER BY f2 RANGE BETWEEN '1'::bigint PRECEDING AND '1'::bigint FOLLOWING)
-> Sort
Sort Key: f1
-> Seq Scan on t1
Filter: (f1 = f2)
-(5 rows)
+(6 rows)
select f1, sum(f1) over (partition by f1 order by f2
range between 1 preceding and 1 following)
@@ -3583,14 +3585,15 @@ explain (costs off)
select f1, sum(f1) over (partition by f1 order by f2
groups between 1 preceding and 1 following)
from t1 where f1 = f2;
- QUERY PLAN
----------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------
WindowAgg
+ Window: window1 AS (PARTITION BY f1 ORDER BY f2 GROUPS BETWEEN '1'::bigint PRECEDING AND '1'::bigint FOLLOWING)
-> Sort
Sort Key: f1
-> Seq Scan on t1
Filter: (f1 = f2)
-(5 rows)
+(6 rows)
select f1, sum(f1) over (partition by f1 order by f2
groups between 1 preceding and 1 following)
@@ -3711,13 +3714,14 @@ SELECT
cume_dist() OVER (PARTITION BY depname ORDER BY enroll_date RANGE BETWEEN
CURRENT ROW AND UNBOUNDED FOLLOWING) cd
FROM empsalary;
- QUERY PLAN
-----------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------
WindowAgg
+ Window: window1 AS (PARTITION BY depname ORDER BY enroll_date ROWS UNBOUNDED PRECEDING)
-> Sort
Sort Key: depname, enroll_date
-> Seq Scan on empsalary
-(4 rows)
+(5 rows)
-- Ensure WindowFuncs which cannot support their WindowClause's frameOptions
-- being changed are untouched
@@ -3731,18 +3735,20 @@ SELECT
count(*) OVER (PARTITION BY depname ORDER BY enroll_date RANGE BETWEEN
CURRENT ROW AND CURRENT ROW) cnt
FROM empsalary;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------
WindowAgg
- Output: empno, depname, (row_number() OVER (?)), (rank() OVER (?)), count(*) OVER (?), enroll_date
+ Output: empno, depname, (row_number() OVER window1), (rank() OVER window1), count(*) OVER window2, enroll_date
+ Window: window2 AS (PARTITION BY empsalary.depname ORDER BY empsalary.enroll_date RANGE BETWEEN CURRENT ROW AND
CURRENTROW)
-> WindowAgg
- Output: depname, enroll_date, empno, row_number() OVER (?), rank() OVER (?)
+ Output: depname, enroll_date, empno, row_number() OVER window1, rank() OVER window1
+ Window: window1 AS (PARTITION BY empsalary.depname ORDER BY empsalary.enroll_date ROWS UNBOUNDED PRECEDING)
-> Sort
Output: depname, enroll_date, empno
Sort Key: empsalary.depname, empsalary.enroll_date
-> Seq Scan on pg_temp.empsalary
Output: depname, enroll_date, empno
-(9 rows)
+(11 rows)
-- Ensure the above query gives us the expected results
SELECT
@@ -3777,16 +3783,18 @@ SELECT * FROM
min(salary) OVER (PARTITION BY depname || 'A', depname) depminsalary
FROM empsalary) emp
WHERE depname = 'sales';
- QUERY PLAN
---------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------
Subquery Scan on emp
-> WindowAgg
+ Window: window2 AS ()
-> WindowAgg
+ Window: window1 AS (PARTITION BY (((empsalary.depname)::text || 'A'::text)))
-> Sort
Sort Key: (((empsalary.depname)::text || 'A'::text))
-> Seq Scan on empsalary
Filter: ((depname)::text = 'sales'::text)
-(7 rows)
+(9 rows)
-- pushdown is unsafe because there's a PARTITION BY clause without depname:
EXPLAIN (COSTS OFF)
@@ -3796,18 +3804,20 @@ SELECT * FROM
min(salary) OVER (PARTITION BY depname) depminsalary
FROM empsalary) emp
WHERE depname = 'sales';
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------
Subquery Scan on emp
Filter: ((emp.depname)::text = 'sales'::text)
-> WindowAgg
+ Window: window2 AS (PARTITION BY empsalary.enroll_date)
-> Sort
Sort Key: empsalary.enroll_date
-> WindowAgg
+ Window: window1 AS (PARTITION BY empsalary.depname)
-> Sort
Sort Key: empsalary.depname
-> Seq Scan on empsalary
-(9 rows)
+(11 rows)
-- Test window function run conditions are properly pushed down into the
-- WindowAgg
@@ -3817,14 +3827,15 @@ SELECT * FROM
row_number() OVER (ORDER BY empno) rn
FROM empsalary) emp
WHERE rn < 3;
- QUERY PLAN
-----------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------
WindowAgg
- Run Condition: (row_number() OVER (?) < 3)
+ Window: window1 AS (ORDER BY empsalary.empno ROWS UNBOUNDED PRECEDING)
+ Run Condition: (row_number() OVER window1 < 3)
-> Sort
Sort Key: empsalary.empno
-> Seq Scan on empsalary
-(5 rows)
+(6 rows)
-- The following 3 statements should result the same result.
SELECT * FROM
@@ -3868,14 +3879,15 @@ SELECT * FROM
rank() OVER (ORDER BY salary DESC) r
FROM empsalary) emp
WHERE r <= 3;
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------
WindowAgg
- Run Condition: (rank() OVER (?) <= 3)
+ Window: window1 AS (ORDER BY empsalary.salary ROWS UNBOUNDED PRECEDING)
+ Run Condition: (rank() OVER window1 <= 3)
-> Sort
Sort Key: empsalary.salary DESC
-> Seq Scan on empsalary
-(5 rows)
+(6 rows)
SELECT * FROM
(SELECT empno,
@@ -3898,16 +3910,17 @@ SELECT * FROM
dense_rank() OVER (ORDER BY salary DESC) dr
FROM empsalary) emp
WHERE dr = 1;
- QUERY PLAN
------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------
Subquery Scan on emp
Filter: (emp.dr = 1)
-> WindowAgg
- Run Condition: (dense_rank() OVER (?) <= 1)
+ Window: window1 AS (ORDER BY empsalary.salary ROWS UNBOUNDED PRECEDING)
+ Run Condition: (dense_rank() OVER window1 <= 1)
-> Sort
Sort Key: empsalary.salary DESC
-> Seq Scan on empsalary
-(7 rows)
+(8 rows)
SELECT * FROM
(SELECT empno,
@@ -3928,14 +3941,15 @@ SELECT * FROM
count(*) OVER (ORDER BY salary DESC) c
FROM empsalary) emp
WHERE c <= 3;
- QUERY PLAN
--------------------------------------------
+ QUERY PLAN
+--------------------------------------------------
WindowAgg
- Run Condition: (count(*) OVER (?) <= 3)
+ Window: window1 AS (ORDER BY empsalary.salary)
+ Run Condition: (count(*) OVER window1 <= 3)
-> Sort
Sort Key: empsalary.salary DESC
-> Seq Scan on empsalary
-(5 rows)
+(6 rows)
SELECT * FROM
(SELECT empno,
@@ -3957,14 +3971,15 @@ SELECT * FROM
count(empno) OVER (ORDER BY salary DESC) c
FROM empsalary) emp
WHERE c <= 3;
- QUERY PLAN
----------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------
WindowAgg
- Run Condition: (count(empsalary.empno) OVER (?) <= 3)
+ Window: window1 AS (ORDER BY empsalary.salary)
+ Run Condition: (count(empsalary.empno) OVER window1 <= 3)
-> Sort
Sort Key: empsalary.salary DESC
-> Seq Scan on empsalary
-(5 rows)
+(6 rows)
SELECT * FROM
(SELECT empno,
@@ -3986,14 +4001,15 @@ SELECT * FROM
count(*) OVER (ORDER BY salary DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) c
FROM empsalary) emp
WHERE c >= 3;
- QUERY PLAN
--------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------
WindowAgg
- Run Condition: (count(*) OVER (?) >= 3)
+ Window: window1 AS (ORDER BY empsalary.salary ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ Run Condition: (count(*) OVER window1 >= 3)
-> Sort
Sort Key: empsalary.salary DESC
-> Seq Scan on empsalary
-(5 rows)
+(6 rows)
EXPLAIN (COSTS OFF)
SELECT * FROM
@@ -4002,12 +4018,13 @@ SELECT * FROM
count(*) OVER () c
FROM empsalary) emp
WHERE 11 <= c;
- QUERY PLAN
---------------------------------------------
+ QUERY PLAN
+------------------------------------------------
WindowAgg
- Run Condition: (11 <= count(*) OVER (?))
+ Window: window1 AS ()
+ Run Condition: (11 <= count(*) OVER window1)
-> Seq Scan on empsalary
-(3 rows)
+(4 rows)
EXPLAIN (COSTS OFF)
SELECT * FROM
@@ -4017,16 +4034,17 @@ SELECT * FROM
dense_rank() OVER (ORDER BY salary DESC) dr
FROM empsalary) emp
WHERE dr = 1;
- QUERY PLAN
------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------
Subquery Scan on emp
Filter: (emp.dr = 1)
-> WindowAgg
- Run Condition: (dense_rank() OVER (?) <= 1)
+ Window: window1 AS (ORDER BY empsalary.salary)
+ Run Condition: (dense_rank() OVER window1 <= 1)
-> Sort
Sort Key: empsalary.salary DESC
-> Seq Scan on empsalary
-(7 rows)
+(8 rows)
-- Ensure we get a run condition when there's a PARTITION BY clause
EXPLAIN (COSTS OFF)
@@ -4036,14 +4054,15 @@ SELECT * FROM
row_number() OVER (PARTITION BY depname ORDER BY empno) rn
FROM empsalary) emp
WHERE rn < 3;
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------
WindowAgg
- Run Condition: (row_number() OVER (?) < 3)
+ Window: window1 AS (PARTITION BY empsalary.depname ORDER BY empsalary.empno ROWS UNBOUNDED PRECEDING)
+ Run Condition: (row_number() OVER window1 < 3)
-> Sort
Sort Key: empsalary.depname, empsalary.empno
-> Seq Scan on empsalary
-(5 rows)
+(6 rows)
-- and ensure we get the correct results from the above plan
SELECT * FROM
@@ -4071,15 +4090,16 @@ SELECT empno, depname FROM
row_number() OVER (PARTITION BY depname ORDER BY empno) rn
FROM empsalary) emp
WHERE rn < 3;
- QUERY PLAN
-------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------
Subquery Scan on emp
-> WindowAgg
- Run Condition: (row_number() OVER (?) < 3)
+ Window: window1 AS (PARTITION BY empsalary.depname ORDER BY empsalary.empno ROWS UNBOUNDED PRECEDING)
+ Run Condition: (row_number() OVER window1 < 3)
-> Sort
Sort Key: empsalary.depname, empsalary.empno
-> Seq Scan on empsalary
-(6 rows)
+(7 rows)
-- likewise with count(empno) instead of row_number()
EXPLAIN (COSTS OFF)
@@ -4090,14 +4110,15 @@ SELECT * FROM
count(empno) OVER (PARTITION BY depname ORDER BY salary DESC) c
FROM empsalary) emp
WHERE c <= 3;
- QUERY PLAN
-------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------
WindowAgg
- Run Condition: (count(empsalary.empno) OVER (?) <= 3)
+ Window: window1 AS (PARTITION BY empsalary.depname ORDER BY empsalary.salary)
+ Run Condition: (count(empsalary.empno) OVER window1 <= 3)
-> Sort
Sort Key: empsalary.depname, empsalary.salary DESC
-> Seq Scan on empsalary
-(5 rows)
+(6 rows)
-- and again, check the results are what we expect.
SELECT * FROM
@@ -4129,12 +4150,13 @@ SELECT * FROM
count(empno) OVER () c
FROM empsalary) emp
WHERE c = 1;
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------
WindowAgg
- Run Condition: (count(empsalary.empno) OVER (?) = 1)
+ Window: window1 AS ()
+ Run Condition: (count(empsalary.empno) OVER window1 = 1)
-> Seq Scan on empsalary
-(3 rows)
+(4 rows)
-- Try another case with a WindowFunc with a byref return type
SELECT * FROM
@@ -4157,23 +4179,26 @@ SELECT * FROM
ntile(2) OVER (PARTITION BY depname) nt -- w2
FROM empsalary
) e WHERE rn <= 1 AND c1 <= 3 AND nt < 2;
- QUERY PLAN
------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------
Subquery Scan on e
-> WindowAgg
- Filter: (((row_number() OVER (?)) <= 1) AND ((ntile(2) OVER (?)) < 2))
- Run Condition: (count(empsalary.salary) OVER (?) <= 3)
+ Window: window3 AS (PARTITION BY (((empsalary.depname)::text || ''::text)))
+ Filter: (((row_number() OVER window2) <= 1) AND ((ntile(2) OVER window2) < 2))
+ Run Condition: (count(empsalary.salary) OVER window3 <= 3)
-> Sort
Sort Key: (((empsalary.depname)::text || ''::text))
-> WindowAgg
- Run Condition: ((row_number() OVER (?) <= 1) AND (ntile(2) OVER (?) < 2))
+ Window: window2 AS (PARTITION BY empsalary.depname)
+ Run Condition: ((row_number() OVER window2 <= 1) AND (ntile(2) OVER window2 < 2))
-> Sort
Sort Key: empsalary.depname
-> WindowAgg
+ Window: window1 AS (PARTITION BY ((''::text || (empsalary.depname)::text)))
-> Sort
Sort Key: ((''::text || (empsalary.depname)::text))
-> Seq Scan on empsalary
-(14 rows)
+(17 rows)
-- Ensure we correctly filter out all of the run conditions from each window
SELECT * FROM
@@ -4199,12 +4224,13 @@ SELECT 1 FROM
FROM empsalary e1 LEFT JOIN empsalary e2 ON TRUE
WHERE e1.empno = e2.empno) s
WHERE s.c = 1;
- QUERY PLAN
----------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------
Subquery Scan on s
Filter: (s.c = 1)
-> WindowAgg
- Run Condition: (ntile(e2.salary) OVER (?) <= 1)
+ Window: window1 AS (PARTITION BY e1.depname ROWS UNBOUNDED PRECEDING)
+ Run Condition: (ntile(e2.salary) OVER window1 <= 1)
-> Sort
Sort Key: e1.depname
-> Merge Join
@@ -4215,7 +4241,7 @@ WHERE s.c = 1;
-> Sort
Sort Key: e2.empno
-> Seq Scan on empsalary e2
-(14 rows)
+(15 rows)
-- Ensure the run condition optimization is used in cases where the WindowFunc
-- has a Var from another query level
@@ -4224,16 +4250,17 @@ SELECT 1 FROM
(SELECT ntile(s1.x) OVER () AS c
FROM (SELECT (SELECT 1) AS x) AS s1) s
WHERE s.c = 1;
- QUERY PLAN
------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Subquery Scan on s
Filter: (s.c = 1)
-> WindowAgg
- Run Condition: (ntile((InitPlan 1).col1) OVER (?) <= 1)
+ Window: window1 AS (ROWS UNBOUNDED PRECEDING)
+ Run Condition: (ntile((InitPlan 1).col1) OVER window1 <= 1)
InitPlan 1
-> Result
-> Result
-(7 rows)
+(8 rows)
-- Tests to ensure we don't push down the run condition when it's not valid to
-- do so.
@@ -4246,15 +4273,16 @@ SELECT * FROM
count(*) OVER (ORDER BY salary DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) c
FROM empsalary) emp
WHERE c <= 3;
- QUERY PLAN
------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------
Subquery Scan on emp
Filter: (emp.c <= 3)
-> WindowAgg
+ Window: window1 AS (ORDER BY empsalary.salary ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
-> Sort
Sort Key: empsalary.salary DESC
-> Seq Scan on empsalary
-(6 rows)
+(7 rows)
-- Ensure we don't push down when the window function's monotonic properties
-- don't match that of the clauses.
@@ -4265,15 +4293,16 @@ SELECT * FROM
count(*) OVER (ORDER BY salary) c
FROM empsalary) emp
WHERE 3 <= c;
- QUERY PLAN
-------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------
Subquery Scan on emp
Filter: (3 <= emp.c)
-> WindowAgg
+ Window: window1 AS (ORDER BY empsalary.salary)
-> Sort
Sort Key: empsalary.salary
-> Seq Scan on empsalary
-(6 rows)
+(7 rows)
-- Ensure we don't use a run condition when there's a volatile function in the
-- WindowFunc
@@ -4284,15 +4313,16 @@ SELECT * FROM
count(random()) OVER (ORDER BY empno DESC) c
FROM empsalary) emp
WHERE c = 1;
- QUERY PLAN
-----------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------
Subquery Scan on emp
Filter: (emp.c = 1)
-> WindowAgg
+ Window: window1 AS (ORDER BY empsalary.empno)
-> Sort
Sort Key: empsalary.empno DESC
-> Seq Scan on empsalary
-(6 rows)
+(7 rows)
-- Ensure we don't use a run condition when the WindowFunc contains subplans
EXPLAIN (COSTS OFF)
@@ -4302,17 +4332,18 @@ SELECT * FROM
count((SELECT 1)) OVER (ORDER BY empno DESC) c
FROM empsalary) emp
WHERE c = 1;
- QUERY PLAN
-----------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------
Subquery Scan on emp
Filter: (emp.c = 1)
-> WindowAgg
+ Window: window1 AS (ORDER BY empsalary.empno)
InitPlan 1
-> Result
-> Sort
Sort Key: empsalary.empno DESC
-> Seq Scan on empsalary
-(8 rows)
+(9 rows)
-- Test Sort node collapsing
EXPLAIN (COSTS OFF)
@@ -4322,16 +4353,18 @@ SELECT * FROM
min(salary) OVER (PARTITION BY depname, empno order by enroll_date) depminsalary
FROM empsalary) emp
WHERE depname = 'sales';
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------
Subquery Scan on emp
-> WindowAgg
+ Window: window2 AS (ORDER BY empsalary.empno)
-> WindowAgg
+ Window: window1 AS (PARTITION BY empsalary.empno ORDER BY empsalary.enroll_date)
-> Sort
Sort Key: empsalary.empno, empsalary.enroll_date
-> Seq Scan on empsalary
Filter: ((depname)::text = 'sales'::text)
-(7 rows)
+(9 rows)
-- Ensure that the evaluation order of the WindowAggs results in the WindowAgg
-- with the same sort order that's required by the ORDER BY is evaluated last.
@@ -4343,17 +4376,19 @@ SELECT empno,
min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary
FROM empsalary
ORDER BY depname, empno;
- QUERY PLAN
-----------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------
WindowAgg
+ Window: window2 AS (PARTITION BY depname ORDER BY empno)
-> Incremental Sort
Sort Key: depname, empno
Presorted Key: depname
-> WindowAgg
+ Window: window1 AS (PARTITION BY depname ORDER BY enroll_date)
-> Sort
Sort Key: depname, enroll_date
-> Seq Scan on empsalary
-(8 rows)
+(10 rows)
-- As above, but with an adjusted ORDER BY to ensure the above plan didn't
-- perform only 2 sorts by accident.
@@ -4365,17 +4400,19 @@ SELECT empno,
min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary
FROM empsalary
ORDER BY depname, enroll_date;
- QUERY PLAN
------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------
WindowAgg
+ Window: window2 AS (PARTITION BY depname ORDER BY enroll_date)
-> Incremental Sort
Sort Key: depname, enroll_date
Presorted Key: depname
-> WindowAgg
+ Window: window1 AS (PARTITION BY depname ORDER BY empno)
-> Sort
Sort Key: depname, empno
-> Seq Scan on empsalary
-(8 rows)
+(10 rows)
SET enable_hashagg TO off;
-- Ensure we don't get a sort for both DISTINCT and ORDER BY. We expect the
@@ -4389,21 +4426,23 @@ SELECT DISTINCT
min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary
FROM empsalary
ORDER BY depname, enroll_date;
- QUERY PLAN
------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------
Unique
-> Incremental Sort
- Sort Key: depname, enroll_date, empno, (sum(salary) OVER (?)), (min(salary) OVER (?))
+ Sort Key: depname, enroll_date, empno, (sum(salary) OVER window1), (min(salary) OVER window2)
Presorted Key: depname, enroll_date
-> WindowAgg
+ Window: window2 AS (PARTITION BY depname ORDER BY enroll_date)
-> Incremental Sort
Sort Key: depname, enroll_date
Presorted Key: depname
-> WindowAgg
+ Window: window1 AS (PARTITION BY depname ORDER BY empno)
-> Sort
Sort Key: depname, empno
-> Seq Scan on empsalary
-(12 rows)
+(14 rows)
-- As above but adjust the ORDER BY clause to help ensure the plan with the
-- minimum amount of sorting wasn't a fluke.
@@ -4416,21 +4455,23 @@ SELECT DISTINCT
min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary
FROM empsalary
ORDER BY depname, empno;
- QUERY PLAN
------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------
Unique
-> Incremental Sort
- Sort Key: depname, empno, enroll_date, (sum(salary) OVER (?)), (min(salary) OVER (?))
+ Sort Key: depname, empno, enroll_date, (sum(salary) OVER window2), (min(salary) OVER window1)
Presorted Key: depname, empno
-> WindowAgg
+ Window: window2 AS (PARTITION BY depname ORDER BY empno)
-> Incremental Sort
Sort Key: depname, empno
Presorted Key: depname
-> WindowAgg
+ Window: window1 AS (PARTITION BY depname ORDER BY enroll_date)
-> Sort
Sort Key: depname, enroll_date
-> Seq Scan on empsalary
-(12 rows)
+(14 rows)
RESET enable_hashagg;
-- Test Sort node reordering
@@ -4439,14 +4480,16 @@ SELECT
lead(1) OVER (PARTITION BY depname ORDER BY salary, enroll_date),
lag(1) OVER (PARTITION BY depname ORDER BY salary,enroll_date,empno)
FROM empsalary;
- QUERY PLAN
--------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------
WindowAgg
+ Window: window2 AS (PARTITION BY depname ORDER BY salary, enroll_date)
-> WindowAgg
+ Window: window1 AS (PARTITION BY depname ORDER BY salary, enroll_date, empno)
-> Sort
Sort Key: depname, salary, enroll_date, empno
-> Seq Scan on empsalary
-(5 rows)
+(7 rows)
-- Test incremental sorting
EXPLAIN (COSTS OFF)
@@ -4459,19 +4502,21 @@ SELECT * FROM
row_number() OVER (PARTITION BY depname ORDER BY enroll_date DESC) AS last_emp
FROM empsalary) emp
WHERE first_emp = 1 OR last_emp = 1;
- QUERY PLAN
------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on emp
Filter: ((emp.first_emp = 1) OR (emp.last_emp = 1))
-> WindowAgg
+ Window: window2 AS (PARTITION BY empsalary.depname ORDER BY empsalary.enroll_date ROWS UNBOUNDED PRECEDING)
-> Incremental Sort
Sort Key: empsalary.depname, empsalary.enroll_date
Presorted Key: empsalary.depname
-> WindowAgg
+ Window: window1 AS (PARTITION BY empsalary.depname ORDER BY empsalary.enroll_date ROWS UNBOUNDED
PRECEDING)
-> Sort
Sort Key: empsalary.depname, empsalary.enroll_date DESC
-> Seq Scan on empsalary
-(10 rows)
+(12 rows)
SELECT * FROM
(SELECT depname,
@@ -5299,11 +5344,12 @@ LIMIT 1;
--------------------------------------------------------------------------
Limit
-> WindowAgg
+ Window: window1 AS (ORDER BY t1.unique1)
-> Nested Loop
-> Index Only Scan using tenk1_unique1 on tenk1 t1
-> Index Only Scan using tenk1_thous_tenthous on tenk1 t2
Index Cond: (tenthous = t1.unique1)
-(6 rows)
+(7 rows)
-- Ensure we get a cheap total plan. Lack of ORDER BY in the WindowClause
-- means that all rows must be read from the join, so a cheap startup plan
@@ -5317,13 +5363,14 @@ LIMIT 1;
-------------------------------------------------------------------
Limit
-> WindowAgg
+ Window: window1 AS ()
-> Hash Join
Hash Cond: (t1.unique1 = t2.tenthous)
-> Index Only Scan using tenk1_unique1 on tenk1 t1
-> Hash
-> Seq Scan on tenk1 t2
Filter: (two = 1)
-(8 rows)
+(9 rows)
-- Ensure we get a cheap total plan. This time use UNBOUNDED FOLLOWING, which
-- needs to read all join rows to output the first WindowAgg row.
@@ -5331,17 +5378,18 @@ EXPLAIN (COSTS OFF)
SELECT COUNT(*) OVER (ORDER BY t1.unique1 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM tenk1 t1 INNER JOIN tenk1 t2 ON t1.unique1 = t2.tenthous
LIMIT 1;
- QUERY PLAN
---------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
Limit
-> WindowAgg
+ Window: window1 AS (ORDER BY t1.unique1 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
-> Merge Join
Merge Cond: (t1.unique1 = t2.tenthous)
-> Index Only Scan using tenk1_unique1 on tenk1 t1
-> Sort
Sort Key: t2.tenthous
-> Index Only Scan using tenk1_thous_tenthous on tenk1 t2
-(8 rows)
+(9 rows)
-- Ensure we get a cheap total plan. This time use 10000 FOLLOWING so we need
-- to read all join rows.
@@ -5349,17 +5397,18 @@ EXPLAIN (COSTS OFF)
SELECT COUNT(*) OVER (ORDER BY t1.unique1 ROWS BETWEEN UNBOUNDED PRECEDING AND 10000 FOLLOWING)
FROM tenk1 t1 INNER JOIN tenk1 t2 ON t1.unique1 = t2.tenthous
LIMIT 1;
- QUERY PLAN
---------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------
Limit
-> WindowAgg
+ Window: window1 AS (ORDER BY t1.unique1 ROWS BETWEEN UNBOUNDED PRECEDING AND '10000'::bigint FOLLOWING)
-> Merge Join
Merge Cond: (t1.unique1 = t2.tenthous)
-> Index Only Scan using tenk1_unique1 on tenk1 t1
-> Sort
Sort Key: t2.tenthous
-> Index Only Scan using tenk1_thous_tenthous on tenk1 t2
-(8 rows)
+(9 rows)
-- Tests for problems with failure to walk or mutate expressions
-- within window frame clauses.
@@ -5384,14 +5433,15 @@ AS $$
WINDOW w AS (ORDER BY s ROWS BETWEEN CURRENT ROW AND GROUP_SIZE FOLLOWING)
$$ LANGUAGE SQL STABLE;
EXPLAIN (costs off) SELECT * FROM pg_temp.f(2);
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------
Subquery Scan on f
-> WindowAgg
+ Window: w AS (ORDER BY s.s ROWS BETWEEN CURRENT ROW AND '2'::bigint FOLLOWING)
-> Sort
Sort Key: s.s
-> Function Scan on generate_series s
-(5 rows)
+(6 rows)
SELECT * FROM pg_temp.f(2);
f
On Mon, 10 Mar 2025 at 11:19, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> OK, here's v2 done like that. I do like this output better.
> I backed off the idea of putting the WindowClause as such
> into the plan, partly because I didn't feel like debugging
> the setrefs.c problem that David discovered upthread.
> This way does require a bit more code, but I think it's less
> likely to have bugs.
This output is much nicer. The patch looks good to me.
What are your thoughts on being a bit more brief with the naming and
just prefix with "w" instead of "window"? Looking at window.out, I see
that the EXPLAIN output does become quite a bit wider than before. I
favour the idea of saving a bit of space. There is an example in
src/sgml/advanced.sgml that has "OVER w", so it does not seem overly
strange to me to name them "w1", "w2", etc.
> * In passing, I editorialized on the order in which the Run Condition
> annotation comes out:
>
> case T_WindowAgg:
> + show_window_def(castNode(WindowAggState, planstate), ancestors, es);
> show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
> + show_upper_qual(((WindowAgg *) plan)->runConditionOrig,
> + "Run Condition", planstate, ancestors, es);
> if (plan->qual)
> show_instrumentation_count("Rows Removed by Filter", 1,
> planstate, es);
> - show_upper_qual(((WindowAgg *) plan)->runConditionOrig,
> - "Run Condition", planstate, ancestors, es);
> show_windowagg_info(castNode(WindowAggState, planstate), es);
> break;
>
> It seemed quite weird to me to have the Run Condition plan property
> come out in the middle of properties that only appear in EXPLAIN
> ANALYZE mode. Maybe there's a reason for this other than "people
> added new properties at the end", but I don't see it.
I did it that way because "Rows Removed by Filter" is a property of
"Filter", so it makes sense to me for those to be together. It doesn't
make sense to me to put something unrelated between them.
If you look at BitmapHeapScan output, this keeps the related outputs
together, i.e:
-> Parallel Bitmap Heap Scan on ab (cost=111.20..82787.64 rows=1
width=8) (actual time=172.498..172.499 rows=0.00 loops=3)
Recheck Cond: (a = 1)
Rows Removed by Index Recheck: 705225
Filter: (b = 3)
Rows Removed by Filter: 3333
What you're proposing seems equivalent to if we did it like:
-> Parallel Bitmap Heap Scan on ab (cost=111.20..82787.64 rows=1
width=8) (actual time=172.498..172.499 rows=0.00 loops=3)
Recheck Cond: (a = 1)
Filter: (b = 3)
Rows Removed by Index Recheck: 705225
Rows Removed by Filter: 3333
David
David Rowley <dgrowleyml@gmail.com> writes:
> What are your thoughts on being a bit more brief with the naming and
> just prefix with "w" instead of "window"? Looking at window.out, I see
> that the EXPLAIN output does become quite a bit wider than before. I
> favour the idea of saving a bit of space. There is an example in
> src/sgml/advanced.sgml that has "OVER w", so it does not seem overly
> strange to me to name them "w1", "w2", etc.
OK by me, any objections elsewhere?
>> * In passing, I editorialized on the order in which the Run Condition
>> annotation comes out:
> I did it that way because "Rows Removed by Filter" is a property of
> "Filter", so it makes sense to me for those to be together. It doesn't
> make sense to me to put something unrelated between them.
Hmm, OK. Do you think it could be sensible to put Run Condition
before Filter, then? On the same grounds of "keeping related
things together", it could be argued that Run Condition is
related to the Window property. Also, the Run Condition acts
before the Filter does, if I've got my head screwed on straight.
regards, tom lane
On Mon, 10 Mar 2025 at 14:13, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Hmm, OK. Do you think it could be sensible to put Run Condition > before Filter, then? On the same grounds of "keeping related > things together", it could be argued that Run Condition is > related to the Window property. Also, the Run Condition acts > before the Filter does, if I've got my head screwed on straight. Yes, directly after the "Window" property makes sense for the reason you stated. Thanks for thinking of that. David
On 2025-Mar-09, Tom Lane wrote: > David Rowley <dgrowleyml@gmail.com> writes: > > What are your thoughts on being a bit more brief with the naming and > > just prefix with "w" instead of "window"? Looking at window.out, I see > > that the EXPLAIN output does become quite a bit wider than before. I > > favour the idea of saving a bit of space. There is an example in > > src/sgml/advanced.sgml that has "OVER w", so it does not seem overly > > strange to me to name them "w1", "w2", etc. > > OK by me, any objections elsewhere? WFM. -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
=?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@alvh.no-ip.org> writes:
> On 2025-Mar-09, Tom Lane wrote:
>> David Rowley <dgrowleyml@gmail.com> writes:
>>> What are your thoughts on being a bit more brief with the naming and
>>> just prefix with "w" instead of "window"?
>> OK by me, any objections elsewhere?
> WFM.
Here's a hopefully-final v3 that makes the two changes discussed.
Now with a draft commit message, too.
I looked for documentation examples that needed updates, but there
don't seem to be any. Our documentation of EXPLAIN output is
mighty thin anyway. I don't want to try to improve that situation
as part of this patch.
regards, tom lane
From 4f30b4302c8398616d4d3d44f710ed437c59f6b8 Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Mon, 10 Mar 2025 12:09:43 -0400
Subject: [PATCH v3] Improve EXPLAIN's display of window functions.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Up to now we just punted on showing the window definitions used
in a plan, with window function calls represented as "OVER (?)".
To improve that, show the window definition implemented by each
WindowAgg plan node, and reference their window names in OVER.
For nameless window clauses generated by "OVER (...)", assign
unique names w1, w2, etc.
In passing, re-order the properties shown for a WindowAgg node
so that the Run Condition (if any) appears after the Window
property and before the Filter (if any). This seems more
sensible since the Run Condition is associated with the Window
and acts before the Filter.
Thanks to David G. Johnston and Álvaro Herrera for design
suggestions.
Author: Tom Lane <tgl@sss.pgh.pa.us>
Reviewed-by: David Rowley <dgrowleyml@gmail.com>
Discussion: https://postgr.es/m/144530.1741469955@sss.pgh.pa.us
---
.../postgres_fdw/expected/postgres_fdw.out | 25 +-
src/backend/commands/explain.c | 117 ++++++-
src/backend/optimizer/plan/createplan.c | 39 +--
src/backend/optimizer/plan/planner.c | 51 +++
src/backend/utils/adt/ruleutils.c | 150 ++++++---
src/include/nodes/plannodes.h | 3 +
src/include/utils/ruleutils.h | 5 +
src/test/regress/expected/box.out | 14 +-
.../regress/expected/create_index_spgist.out | 42 ++-
src/test/regress/expected/explain.out | 22 +-
.../regress/expected/generated_virtual.out | 3 +-
src/test/regress/expected/groupingsets.out | 12 +-
src/test/regress/expected/partition_prune.out | 4 +-
src/test/regress/expected/polygon.out | 3 +-
src/test/regress/expected/select_parallel.out | 7 +-
src/test/regress/expected/sqljson.out | 18 +-
src/test/regress/expected/window.out | 308 ++++++++++--------
src/test/regress/sql/explain.sql | 4 +
18 files changed, 574 insertions(+), 253 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index c68119030ab..bb4ed3059c4 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -3968,10 +3968,11 @@ select c2, sum(c2), count(c2) over (partition by c2%2) from ft2 where c2 < 10 gr
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Sort
- Output: c2, (sum(c2)), (count(c2) OVER (?)), ((c2 % 2))
+ Output: c2, (sum(c2)), (count(c2) OVER w1), ((c2 % 2))
Sort Key: ft2.c2
-> WindowAgg
- Output: c2, (sum(c2)), count(c2) OVER (?), ((c2 % 2))
+ Output: c2, (sum(c2)), count(c2) OVER w1, ((c2 % 2))
+ Window: w1 AS (PARTITION BY ((ft2.c2 % 2)))
-> Sort
Output: c2, ((c2 % 2)), (sum(c2))
Sort Key: ((ft2.c2 % 2))
@@ -3979,7 +3980,7 @@ select c2, sum(c2), count(c2) over (partition by c2%2) from ft2 where c2 < 10 gr
Output: c2, ((c2 % 2)), (sum(c2))
Relations: Aggregate on (public.ft2)
Remote SQL: SELECT c2, (c2 % 2), sum(c2) FROM "S 1"."T 1" WHERE ((c2 < 10)) GROUP BY 1
-(12 rows)
+(13 rows)
select c2, sum(c2), count(c2) over (partition by c2%2) from ft2 where c2 < 10 group by c2 order by 1;
c2 | sum | count
@@ -4001,10 +4002,11 @@ select c2, array_agg(c2) over (partition by c2%2 order by c2 desc) from ft1 wher
QUERY PLAN
---------------------------------------------------------------------------------------------------
Sort
- Output: c2, (array_agg(c2) OVER (?)), ((c2 % 2))
+ Output: c2, (array_agg(c2) OVER w1), ((c2 % 2))
Sort Key: ft1.c2
-> WindowAgg
- Output: c2, array_agg(c2) OVER (?), ((c2 % 2))
+ Output: c2, array_agg(c2) OVER w1, ((c2 % 2))
+ Window: w1 AS (PARTITION BY ((ft1.c2 % 2)) ORDER BY ft1.c2)
-> Sort
Output: c2, ((c2 % 2))
Sort Key: ((ft1.c2 % 2)), ft1.c2 DESC
@@ -4012,7 +4014,7 @@ select c2, array_agg(c2) over (partition by c2%2 order by c2 desc) from ft1 wher
Output: c2, ((c2 % 2))
Relations: Aggregate on (public.ft1)
Remote SQL: SELECT c2, (c2 % 2) FROM "S 1"."T 1" WHERE ((c2 < 10)) GROUP BY 1
-(12 rows)
+(13 rows)
select c2, array_agg(c2) over (partition by c2%2 order by c2 desc) from ft1 where c2 < 10 group by c2 order by 1;
c2 | array_agg
@@ -4031,13 +4033,14 @@ select c2, array_agg(c2) over (partition by c2%2 order by c2 desc) from ft1 wher
explain (verbose, costs off)
select c2, array_agg(c2) over (partition by c2%2 order by c2 range between current row and unbounded following) from
ft1where c2 < 10 group by c2 order by 1;
- QUERY PLAN
----------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------
Sort
- Output: c2, (array_agg(c2) OVER (?)), ((c2 % 2))
+ Output: c2, (array_agg(c2) OVER w1), ((c2 % 2))
Sort Key: ft1.c2
-> WindowAgg
- Output: c2, array_agg(c2) OVER (?), ((c2 % 2))
+ Output: c2, array_agg(c2) OVER w1, ((c2 % 2))
+ Window: w1 AS (PARTITION BY ((ft1.c2 % 2)) ORDER BY ft1.c2 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
-> Sort
Output: c2, ((c2 % 2))
Sort Key: ((ft1.c2 % 2)), ft1.c2
@@ -4045,7 +4048,7 @@ select c2, array_agg(c2) over (partition by c2%2 order by c2 range between curre
Output: c2, ((c2 % 2))
Relations: Aggregate on (public.ft1)
Remote SQL: SELECT c2, (c2 % 2) FROM "S 1"."T 1" WHERE ((c2 < 10)) GROUP BY 1
-(12 rows)
+(13 rows)
select c2, array_agg(c2) over (partition by c2%2 order by c2 range between current row and unbounded following) from
ft1where c2 < 10 group by c2 order by 1;
c2 | array_agg
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index d8a7232cedb..5701786ae08 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -107,6 +107,11 @@ static void show_sort_group_keys(PlanState *planstate, const char *qlabel,
List *ancestors, ExplainState *es);
static void show_sortorder_options(StringInfo buf, Node *sortexpr,
Oid sortOperator, Oid collation, bool nullsFirst);
+static void show_window_def(WindowAggState *planstate,
+ List *ancestors, ExplainState *es);
+static void show_window_keys(StringInfo buf, PlanState *planstate,
+ int nkeys, AttrNumber *keycols,
+ List *ancestors, ExplainState *es);
static void show_storage_info(char *maxStorageType, int64 maxSpaceUsed,
ExplainState *es);
static void show_tablesample(TableSampleClause *tsc, PlanState *planstate,
@@ -2329,12 +2334,13 @@ ExplainNode(PlanState *planstate, List *ancestors,
planstate, es);
break;
case T_WindowAgg:
+ show_window_def(castNode(WindowAggState, planstate), ancestors, es);
+ show_upper_qual(((WindowAgg *) plan)->runConditionOrig,
+ "Run Condition", planstate, ancestors, es);
show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
if (plan->qual)
show_instrumentation_count("Rows Removed by Filter", 1,
planstate, es);
- show_upper_qual(((WindowAgg *) plan)->runConditionOrig,
- "Run Condition", planstate, ancestors, es);
show_windowagg_info(castNode(WindowAggState, planstate), es);
break;
case T_Group:
@@ -3003,6 +3009,113 @@ show_sortorder_options(StringInfo buf, Node *sortexpr,
}
}
+/*
+ * Show the window definition for a WindowAgg node.
+ */
+static void
+show_window_def(WindowAggState *planstate, List *ancestors, ExplainState *es)
+{
+ WindowAgg *wagg = (WindowAgg *) planstate->ss.ps.plan;
+ StringInfoData wbuf;
+ bool needspace = false;
+
+ initStringInfo(&wbuf);
+ appendStringInfo(&wbuf, "%s AS (", quote_identifier(wagg->winname));
+
+ /* The key columns refer to the tlist of the child plan */
+ ancestors = lcons(wagg, ancestors);
+ if (wagg->partNumCols > 0)
+ {
+ appendStringInfoString(&wbuf, "PARTITION BY ");
+ show_window_keys(&wbuf, outerPlanState(planstate),
+ wagg->partNumCols, wagg->partColIdx,
+ ancestors, es);
+ needspace = true;
+ }
+ if (wagg->ordNumCols > 0)
+ {
+ if (needspace)
+ appendStringInfoChar(&wbuf, ' ');
+ appendStringInfoString(&wbuf, "ORDER BY ");
+ show_window_keys(&wbuf, outerPlanState(planstate),
+ wagg->ordNumCols, wagg->ordColIdx,
+ ancestors, es);
+ needspace = true;
+ }
+ ancestors = list_delete_first(ancestors);
+ if (wagg->frameOptions & FRAMEOPTION_NONDEFAULT)
+ {
+ List *context;
+ bool useprefix;
+ char *framestr;
+
+ /* Set up deparsing context for possible frame expressions */
+ context = set_deparse_context_plan(es->deparse_cxt,
+ (Plan *) wagg,
+ ancestors);
+ useprefix = (es->rtable_size > 1 || es->verbose);
+ framestr = get_window_frame_options_for_explain(wagg->frameOptions,
+ wagg->startOffset,
+ wagg->endOffset,
+ context,
+ useprefix);
+ if (needspace)
+ appendStringInfoChar(&wbuf, ' ');
+ appendStringInfoString(&wbuf, framestr);
+ pfree(framestr);
+ }
+ appendStringInfoChar(&wbuf, ')');
+ ExplainPropertyText("Window", wbuf.data, es);
+ pfree(wbuf.data);
+}
+
+/*
+ * Append the keys of a window's PARTITION BY or ORDER BY clause to buf.
+ * We can't use show_sort_group_keys for this because that's too opinionated
+ * about how the result will be displayed.
+ * Note that the "planstate" node should be the WindowAgg's child.
+ */
+static void
+show_window_keys(StringInfo buf, PlanState *planstate,
+ int nkeys, AttrNumber *keycols,
+ List *ancestors, ExplainState *es)
+{
+ Plan *plan = planstate->plan;
+ List *context;
+ bool useprefix;
+
+ /* Set up deparsing context */
+ context = set_deparse_context_plan(es->deparse_cxt,
+ plan,
+ ancestors);
+ useprefix = (es->rtable_size > 1 || es->verbose);
+
+ for (int keyno = 0; keyno < nkeys; keyno++)
+ {
+ /* find key expression in tlist */
+ AttrNumber keyresno = keycols[keyno];
+ TargetEntry *target = get_tle_by_resno(plan->targetlist,
+ keyresno);
+ char *exprstr;
+
+ if (!target)
+ elog(ERROR, "no tlist entry for key %d", keyresno);
+ /* Deparse the expression, showing any top-level cast */
+ exprstr = deparse_expression((Node *) target->expr, context,
+ useprefix, true);
+ if (keyno > 0)
+ appendStringInfoString(buf, ", ");
+ appendStringInfoString(buf, exprstr);
+ pfree(exprstr);
+
+ /*
+ * We don't attempt to provide sort order information because
+ * WindowAgg carries equality operators not comparison operators;
+ * compare show_agg_keys.
+ */
+ }
+}
+
/*
* Show information on storage method and maximum memory/disk space used.
*/
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 816a2b2a576..75e2b0b9036 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -285,12 +285,9 @@ static Memoize *make_memoize(Plan *lefttree, Oid *hashoperators,
Oid *collations, List *param_exprs,
bool singlerow, bool binary_mode,
uint32 est_entries, Bitmapset *keyparamids);
-static WindowAgg *make_windowagg(List *tlist, Index winref,
+static WindowAgg *make_windowagg(List *tlist, WindowClause *wc,
int partNumCols, AttrNumber *partColIdx, Oid *partOperators, Oid *partCollations,
int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, Oid *ordCollations,
- int frameOptions, Node *startOffset, Node *endOffset,
- Oid startInRangeFunc, Oid endInRangeFunc,
- Oid inRangeColl, bool inRangeAsc, bool inRangeNullsFirst,
List *runCondition, List *qual, bool topWindow,
Plan *lefttree);
static Group *make_group(List *tlist, List *qual, int numGroupCols,
@@ -2683,7 +2680,7 @@ create_windowagg_plan(PlannerInfo *root, WindowAggPath *best_path)
/* And finally we can make the WindowAgg node */
plan = make_windowagg(tlist,
- wc->winref,
+ wc,
partNumCols,
partColIdx,
partOperators,
@@ -2692,14 +2689,6 @@ create_windowagg_plan(PlannerInfo *root, WindowAggPath *best_path)
ordColIdx,
ordOperators,
ordCollations,
- wc->frameOptions,
- wc->startOffset,
- wc->endOffset,
- wc->startInRangeFunc,
- wc->endInRangeFunc,
- wc->inRangeColl,
- wc->inRangeAsc,
- wc->inRangeNullsFirst,
best_path->runCondition,
best_path->qual,
best_path->topwindow,
@@ -6704,18 +6693,16 @@ make_agg(List *tlist, List *qual,
}
static WindowAgg *
-make_windowagg(List *tlist, Index winref,
+make_windowagg(List *tlist, WindowClause *wc,
int partNumCols, AttrNumber *partColIdx, Oid *partOperators, Oid *partCollations,
int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, Oid *ordCollations,
- int frameOptions, Node *startOffset, Node *endOffset,
- Oid startInRangeFunc, Oid endInRangeFunc,
- Oid inRangeColl, bool inRangeAsc, bool inRangeNullsFirst,
List *runCondition, List *qual, bool topWindow, Plan *lefttree)
{
WindowAgg *node = makeNode(WindowAgg);
Plan *plan = &node->plan;
- node->winref = winref;
+ node->winname = wc->name;
+ node->winref = wc->winref;
node->partNumCols = partNumCols;
node->partColIdx = partColIdx;
node->partOperators = partOperators;
@@ -6724,17 +6711,17 @@ make_windowagg(List *tlist, Index winref,
node->ordColIdx = ordColIdx;
node->ordOperators = ordOperators;
node->ordCollations = ordCollations;
- node->frameOptions = frameOptions;
- node->startOffset = startOffset;
- node->endOffset = endOffset;
+ node->frameOptions = wc->frameOptions;
+ node->startOffset = wc->startOffset;
+ node->endOffset = wc->endOffset;
node->runCondition = runCondition;
/* a duplicate of the above for EXPLAIN */
node->runConditionOrig = runCondition;
- node->startInRangeFunc = startInRangeFunc;
- node->endInRangeFunc = endInRangeFunc;
- node->inRangeColl = inRangeColl;
- node->inRangeAsc = inRangeAsc;
- node->inRangeNullsFirst = inRangeNullsFirst;
+ node->startInRangeFunc = wc->startInRangeFunc;
+ node->endInRangeFunc = wc->endInRangeFunc;
+ node->inRangeColl = wc->inRangeColl;
+ node->inRangeAsc = wc->inRangeAsc;
+ node->inRangeNullsFirst = wc->inRangeNullsFirst;
node->topWindow = topWindow;
plan->targetlist = tlist;
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 014e80c30e6..36cabd0e74b 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -214,6 +214,7 @@ static List *postprocess_setop_tlist(List *new_tlist, List *orig_tlist);
static void optimize_window_clauses(PlannerInfo *root,
WindowFuncLists *wflists);
static List *select_active_windows(PlannerInfo *root, WindowFuncLists *wflists);
+static void name_active_windows(List *activeWindows);
static PathTarget *make_window_input_target(PlannerInfo *root,
PathTarget *final_target,
List *activeWindows);
@@ -1539,7 +1540,11 @@ grouping_planner(PlannerInfo *root, double tuple_fraction,
*/
optimize_window_clauses(root, wflists);
+ /* Extract the list of windows actually in use. */
activeWindows = select_active_windows(root, wflists);
+
+ /* Make sure they all have names, for EXPLAIN's use. */
+ name_active_windows(activeWindows);
}
else
parse->hasWindowFuncs = false;
@@ -5914,6 +5919,52 @@ select_active_windows(PlannerInfo *root, WindowFuncLists *wflists)
return result;
}
+/*
+ * name_active_windows
+ * Ensure all active windows have unique names.
+ *
+ * The parser will have checked that user-assigned window names are unique
+ * within the Query. Here we assign made-up names to any unnamed
+ * WindowClauses for the benefit of EXPLAIN. (We don't want to do this
+ * at parse time, because it'd mess up decompilation of views.)
+ *
+ * activeWindows: result of select_active_windows
+ */
+static void
+name_active_windows(List *activeWindows)
+{
+ int next_n = 1;
+ char newname[32];
+ ListCell *lc;
+
+ foreach(lc, activeWindows)
+ {
+ WindowClause *wc = lfirst_node(WindowClause, lc);
+
+ /* Nothing to do if it has a name already. */
+ if (wc->name)
+ continue;
+
+ /* Select a name not currently present in the list. */
+ for (;;)
+ {
+ ListCell *lc2;
+
+ snprintf(newname, sizeof(newname), "w%d", next_n++);
+ foreach(lc2, activeWindows)
+ {
+ WindowClause *wc2 = lfirst_node(WindowClause, lc2);
+
+ if (wc2->name && strcmp(wc2->name, newname) == 0)
+ break; /* matched */
+ }
+ if (lc2 == NULL)
+ break; /* reached the end with no match */
+ }
+ wc->name = pstrdup(newname);
+ }
+}
+
/*
* common_prefix_cmp
* QSort comparison function for WindowClauseSortData
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index d11a8a20eea..9e90acedb91 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -441,6 +441,9 @@ static void get_rule_orderby(List *orderList, List *targetList,
static void get_rule_windowclause(Query *query, deparse_context *context);
static void get_rule_windowspec(WindowClause *wc, List *targetList,
deparse_context *context);
+static void get_window_frame_options(int frameOptions,
+ Node *startOffset, Node *endOffset,
+ deparse_context *context);
static char *get_variable(Var *var, int levelsup, bool istoplevel,
deparse_context *context);
static void get_special_variable(Node *node, deparse_context *context,
@@ -6811,45 +6814,64 @@ get_rule_windowspec(WindowClause *wc, List *targetList,
{
if (needspace)
appendStringInfoChar(buf, ' ');
- if (wc->frameOptions & FRAMEOPTION_RANGE)
+ get_window_frame_options(wc->frameOptions,
+ wc->startOffset, wc->endOffset,
+ context);
+ }
+ appendStringInfoChar(buf, ')');
+}
+
+/*
+ * Append the description of a window's framing options to context->buf
+ */
+static void
+get_window_frame_options(int frameOptions,
+ Node *startOffset, Node *endOffset,
+ deparse_context *context)
+{
+ StringInfo buf = context->buf;
+
+ if (frameOptions & FRAMEOPTION_NONDEFAULT)
+ {
+ if (frameOptions & FRAMEOPTION_RANGE)
appendStringInfoString(buf, "RANGE ");
- else if (wc->frameOptions & FRAMEOPTION_ROWS)
+ else if (frameOptions & FRAMEOPTION_ROWS)
appendStringInfoString(buf, "ROWS ");
- else if (wc->frameOptions & FRAMEOPTION_GROUPS)
+ else if (frameOptions & FRAMEOPTION_GROUPS)
appendStringInfoString(buf, "GROUPS ");
else
Assert(false);
- if (wc->frameOptions & FRAMEOPTION_BETWEEN)
+ if (frameOptions & FRAMEOPTION_BETWEEN)
appendStringInfoString(buf, "BETWEEN ");
- if (wc->frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING)
+ if (frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING)
appendStringInfoString(buf, "UNBOUNDED PRECEDING ");
- else if (wc->frameOptions & FRAMEOPTION_START_CURRENT_ROW)
+ else if (frameOptions & FRAMEOPTION_START_CURRENT_ROW)
appendStringInfoString(buf, "CURRENT ROW ");
- else if (wc->frameOptions & FRAMEOPTION_START_OFFSET)
+ else if (frameOptions & FRAMEOPTION_START_OFFSET)
{
- get_rule_expr(wc->startOffset, context, false);
- if (wc->frameOptions & FRAMEOPTION_START_OFFSET_PRECEDING)
+ get_rule_expr(startOffset, context, false);
+ if (frameOptions & FRAMEOPTION_START_OFFSET_PRECEDING)
appendStringInfoString(buf, " PRECEDING ");
- else if (wc->frameOptions & FRAMEOPTION_START_OFFSET_FOLLOWING)
+ else if (frameOptions & FRAMEOPTION_START_OFFSET_FOLLOWING)
appendStringInfoString(buf, " FOLLOWING ");
else
Assert(false);
}
else
Assert(false);
- if (wc->frameOptions & FRAMEOPTION_BETWEEN)
+ if (frameOptions & FRAMEOPTION_BETWEEN)
{
appendStringInfoString(buf, "AND ");
- if (wc->frameOptions & FRAMEOPTION_END_UNBOUNDED_FOLLOWING)
+ if (frameOptions & FRAMEOPTION_END_UNBOUNDED_FOLLOWING)
appendStringInfoString(buf, "UNBOUNDED FOLLOWING ");
- else if (wc->frameOptions & FRAMEOPTION_END_CURRENT_ROW)
+ else if (frameOptions & FRAMEOPTION_END_CURRENT_ROW)
appendStringInfoString(buf, "CURRENT ROW ");
- else if (wc->frameOptions & FRAMEOPTION_END_OFFSET)
+ else if (frameOptions & FRAMEOPTION_END_OFFSET)
{
- get_rule_expr(wc->endOffset, context, false);
- if (wc->frameOptions & FRAMEOPTION_END_OFFSET_PRECEDING)
+ get_rule_expr(endOffset, context, false);
+ if (frameOptions & FRAMEOPTION_END_OFFSET_PRECEDING)
appendStringInfoString(buf, " PRECEDING ");
- else if (wc->frameOptions & FRAMEOPTION_END_OFFSET_FOLLOWING)
+ else if (frameOptions & FRAMEOPTION_END_OFFSET_FOLLOWING)
appendStringInfoString(buf, " FOLLOWING ");
else
Assert(false);
@@ -6857,16 +6879,46 @@ get_rule_windowspec(WindowClause *wc, List *targetList,
else
Assert(false);
}
- if (wc->frameOptions & FRAMEOPTION_EXCLUDE_CURRENT_ROW)
+ if (frameOptions & FRAMEOPTION_EXCLUDE_CURRENT_ROW)
appendStringInfoString(buf, "EXCLUDE CURRENT ROW ");
- else if (wc->frameOptions & FRAMEOPTION_EXCLUDE_GROUP)
+ else if (frameOptions & FRAMEOPTION_EXCLUDE_GROUP)
appendStringInfoString(buf, "EXCLUDE GROUP ");
- else if (wc->frameOptions & FRAMEOPTION_EXCLUDE_TIES)
+ else if (frameOptions & FRAMEOPTION_EXCLUDE_TIES)
appendStringInfoString(buf, "EXCLUDE TIES ");
/* we will now have a trailing space; remove it */
- buf->len--;
+ buf->data[--(buf->len)] = '\0';
}
- appendStringInfoChar(buf, ')');
+}
+
+/*
+ * Return the description of a window's framing options as a palloc'd string
+ */
+char *
+get_window_frame_options_for_explain(int frameOptions,
+ Node *startOffset, Node *endOffset,
+ List *dpcontext, bool forceprefix)
+{
+ StringInfoData buf;
+ deparse_context context;
+
+ initStringInfo(&buf);
+ context.buf = &buf;
+ context.namespaces = dpcontext;
+ context.resultDesc = NULL;
+ context.targetList = NIL;
+ context.windowClause = NIL;
+ context.varprefix = forceprefix;
+ context.prettyFlags = 0;
+ context.wrapColumn = WRAP_COLUMN_DEFAULT;
+ context.indentLevel = 0;
+ context.colNamesVisible = true;
+ context.inGroupBy = false;
+ context.varInOrderBy = false;
+ context.appendparents = NULL;
+
+ get_window_frame_options(frameOptions, startOffset, endOffset, &context);
+
+ return buf.data;
}
/* ----------
@@ -11030,30 +11082,50 @@ get_windowfunc_expr_helper(WindowFunc *wfunc, deparse_context *context,
appendStringInfoString(buf, ") OVER ");
- foreach(l, context->windowClause)
+ if (context->windowClause)
{
- WindowClause *wc = (WindowClause *) lfirst(l);
-
- if (wc->winref == wfunc->winref)
+ /* Query-decompilation case: search the windowClause list */
+ foreach(l, context->windowClause)
{
- if (wc->name)
- appendStringInfoString(buf, quote_identifier(wc->name));
- else
- get_rule_windowspec(wc, context->targetList, context);
- break;
+ WindowClause *wc = (WindowClause *) lfirst(l);
+
+ if (wc->winref == wfunc->winref)
+ {
+ if (wc->name)
+ appendStringInfoString(buf, quote_identifier(wc->name));
+ else
+ get_rule_windowspec(wc, context->targetList, context);
+ break;
+ }
}
- }
- if (l == NULL)
- {
- if (context->windowClause)
+ if (l == NULL)
elog(ERROR, "could not find window clause for winref %u",
wfunc->winref);
-
+ }
+ else
+ {
/*
- * In EXPLAIN, we don't have window context information available, so
- * we have to settle for this:
+ * In EXPLAIN, search the namespace stack for a matching WindowAgg
+ * node (probably it's always the first entry), and print winname.
*/
- appendStringInfoString(buf, "(?)");
+ foreach(l, context->namespaces)
+ {
+ deparse_namespace *dpns = (deparse_namespace *) lfirst(l);
+
+ if (dpns->plan && IsA(dpns->plan, WindowAgg))
+ {
+ WindowAgg *wagg = (WindowAgg *) dpns->plan;
+
+ if (wagg->winref == wfunc->winref)
+ {
+ appendStringInfoString(buf, quote_identifier(wagg->winname));
+ break;
+ }
+ }
+ }
+ if (l == NULL)
+ elog(ERROR, "could not find window clause for winref %u",
+ wfunc->winref);
}
}
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index bf1f25c0dba..22841211f48 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -1171,6 +1171,9 @@ typedef struct WindowAgg
{
Plan plan;
+ /* name of WindowClause implemented by this node */
+ char *winname;
+
/* ID referenced by window functions */
Index winref;
diff --git a/src/include/utils/ruleutils.h b/src/include/utils/ruleutils.h
index aa7a8a3800f..5f2ea2e4d0e 100644
--- a/src/include/utils/ruleutils.h
+++ b/src/include/utils/ruleutils.h
@@ -43,6 +43,11 @@ extern List *set_deparse_context_plan(List *dpcontext,
struct Plan *plan, List *ancestors);
extern List *select_rtable_names_for_explain(List *rtable,
Bitmapset *rels_used);
+extern char *get_window_frame_options_for_explain(int frameOptions,
+ Node *startOffset,
+ Node *endOffset,
+ List *dpcontext,
+ bool forceprefix);
extern char *generate_collation_name(Oid collid);
extern char *generate_opclass_name(Oid opclass);
extern char *get_range_partbound_string(List *bound_datums);
diff --git a/src/test/regress/expected/box.out b/src/test/regress/expected/box.out
index 8c9e9e39355..10760870ce7 100644
--- a/src/test/regress/expected/box.out
+++ b/src/test/regress/expected/box.out
@@ -594,12 +594,13 @@ SET enable_bitmapscan = OFF;
EXPLAIN (COSTS OFF)
SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id
FROM quad_box_tbl;
- QUERY PLAN
----------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------
WindowAgg
+ Window: w1 AS (ORDER BY (b <-> '(123,456)'::point) ROWS UNBOUNDED PRECEDING)
-> Index Scan using quad_box_tbl_idx on quad_box_tbl
Order By: (b <-> '(123,456)'::point)
-(3 rows)
+(4 rows)
CREATE TEMP TABLE quad_box_tbl_ord_idx1 AS
SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id
@@ -616,13 +617,14 @@ WHERE seq.id IS NULL OR idx.id IS NULL;
EXPLAIN (COSTS OFF)
SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id
FROM quad_box_tbl WHERE b <@ box '((200,300),(500,600))';
- QUERY PLAN
----------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------
WindowAgg
+ Window: w1 AS (ORDER BY (b <-> '(123,456)'::point) ROWS UNBOUNDED PRECEDING)
-> Index Scan using quad_box_tbl_idx on quad_box_tbl
Index Cond: (b <@ '(500,600),(200,300)'::box)
Order By: (b <-> '(123,456)'::point)
-(4 rows)
+(5 rows)
CREATE TEMP TABLE quad_box_tbl_ord_idx2 AS
SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id
diff --git a/src/test/regress/expected/create_index_spgist.out b/src/test/regress/expected/create_index_spgist.out
index 5c04df9c01b..c6beb0efaff 100644
--- a/src/test/regress/expected/create_index_spgist.out
+++ b/src/test/regress/expected/create_index_spgist.out
@@ -329,12 +329,13 @@ SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)';
EXPLAIN (COSTS OFF)
SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM quad_point_tbl;
- QUERY PLAN
------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------
WindowAgg
+ Window: w1 AS (ORDER BY (p <-> '(0,0)'::point) ROWS UNBOUNDED PRECEDING)
-> Index Only Scan using sp_quad_ind on quad_point_tbl
Order By: (p <-> '(0,0)'::point)
-(3 rows)
+(4 rows)
CREATE TEMP TABLE quad_point_tbl_ord_idx1 AS
SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
@@ -349,13 +350,14 @@ WHERE seq.dist IS DISTINCT FROM idx.dist;
EXPLAIN (COSTS OFF)
SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
- QUERY PLAN
------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------
WindowAgg
+ Window: w1 AS (ORDER BY (p <-> '(0,0)'::point) ROWS UNBOUNDED PRECEDING)
-> Index Only Scan using sp_quad_ind on quad_point_tbl
Index Cond: (p <@ '(1000,1000),(200,200)'::box)
Order By: (p <-> '(0,0)'::point)
-(4 rows)
+(5 rows)
CREATE TEMP TABLE quad_point_tbl_ord_idx2 AS
SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
@@ -370,13 +372,14 @@ WHERE seq.dist IS DISTINCT FROM idx.dist;
EXPLAIN (COSTS OFF)
SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
FROM quad_point_tbl WHERE p IS NOT NULL;
- QUERY PLAN
------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------
WindowAgg
+ Window: w1 AS (ORDER BY (p <-> '(333,400)'::point) ROWS UNBOUNDED PRECEDING)
-> Index Only Scan using sp_quad_ind on quad_point_tbl
Index Cond: (p IS NOT NULL)
Order By: (p <-> '(333,400)'::point)
-(4 rows)
+(5 rows)
CREATE TEMP TABLE quad_point_tbl_ord_idx3 AS
SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
@@ -496,12 +499,13 @@ SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)';
EXPLAIN (COSTS OFF)
SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM kd_point_tbl;
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------
WindowAgg
+ Window: w1 AS (ORDER BY (p <-> '(0,0)'::point) ROWS UNBOUNDED PRECEDING)
-> Index Only Scan using sp_kd_ind on kd_point_tbl
Order By: (p <-> '(0,0)'::point)
-(3 rows)
+(4 rows)
CREATE TEMP TABLE kd_point_tbl_ord_idx1 AS
SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
@@ -516,13 +520,14 @@ WHERE seq.dist IS DISTINCT FROM idx.dist;
EXPLAIN (COSTS OFF)
SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)';
- QUERY PLAN
----------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------
WindowAgg
+ Window: w1 AS (ORDER BY (p <-> '(0,0)'::point) ROWS UNBOUNDED PRECEDING)
-> Index Only Scan using sp_kd_ind on kd_point_tbl
Index Cond: (p <@ '(1000,1000),(200,200)'::box)
Order By: (p <-> '(0,0)'::point)
-(4 rows)
+(5 rows)
CREATE TEMP TABLE kd_point_tbl_ord_idx2 AS
SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
@@ -537,13 +542,14 @@ WHERE seq.dist IS DISTINCT FROM idx.dist;
EXPLAIN (COSTS OFF)
SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
FROM kd_point_tbl WHERE p IS NOT NULL;
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------
WindowAgg
+ Window: w1 AS (ORDER BY (p <-> '(333,400)'::point) ROWS UNBOUNDED PRECEDING)
-> Index Only Scan using sp_kd_ind on kd_point_tbl
Index Cond: (p IS NOT NULL)
Order By: (p <-> '(333,400)'::point)
-(4 rows)
+(5 rows)
CREATE TEMP TABLE kd_point_tbl_ord_idx3 AS
SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out
index f5d60e50893..1be2df897ec 100644
--- a/src/test/regress/expected/explain.out
+++ b/src/test/regress/expected/explain.out
@@ -243,6 +243,19 @@ select explain_filter('explain (buffers, format json) select * from int8_tbl i8'
]
(1 row)
+-- Check expansion of window definitions
+select explain_filter('explain select sum(unique1) over w, sum(unique2) over (w order by hundred) from tenk1 window w
as(partition by ten)');
+ explain_filter
+---------------------------------------------------------------------
+ WindowAgg (cost=N.N..N.N rows=N width=N)
+ Window: w AS (PARTITION BY ten)
+ -> WindowAgg (cost=N.N..N.N rows=N width=N)
+ Window: w1 AS (PARTITION BY ten ORDER BY hundred)
+ -> Sort (cost=N.N..N.N rows=N width=N)
+ Sort Key: ten, hundred
+ -> Seq Scan on tenk1 (cost=N.N..N.N rows=N width=N)
+(7 rows)
+
-- Check output including I/O timings. These fields are conditional
-- but always set in JSON format, so check them only in this case.
set track_io_timing = on;
@@ -742,11 +755,12 @@ select explain_filter('explain (analyze,buffers off,costs off) select sum(n) ove
explain_filter
----------------------------------------------------------------------------------
WindowAgg (actual time=N.N..N.N rows=N.N loops=N)
+ Window: w1 AS ()
Storage: Memory Maximum Storage: NkB
-> Function Scan on generate_series a (actual time=N.N..N.N rows=N.N loops=N)
Planning Time: N.N ms
Execution Time: N.N ms
-(5 rows)
+(6 rows)
-- Test tuplestore storage usage in Window aggregate (disk case)
set work_mem to 64;
@@ -754,17 +768,19 @@ select explain_filter('explain (analyze,buffers off,costs off) select sum(n) ove
explain_filter
----------------------------------------------------------------------------------
WindowAgg (actual time=N.N..N.N rows=N.N loops=N)
+ Window: w1 AS ()
Storage: Disk Maximum Storage: NkB
-> Function Scan on generate_series a (actual time=N.N..N.N rows=N.N loops=N)
Planning Time: N.N ms
Execution Time: N.N ms
-(5 rows)
+(6 rows)
-- Test tuplestore storage usage in Window aggregate (memory and disk case, final result is disk)
select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over(partition by m) from (SELECT n < 3
asm, n from generate_series(1,2000) a(n))');
explain_filter
----------------------------------------------------------------------------------------
WindowAgg (actual time=N.N..N.N rows=N.N loops=N)
+ Window: w1 AS (PARTITION BY ((a.n < N)))
Storage: Disk Maximum Storage: NkB
-> Sort (actual time=N.N..N.N rows=N.N loops=N)
Sort Key: ((a.n < N))
@@ -772,6 +788,6 @@ select explain_filter('explain (analyze,buffers off,costs off) select sum(n) ove
-> Function Scan on generate_series a (actual time=N.N..N.N rows=N.N loops=N)
Planning Time: N.N ms
Execution Time: N.N ms
-(8 rows)
+(9 rows)
reset work_mem;
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index 7ef05f45be7..dc09c85938e 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -1427,6 +1427,7 @@ order by t1.a;
Sort
Sort Key: t1.a
-> WindowAgg
+ Window: w1 AS (PARTITION BY t2.a)
-> Sort
Sort Key: t2.a
-> Nested Loop Left Join
@@ -1434,7 +1435,7 @@ order by t1.a;
-> Seq Scan on gtest32 t1
-> Materialize
-> Seq Scan on gtest32 t2
-(10 rows)
+(11 rows)
select sum(t2.b) over (partition by t2.a),
sum(t2.c) over (partition by t2.a),
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index d7c9b44605d..449f0384225 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -1436,8 +1436,9 @@ explain (costs off)
QUERY PLAN
---------------------------------------------
Sort
- Sort Key: (sum((sum(c))) OVER (?)), a, b
+ Sort Key: (sum((sum(c))) OVER w1), a, b
-> WindowAgg
+ Window: w1 AS (ORDER BY a, b)
-> Sort
Sort Key: a, b
-> MixedAggregate
@@ -1446,7 +1447,7 @@ explain (costs off)
Hash Key: b
Group Key: ()
-> Seq Scan on gstest2
-(11 rows)
+(12 rows)
select a, b, sum(v.x)
from (values (1),(2)) v(x), gstest_data(v.x)
@@ -2427,9 +2428,10 @@ explain (costs off)
select a, b, row_number() over (order by a, b nulls first)
from (values (1, 1), (2, 2)) as t (a, b) where a = b
group by grouping sets((a, b), (a));
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------
WindowAgg
+ Window: w1 AS (ORDER BY "*VALUES*".column1, "*VALUES*".column2 ROWS UNBOUNDED PRECEDING)
-> Sort
Sort Key: "*VALUES*".column1, "*VALUES*".column2 NULLS FIRST
-> HashAggregate
@@ -2437,7 +2439,7 @@ group by grouping sets((a, b), (a));
Hash Key: "*VALUES*".column1
-> Values Scan on "*VALUES*"
Filter: (column1 = column2)
-(8 rows)
+(9 rows)
select a, b, row_number() over (order by a, b nulls first)
from (values (1, 1), (2, 2)) as t (a, b) where a = b
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index d95d2395d48..39b36a2442f 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -4606,6 +4606,7 @@ select min(a) over (partition by a order by a) from part_abc where a >= stable_o
Append
-> Subquery Scan on "*SELECT* 1_1"
-> WindowAgg
+ Window: w1 AS (PARTITION BY part_abc.a ORDER BY part_abc.a)
-> Append
Subplans Removed: 1
-> Index Scan using part_abc_2_a_idx on part_abc_2 part_abc_1
@@ -4622,6 +4623,7 @@ select min(a) over (partition by a order by a) from part_abc where a >= stable_o
Filter: (d <= stable_one())
-> Subquery Scan on "*SELECT* 2"
-> WindowAgg
+ Window: w1 AS (PARTITION BY part_abc_5.a ORDER BY part_abc_5.a)
-> Append
Subplans Removed: 1
-> Index Scan using part_abc_2_a_idx on part_abc_2 part_abc_6
@@ -4636,7 +4638,7 @@ select min(a) over (partition by a order by a) from part_abc where a >= stable_o
-> Index Scan using part_abc_3_3_a_idx on part_abc_3_3 part_abc_9
Index Cond: (a >= (stable_one() + 1))
Filter: (d >= stable_one())
-(33 rows)
+(35 rows)
drop view part_abc_view;
drop table part_abc;
diff --git a/src/test/regress/expected/polygon.out b/src/test/regress/expected/polygon.out
index 7a9778e70fd..c01848f103e 100644
--- a/src/test/regress/expected/polygon.out
+++ b/src/test/regress/expected/polygon.out
@@ -286,10 +286,11 @@ FROM quad_poly_tbl WHERE p <@ polygon '((300,300),(400,600),(600,500),(700,200))
QUERY PLAN
---------------------------------------------------------------------------------
WindowAgg
+ Window: w1 AS (ORDER BY (p <-> '(123,456)'::point) ROWS UNBOUNDED PRECEDING)
-> Index Scan using quad_poly_tbl_idx on quad_poly_tbl
Index Cond: (p <@ '((300,300),(400,600),(600,500),(700,200))'::polygon)
Order By: (p <-> '(123,456)'::point)
-(4 rows)
+(5 rows)
CREATE TEMP TABLE quad_poly_tbl_ord_idx2 AS
SELECT rank() OVER (ORDER BY p <-> point '123,456') n, p <-> point '123,456' dist, id
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 56509540f2a..0185ef661b1 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -1130,9 +1130,10 @@ explain (costs off, verbose)
Aggregate
Output: count(*)
-> Hash Right Semi Join
- Hash Cond: ((b.unique1 = a.unique1) AND ((row_number() OVER (?)) = a.two))
+ Hash Cond: ((b.unique1 = a.unique1) AND ((row_number() OVER w1) = a.two))
-> WindowAgg
- Output: b.unique1, row_number() OVER (?)
+ Output: b.unique1, row_number() OVER w1
+ Window: w1 AS (ROWS UNBOUNDED PRECEDING)
-> Gather
Output: b.unique1
Workers Planned: 4
@@ -1145,7 +1146,7 @@ explain (costs off, verbose)
Workers Planned: 4
-> Parallel Seq Scan on public.tenk1 a
Output: a.unique1, a.two
-(18 rows)
+(19 rows)
-- LIMIT/OFFSET within sub-selects can't be pushed to workers.
explain (costs off)
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
index 5e664fae084..7c3e673e5ea 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -1011,17 +1011,18 @@ FROM generate_series(1,5) i;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT JSON_OBJECTAGG(i: ('111' || i)::bytea FORMAT JSON WITH UNIQUE RETURNING text) OVER (PARTITION BY i % 2)
FROM generate_series(1,5) i;
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------
WindowAgg
- Output: JSON_OBJECTAGG(i : (('111'::text || (i)::text))::bytea FORMAT JSON WITH UNIQUE KEYS RETURNING text) OVER
(?),((i % 2))
+ Output: JSON_OBJECTAGG(i : (('111'::text || (i)::text))::bytea FORMAT JSON WITH UNIQUE KEYS RETURNING text) OVER
w1,((i % 2))
+ Window: w1 AS (PARTITION BY ((i.i % 2)))
-> Sort
Output: ((i % 2)), i
Sort Key: ((i.i % 2))
-> Function Scan on pg_catalog.generate_series i
Output: (i % 2), i
Function Call: generate_series(1, 5)
-(8 rows)
+(9 rows)
CREATE VIEW json_objectagg_view AS
SELECT JSON_OBJECTAGG(i: ('111' || i)::bytea FORMAT JSON WITH UNIQUE RETURNING text) FILTER (WHERE i > 3)
@@ -1047,17 +1048,18 @@ FROM generate_series(1,5) i;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT JSON_ARRAYAGG(('111' || i)::bytea FORMAT JSON NULL ON NULL RETURNING text) OVER (PARTITION BY i % 2)
FROM generate_series(1,5) i;
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------
WindowAgg
- Output: JSON_ARRAYAGG((('111'::text || (i)::text))::bytea FORMAT JSON NULL ON NULL RETURNING text) OVER (?), ((i %
2))
+ Output: JSON_ARRAYAGG((('111'::text || (i)::text))::bytea FORMAT JSON NULL ON NULL RETURNING text) OVER w1, ((i %
2))
+ Window: w1 AS (PARTITION BY ((i.i % 2)))
-> Sort
Output: ((i % 2)), i
Sort Key: ((i.i % 2))
-> Function Scan on pg_catalog.generate_series i
Output: (i % 2), i
Function Call: generate_series(1, 5)
-(8 rows)
+(9 rows)
CREATE VIEW json_arrayagg_view AS
SELECT JSON_ARRAYAGG(('111' || i)::bytea FORMAT JSON NULL ON NULL RETURNING text) FILTER (WHERE i > 3)
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 23d1463df22..b86b668f433 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -652,10 +652,11 @@ select first_value(max(x)) over (), y
QUERY PLAN
---------------------------------------------
WindowAgg
+ Window: w1 AS ()
-> HashAggregate
Group Key: (tenk1.ten + tenk1.four)
-> Seq Scan on tenk1
-(4 rows)
+(5 rows)
-- window functions returning pass-by-ref values from different rows
select x, lag(x, 1) over (order by x), lead(x, 3) over (order by x)
@@ -3537,14 +3538,15 @@ explain (costs off)
select f1, sum(f1) over (partition by f1 order by f2
range between 1 preceding and 1 following)
from t1 where f1 = f2;
- QUERY PLAN
----------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------
WindowAgg
+ Window: w1 AS (PARTITION BY f1 ORDER BY f2 RANGE BETWEEN '1'::bigint PRECEDING AND '1'::bigint FOLLOWING)
-> Sort
Sort Key: f1
-> Seq Scan on t1
Filter: (f1 = f2)
-(5 rows)
+(6 rows)
select f1, sum(f1) over (partition by f1 order by f2
range between 1 preceding and 1 following)
@@ -3583,14 +3585,15 @@ explain (costs off)
select f1, sum(f1) over (partition by f1 order by f2
groups between 1 preceding and 1 following)
from t1 where f1 = f2;
- QUERY PLAN
----------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------
WindowAgg
+ Window: w1 AS (PARTITION BY f1 ORDER BY f2 GROUPS BETWEEN '1'::bigint PRECEDING AND '1'::bigint FOLLOWING)
-> Sort
Sort Key: f1
-> Seq Scan on t1
Filter: (f1 = f2)
-(5 rows)
+(6 rows)
select f1, sum(f1) over (partition by f1 order by f2
groups between 1 preceding and 1 following)
@@ -3711,13 +3714,14 @@ SELECT
cume_dist() OVER (PARTITION BY depname ORDER BY enroll_date RANGE BETWEEN
CURRENT ROW AND UNBOUNDED FOLLOWING) cd
FROM empsalary;
- QUERY PLAN
-----------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------
WindowAgg
+ Window: w1 AS (PARTITION BY depname ORDER BY enroll_date ROWS UNBOUNDED PRECEDING)
-> Sort
Sort Key: depname, enroll_date
-> Seq Scan on empsalary
-(4 rows)
+(5 rows)
-- Ensure WindowFuncs which cannot support their WindowClause's frameOptions
-- being changed are untouched
@@ -3731,18 +3735,20 @@ SELECT
count(*) OVER (PARTITION BY depname ORDER BY enroll_date RANGE BETWEEN
CURRENT ROW AND CURRENT ROW) cnt
FROM empsalary;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------
WindowAgg
- Output: empno, depname, (row_number() OVER (?)), (rank() OVER (?)), count(*) OVER (?), enroll_date
+ Output: empno, depname, (row_number() OVER w1), (rank() OVER w1), count(*) OVER w2, enroll_date
+ Window: w2 AS (PARTITION BY empsalary.depname ORDER BY empsalary.enroll_date RANGE BETWEEN CURRENT ROW AND CURRENT
ROW)
-> WindowAgg
- Output: depname, enroll_date, empno, row_number() OVER (?), rank() OVER (?)
+ Output: depname, enroll_date, empno, row_number() OVER w1, rank() OVER w1
+ Window: w1 AS (PARTITION BY empsalary.depname ORDER BY empsalary.enroll_date ROWS UNBOUNDED PRECEDING)
-> Sort
Output: depname, enroll_date, empno
Sort Key: empsalary.depname, empsalary.enroll_date
-> Seq Scan on pg_temp.empsalary
Output: depname, enroll_date, empno
-(9 rows)
+(11 rows)
-- Ensure the above query gives us the expected results
SELECT
@@ -3777,16 +3783,18 @@ SELECT * FROM
min(salary) OVER (PARTITION BY depname || 'A', depname) depminsalary
FROM empsalary) emp
WHERE depname = 'sales';
- QUERY PLAN
---------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------
Subquery Scan on emp
-> WindowAgg
+ Window: w2 AS ()
-> WindowAgg
+ Window: w1 AS (PARTITION BY (((empsalary.depname)::text || 'A'::text)))
-> Sort
Sort Key: (((empsalary.depname)::text || 'A'::text))
-> Seq Scan on empsalary
Filter: ((depname)::text = 'sales'::text)
-(7 rows)
+(9 rows)
-- pushdown is unsafe because there's a PARTITION BY clause without depname:
EXPLAIN (COSTS OFF)
@@ -3796,18 +3804,20 @@ SELECT * FROM
min(salary) OVER (PARTITION BY depname) depminsalary
FROM empsalary) emp
WHERE depname = 'sales';
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------
Subquery Scan on emp
Filter: ((emp.depname)::text = 'sales'::text)
-> WindowAgg
+ Window: w2 AS (PARTITION BY empsalary.enroll_date)
-> Sort
Sort Key: empsalary.enroll_date
-> WindowAgg
+ Window: w1 AS (PARTITION BY empsalary.depname)
-> Sort
Sort Key: empsalary.depname
-> Seq Scan on empsalary
-(9 rows)
+(11 rows)
-- Test window function run conditions are properly pushed down into the
-- WindowAgg
@@ -3817,14 +3827,15 @@ SELECT * FROM
row_number() OVER (ORDER BY empno) rn
FROM empsalary) emp
WHERE rn < 3;
- QUERY PLAN
-----------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
WindowAgg
- Run Condition: (row_number() OVER (?) < 3)
+ Window: w1 AS (ORDER BY empsalary.empno ROWS UNBOUNDED PRECEDING)
+ Run Condition: (row_number() OVER w1 < 3)
-> Sort
Sort Key: empsalary.empno
-> Seq Scan on empsalary
-(5 rows)
+(6 rows)
-- The following 3 statements should result the same result.
SELECT * FROM
@@ -3868,14 +3879,15 @@ SELECT * FROM
rank() OVER (ORDER BY salary DESC) r
FROM empsalary) emp
WHERE r <= 3;
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------
WindowAgg
- Run Condition: (rank() OVER (?) <= 3)
+ Window: w1 AS (ORDER BY empsalary.salary ROWS UNBOUNDED PRECEDING)
+ Run Condition: (rank() OVER w1 <= 3)
-> Sort
Sort Key: empsalary.salary DESC
-> Seq Scan on empsalary
-(5 rows)
+(6 rows)
SELECT * FROM
(SELECT empno,
@@ -3898,16 +3910,17 @@ SELECT * FROM
dense_rank() OVER (ORDER BY salary DESC) dr
FROM empsalary) emp
WHERE dr = 1;
- QUERY PLAN
------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------
Subquery Scan on emp
Filter: (emp.dr = 1)
-> WindowAgg
- Run Condition: (dense_rank() OVER (?) <= 1)
+ Window: w1 AS (ORDER BY empsalary.salary ROWS UNBOUNDED PRECEDING)
+ Run Condition: (dense_rank() OVER w1 <= 1)
-> Sort
Sort Key: empsalary.salary DESC
-> Seq Scan on empsalary
-(7 rows)
+(8 rows)
SELECT * FROM
(SELECT empno,
@@ -3928,14 +3941,15 @@ SELECT * FROM
count(*) OVER (ORDER BY salary DESC) c
FROM empsalary) emp
WHERE c <= 3;
- QUERY PLAN
--------------------------------------------
+ QUERY PLAN
+---------------------------------------------
WindowAgg
- Run Condition: (count(*) OVER (?) <= 3)
+ Window: w1 AS (ORDER BY empsalary.salary)
+ Run Condition: (count(*) OVER w1 <= 3)
-> Sort
Sort Key: empsalary.salary DESC
-> Seq Scan on empsalary
-(5 rows)
+(6 rows)
SELECT * FROM
(SELECT empno,
@@ -3957,14 +3971,15 @@ SELECT * FROM
count(empno) OVER (ORDER BY salary DESC) c
FROM empsalary) emp
WHERE c <= 3;
- QUERY PLAN
----------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------
WindowAgg
- Run Condition: (count(empsalary.empno) OVER (?) <= 3)
+ Window: w1 AS (ORDER BY empsalary.salary)
+ Run Condition: (count(empsalary.empno) OVER w1 <= 3)
-> Sort
Sort Key: empsalary.salary DESC
-> Seq Scan on empsalary
-(5 rows)
+(6 rows)
SELECT * FROM
(SELECT empno,
@@ -3986,14 +4001,15 @@ SELECT * FROM
count(*) OVER (ORDER BY salary DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) c
FROM empsalary) emp
WHERE c >= 3;
- QUERY PLAN
--------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
WindowAgg
- Run Condition: (count(*) OVER (?) >= 3)
+ Window: w1 AS (ORDER BY empsalary.salary ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ Run Condition: (count(*) OVER w1 >= 3)
-> Sort
Sort Key: empsalary.salary DESC
-> Seq Scan on empsalary
-(5 rows)
+(6 rows)
EXPLAIN (COSTS OFF)
SELECT * FROM
@@ -4002,12 +4018,13 @@ SELECT * FROM
count(*) OVER () c
FROM empsalary) emp
WHERE 11 <= c;
- QUERY PLAN
---------------------------------------------
+ QUERY PLAN
+-------------------------------------------
WindowAgg
- Run Condition: (11 <= count(*) OVER (?))
+ Window: w1 AS ()
+ Run Condition: (11 <= count(*) OVER w1)
-> Seq Scan on empsalary
-(3 rows)
+(4 rows)
EXPLAIN (COSTS OFF)
SELECT * FROM
@@ -4017,16 +4034,17 @@ SELECT * FROM
dense_rank() OVER (ORDER BY salary DESC) dr
FROM empsalary) emp
WHERE dr = 1;
- QUERY PLAN
------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------
Subquery Scan on emp
Filter: (emp.dr = 1)
-> WindowAgg
- Run Condition: (dense_rank() OVER (?) <= 1)
+ Window: w1 AS (ORDER BY empsalary.salary)
+ Run Condition: (dense_rank() OVER w1 <= 1)
-> Sort
Sort Key: empsalary.salary DESC
-> Seq Scan on empsalary
-(7 rows)
+(8 rows)
-- Ensure we get a run condition when there's a PARTITION BY clause
EXPLAIN (COSTS OFF)
@@ -4036,14 +4054,15 @@ SELECT * FROM
row_number() OVER (PARTITION BY depname ORDER BY empno) rn
FROM empsalary) emp
WHERE rn < 3;
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
WindowAgg
- Run Condition: (row_number() OVER (?) < 3)
+ Window: w1 AS (PARTITION BY empsalary.depname ORDER BY empsalary.empno ROWS UNBOUNDED PRECEDING)
+ Run Condition: (row_number() OVER w1 < 3)
-> Sort
Sort Key: empsalary.depname, empsalary.empno
-> Seq Scan on empsalary
-(5 rows)
+(6 rows)
-- and ensure we get the correct results from the above plan
SELECT * FROM
@@ -4071,15 +4090,16 @@ SELECT empno, depname FROM
row_number() OVER (PARTITION BY depname ORDER BY empno) rn
FROM empsalary) emp
WHERE rn < 3;
- QUERY PLAN
-------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
Subquery Scan on emp
-> WindowAgg
- Run Condition: (row_number() OVER (?) < 3)
+ Window: w1 AS (PARTITION BY empsalary.depname ORDER BY empsalary.empno ROWS UNBOUNDED PRECEDING)
+ Run Condition: (row_number() OVER w1 < 3)
-> Sort
Sort Key: empsalary.depname, empsalary.empno
-> Seq Scan on empsalary
-(6 rows)
+(7 rows)
-- likewise with count(empno) instead of row_number()
EXPLAIN (COSTS OFF)
@@ -4090,14 +4110,15 @@ SELECT * FROM
count(empno) OVER (PARTITION BY depname ORDER BY salary DESC) c
FROM empsalary) emp
WHERE c <= 3;
- QUERY PLAN
-------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------
WindowAgg
- Run Condition: (count(empsalary.empno) OVER (?) <= 3)
+ Window: w1 AS (PARTITION BY empsalary.depname ORDER BY empsalary.salary)
+ Run Condition: (count(empsalary.empno) OVER w1 <= 3)
-> Sort
Sort Key: empsalary.depname, empsalary.salary DESC
-> Seq Scan on empsalary
-(5 rows)
+(6 rows)
-- and again, check the results are what we expect.
SELECT * FROM
@@ -4129,12 +4150,13 @@ SELECT * FROM
count(empno) OVER () c
FROM empsalary) emp
WHERE c = 1;
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------
WindowAgg
- Run Condition: (count(empsalary.empno) OVER (?) = 1)
+ Window: w1 AS ()
+ Run Condition: (count(empsalary.empno) OVER w1 = 1)
-> Seq Scan on empsalary
-(3 rows)
+(4 rows)
-- Try another case with a WindowFunc with a byref return type
SELECT * FROM
@@ -4157,23 +4179,26 @@ SELECT * FROM
ntile(2) OVER (PARTITION BY depname) nt -- w2
FROM empsalary
) e WHERE rn <= 1 AND c1 <= 3 AND nt < 2;
- QUERY PLAN
------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------
Subquery Scan on e
-> WindowAgg
- Filter: (((row_number() OVER (?)) <= 1) AND ((ntile(2) OVER (?)) < 2))
- Run Condition: (count(empsalary.salary) OVER (?) <= 3)
+ Window: w3 AS (PARTITION BY (((empsalary.depname)::text || ''::text)))
+ Run Condition: (count(empsalary.salary) OVER w3 <= 3)
+ Filter: (((row_number() OVER w2) <= 1) AND ((ntile(2) OVER w2) < 2))
-> Sort
Sort Key: (((empsalary.depname)::text || ''::text))
-> WindowAgg
- Run Condition: ((row_number() OVER (?) <= 1) AND (ntile(2) OVER (?) < 2))
+ Window: w2 AS (PARTITION BY empsalary.depname)
+ Run Condition: ((row_number() OVER w2 <= 1) AND (ntile(2) OVER w2 < 2))
-> Sort
Sort Key: empsalary.depname
-> WindowAgg
+ Window: w1 AS (PARTITION BY ((''::text || (empsalary.depname)::text)))
-> Sort
Sort Key: ((''::text || (empsalary.depname)::text))
-> Seq Scan on empsalary
-(14 rows)
+(17 rows)
-- Ensure we correctly filter out all of the run conditions from each window
SELECT * FROM
@@ -4199,12 +4224,13 @@ SELECT 1 FROM
FROM empsalary e1 LEFT JOIN empsalary e2 ON TRUE
WHERE e1.empno = e2.empno) s
WHERE s.c = 1;
- QUERY PLAN
----------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------
Subquery Scan on s
Filter: (s.c = 1)
-> WindowAgg
- Run Condition: (ntile(e2.salary) OVER (?) <= 1)
+ Window: w1 AS (PARTITION BY e1.depname ROWS UNBOUNDED PRECEDING)
+ Run Condition: (ntile(e2.salary) OVER w1 <= 1)
-> Sort
Sort Key: e1.depname
-> Merge Join
@@ -4215,7 +4241,7 @@ WHERE s.c = 1;
-> Sort
Sort Key: e2.empno
-> Seq Scan on empsalary e2
-(14 rows)
+(15 rows)
-- Ensure the run condition optimization is used in cases where the WindowFunc
-- has a Var from another query level
@@ -4224,16 +4250,17 @@ SELECT 1 FROM
(SELECT ntile(s1.x) OVER () AS c
FROM (SELECT (SELECT 1) AS x) AS s1) s
WHERE s.c = 1;
- QUERY PLAN
------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------
Subquery Scan on s
Filter: (s.c = 1)
-> WindowAgg
- Run Condition: (ntile((InitPlan 1).col1) OVER (?) <= 1)
+ Window: w1 AS (ROWS UNBOUNDED PRECEDING)
+ Run Condition: (ntile((InitPlan 1).col1) OVER w1 <= 1)
InitPlan 1
-> Result
-> Result
-(7 rows)
+(8 rows)
-- Tests to ensure we don't push down the run condition when it's not valid to
-- do so.
@@ -4246,15 +4273,16 @@ SELECT * FROM
count(*) OVER (ORDER BY salary DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) c
FROM empsalary) emp
WHERE c <= 3;
- QUERY PLAN
------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
Subquery Scan on emp
Filter: (emp.c <= 3)
-> WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
-> Sort
Sort Key: empsalary.salary DESC
-> Seq Scan on empsalary
-(6 rows)
+(7 rows)
-- Ensure we don't push down when the window function's monotonic properties
-- don't match that of the clauses.
@@ -4265,15 +4293,16 @@ SELECT * FROM
count(*) OVER (ORDER BY salary) c
FROM empsalary) emp
WHERE 3 <= c;
- QUERY PLAN
-------------------------------------------
+ QUERY PLAN
+---------------------------------------------------
Subquery Scan on emp
Filter: (3 <= emp.c)
-> WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary)
-> Sort
Sort Key: empsalary.salary
-> Seq Scan on empsalary
-(6 rows)
+(7 rows)
-- Ensure we don't use a run condition when there's a volatile function in the
-- WindowFunc
@@ -4284,15 +4313,16 @@ SELECT * FROM
count(random()) OVER (ORDER BY empno DESC) c
FROM empsalary) emp
WHERE c = 1;
- QUERY PLAN
-----------------------------------------------
+ QUERY PLAN
+--------------------------------------------------
Subquery Scan on emp
Filter: (emp.c = 1)
-> WindowAgg
+ Window: w1 AS (ORDER BY empsalary.empno)
-> Sort
Sort Key: empsalary.empno DESC
-> Seq Scan on empsalary
-(6 rows)
+(7 rows)
-- Ensure we don't use a run condition when the WindowFunc contains subplans
EXPLAIN (COSTS OFF)
@@ -4302,17 +4332,18 @@ SELECT * FROM
count((SELECT 1)) OVER (ORDER BY empno DESC) c
FROM empsalary) emp
WHERE c = 1;
- QUERY PLAN
-----------------------------------------------
+ QUERY PLAN
+--------------------------------------------------
Subquery Scan on emp
Filter: (emp.c = 1)
-> WindowAgg
+ Window: w1 AS (ORDER BY empsalary.empno)
InitPlan 1
-> Result
-> Sort
Sort Key: empsalary.empno DESC
-> Seq Scan on empsalary
-(8 rows)
+(9 rows)
-- Test Sort node collapsing
EXPLAIN (COSTS OFF)
@@ -4322,16 +4353,18 @@ SELECT * FROM
min(salary) OVER (PARTITION BY depname, empno order by enroll_date) depminsalary
FROM empsalary) emp
WHERE depname = 'sales';
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------
Subquery Scan on emp
-> WindowAgg
+ Window: w2 AS (ORDER BY empsalary.empno)
-> WindowAgg
+ Window: w1 AS (PARTITION BY empsalary.empno ORDER BY empsalary.enroll_date)
-> Sort
Sort Key: empsalary.empno, empsalary.enroll_date
-> Seq Scan on empsalary
Filter: ((depname)::text = 'sales'::text)
-(7 rows)
+(9 rows)
-- Ensure that the evaluation order of the WindowAggs results in the WindowAgg
-- with the same sort order that's required by the ORDER BY is evaluated last.
@@ -4343,17 +4376,19 @@ SELECT empno,
min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary
FROM empsalary
ORDER BY depname, empno;
- QUERY PLAN
-----------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------
WindowAgg
+ Window: w2 AS (PARTITION BY depname ORDER BY empno)
-> Incremental Sort
Sort Key: depname, empno
Presorted Key: depname
-> WindowAgg
+ Window: w1 AS (PARTITION BY depname ORDER BY enroll_date)
-> Sort
Sort Key: depname, enroll_date
-> Seq Scan on empsalary
-(8 rows)
+(10 rows)
-- As above, but with an adjusted ORDER BY to ensure the above plan didn't
-- perform only 2 sorts by accident.
@@ -4365,17 +4400,19 @@ SELECT empno,
min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary
FROM empsalary
ORDER BY depname, enroll_date;
- QUERY PLAN
------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------
WindowAgg
+ Window: w2 AS (PARTITION BY depname ORDER BY enroll_date)
-> Incremental Sort
Sort Key: depname, enroll_date
Presorted Key: depname
-> WindowAgg
+ Window: w1 AS (PARTITION BY depname ORDER BY empno)
-> Sort
Sort Key: depname, empno
-> Seq Scan on empsalary
-(8 rows)
+(10 rows)
SET enable_hashagg TO off;
-- Ensure we don't get a sort for both DISTINCT and ORDER BY. We expect the
@@ -4389,21 +4426,23 @@ SELECT DISTINCT
min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary
FROM empsalary
ORDER BY depname, enroll_date;
- QUERY PLAN
------------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------
Unique
-> Incremental Sort
- Sort Key: depname, enroll_date, empno, (sum(salary) OVER (?)), (min(salary) OVER (?))
+ Sort Key: depname, enroll_date, empno, (sum(salary) OVER w1), (min(salary) OVER w2)
Presorted Key: depname, enroll_date
-> WindowAgg
+ Window: w2 AS (PARTITION BY depname ORDER BY enroll_date)
-> Incremental Sort
Sort Key: depname, enroll_date
Presorted Key: depname
-> WindowAgg
+ Window: w1 AS (PARTITION BY depname ORDER BY empno)
-> Sort
Sort Key: depname, empno
-> Seq Scan on empsalary
-(12 rows)
+(14 rows)
-- As above but adjust the ORDER BY clause to help ensure the plan with the
-- minimum amount of sorting wasn't a fluke.
@@ -4416,21 +4455,23 @@ SELECT DISTINCT
min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary
FROM empsalary
ORDER BY depname, empno;
- QUERY PLAN
------------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------
Unique
-> Incremental Sort
- Sort Key: depname, empno, enroll_date, (sum(salary) OVER (?)), (min(salary) OVER (?))
+ Sort Key: depname, empno, enroll_date, (sum(salary) OVER w2), (min(salary) OVER w1)
Presorted Key: depname, empno
-> WindowAgg
+ Window: w2 AS (PARTITION BY depname ORDER BY empno)
-> Incremental Sort
Sort Key: depname, empno
Presorted Key: depname
-> WindowAgg
+ Window: w1 AS (PARTITION BY depname ORDER BY enroll_date)
-> Sort
Sort Key: depname, enroll_date
-> Seq Scan on empsalary
-(12 rows)
+(14 rows)
RESET enable_hashagg;
-- Test Sort node reordering
@@ -4439,14 +4480,16 @@ SELECT
lead(1) OVER (PARTITION BY depname ORDER BY salary, enroll_date),
lag(1) OVER (PARTITION BY depname ORDER BY salary,enroll_date,empno)
FROM empsalary;
- QUERY PLAN
--------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------
WindowAgg
+ Window: w2 AS (PARTITION BY depname ORDER BY salary, enroll_date)
-> WindowAgg
+ Window: w1 AS (PARTITION BY depname ORDER BY salary, enroll_date, empno)
-> Sort
Sort Key: depname, salary, enroll_date, empno
-> Seq Scan on empsalary
-(5 rows)
+(7 rows)
-- Test incremental sorting
EXPLAIN (COSTS OFF)
@@ -4459,19 +4502,21 @@ SELECT * FROM
row_number() OVER (PARTITION BY depname ORDER BY enroll_date DESC) AS last_emp
FROM empsalary) emp
WHERE first_emp = 1 OR last_emp = 1;
- QUERY PLAN
------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------
Subquery Scan on emp
Filter: ((emp.first_emp = 1) OR (emp.last_emp = 1))
-> WindowAgg
+ Window: w2 AS (PARTITION BY empsalary.depname ORDER BY empsalary.enroll_date ROWS UNBOUNDED PRECEDING)
-> Incremental Sort
Sort Key: empsalary.depname, empsalary.enroll_date
Presorted Key: empsalary.depname
-> WindowAgg
+ Window: w1 AS (PARTITION BY empsalary.depname ORDER BY empsalary.enroll_date ROWS UNBOUNDED
PRECEDING)
-> Sort
Sort Key: empsalary.depname, empsalary.enroll_date DESC
-> Seq Scan on empsalary
-(10 rows)
+(12 rows)
SELECT * FROM
(SELECT depname,
@@ -5299,11 +5344,12 @@ LIMIT 1;
--------------------------------------------------------------------------
Limit
-> WindowAgg
+ Window: w1 AS (ORDER BY t1.unique1)
-> Nested Loop
-> Index Only Scan using tenk1_unique1 on tenk1 t1
-> Index Only Scan using tenk1_thous_tenthous on tenk1 t2
Index Cond: (tenthous = t1.unique1)
-(6 rows)
+(7 rows)
-- Ensure we get a cheap total plan. Lack of ORDER BY in the WindowClause
-- means that all rows must be read from the join, so a cheap startup plan
@@ -5317,13 +5363,14 @@ LIMIT 1;
-------------------------------------------------------------------
Limit
-> WindowAgg
+ Window: w1 AS ()
-> Hash Join
Hash Cond: (t1.unique1 = t2.tenthous)
-> Index Only Scan using tenk1_unique1 on tenk1 t1
-> Hash
-> Seq Scan on tenk1 t2
Filter: (two = 1)
-(8 rows)
+(9 rows)
-- Ensure we get a cheap total plan. This time use UNBOUNDED FOLLOWING, which
-- needs to read all join rows to output the first WindowAgg row.
@@ -5331,17 +5378,18 @@ EXPLAIN (COSTS OFF)
SELECT COUNT(*) OVER (ORDER BY t1.unique1 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM tenk1 t1 INNER JOIN tenk1 t2 ON t1.unique1 = t2.tenthous
LIMIT 1;
- QUERY PLAN
---------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------
Limit
-> WindowAgg
+ Window: w1 AS (ORDER BY t1.unique1 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
-> Merge Join
Merge Cond: (t1.unique1 = t2.tenthous)
-> Index Only Scan using tenk1_unique1 on tenk1 t1
-> Sort
Sort Key: t2.tenthous
-> Index Only Scan using tenk1_thous_tenthous on tenk1 t2
-(8 rows)
+(9 rows)
-- Ensure we get a cheap total plan. This time use 10000 FOLLOWING so we need
-- to read all join rows.
@@ -5349,17 +5397,18 @@ EXPLAIN (COSTS OFF)
SELECT COUNT(*) OVER (ORDER BY t1.unique1 ROWS BETWEEN UNBOUNDED PRECEDING AND 10000 FOLLOWING)
FROM tenk1 t1 INNER JOIN tenk1 t2 ON t1.unique1 = t2.tenthous
LIMIT 1;
- QUERY PLAN
---------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
Limit
-> WindowAgg
+ Window: w1 AS (ORDER BY t1.unique1 ROWS BETWEEN UNBOUNDED PRECEDING AND '10000'::bigint FOLLOWING)
-> Merge Join
Merge Cond: (t1.unique1 = t2.tenthous)
-> Index Only Scan using tenk1_unique1 on tenk1 t1
-> Sort
Sort Key: t2.tenthous
-> Index Only Scan using tenk1_thous_tenthous on tenk1 t2
-(8 rows)
+(9 rows)
-- Tests for problems with failure to walk or mutate expressions
-- within window frame clauses.
@@ -5384,14 +5433,15 @@ AS $$
WINDOW w AS (ORDER BY s ROWS BETWEEN CURRENT ROW AND GROUP_SIZE FOLLOWING)
$$ LANGUAGE SQL STABLE;
EXPLAIN (costs off) SELECT * FROM pg_temp.f(2);
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------
Subquery Scan on f
-> WindowAgg
+ Window: w AS (ORDER BY s.s ROWS BETWEEN CURRENT ROW AND '2'::bigint FOLLOWING)
-> Sort
Sort Key: s.s
-> Function Scan on generate_series s
-(5 rows)
+(6 rows)
SELECT * FROM pg_temp.f(2);
f
diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql
index 0bafa870496..415b2123530 100644
--- a/src/test/regress/sql/explain.sql
+++ b/src/test/regress/sql/explain.sql
@@ -70,6 +70,10 @@ select explain_filter('explain (analyze, serialize, buffers, format yaml) select
select explain_filter('explain (buffers, format text) select * from int8_tbl i8');
select explain_filter('explain (buffers, format json) select * from int8_tbl i8');
+-- Check expansion of window definitions
+
+select explain_filter('explain select sum(unique1) over w, sum(unique2) over (w order by hundred) from tenk1 window w
as(partition by ten)');
+
-- Check output including I/O timings. These fields are conditional
-- but always set in JSON format, so check them only in this case.
set track_io_timing = on;
--
2.43.5
On Tue, 11 Mar 2025 at 05:18, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Here's a hopefully-final v3 that makes the two changes discussed. > Now with a draft commit message, too. Looks good to me. The only minor points I noted down while reviewing were 1) name_active_windows()'s newname variable could be halved in size and, 2) explain.sql's new test could name the window "w1" instead of "w" to exercise the name selection code a bit better. Both are minor points, but I thought I'd mention them anyway. David
David Rowley <dgrowleyml@gmail.com> writes:
> The only minor points I noted down while reviewing were 1)
> name_active_windows()'s newname variable could be halved in size and,
> 2) explain.sql's new test could name the window "w1" instead of "w" to
> exercise the name selection code a bit better. Both are minor points,
> but I thought I'd mention them anyway.
Thanks, pushed with those points addressed.
regards, tom lane