Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tr1tC-00Dhqh-Sk for pgsql-hackers@arkaria.postgresql.org; Sat, 08 Mar 2025 21:39:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tr1tA-009bvx-Eh for pgsql-hackers@arkaria.postgresql.org; Sat, 08 Mar 2025 21:39:20 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tr1tA-009bvn-1k for pgsql-hackers@lists.postgresql.org; Sat, 08 Mar 2025 21:39:20 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tr1t5-001kHu-1u for pgsql-hackers@lists.postgresql.org; Sat, 08 Mar 2025 21:39:19 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 528LdF5D144531 for ; Sat, 8 Mar 2025 16:39:15 -0500 From: Tom Lane To: pgsql-hackers@lists.postgresql.org Subject: Printing window function OVER clauses in EXPLAIN MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <144350.1741469852.0@sss.pgh.pa.us> Date: Sat, 08 Mar 2025 16:39:15 -0500 Message-ID: <144530.1741469955@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <144350.1741469852.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable 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%3Dn9K= UgfKQje_pbLROEBmA9Ru5XWidw%40mail.gmail.com ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="v1-show-window-clauses-in-EXPLAIN.patch"; charset="us-ascii" Content-ID: <144350.1741469852.2@sss.pgh.pa.us> Content-Description: v1-show-window-clauses-in-EXPLAIN.patch Content-Transfer-Encoding: quoted-printable diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/post= gres_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 c= 2%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)))), ((c= 2 % 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 o= rder 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 D= ESC)), ((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 o= rder by c2 desc) from ft1 wher = explain (verbose, costs off) select c2, array_agg(c2) over (partition by c2%2 order by c2 range betwee= n current row and unbounded following) from ft1 where c2 < 10 group by c2 = order by 1; - QUERY PLAN = = --------------------------------------------------------------------------= -------------------------- + QUERY PL= AN = +-------------------------------------------------------------------------= ------------------------------------------------------------------- Sort - Output: c2, (array_agg(c2) OVER (?)), ((c2 % 2)) + Output: c2, (array_agg(c2) OVER (PARTITION BY ((c2 % 2)) ORDER BY c2 R= ANGE 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/n= odeWindowAgg.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, i= nt eflags) ExprContext *tmpcontext; WindowStatePerFunc perfunc; WindowStatePerAgg peragg; - int frameOptions =3D node->frameOptions; + int frameOptions =3D node->winclause->frameOptions; int numfuncs, wfuncno, numaggs, @@ -2615,9 +2615,9 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, i= nt eflags) AclResult aclresult; int i; = - if (wfunc->winref !=3D node->winref) /* planner screwed up? */ + if (wfunc->winref !=3D node->winclause->winref) /* planner screwed up? = */ elog(ERROR, "WindowFunc with winref %u assigned to WindowAgg with winr= ef %u", - wfunc->winref, node->winref); + wfunc->winref, node->winclause->winref); = /* Look for a previous duplicate window function */ for (i =3D 0; i <=3D wfuncno; i++) @@ -2709,19 +2709,19 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate,= int eflags) winstate->status =3D WINDOWAGG_RUN; = /* initialize frame bound offset expressions */ - winstate->startOffset =3D ExecInitExpr((Expr *) node->startOffset, + winstate->startOffset =3D ExecInitExpr((Expr *) node->winclause->startOf= fset, (PlanState *) winstate); - winstate->endOffset =3D ExecInitExpr((Expr *) node->endOffset, + winstate->endOffset =3D 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 =3D node->inRangeColl; - winstate->inRangeAsc =3D node->inRangeAsc; - winstate->inRangeNullsFirst =3D node->inRangeNullsFirst; + if (OidIsValid(node->winclause->startInRangeFunc)) + fmgr_info(node->winclause->startInRangeFunc, &winstate->startInRangeFun= c); + if (OidIsValid(node->winclause->endInRangeFunc)) + fmgr_info(node->winclause->endInRangeFunc, &winstate->endInRangeFunc); + winstate->inRangeColl =3D node->winclause->inRangeColl; + winstate->inRangeAsc =3D node->winclause->inRangeAsc; + winstate->inRangeNullsFirst =3D node->winclause->inRangeNullsFirst; = winstate->all_first =3D true; winstate->partition_spooled =3D false; diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimiz= er/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 *has= hoperators, 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 *o= rdCollations, - 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, WindowAggPa= th *best_path) = /* And finally we can make the WindowAgg node */ plan =3D make_windowagg(tlist, - wc->winref, + wc, partNumCols, partColIdx, partOperators, @@ -2692,14 +2689,6 @@ create_windowagg_plan(PlannerInfo *root, WindowAggP= ath *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 *p= artCollations, int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, Oid *ordC= ollations, - 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 =3D makeNode(WindowAgg); Plan *plan =3D &node->plan; = - node->winref =3D winref; + node->winclause =3D winclause; node->partNumCols =3D partNumCols; node->partColIdx =3D partColIdx; node->partOperators =3D partOperators; @@ -6724,17 +6710,9 @@ make_windowagg(List *tlist, Index winref, node->ordColIdx =3D ordColIdx; node->ordOperators =3D ordOperators; node->ordCollations =3D ordCollations; - node->frameOptions =3D frameOptions; - node->startOffset =3D startOffset; - node->endOffset =3D endOffset; node->runCondition =3D runCondition; /* a duplicate of the above for EXPLAIN */ node->runConditionOrig =3D runCondition; - node->startInRangeFunc =3D startInRangeFunc; - node->endInRangeFunc =3D endInRangeFunc; - node->inRangeColl =3D inRangeColl; - node->inRangeAsc =3D inRangeAsc; - node->inRangeNullsFirst =3D inRangeNullsFirst; node->topWindow =3D topWindow; = plan->targetlist =3D 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 rto= ffset) 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 =3D - fix_scan_expr(root, wplan->startOffset, rtoffset, 1); - wplan->endOffset =3D - fix_scan_expr(root, wplan->endOffset, rtoffset, 1); + wplan->winclause =3D (WindowClause *) + fix_scan_expr(root, (Node *) wplan->winclause, rtoffset, 1); wplan->runCondition =3D fix_scan_list(root, wplan->runCondition, rtoffset, diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimize= r/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/rul= eutils.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, de= parse_context *context, = appendStringInfoString(buf, ") OVER "); = - foreach(l, context->windowClause) + if (context->windowClause) { - WindowClause *wc =3D (WindowClause *) lfirst(l); - - if (wc->winref =3D=3D 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 =3D (WindowClause *) lfirst(l); + + if (wc->winref =3D=3D wfunc->winref) + { + if (wc->name) + appendStringInfoString(buf, quote_identifier(wc->name)); + else + get_rule_windowspec(wc, context->targetList, context); + break; + } } - } - if (l =3D=3D NULL) - { - if (context->windowClause) + if (l =3D=3D 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 =3D (deparse_namespace *) lfirst(l); + + if (dpns->plan && IsA(dpns->plan, WindowAgg)) + { + WindowClause *wc =3D ((WindowAgg *) dpns->plan)->winclause; + + if (wc->winref =3D=3D 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 =3D=3D 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/regr= ess/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 =3D a.unique1) AND ((row_number() OVER (?= )) =3D a.two)) + Hash Cond: ((b.unique1 =3D a.unique1) AND ((row_number() OVER (R= OWS UNBOUNDED PRECEDING)) =3D a.two)) -> WindowAgg - Output: b.unique1, row_number() OVER (?) + Output: b.unique1, row_number() OVER (ROWS UNBOUNDED PRECE= DING) -> Gather Output: b.unique1 Workers Planned: 4 diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expe= cted/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 RETU= RNING text) OVER (PARTITION BY i % 2) FROM generate_series(1,5) i; - QUERY PLAN = = --------------------------------------------------------------------------= ---------------------------------------------------------- + QU= ERY 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 (PARTITION BY ((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 RETURNI= NG text) OVER (PARTITION BY i % 2) FROM generate_series(1,5) i; - QUERY PLAN = = --------------------------------------------------------------------------= ------------------------------------------------- + QUERY P= LAN = +-------------------------------------------------------------------------= ---------------------------------------------------------------------- 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/expec= ted/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 BETWEE= N CURRENT ROW AND CURRENT ROW) cnt FROM empsalary; - QUERY PLAN = = --------------------------------------------------------------------------= ----------------------------- + = = QUERY PLAN = = = +-------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= ------------------- WindowAgg - Output: empno, depname, (row_number() OVER (?)), (rank() OVER (?)), co= unt(*) OVER (?), enroll_date + Output: empno, depname, (row_number() OVER (PARTITION BY depname ORDER= BY enroll_date ROWS UNBOUNDED PRECEDING)), (rank() OVER (PARTITION BY dep= name ORDER BY enroll_date ROWS UNBOUNDED PRECEDING)), count(*) OVER (PARTI= TION BY depname ORDER BY 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 (PARTITIO= N 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 @@ -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 UNBOU= NDED 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 <=3D 3; - QUERY PLAN = ------------------------------------------ + QUERY PLAN = = +-------------------------------------------------------------------------= ---------------------- WindowAgg - Run Condition: (rank() OVER (?) <=3D 3) + Run Condition: (rank() OVER (ORDER BY empsalary.salary DESC ROWS UNBOU= NDED PRECEDING) <=3D 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 =3D 1; - QUERY PLAN = ------------------------------------------------------ + QUERY PLAN = = +-------------------------------------------------------------------------= ---------------------------------- Subquery Scan on emp Filter: (emp.dr =3D 1) -> WindowAgg - Run Condition: (dense_rank() OVER (?) <=3D 1) + Run Condition: (dense_rank() OVER (ORDER BY empsalary.salary DES= C ROWS UNBOUNDED PRECEDING) <=3D 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 <=3D 3; - QUERY PLAN = -------------------------------------------- + QUERY PLAN = +------------------------------------------------------------------------ WindowAgg - Run Condition: (count(*) OVER (?) <=3D 3) + Run Condition: (count(*) OVER (ORDER BY empsalary.salary DESC) <=3D 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 <=3D 3; - QUERY PLAN = ---------------------------------------------------------- + QUERY PLAN = = +-------------------------------------------------------------------------= ------------- WindowAgg - Run Condition: (count(empsalary.empno) OVER (?) <=3D 3) + Run Condition: (count(empsalary.empno) OVER (ORDER BY empsalary.salary= DESC) <=3D 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 AN= D UNBOUNDED FOLLOWING) c FROM empsalary) emp WHERE c >=3D 3; - QUERY PLAN = -------------------------------------------- + QUERY PLAN = = +-------------------------------------------------------------------------= ------------------------------------------------ WindowAgg - Run Condition: (count(*) OVER (?) >=3D 3) + Run Condition: (count(*) OVER (ORDER BY empsalary.salary DESC ROWS BET= WEEN CURRENT ROW AND UNBOUNDED FOLLOWING) >=3D 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 <=3D c; - QUERY PLAN = --------------------------------------------- + QUERY PLAN = +------------------------------------------- WindowAgg - Run Condition: (11 <=3D count(*) OVER (?)) + Run Condition: (11 <=3D 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 =3D 1; - QUERY PLAN = ------------------------------------------------------ + QUERY PLAN = = +-------------------------------------------------------------------------= --------- Subquery Scan on emp Filter: (emp.dr =3D 1) -> WindowAgg - Run Condition: (dense_rank() OVER (?) <=3D 1) + Run Condition: (dense_rank() OVER (ORDER BY empsalary.salary DES= C) <=3D 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 ORDE= R 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.depnam= e 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 <=3D 3; - QUERY PLAN = ------------------------------------------------------------- + QUERY PLAN = = +-------------------------------------------------------------------------= -------------------------------------------- WindowAgg - Run Condition: (count(empsalary.empno) OVER (?) <=3D 3) + Run Condition: (count(empsalary.empno) OVER (PARTITION BY empsalary.de= pname ORDER BY empsalary.salary DESC) <=3D 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 =3D 1; - QUERY PLAN = --------------------------------------------------------- + QUERY PLAN = +------------------------------------------------------- WindowAgg - Run Condition: (count(empsalary.empno) OVER (?) =3D 1) + Run Condition: (count(empsalary.empno) OVER () =3D 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 <=3D 1 AND c1 <=3D 3 AND nt < 2; - QUERY PLAN = = --------------------------------------------------------------------------= ---------------------- + QU= ERY PLAN = = +-------------------------------------------------------------------------= --------------------------------------------------------------------------= ------ Subquery Scan on e -> WindowAgg - Filter: (((row_number() OVER (?)) <=3D 1) AND ((ntile(2) OVER (?= )) < 2)) - Run Condition: (count(empsalary.salary) OVER (?) <=3D 3) + Filter: (((row_number() OVER (PARTITION BY empsalary.depname)) <= =3D 1) AND ((ntile(2) OVER (PARTITION BY empsalary.depname)) < 2)) + Run Condition: (count(empsalary.salary) OVER (PARTITION BY (((em= psalary.depname)::text || ''::text))) <=3D 3) -> Sort Sort Key: (((empsalary.depname)::text || ''::text)) -> WindowAgg - Run Condition: ((row_number() OVER (?) <=3D 1) AND (= ntile(2) OVER (?) < 2)) + Run Condition: ((row_number() OVER (PARTITION BY emp= salary.depname) <=3D 1) AND (ntile(2) OVER (PARTITION BY 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 =3D e2.empno) s WHERE s.c =3D 1; - QUERY PLAN = ---------------------------------------------------------- + QUERY PLAN = = +-------------------------------------------------------------------------= ------------------------------- Subquery Scan on s Filter: (s.c =3D 1) -> WindowAgg - Run Condition: (ntile(e2.salary) OVER (?) <=3D 1) + Run Condition: (ntile(e2.salary) OVER (PARTITION BY e1.depname R= OWS UNBOUNDED PRECEDING) <=3D 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 =3D 1; - QUERY PLAN = ------------------------------------------------------------------ + QUERY PLAN = = +-------------------------------------------------------------------------= --------------- Subquery Scan on s Filter: (s.c =3D 1) -> WindowAgg - Run Condition: (ntile((InitPlan 1).col1) OVER (?) <=3D 1) + Run Condition: (ntile((InitPlan 1).col1) OVER (ROWS UNBOUNDED PR= ECEDING) <=3D 1) InitPlan 1 -> Result -> Result @@ -4389,11 +4389,11 @@ SELECT DISTINCT min(salary) OVER (PARTITION BY depname order by enroll_date) depmi= nsalary 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 (PARTIT= ION BY depname ORDER BY empno)), (min(salary) OVER (PARTITION BY depname O= RDER 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) depmi= nsalary 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 (PARTIT= ION BY depname ORDER BY empno)), (min(salary) OVER (PARTITION BY depname O= RDER BY enroll_date)) Presorted Key: depname, empno -> WindowAgg -> Incremental Sort ------- =_aaaaaaaaaa0--