Printing window function OVER clauses in EXPLAIN - Mailing list pgsql-hackers
| From | Tom Lane |
|---|---|
| Subject | Printing window function OVER clauses in EXPLAIN |
| Date | |
| Msg-id | 144530.1741469955@sss.pgh.pa.us Whole thread Raw |
| Responses |
Re: Printing window function OVER clauses in EXPLAIN
Re: Printing window function OVER clauses in EXPLAIN |
| List | pgsql-hackers |
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
pgsql-hackers by date: