diff options
| -rw-r--r-- | contrib/postgres_fdw/expected/postgres_fdw.out | 25 | ||||
| -rw-r--r-- | src/backend/commands/explain.c | 117 | ||||
| -rw-r--r-- | src/backend/optimizer/plan/createplan.c | 39 | ||||
| -rw-r--r-- | src/backend/optimizer/plan/planner.c | 51 | ||||
| -rw-r--r-- | src/backend/utils/adt/ruleutils.c | 150 | ||||
| -rw-r--r-- | src/include/nodes/plannodes.h | 3 | ||||
| -rw-r--r-- | src/include/utils/ruleutils.h | 5 | ||||
| -rw-r--r-- | src/test/regress/expected/box.out | 14 | ||||
| -rw-r--r-- | src/test/regress/expected/create_index_spgist.out | 42 | ||||
| -rw-r--r-- | src/test/regress/expected/explain.out | 45 | ||||
| -rw-r--r-- | src/test/regress/expected/generated_virtual.out | 3 | ||||
| -rw-r--r-- | src/test/regress/expected/groupingsets.out | 12 | ||||
| -rw-r--r-- | src/test/regress/expected/partition_prune.out | 4 | ||||
| -rw-r--r-- | src/test/regress/expected/polygon.out | 3 | ||||
| -rw-r--r-- | src/test/regress/expected/select_parallel.out | 7 | ||||
| -rw-r--r-- | src/test/regress/expected/sqljson.out | 18 | ||||
| -rw-r--r-- | src/test/regress/expected/window.out | 308 | ||||
| -rw-r--r-- | src/test/regress/sql/explain.sql | 5 |
18 files changed, 598 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 ft1 where 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 ft1 where 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 adefc5471a3..19ffcc2cacb 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, @@ -2333,12 +2338,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: @@ -3008,6 +3014,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. */ static void 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..a4d523dcb0f 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; @@ -5915,6 +5920,52 @@ select_active_windows(PlannerInfo *root, WindowFuncLists *wflists) } /* + * 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[16]; + 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..340747a8f75 100644 --- a/src/test/regress/expected/explain.out +++ b/src/test/regress/expected/explain.out @@ -243,6 +243,42 @@ select explain_filter('explain (buffers, format json) select * from int8_tbl i8' ] (1 row) +-- Check expansion of window definitions +select explain_filter('explain verbose select sum(unique1) over w, sum(unique2) over (w order by hundred), sum(tenthous) 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) + Output: sum(unique1) OVER w, (sum(unique2) OVER w1), (sum(tenthous) OVER w1), ten, hundred + Window: w AS (PARTITION BY tenk1.ten) + -> WindowAgg (cost=N.N..N.N rows=N width=N) + Output: ten, hundred, unique1, unique2, tenthous, sum(unique2) OVER w1, sum(tenthous) OVER w1 + Window: w1 AS (PARTITION BY tenk1.ten ORDER BY tenk1.hundred) + -> Sort (cost=N.N..N.N rows=N width=N) + Output: ten, hundred, unique1, unique2, tenthous + Sort Key: tenk1.ten, tenk1.hundred + -> Seq Scan on public.tenk1 (cost=N.N..N.N rows=N width=N) + Output: ten, hundred, unique1, unique2, tenthous +(11 rows) + +select explain_filter('explain verbose select sum(unique1) over w1, sum(unique2) over (w1 order by hundred), sum(tenthous) over (w1 order by hundred rows 10 preceding) from tenk1 window w1 as (partition by ten)'); + explain_filter +--------------------------------------------------------------------------------------------------------- + WindowAgg (cost=N.N..N.N rows=N width=N) + Output: sum(unique1) OVER w1, (sum(unique2) OVER w2), (sum(tenthous) OVER w3), ten, hundred + Window: w1 AS (PARTITION BY tenk1.ten) + -> WindowAgg (cost=N.N..N.N rows=N width=N) + Output: ten, hundred, unique1, unique2, tenthous, (sum(unique2) OVER w2), sum(tenthous) OVER w3 + Window: w3 AS (PARTITION BY tenk1.ten ORDER BY tenk1.hundred ROWS 'N'::bigint PRECEDING) + -> WindowAgg (cost=N.N..N.N rows=N width=N) + Output: ten, hundred, unique1, unique2, tenthous, sum(unique2) OVER w2 + Window: w2 AS (PARTITION BY tenk1.ten ORDER BY tenk1.hundred) + -> Sort (cost=N.N..N.N rows=N width=N) + Output: ten, hundred, unique1, unique2, tenthous + Sort Key: tenk1.ten, tenk1.hundred + -> Seq Scan on public.tenk1 (cost=N.N..N.N rows=N width=N) + Output: ten, hundred, unique1, unique2, tenthous +(14 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 +778,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 +791,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 as m, 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 +811,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 34f2b0b8dbd..8097f4e9282 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -4678,6 +4678,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 @@ -4694,6 +4695,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 @@ -4708,7 +4710,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..b266764089f 100644 --- a/src/test/regress/sql/explain.sql +++ b/src/test/regress/sql/explain.sql @@ -70,6 +70,11 @@ 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 verbose select sum(unique1) over w, sum(unique2) over (w order by hundred), sum(tenthous) over (w order by hundred) from tenk1 window w as (partition by ten)'); +select explain_filter('explain verbose select sum(unique1) over w1, sum(unique2) over (w1 order by hundred), sum(tenthous) over (w1 order by hundred rows 10 preceding) from tenk1 window w1 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; |