Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit8b1b342

Browse files
committed
Improve EXPLAIN's display of window functions.
Up to now we just punted on showing the window definitions usedin a plan, with window function calls represented as "OVER (?)".To improve that, show the window definition implemented by eachWindowAgg plan node, and reference their window names in OVER.For nameless window clauses generated by "OVER (...)", assignunique names w1, w2, etc.In passing, re-order the properties shown for a WindowAgg nodeso that the Run Condition (if any) appears after the Windowproperty and before the Filter (if any). This seems moresensible since the Run Condition is associated with the Windowand acts before the Filter.Thanks to David G. Johnston and Álvaro Herrera for designsuggestions.Author: Tom Lane <tgl@sss.pgh.pa.us>Reviewed-by: David Rowley <dgrowleyml@gmail.com>Discussion:https://postgr.es/m/144530.1741469955@sss.pgh.pa.us
1 parent426ea61 commit8b1b342

File tree

18 files changed

+598
-253
lines changed

18 files changed

+598
-253
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 14 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -3968,18 +3968,19 @@ select c2, sum(c2), count(c2) over (partition by c2%2) from ft2 where c2 < 10 gr
39683968
QUERY PLAN
39693969
------------------------------------------------------------------------------------------------------------
39703970
Sort
3971-
Output: c2, (sum(c2)), (count(c2) OVER(?)), ((c2 % 2))
3971+
Output: c2, (sum(c2)), (count(c2) OVERw1), ((c2 % 2))
39723972
Sort Key: ft2.c2
39733973
-> WindowAgg
3974-
Output: c2, (sum(c2)), count(c2) OVER (?), ((c2 % 2))
3974+
Output: c2, (sum(c2)), count(c2) OVER w1, ((c2 % 2))
3975+
Window: w1 AS (PARTITION BY ((ft2.c2 % 2)))
39753976
-> Sort
39763977
Output: c2, ((c2 % 2)), (sum(c2))
39773978
Sort Key: ((ft2.c2 % 2))
39783979
-> Foreign Scan
39793980
Output: c2, ((c2 % 2)), (sum(c2))
39803981
Relations: Aggregate on (public.ft2)
39813982
Remote SQL: SELECT c2, (c2 % 2), sum(c2) FROM "S 1"."T 1" WHERE ((c2 < 10)) GROUP BY 1
3982-
(12 rows)
3983+
(13 rows)
39833984

39843985
select c2, sum(c2), count(c2) over (partition by c2%2) from ft2 where c2 < 10 group by c2 order by 1;
39853986
c2 | sum | count
@@ -4001,18 +4002,19 @@ select c2, array_agg(c2) over (partition by c2%2 order by c2 desc) from ft1 wher
40014002
QUERY PLAN
40024003
---------------------------------------------------------------------------------------------------
40034004
Sort
4004-
Output: c2, (array_agg(c2) OVER(?)), ((c2 % 2))
4005+
Output: c2, (array_agg(c2) OVERw1), ((c2 % 2))
40054006
Sort Key: ft1.c2
40064007
-> WindowAgg
4007-
Output: c2, array_agg(c2) OVER (?), ((c2 % 2))
4008+
Output: c2, array_agg(c2) OVER w1, ((c2 % 2))
4009+
Window: w1 AS (PARTITION BY ((ft1.c2 % 2)) ORDER BY ft1.c2)
40084010
-> Sort
40094011
Output: c2, ((c2 % 2))
40104012
Sort Key: ((ft1.c2 % 2)), ft1.c2 DESC
40114013
-> Foreign Scan
40124014
Output: c2, ((c2 % 2))
40134015
Relations: Aggregate on (public.ft1)
40144016
Remote SQL: SELECT c2, (c2 % 2) FROM "S 1"."T 1" WHERE ((c2 < 10)) GROUP BY 1
4015-
(12 rows)
4017+
(13 rows)
40164018

40174019
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;
40184020
c2 | array_agg
@@ -4031,21 +4033,22 @@ select c2, array_agg(c2) over (partition by c2%2 order by c2 desc) from ft1 wher
40314033

40324034
explain (verbose, costs off)
40334035
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;
4034-
QUERY PLAN
4035-
---------------------------------------------------------------------------------------------------
4036+
QUERY PLAN
4037+
-----------------------------------------------------------------------------------------------------------------------
40364038
Sort
4037-
Output: c2, (array_agg(c2) OVER(?)), ((c2 % 2))
4039+
Output: c2, (array_agg(c2) OVERw1), ((c2 % 2))
40384040
Sort Key: ft1.c2
40394041
-> WindowAgg
4040-
Output: c2, array_agg(c2) OVER (?), ((c2 % 2))
4042+
Output: c2, array_agg(c2) OVER w1, ((c2 % 2))
4043+
Window: w1 AS (PARTITION BY ((ft1.c2 % 2)) ORDER BY ft1.c2 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
40414044
-> Sort
40424045
Output: c2, ((c2 % 2))
40434046
Sort Key: ((ft1.c2 % 2)), ft1.c2
40444047
-> Foreign Scan
40454048
Output: c2, ((c2 % 2))
40464049
Relations: Aggregate on (public.ft1)
40474050
Remote SQL: SELECT c2, (c2 % 2) FROM "S 1"."T 1" WHERE ((c2 < 10)) GROUP BY 1
4048-
(12 rows)
4051+
(13 rows)
40494052

40504053
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;
40514054
c2 | array_agg

‎src/backend/commands/explain.c

Lines changed: 115 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -107,6 +107,11 @@ static void show_sort_group_keys(PlanState *planstate, const char *qlabel,
107107
List*ancestors,ExplainState*es);
108108
staticvoidshow_sortorder_options(StringInfobuf,Node*sortexpr,
109109
OidsortOperator,Oidcollation,boolnullsFirst);
110+
staticvoidshow_window_def(WindowAggState*planstate,
111+
List*ancestors,ExplainState*es);
112+
staticvoidshow_window_keys(StringInfobuf,PlanState*planstate,
113+
intnkeys,AttrNumber*keycols,
114+
List*ancestors,ExplainState*es);
110115
staticvoidshow_storage_info(char*maxStorageType,int64maxSpaceUsed,
111116
ExplainState*es);
112117
staticvoidshow_tablesample(TableSampleClause*tsc,PlanState*planstate,
@@ -2333,12 +2338,13 @@ ExplainNode(PlanState *planstate, List *ancestors,
23332338
planstate,es);
23342339
break;
23352340
caseT_WindowAgg:
2341+
show_window_def(castNode(WindowAggState,planstate),ancestors,es);
2342+
show_upper_qual(((WindowAgg*)plan)->runConditionOrig,
2343+
"Run Condition",planstate,ancestors,es);
23362344
show_upper_qual(plan->qual,"Filter",planstate,ancestors,es);
23372345
if (plan->qual)
23382346
show_instrumentation_count("Rows Removed by Filter",1,
23392347
planstate,es);
2340-
show_upper_qual(((WindowAgg*)plan)->runConditionOrig,
2341-
"Run Condition",planstate,ancestors,es);
23422348
show_windowagg_info(castNode(WindowAggState,planstate),es);
23432349
break;
23442350
caseT_Group:
@@ -3007,6 +3013,113 @@ show_sortorder_options(StringInfo buf, Node *sortexpr,
30073013
}
30083014
}
30093015

3016+
/*
3017+
* Show the window definition for a WindowAgg node.
3018+
*/
3019+
staticvoid
3020+
show_window_def(WindowAggState*planstate,List*ancestors,ExplainState*es)
3021+
{
3022+
WindowAgg*wagg= (WindowAgg*)planstate->ss.ps.plan;
3023+
StringInfoDatawbuf;
3024+
boolneedspace= false;
3025+
3026+
initStringInfo(&wbuf);
3027+
appendStringInfo(&wbuf,"%s AS (",quote_identifier(wagg->winname));
3028+
3029+
/* The key columns refer to the tlist of the child plan */
3030+
ancestors=lcons(wagg,ancestors);
3031+
if (wagg->partNumCols>0)
3032+
{
3033+
appendStringInfoString(&wbuf,"PARTITION BY ");
3034+
show_window_keys(&wbuf,outerPlanState(planstate),
3035+
wagg->partNumCols,wagg->partColIdx,
3036+
ancestors,es);
3037+
needspace= true;
3038+
}
3039+
if (wagg->ordNumCols>0)
3040+
{
3041+
if (needspace)
3042+
appendStringInfoChar(&wbuf,' ');
3043+
appendStringInfoString(&wbuf,"ORDER BY ");
3044+
show_window_keys(&wbuf,outerPlanState(planstate),
3045+
wagg->ordNumCols,wagg->ordColIdx,
3046+
ancestors,es);
3047+
needspace= true;
3048+
}
3049+
ancestors=list_delete_first(ancestors);
3050+
if (wagg->frameOptions&FRAMEOPTION_NONDEFAULT)
3051+
{
3052+
List*context;
3053+
booluseprefix;
3054+
char*framestr;
3055+
3056+
/* Set up deparsing context for possible frame expressions */
3057+
context=set_deparse_context_plan(es->deparse_cxt,
3058+
(Plan*)wagg,
3059+
ancestors);
3060+
useprefix= (es->rtable_size>1||es->verbose);
3061+
framestr=get_window_frame_options_for_explain(wagg->frameOptions,
3062+
wagg->startOffset,
3063+
wagg->endOffset,
3064+
context,
3065+
useprefix);
3066+
if (needspace)
3067+
appendStringInfoChar(&wbuf,' ');
3068+
appendStringInfoString(&wbuf,framestr);
3069+
pfree(framestr);
3070+
}
3071+
appendStringInfoChar(&wbuf,')');
3072+
ExplainPropertyText("Window",wbuf.data,es);
3073+
pfree(wbuf.data);
3074+
}
3075+
3076+
/*
3077+
* Append the keys of a window's PARTITION BY or ORDER BY clause to buf.
3078+
* We can't use show_sort_group_keys for this because that's too opinionated
3079+
* about how the result will be displayed.
3080+
* Note that the "planstate" node should be the WindowAgg's child.
3081+
*/
3082+
staticvoid
3083+
show_window_keys(StringInfobuf,PlanState*planstate,
3084+
intnkeys,AttrNumber*keycols,
3085+
List*ancestors,ExplainState*es)
3086+
{
3087+
Plan*plan=planstate->plan;
3088+
List*context;
3089+
booluseprefix;
3090+
3091+
/* Set up deparsing context */
3092+
context=set_deparse_context_plan(es->deparse_cxt,
3093+
plan,
3094+
ancestors);
3095+
useprefix= (es->rtable_size>1||es->verbose);
3096+
3097+
for (intkeyno=0;keyno<nkeys;keyno++)
3098+
{
3099+
/* find key expression in tlist */
3100+
AttrNumberkeyresno=keycols[keyno];
3101+
TargetEntry*target=get_tle_by_resno(plan->targetlist,
3102+
keyresno);
3103+
char*exprstr;
3104+
3105+
if (!target)
3106+
elog(ERROR,"no tlist entry for key %d",keyresno);
3107+
/* Deparse the expression, showing any top-level cast */
3108+
exprstr=deparse_expression((Node*)target->expr,context,
3109+
useprefix, true);
3110+
if (keyno>0)
3111+
appendStringInfoString(buf,", ");
3112+
appendStringInfoString(buf,exprstr);
3113+
pfree(exprstr);
3114+
3115+
/*
3116+
* We don't attempt to provide sort order information because
3117+
* WindowAgg carries equality operators not comparison operators;
3118+
* compare show_agg_keys.
3119+
*/
3120+
}
3121+
}
3122+
30103123
/*
30113124
* Show information on storage method and maximum memory/disk space used.
30123125
*/

‎src/backend/optimizer/plan/createplan.c

Lines changed: 13 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -285,12 +285,9 @@ static Memoize *make_memoize(Plan *lefttree, Oid *hashoperators,
285285
Oid*collations,List*param_exprs,
286286
boolsinglerow,boolbinary_mode,
287287
uint32est_entries,Bitmapset*keyparamids);
288-
staticWindowAgg*make_windowagg(List*tlist,Indexwinref,
288+
staticWindowAgg*make_windowagg(List*tlist,WindowClause*wc,
289289
intpartNumCols,AttrNumber*partColIdx,Oid*partOperators,Oid*partCollations,
290290
intordNumCols,AttrNumber*ordColIdx,Oid*ordOperators,Oid*ordCollations,
291-
intframeOptions,Node*startOffset,Node*endOffset,
292-
OidstartInRangeFunc,OidendInRangeFunc,
293-
OidinRangeColl,boolinRangeAsc,boolinRangeNullsFirst,
294291
List*runCondition,List*qual,booltopWindow,
295292
Plan*lefttree);
296293
staticGroup*make_group(List*tlist,List*qual,intnumGroupCols,
@@ -2683,7 +2680,7 @@ create_windowagg_plan(PlannerInfo *root, WindowAggPath *best_path)
26832680

26842681
/* And finally we can make the WindowAgg node */
26852682
plan=make_windowagg(tlist,
2686-
wc->winref,
2683+
wc,
26872684
partNumCols,
26882685
partColIdx,
26892686
partOperators,
@@ -2692,14 +2689,6 @@ create_windowagg_plan(PlannerInfo *root, WindowAggPath *best_path)
26922689
ordColIdx,
26932690
ordOperators,
26942691
ordCollations,
2695-
wc->frameOptions,
2696-
wc->startOffset,
2697-
wc->endOffset,
2698-
wc->startInRangeFunc,
2699-
wc->endInRangeFunc,
2700-
wc->inRangeColl,
2701-
wc->inRangeAsc,
2702-
wc->inRangeNullsFirst,
27032692
best_path->runCondition,
27042693
best_path->qual,
27052694
best_path->topwindow,
@@ -6704,18 +6693,16 @@ make_agg(List *tlist, List *qual,
67046693
}
67056694

67066695
staticWindowAgg*
6707-
make_windowagg(List*tlist,Indexwinref,
6696+
make_windowagg(List*tlist,WindowClause*wc,
67086697
intpartNumCols,AttrNumber*partColIdx,Oid*partOperators,Oid*partCollations,
67096698
intordNumCols,AttrNumber*ordColIdx,Oid*ordOperators,Oid*ordCollations,
6710-
intframeOptions,Node*startOffset,Node*endOffset,
6711-
OidstartInRangeFunc,OidendInRangeFunc,
6712-
OidinRangeColl,boolinRangeAsc,boolinRangeNullsFirst,
67136699
List*runCondition,List*qual,booltopWindow,Plan*lefttree)
67146700
{
67156701
WindowAgg*node=makeNode(WindowAgg);
67166702
Plan*plan=&node->plan;
67176703

6718-
node->winref=winref;
6704+
node->winname=wc->name;
6705+
node->winref=wc->winref;
67196706
node->partNumCols=partNumCols;
67206707
node->partColIdx=partColIdx;
67216708
node->partOperators=partOperators;
@@ -6724,17 +6711,17 @@ make_windowagg(List *tlist, Index winref,
67246711
node->ordColIdx=ordColIdx;
67256712
node->ordOperators=ordOperators;
67266713
node->ordCollations=ordCollations;
6727-
node->frameOptions=frameOptions;
6728-
node->startOffset=startOffset;
6729-
node->endOffset=endOffset;
6714+
node->frameOptions=wc->frameOptions;
6715+
node->startOffset=wc->startOffset;
6716+
node->endOffset=wc->endOffset;
67306717
node->runCondition=runCondition;
67316718
/* a duplicate of the above for EXPLAIN */
67326719
node->runConditionOrig=runCondition;
6733-
node->startInRangeFunc=startInRangeFunc;
6734-
node->endInRangeFunc=endInRangeFunc;
6735-
node->inRangeColl=inRangeColl;
6736-
node->inRangeAsc=inRangeAsc;
6737-
node->inRangeNullsFirst=inRangeNullsFirst;
6720+
node->startInRangeFunc=wc->startInRangeFunc;
6721+
node->endInRangeFunc=wc->endInRangeFunc;
6722+
node->inRangeColl=wc->inRangeColl;
6723+
node->inRangeAsc=wc->inRangeAsc;
6724+
node->inRangeNullsFirst=wc->inRangeNullsFirst;
67386725
node->topWindow=topWindow;
67396726

67406727
plan->targetlist=tlist;

‎src/backend/optimizer/plan/planner.c

Lines changed: 51 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -214,6 +214,7 @@ static List *postprocess_setop_tlist(List *new_tlist, List *orig_tlist);
214214
staticvoidoptimize_window_clauses(PlannerInfo*root,
215215
WindowFuncLists*wflists);
216216
staticList*select_active_windows(PlannerInfo*root,WindowFuncLists*wflists);
217+
staticvoidname_active_windows(List*activeWindows);
217218
staticPathTarget*make_window_input_target(PlannerInfo*root,
218219
PathTarget*final_target,
219220
List*activeWindows);
@@ -1539,7 +1540,11 @@ grouping_planner(PlannerInfo *root, double tuple_fraction,
15391540
*/
15401541
optimize_window_clauses(root,wflists);
15411542

1543+
/* Extract the list of windows actually in use. */
15421544
activeWindows=select_active_windows(root,wflists);
1545+
1546+
/* Make sure they all have names, for EXPLAIN's use. */
1547+
name_active_windows(activeWindows);
15431548
}
15441549
else
15451550
parse->hasWindowFuncs= false;
@@ -5914,6 +5919,52 @@ select_active_windows(PlannerInfo *root, WindowFuncLists *wflists)
59145919
returnresult;
59155920
}
59165921

5922+
/*
5923+
* name_active_windows
5924+
* Ensure all active windows have unique names.
5925+
*
5926+
* The parser will have checked that user-assigned window names are unique
5927+
* within the Query. Here we assign made-up names to any unnamed
5928+
* WindowClauses for the benefit of EXPLAIN. (We don't want to do this
5929+
* at parse time, because it'd mess up decompilation of views.)
5930+
*
5931+
* activeWindows: result of select_active_windows
5932+
*/
5933+
staticvoid
5934+
name_active_windows(List*activeWindows)
5935+
{
5936+
intnext_n=1;
5937+
charnewname[16];
5938+
ListCell*lc;
5939+
5940+
foreach(lc,activeWindows)
5941+
{
5942+
WindowClause*wc=lfirst_node(WindowClause,lc);
5943+
5944+
/* Nothing to do if it has a name already. */
5945+
if (wc->name)
5946+
continue;
5947+
5948+
/* Select a name not currently present in the list. */
5949+
for (;;)
5950+
{
5951+
ListCell*lc2;
5952+
5953+
snprintf(newname,sizeof(newname),"w%d",next_n++);
5954+
foreach(lc2,activeWindows)
5955+
{
5956+
WindowClause*wc2=lfirst_node(WindowClause,lc2);
5957+
5958+
if (wc2->name&&strcmp(wc2->name,newname)==0)
5959+
break;/* matched */
5960+
}
5961+
if (lc2==NULL)
5962+
break;/* reached the end with no match */
5963+
}
5964+
wc->name=pstrdup(newname);
5965+
}
5966+
}
5967+
59175968
/*
59185969
* common_prefix_cmp
59195970
* QSort comparison function for WindowClauseSortData

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp