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

Commitb06fbc7

Browse files
committed
Fix performance issue with qualifications on VIEWs: outer query should
try to push restrictions on the view down into the view subquery,so that they can become indexscan quals or what-have-you rather thanbeing applied at the top level of the subquery. 7.0 and before wereable to do this, though in a much klugier way, and I'd hate to haveanyone complaining that 7.1 is stupider than 7.0 ...
1 parent7705581 commitb06fbc7

File tree

2 files changed

+97
-25
lines changed

2 files changed

+97
-25
lines changed

‎src/backend/optimizer/path/allpaths.c‎

Lines changed: 59 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -8,13 +8,14 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/allpaths.c,v 1.68 2000/12/14 22:30:43 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/allpaths.c,v 1.69 2001/01/18 07:12:37 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
1515

1616
#include"postgres.h"
1717

18+
#include"optimizer/clauses.h"
1819
#include"optimizer/cost.h"
1920
#include"optimizer/geqo.h"
2021
#include"optimizer/pathnode.h"
@@ -23,6 +24,7 @@
2324
#include"optimizer/planner.h"
2425
#include"optimizer/prep.h"
2526
#include"parser/parsetree.h"
27+
#include"rewrite/rewriteManip.h"
2628

2729

2830
boolenable_geqo= true;
@@ -99,12 +101,65 @@ set_base_rel_pathlists(Query *root)
99101
if (rel->issubquery)
100102
{
101103
/* Subquery --- generate a separate plan for it */
104+
List*upperrestrictlist;
105+
List*lst;
102106

103107
/*
104-
* XXX for now, we just apply any restrict clauses that came
105-
* from the outer query as qpquals of the SubqueryScan node.
106-
* Later, think about pushing them down into the subquery itself.
108+
* If there are any restriction clauses that have been attached
109+
* to the subquery relation, consider pushing them down to become
110+
* HAVING quals of the subquery itself. (Not WHERE clauses, since
111+
* they may refer to subquery outputs that are aggregate results.
112+
* But planner.c will transfer them into the subquery's WHERE if
113+
* they do not.) This transformation is useful because it may
114+
* allow us to generate a better plan for the subquery than
115+
* evaluating all the subquery output rows and then filtering
116+
* them.
117+
*
118+
* Currently, we do not push down clauses that contain subselects,
119+
* mainly because I'm not sure it will work correctly (the
120+
* subplan hasn't yet transformed sublinks to subselects).
121+
* Non-pushed-down clauses will get evaluated as qpquals of
122+
* the SubqueryScan node.
123+
*
124+
* XXX Are there any cases where we want to make a policy
125+
* decision not to push down, because it'd result in a worse
126+
* plan?
107127
*/
128+
upperrestrictlist=NIL;
129+
foreach(lst,rel->baserestrictinfo)
130+
{
131+
RestrictInfo*rinfo= (RestrictInfo*)lfirst(lst);
132+
Node*clause= (Node*)rinfo->clause;
133+
134+
if (contain_subplans(clause))
135+
{
136+
/* Keep it in the upper query */
137+
upperrestrictlist=lappend(upperrestrictlist,rinfo);
138+
}
139+
else
140+
{
141+
/*
142+
* We need to replace Vars in the clause (which must
143+
* refer to outputs of the subquery) with copies of the
144+
* subquery's targetlist expressions. Note that at this
145+
* point, any uplevel Vars in the clause should have been
146+
* replaced with Params, so they need no work.
147+
*/
148+
clause=ResolveNew(clause,rti,0,
149+
rte->subquery->targetList,
150+
CMD_SELECT,0);
151+
rte->subquery->havingQual=
152+
make_and_qual(rte->subquery->havingQual,
153+
clause);
154+
/*
155+
* We need not change the subquery's hasAggs or
156+
* hasSublinks flags, since we can't be pushing down
157+
* any aggregates that weren't there before, and we
158+
* don't push down subselects at all.
159+
*/
160+
}
161+
}
162+
rel->baserestrictinfo=upperrestrictlist;
108163

109164
/* Generate the plan for the subquery */
110165
rel->subplan=subquery_planner(rte->subquery,

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

Lines changed: 38 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/optimizer/plan/planner.c,v 1.98 2000/12/14 22:30:43 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/optimizer/plan/planner.c,v 1.99 2001/01/18 07:12:37 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -132,6 +132,7 @@ subquery_planner(Query *parse, double tuple_fraction)
132132
List*saved_initplan=PlannerInitPlan;
133133
intsaved_planid=PlannerPlanId;
134134
Plan*plan;
135+
List*newHaving;
135136
List*lst;
136137

137138
/* Set up for a new level of subquery */
@@ -155,20 +156,6 @@ subquery_planner(Query *parse, double tuple_fraction)
155156
parse->jointree= (FromExpr*)
156157
preprocess_jointree(parse, (Node*)parse->jointree);
157158

158-
/*
159-
* A HAVING clause without aggregates is equivalent to a WHERE clause
160-
* (except it can only refer to grouped fields). If there are no aggs
161-
* anywhere in the query, then we don't want to create an Agg plan
162-
* node, so merge the HAVING condition into WHERE.(We used to
163-
* consider this an error condition, but it seems to be legal SQL.)
164-
*/
165-
if (parse->havingQual!=NULL&& !parse->hasAggs)
166-
{
167-
parse->jointree->quals=make_and_qual(parse->jointree->quals,
168-
parse->havingQual);
169-
parse->havingQual=NULL;
170-
}
171-
172159
/*
173160
* Do expression preprocessing on targetlist and quals.
174161
*/
@@ -181,6 +168,37 @@ subquery_planner(Query *parse, double tuple_fraction)
181168
parse->havingQual=preprocess_expression(parse,parse->havingQual,
182169
EXPRKIND_HAVING);
183170

171+
/*
172+
* A HAVING clause without aggregates is equivalent to a WHERE clause
173+
* (except it can only refer to grouped fields). Transfer any agg-free
174+
* clauses of the HAVING qual into WHERE. This may seem like wasting
175+
* cycles to cater to stupidly-written queries, but there are other
176+
* reasons for doing it. Firstly, if the query contains no aggs at all,
177+
* then we aren't going to generate an Agg plan node, and so there'll be
178+
* no place to execute HAVING conditions; without this transfer, we'd
179+
* lose the HAVING condition entirely, which is wrong. Secondly, when
180+
* we push down a qual condition into a sub-query, it's easiest to push
181+
* the qual into HAVING always, in case it contains aggs, and then let
182+
* this code sort it out.
183+
*
184+
* Note that both havingQual and parse->jointree->quals are in
185+
* implicitly-ANDed-list form at this point, even though they are
186+
* declared as Node *. Also note that contain_agg_clause does not
187+
* recurse into sub-selects, which is exactly what we need here.
188+
*/
189+
newHaving=NIL;
190+
foreach(lst, (List*)parse->havingQual)
191+
{
192+
Node*havingclause= (Node*)lfirst(lst);
193+
194+
if (contain_agg_clause(havingclause))
195+
newHaving=lappend(newHaving,havingclause);
196+
else
197+
parse->jointree->quals= (Node*)
198+
lappend((List*)parse->jointree->quals,havingclause);
199+
}
200+
parse->havingQual= (Node*)newHaving;
201+
184202
/*
185203
* Do the main planning. If we have an inherited target relation,
186204
* that needs special processing, else go straight to grouping_planner.
@@ -554,12 +572,6 @@ preprocess_expression(Query *parse, Node *expr, int kind)
554572
* Check for ungrouped variables passed to subplans. Note we
555573
* do NOT do this for subplans in WHERE (or JOIN/ON); it's legal
556574
* there because WHERE is evaluated pre-GROUP.
557-
*
558-
* An interesting fine point: if subquery_planner reassigned a
559-
* HAVING qual into WHERE, then we will accept references to
560-
* ungrouped vars from subplans in the HAVING qual. This is not
561-
* entirely consistent, but it doesn't seem particularly
562-
* harmful...
563575
*/
564576
check_subplans_for_ungrouped_vars(expr,parse);
565577
}
@@ -1049,6 +1061,11 @@ grouping_planner(Query *parse, double tuple_fraction)
10491061
result_plan);
10501062
/* Note: Agg does not affect any existing sort order of the tuples */
10511063
}
1064+
else
1065+
{
1066+
/* If there are no Aggs, we shouldn't have any HAVING qual anymore */
1067+
Assert(parse->havingQual==NULL);
1068+
}
10521069

10531070
/*
10541071
* If we were not able to make the plan come out in the right order,

0 commit comments

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