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

Commit989067b

Browse files
committed
Extend set-operation planning to keep track of the sort ordering induced
by the set operation, so that redundant sorts at higher levels can beavoided. This was foreseen a good while back, but not done. Per requestfrom Karel Zak.
1 parent5d1af6a commit989067b

File tree

3 files changed

+63
-29
lines changed

3 files changed

+63
-29
lines changed

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

Lines changed: 15 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/plan/planner.c,v 1.167 2004/02/13 22:26:30 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/plan/planner.c,v 1.168 2004/04/07 18:17:24 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -572,11 +572,23 @@ grouping_planner(Query *parse, double tuple_fraction)
572572

573573
if (parse->setOperations)
574574
{
575+
List*set_sortclauses;
576+
575577
/*
576578
* Construct the plan for set operations. The result will not
577579
* need any work except perhaps a top-level sort and/or LIMIT.
578580
*/
579-
result_plan=plan_set_operations(parse);
581+
result_plan=plan_set_operations(parse,
582+
&set_sortclauses);
583+
584+
/*
585+
* Calculate pathkeys representing the sort order (if any) of the
586+
* set operation's result. We have to do this before overwriting
587+
* the sort key information...
588+
*/
589+
current_pathkeys=make_pathkeys_for_sortclauses(set_sortclauses,
590+
result_plan->targetlist);
591+
current_pathkeys=canonicalize_pathkeys(parse,current_pathkeys);
580592

581593
/*
582594
* We should not need to call preprocess_targetlist, since we must
@@ -599,16 +611,7 @@ grouping_planner(Query *parse, double tuple_fraction)
599611
errmsg("SELECT FOR UPDATE is not allowed with UNION/INTERSECT/EXCEPT")));
600612

601613
/*
602-
* We set current_pathkeys NIL indicating we do not know sort
603-
* order. This is correct when the top set operation is UNION
604-
* ALL, since the appended-together results are unsorted even if
605-
* the subplans were sorted. For other set operations we could be
606-
* smarter --- room for future improvement!
607-
*/
608-
current_pathkeys=NIL;
609-
610-
/*
611-
* Calculate pathkeys that represent ordering requirements
614+
* Calculate pathkeys that represent result ordering requirements
612615
*/
613616
sort_pathkeys=make_pathkeys_for_sortclauses(parse->sortClause,
614617
tlist);

‎src/backend/optimizer/prep/prepunion.c

Lines changed: 46 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@
1414
*
1515
*
1616
* IDENTIFICATION
17-
* $PostgreSQL: pgsql/src/backend/optimizer/prep/prepunion.c,v 1.108 2004/01/18 00:50:02 tgl Exp $
17+
* $PostgreSQL: pgsql/src/backend/optimizer/prep/prepunion.c,v 1.109 2004/04/07 18:17:25 tgl Exp $
1818
*
1919
*-------------------------------------------------------------------------
2020
*/
@@ -45,11 +45,12 @@ typedef struct
4545

4646
staticPlan*recurse_set_operations(Node*setOp,Query*parse,
4747
List*colTypes,booljunkOK,
48-
intflag,List*refnames_tlist);
48+
intflag,List*refnames_tlist,
49+
List**sortClauses);
4950
staticPlan*generate_union_plan(SetOperationStmt*op,Query*parse,
50-
List*refnames_tlist);
51+
List*refnames_tlist,List**sortClauses);
5152
staticPlan*generate_nonunion_plan(SetOperationStmt*op,Query*parse,
52-
List*refnames_tlist);
53+
List*refnames_tlist,List**sortClauses);
5354
staticList*recurse_union_children(Node*setOp,Query*parse,
5455
SetOperationStmt*top_union,
5556
List*refnames_tlist);
@@ -75,9 +76,12 @@ static List *adjust_inherited_tlist(List *tlist, Oid old_relid, Oid new_relid);
7576
* This routine only deals with the setOperations tree of the given query.
7677
* Any top-level ORDER BY requested in parse->sortClause will be added
7778
* when we return to grouping_planner.
79+
*
80+
* *sortClauses is an output argument: it is set to a list of SortClauses
81+
* representing the result ordering of the topmost set operation.
7882
*/
7983
Plan*
80-
plan_set_operations(Query*parse)
84+
plan_set_operations(Query*parse,List**sortClauses)
8185
{
8286
SetOperationStmt*topop= (SetOperationStmt*)parse->setOperations;
8387
Node*node;
@@ -113,7 +117,8 @@ plan_set_operations(Query *parse)
113117
*/
114118
returnrecurse_set_operations((Node*)topop,parse,
115119
topop->colTypes, true,-1,
116-
leftmostQuery->targetList);
120+
leftmostQuery->targetList,
121+
sortClauses);
117122
}
118123

119124
/*
@@ -124,11 +129,13 @@ plan_set_operations(Query *parse)
124129
* junkOK: if true, child resjunk columns may be left in the result
125130
* flag: if >= 0, add a resjunk output column indicating value of flag
126131
* refnames_tlist: targetlist to take column names from
132+
* *sortClauses: receives list of SortClauses for result plan, if any
127133
*/
128134
staticPlan*
129135
recurse_set_operations(Node*setOp,Query*parse,
130136
List*colTypes,booljunkOK,
131-
intflag,List*refnames_tlist)
137+
intflag,List*refnames_tlist,
138+
List**sortClauses)
132139
{
133140
if (IsA(setOp,RangeTblRef))
134141
{
@@ -155,6 +162,13 @@ recurse_set_operations(Node *setOp, Query *parse,
155162
NIL,
156163
rtr->rtindex,
157164
subplan);
165+
166+
/*
167+
* We don't bother to determine the subquery's output ordering
168+
* since it won't be reflected in the set-op result anyhow.
169+
*/
170+
*sortClauses=NIL;
171+
158172
returnplan;
159173
}
160174
elseif (IsA(setOp,SetOperationStmt))
@@ -164,9 +178,11 @@ recurse_set_operations(Node *setOp, Query *parse,
164178

165179
/* UNIONs are much different from INTERSECT/EXCEPT */
166180
if (op->op==SETOP_UNION)
167-
plan=generate_union_plan(op,parse,refnames_tlist);
181+
plan=generate_union_plan(op,parse,refnames_tlist,
182+
sortClauses);
168183
else
169-
plan=generate_nonunion_plan(op,parse,refnames_tlist);
184+
plan=generate_nonunion_plan(op,parse,refnames_tlist,
185+
sortClauses);
170186

171187
/*
172188
* If necessary, add a Result node to project the caller-requested
@@ -206,7 +222,8 @@ recurse_set_operations(Node *setOp, Query *parse,
206222
*/
207223
staticPlan*
208224
generate_union_plan(SetOperationStmt*op,Query*parse,
209-
List*refnames_tlist)
225+
List*refnames_tlist,
226+
List**sortClauses)
210227
{
211228
List*planlist;
212229
List*tlist;
@@ -249,7 +266,11 @@ generate_union_plan(SetOperationStmt *op, Query *parse,
249266
sortList=addAllTargetsToSortList(NULL,NIL,tlist, false);
250267
plan= (Plan*)make_sort_from_sortclauses(parse,sortList,plan);
251268
plan= (Plan*)make_unique(plan,sortList);
269+
*sortClauses=sortList;
252270
}
271+
else
272+
*sortClauses=NIL;
273+
253274
returnplan;
254275
}
255276

@@ -258,23 +279,27 @@ generate_union_plan(SetOperationStmt *op, Query *parse,
258279
*/
259280
staticPlan*
260281
generate_nonunion_plan(SetOperationStmt*op,Query*parse,
261-
List*refnames_tlist)
282+
List*refnames_tlist,
283+
List**sortClauses)
262284
{
263285
Plan*lplan,
264286
*rplan,
265287
*plan;
266288
List*tlist,
267289
*sortList,
268-
*planlist;
290+
*planlist,
291+
*child_sortclauses;
269292
SetOpCmdcmd;
270293

271294
/* Recurse on children, ensuring their outputs are marked */
272295
lplan=recurse_set_operations(op->larg,parse,
273296
op->colTypes, false,0,
274-
refnames_tlist);
297+
refnames_tlist,
298+
&child_sortclauses);
275299
rplan=recurse_set_operations(op->rarg,parse,
276300
op->colTypes, false,1,
277-
refnames_tlist);
301+
refnames_tlist,
302+
&child_sortclauses);
278303
planlist=makeList2(lplan,rplan);
279304

280305
/*
@@ -315,6 +340,9 @@ generate_nonunion_plan(SetOperationStmt *op, Query *parse,
315340
break;
316341
}
317342
plan= (Plan*)make_setop(cmd,plan,sortList,length(op->colTypes)+1);
343+
344+
*sortClauses=sortList;
345+
318346
returnplan;
319347
}
320348

@@ -329,6 +357,8 @@ recurse_union_children(Node *setOp, Query *parse,
329357
SetOperationStmt*top_union,
330358
List*refnames_tlist)
331359
{
360+
List*child_sortclauses;
361+
332362
if (IsA(setOp,SetOperationStmt))
333363
{
334364
SetOperationStmt*op= (SetOperationStmt*)setOp;
@@ -359,7 +389,8 @@ recurse_union_children(Node *setOp, Query *parse,
359389
*/
360390
returnmakeList1(recurse_set_operations(setOp,parse,
361391
top_union->colTypes, false,
362-
-1,refnames_tlist));
392+
-1,refnames_tlist,
393+
&child_sortclauses));
363394
}
364395

365396
/*

‎src/include/optimizer/prep.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/optimizer/prep.h,v 1.43 2003/12/28 21:57:37 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/optimizer/prep.h,v 1.44 2004/04/07 18:17:25 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -48,7 +48,7 @@ extern List *preprocess_targetlist(List *tlist, int command_type,
4848
/*
4949
* prototypes for prepunion.c
5050
*/
51-
externPlan*plan_set_operations(Query*parse);
51+
externPlan*plan_set_operations(Query*parse,List**sortClauses);
5252

5353
externList*find_all_inheritors(Oidparentrel);
5454

0 commit comments

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