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

Commit269dbaf

Browse files
committed
Fix case of window function + aggregate + GROUP BY expression.
In commit1bc16a9 I added a minoroptimization to drop the component variables of a GROUP BY expression fromthe target list computed at the aggregation level of a query, if those Varsweren't referenced elsewhere in the tlist. However, I overlooked that thewindow-function planning code would deconstruct such expressions and thusneed to have access to their component variables. Fix it to not do that.While at it, I removed the distinction between volatile and nonvolatilewindow partition/order expressions: the code now computes all of themat the aggregation level. This saves a relatively expensive check forvolatility, and it's unclear that the resulting plan isn't better anyway.Per bug #7535 from Louis-David Mitterrand. Back-patch to 9.2.
1 parentadff00a commit269dbaf

File tree

3 files changed

+137
-44
lines changed

3 files changed

+137
-44
lines changed

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

Lines changed: 119 additions & 44 deletions
Original file line numberDiff line numberDiff line change
@@ -86,8 +86,8 @@ static void locate_grouping_columns(PlannerInfo *root,
8686
AttrNumber*groupColIdx);
8787
staticList*postprocess_setop_tlist(List*new_tlist,List*orig_tlist);
8888
staticList*select_active_windows(PlannerInfo*root,WindowFuncLists*wflists);
89-
staticList*add_volatile_sort_exprs(List*window_tlist,List*tlist,
90-
List*activeWindows);
89+
staticList*make_windowInputTargetList(PlannerInfo*root,
90+
List*tlist,List*activeWindows);
9191
staticList*make_pathkeys_for_window(PlannerInfo*root,WindowClause*wc,
9292
List*tlist,boolcanonicalize);
9393
staticvoidget_column_info_for_window(PlannerInfo*root,WindowClause*wc,
@@ -1512,31 +1512,25 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
15121512

15131513
/*
15141514
* The "base" targetlist for all steps of the windowing process is
1515-
* a flat tlist of all Vars and Aggs needed in the result. (In
1515+
* a flat tlist of all Vars and Aggs needed in the result.(In
15161516
* some cases we wouldn't need to propagate all of these all the
15171517
* way to the top, since they might only be needed as inputs to
15181518
* WindowFuncs. It's probably not worth trying to optimize that
1519-
* though.) We also need any volatile sort expressions, because
1520-
* make_sort_from_pathkeys won't add those on its own, and anyway
1521-
* we want them evaluated only once at the bottom of the stack. As
1522-
* we climb up the stack, we add outputs for the WindowFuncs
1523-
* computed at each level.Also, each input tlist has to present
1524-
* all the columns needed to sort the data for the next WindowAgg
1525-
* step. That's handled internally by make_sort_from_pathkeys,
1526-
* but we need the copyObject steps here to ensure that each plan
1527-
* node has a separately modifiable tlist.
1528-
*
1529-
* Note: it's essential here to use PVC_INCLUDE_AGGREGATES so that
1530-
* Vars mentioned only in aggregate expressions aren't pulled out
1531-
* as separate targetlist entries.Otherwise we could be putting
1532-
* ungrouped Vars directly into an Agg node's tlist, resulting in
1533-
* undefined behavior.
1519+
* though.) We also add window partitioning and sorting
1520+
* expressions to the base tlist, to ensure they're computed only
1521+
* once at the bottom of the stack (that's critical for volatile
1522+
* functions). As we climb up the stack, we'll add outputs for
1523+
* the WindowFuncs computed at each level.
1524+
*/
1525+
window_tlist=make_windowInputTargetList(root,
1526+
tlist,
1527+
activeWindows);
1528+
1529+
/*
1530+
* The copyObject steps here are needed to ensure that each plan
1531+
* node has a separately modifiable tlist. (XXX wouldn't a
1532+
* shallow list copy do for that?)
15341533
*/
1535-
window_tlist=flatten_tlist(tlist,
1536-
PVC_INCLUDE_AGGREGATES,
1537-
PVC_INCLUDE_PLACEHOLDERS);
1538-
window_tlist=add_volatile_sort_exprs(window_tlist,tlist,
1539-
activeWindows);
15401534
result_plan->targetlist= (List*)copyObject(window_tlist);
15411535

15421536
foreach(l,activeWindows)
@@ -1560,9 +1554,11 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
15601554
* really have to sort. Even when no explicit sort is needed,
15611555
* we need to have suitable resjunk items added to the input
15621556
* plan's tlist for any partitioning or ordering columns that
1563-
* aren't plain Vars. Furthermore, this way we can use
1564-
* existing infrastructure to identify which input columns are
1565-
* the interesting ones.
1557+
* aren't plain Vars. (In theory, make_windowInputTargetList
1558+
* should have provided all such columns, but let's not assume
1559+
* that here.) Furthermore, this way we can use existing
1560+
* infrastructure to identify which input columns are the
1561+
* interesting ones.
15661562
*/
15671563
if (window_pathkeys)
15681564
{
@@ -2963,18 +2959,57 @@ select_active_windows(PlannerInfo *root, WindowFuncLists *wflists)
29632959
}
29642960

29652961
/*
2966-
* add_volatile_sort_exprs
2967-
*Identify any volatile sort/group expressions used by the active
2968-
*windows, and add them to window_tlist if not already present.
2969-
*Return the modified window_tlist.
2962+
* make_windowInputTargetList
2963+
* Generate appropriate target list for initial input to WindowAgg nodes.
2964+
*
2965+
* When grouping_planner inserts one or more WindowAgg nodes into the plan,
2966+
* this function computes the initial target list to be computed by the node
2967+
* just below the first WindowAgg. This list must contain all values needed
2968+
* to evaluate the window functions, compute the final target list, and
2969+
* perform any required final sort step. If multiple WindowAggs are needed,
2970+
* each intermediate one adds its window function results onto this tlist;
2971+
* only the topmost WindowAgg computes the actual desired target list.
2972+
*
2973+
* This function is much like make_subplanTargetList, though not quite enough
2974+
* like it to share code. As in that function, we flatten most expressions
2975+
* into their component variables. But we do not want to flatten window
2976+
* PARTITION BY/ORDER BY clauses, since that might result in multiple
2977+
* evaluations of them, which would be bad (possibly even resulting in
2978+
* inconsistent answers, if they contain volatile functions). Also, we must
2979+
* not flatten GROUP BY clauses that were left unflattened by
2980+
* make_subplanTargetList, because we may no longer have access to the
2981+
* individual Vars in them.
2982+
*
2983+
* Another key difference from make_subplanTargetList is that we don't flatten
2984+
* Aggref expressions, since those are to be computed below the window
2985+
* functions and just referenced like Vars above that.
2986+
*
2987+
* 'tlist' is the query's final target list.
2988+
* 'activeWindows' is the list of active windows previously identified by
2989+
*select_active_windows.
2990+
*
2991+
* The result is the targetlist to be computed by the plan node immediately
2992+
* below the first WindowAgg node.
29702993
*/
29712994
staticList*
2972-
add_volatile_sort_exprs(List*window_tlist,List*tlist,List*activeWindows)
2995+
make_windowInputTargetList(PlannerInfo*root,
2996+
List*tlist,
2997+
List*activeWindows)
29732998
{
2974-
Bitmapset*sgrefs=NULL;
2999+
Query*parse=root->parse;
3000+
Bitmapset*sgrefs;
3001+
List*new_tlist;
3002+
List*flattenable_cols;
3003+
List*flattenable_vars;
29753004
ListCell*lc;
29763005

2977-
/* First, collect the sortgrouprefs of the windows into a bitmapset */
3006+
Assert(parse->hasWindowFuncs);
3007+
3008+
/*
3009+
* Collect the sortgroupref numbers of window PARTITION/ORDER BY clauses
3010+
* into a bitmapset for convenient reference below.
3011+
*/
3012+
sgrefs=NULL;
29783013
foreach(lc,activeWindows)
29793014
{
29803015
WindowClause*wc= (WindowClause*)lfirst(lc);
@@ -2994,34 +3029,74 @@ add_volatile_sort_exprs(List *window_tlist, List *tlist, List *activeWindows)
29943029
}
29953030
}
29963031

3032+
/* Add in sortgroupref numbers of GROUP BY clauses, too */
3033+
foreach(lc,parse->groupClause)
3034+
{
3035+
SortGroupClause*grpcl= (SortGroupClause*)lfirst(lc);
3036+
3037+
sgrefs=bms_add_member(sgrefs,grpcl->tleSortGroupRef);
3038+
}
3039+
29973040
/*
2998-
* Now scan the original tlist to find the referenced expressions. Any
2999-
* that are volatile must be added to window_tlist.
3000-
*
3001-
* Note: we know that the input window_tlist contains no items marked with
3002-
* ressortgrouprefs, so we don't have to worry about collisions of the
3003-
* reference numbers.
3041+
* Construct a tlist containing all the non-flattenable tlist items, and
3042+
* save aside the others for a moment.
30043043
*/
3044+
new_tlist=NIL;
3045+
flattenable_cols=NIL;
3046+
30053047
foreach(lc,tlist)
30063048
{
30073049
TargetEntry*tle= (TargetEntry*)lfirst(lc);
30083050

3051+
/*
3052+
* Don't want to deconstruct window clauses or GROUP BY items. (Note
3053+
* that such items can't contain window functions, so it's okay to
3054+
* compute them below the WindowAgg nodes.)
3055+
*/
30093056
if (tle->ressortgroupref!=0&&
3010-
bms_is_member(tle->ressortgroupref,sgrefs)&&
3011-
contain_volatile_functions((Node*)tle->expr))
3057+
bms_is_member(tle->ressortgroupref,sgrefs))
30123058
{
3059+
/* Don't want to deconstruct this value, so add to new_tlist */
30133060
TargetEntry*newtle;
30143061

30153062
newtle=makeTargetEntry(tle->expr,
3016-
list_length(window_tlist)+1,
3063+
list_length(new_tlist)+1,
30173064
NULL,
30183065
false);
3066+
/* Preserve its sortgroupref marking, in case it's volatile */
30193067
newtle->ressortgroupref=tle->ressortgroupref;
3020-
window_tlist=lappend(window_tlist,newtle);
3068+
new_tlist=lappend(new_tlist,newtle);
3069+
}
3070+
else
3071+
{
3072+
/*
3073+
* Column is to be flattened, so just remember the expression for
3074+
* later call to pull_var_clause. There's no need for
3075+
* pull_var_clause to examine the TargetEntry node itself.
3076+
*/
3077+
flattenable_cols=lappend(flattenable_cols,tle->expr);
30213078
}
30223079
}
30233080

3024-
returnwindow_tlist;
3081+
/*
3082+
* Pull out all the Vars and Aggrefs mentioned in flattenable columns, and
3083+
* add them to the result tlist if not already present. (Some might be
3084+
* there already because they're used directly as window/group clauses.)
3085+
*
3086+
* Note: it's essential to use PVC_INCLUDE_AGGREGATES here, so that the
3087+
* Aggrefs are placed in the Agg node's tlist and not left to be computed
3088+
* at higher levels.
3089+
*/
3090+
flattenable_vars=pull_var_clause((Node*)flattenable_cols,
3091+
PVC_INCLUDE_AGGREGATES,
3092+
PVC_INCLUDE_PLACEHOLDERS);
3093+
new_tlist=add_to_flat_tlist(new_tlist,flattenable_vars);
3094+
3095+
/* clean up cruft */
3096+
list_free(flattenable_vars);
3097+
list_free(flattenable_cols);
3098+
3099+
returnnew_tlist;
30253100
}
30263101

30273102
/*

‎src/test/regress/expected/window.out

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -614,6 +614,18 @@ group by ten order by ten;
614614
9 | 10040184 | 7
615615
(10 rows)
616616

617+
-- window and aggregate with GROUP BY expression (9.2 bug)
618+
explain (costs off)
619+
select first_value(max(x)) over (), y
620+
from (select unique1 as x, ten+four as y from tenk1) ss
621+
group by y;
622+
QUERY PLAN
623+
-------------------------------
624+
WindowAgg
625+
-> HashAggregate
626+
-> Seq Scan on tenk1
627+
(3 rows)
628+
617629
-- test non-default frame specifications
618630
SELECT four, ten,
619631
sum(ten) over (partition by four order by ten),

‎src/test/regress/sql/window.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -145,6 +145,12 @@ select ten,
145145
from tenk1
146146
group by tenorder by ten;
147147

148+
-- window and aggregate with GROUP BY expression (9.2 bug)
149+
explain (costs off)
150+
select first_value(max(x)) over (), y
151+
from (select unique1as x, ten+fouras yfrom tenk1) ss
152+
group by y;
153+
148154
-- test non-default frame specifications
149155
SELECT four, ten,
150156
sum(ten) over (partition by fourorder by ten),

0 commit comments

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