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

Commita209936

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 parent6b896f5 commita209936

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
@@ -90,8 +90,8 @@ static void locate_grouping_columns(PlannerInfo *root,
9090
AttrNumber*groupColIdx);
9191
staticList*postprocess_setop_tlist(List*new_tlist,List*orig_tlist);
9292
staticList*select_active_windows(PlannerInfo*root,WindowFuncLists*wflists);
93-
staticList*add_volatile_sort_exprs(List*window_tlist,List*tlist,
94-
List*activeWindows);
93+
staticList*make_windowInputTargetList(PlannerInfo*root,
94+
List*tlist,List*activeWindows);
9595
staticList*make_pathkeys_for_window(PlannerInfo*root,WindowClause*wc,
9696
List*tlist,boolcanonicalize);
9797
staticvoidget_column_info_for_window(PlannerInfo*root,WindowClause*wc,
@@ -1555,31 +1555,25 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
15551555

15561556
/*
15571557
* The "base" targetlist for all steps of the windowing process is
1558-
* a flat tlist of all Vars and Aggs needed in the result. (In
1558+
* a flat tlist of all Vars and Aggs needed in the result.(In
15591559
* some cases we wouldn't need to propagate all of these all the
15601560
* way to the top, since they might only be needed as inputs to
15611561
* WindowFuncs. It's probably not worth trying to optimize that
1562-
* though.) We also need any volatile sort expressions, because
1563-
* make_sort_from_pathkeys won't add those on its own, and anyway
1564-
* we want them evaluated only once at the bottom of the stack. As
1565-
* we climb up the stack, we add outputs for the WindowFuncs
1566-
* computed at each level.Also, each input tlist has to present
1567-
* all the columns needed to sort the data for the next WindowAgg
1568-
* step. That's handled internally by make_sort_from_pathkeys,
1569-
* but we need the copyObject steps here to ensure that each plan
1570-
* node has a separately modifiable tlist.
1571-
*
1572-
* Note: it's essential here to use PVC_INCLUDE_AGGREGATES so that
1573-
* Vars mentioned only in aggregate expressions aren't pulled out
1574-
* as separate targetlist entries.Otherwise we could be putting
1575-
* ungrouped Vars directly into an Agg node's tlist, resulting in
1576-
* undefined behavior.
1562+
* though.) We also add window partitioning and sorting
1563+
* expressions to the base tlist, to ensure they're computed only
1564+
* once at the bottom of the stack (that's critical for volatile
1565+
* functions). As we climb up the stack, we'll add outputs for
1566+
* the WindowFuncs computed at each level.
1567+
*/
1568+
window_tlist=make_windowInputTargetList(root,
1569+
tlist,
1570+
activeWindows);
1571+
1572+
/*
1573+
* The copyObject steps here are needed to ensure that each plan
1574+
* node has a separately modifiable tlist. (XXX wouldn't a
1575+
* shallow list copy do for that?)
15771576
*/
1578-
window_tlist=flatten_tlist(tlist,
1579-
PVC_INCLUDE_AGGREGATES,
1580-
PVC_INCLUDE_PLACEHOLDERS);
1581-
window_tlist=add_volatile_sort_exprs(window_tlist,tlist,
1582-
activeWindows);
15831577
result_plan->targetlist= (List*)copyObject(window_tlist);
15841578

15851579
foreach(l,activeWindows)
@@ -1603,9 +1597,11 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
16031597
* really have to sort. Even when no explicit sort is needed,
16041598
* we need to have suitable resjunk items added to the input
16051599
* plan's tlist for any partitioning or ordering columns that
1606-
* aren't plain Vars. Furthermore, this way we can use
1607-
* existing infrastructure to identify which input columns are
1608-
* the interesting ones.
1600+
* aren't plain Vars. (In theory, make_windowInputTargetList
1601+
* should have provided all such columns, but let's not assume
1602+
* that here.) Furthermore, this way we can use existing
1603+
* infrastructure to identify which input columns are the
1604+
* interesting ones.
16091605
*/
16101606
if (window_pathkeys)
16111607
{
@@ -3006,18 +3002,57 @@ select_active_windows(PlannerInfo *root, WindowFuncLists *wflists)
30063002
}
30073003

30083004
/*
3009-
* add_volatile_sort_exprs
3010-
*Identify any volatile sort/group expressions used by the active
3011-
*windows, and add them to window_tlist if not already present.
3012-
*Return the modified window_tlist.
3005+
* make_windowInputTargetList
3006+
* Generate appropriate target list for initial input to WindowAgg nodes.
3007+
*
3008+
* When grouping_planner inserts one or more WindowAgg nodes into the plan,
3009+
* this function computes the initial target list to be computed by the node
3010+
* just below the first WindowAgg. This list must contain all values needed
3011+
* to evaluate the window functions, compute the final target list, and
3012+
* perform any required final sort step. If multiple WindowAggs are needed,
3013+
* each intermediate one adds its window function results onto this tlist;
3014+
* only the topmost WindowAgg computes the actual desired target list.
3015+
*
3016+
* This function is much like make_subplanTargetList, though not quite enough
3017+
* like it to share code. As in that function, we flatten most expressions
3018+
* into their component variables. But we do not want to flatten window
3019+
* PARTITION BY/ORDER BY clauses, since that might result in multiple
3020+
* evaluations of them, which would be bad (possibly even resulting in
3021+
* inconsistent answers, if they contain volatile functions). Also, we must
3022+
* not flatten GROUP BY clauses that were left unflattened by
3023+
* make_subplanTargetList, because we may no longer have access to the
3024+
* individual Vars in them.
3025+
*
3026+
* Another key difference from make_subplanTargetList is that we don't flatten
3027+
* Aggref expressions, since those are to be computed below the window
3028+
* functions and just referenced like Vars above that.
3029+
*
3030+
* 'tlist' is the query's final target list.
3031+
* 'activeWindows' is the list of active windows previously identified by
3032+
*select_active_windows.
3033+
*
3034+
* The result is the targetlist to be computed by the plan node immediately
3035+
* below the first WindowAgg node.
30133036
*/
30143037
staticList*
3015-
add_volatile_sort_exprs(List*window_tlist,List*tlist,List*activeWindows)
3038+
make_windowInputTargetList(PlannerInfo*root,
3039+
List*tlist,
3040+
List*activeWindows)
30163041
{
3017-
Bitmapset*sgrefs=NULL;
3042+
Query*parse=root->parse;
3043+
Bitmapset*sgrefs;
3044+
List*new_tlist;
3045+
List*flattenable_cols;
3046+
List*flattenable_vars;
30183047
ListCell*lc;
30193048

3020-
/* First, collect the sortgrouprefs of the windows into a bitmapset */
3049+
Assert(parse->hasWindowFuncs);
3050+
3051+
/*
3052+
* Collect the sortgroupref numbers of window PARTITION/ORDER BY clauses
3053+
* into a bitmapset for convenient reference below.
3054+
*/
3055+
sgrefs=NULL;
30213056
foreach(lc,activeWindows)
30223057
{
30233058
WindowClause*wc= (WindowClause*)lfirst(lc);
@@ -3037,34 +3072,74 @@ add_volatile_sort_exprs(List *window_tlist, List *tlist, List *activeWindows)
30373072
}
30383073
}
30393074

3075+
/* Add in sortgroupref numbers of GROUP BY clauses, too */
3076+
foreach(lc,parse->groupClause)
3077+
{
3078+
SortGroupClause*grpcl= (SortGroupClause*)lfirst(lc);
3079+
3080+
sgrefs=bms_add_member(sgrefs,grpcl->tleSortGroupRef);
3081+
}
3082+
30403083
/*
3041-
* Now scan the original tlist to find the referenced expressions. Any
3042-
* that are volatile must be added to window_tlist.
3043-
*
3044-
* Note: we know that the input window_tlist contains no items marked with
3045-
* ressortgrouprefs, so we don't have to worry about collisions of the
3046-
* reference numbers.
3084+
* Construct a tlist containing all the non-flattenable tlist items, and
3085+
* save aside the others for a moment.
30473086
*/
3087+
new_tlist=NIL;
3088+
flattenable_cols=NIL;
3089+
30483090
foreach(lc,tlist)
30493091
{
30503092
TargetEntry*tle= (TargetEntry*)lfirst(lc);
30513093

3094+
/*
3095+
* Don't want to deconstruct window clauses or GROUP BY items. (Note
3096+
* that such items can't contain window functions, so it's okay to
3097+
* compute them below the WindowAgg nodes.)
3098+
*/
30523099
if (tle->ressortgroupref!=0&&
3053-
bms_is_member(tle->ressortgroupref,sgrefs)&&
3054-
contain_volatile_functions((Node*)tle->expr))
3100+
bms_is_member(tle->ressortgroupref,sgrefs))
30553101
{
3102+
/* Don't want to deconstruct this value, so add to new_tlist */
30563103
TargetEntry*newtle;
30573104

30583105
newtle=makeTargetEntry(tle->expr,
3059-
list_length(window_tlist)+1,
3106+
list_length(new_tlist)+1,
30603107
NULL,
30613108
false);
3109+
/* Preserve its sortgroupref marking, in case it's volatile */
30623110
newtle->ressortgroupref=tle->ressortgroupref;
3063-
window_tlist=lappend(window_tlist,newtle);
3111+
new_tlist=lappend(new_tlist,newtle);
3112+
}
3113+
else
3114+
{
3115+
/*
3116+
* Column is to be flattened, so just remember the expression for
3117+
* later call to pull_var_clause. There's no need for
3118+
* pull_var_clause to examine the TargetEntry node itself.
3119+
*/
3120+
flattenable_cols=lappend(flattenable_cols,tle->expr);
30643121
}
30653122
}
30663123

3067-
returnwindow_tlist;
3124+
/*
3125+
* Pull out all the Vars and Aggrefs mentioned in flattenable columns, and
3126+
* add them to the result tlist if not already present. (Some might be
3127+
* there already because they're used directly as window/group clauses.)
3128+
*
3129+
* Note: it's essential to use PVC_INCLUDE_AGGREGATES here, so that the
3130+
* Aggrefs are placed in the Agg node's tlist and not left to be computed
3131+
* at higher levels.
3132+
*/
3133+
flattenable_vars=pull_var_clause((Node*)flattenable_cols,
3134+
PVC_INCLUDE_AGGREGATES,
3135+
PVC_INCLUDE_PLACEHOLDERS);
3136+
new_tlist=add_to_flat_tlist(new_tlist,flattenable_vars);
3137+
3138+
/* clean up cruft */
3139+
list_free(flattenable_vars);
3140+
list_free(flattenable_cols);
3141+
3142+
returnnew_tlist;
30683143
}
30693144

30703145
/*

‎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