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

Commit40db52a

Browse files
committed
Do not push down quals into subqueries that have LIMIT/OFFSET clauses,
since the added qual could change the set of rows that get past theLIMIT. Per discussion on pgsql-sql 7/15/01.
1 parent8ba3548 commit40db52a

File tree

1 file changed

+104
-89
lines changed

1 file changed

+104
-89
lines changed

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

Lines changed: 104 additions & 89 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/allpaths.c,v 1.76 2001/06/05 17:13:51 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/allpaths.c,v 1.77 2001/07/16 17:57:02 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -37,6 +37,8 @@ static void set_plain_rel_pathlist(Query *root, RelOptInfo *rel,
3737
staticvoidset_inherited_rel_pathlist(Query*root,RelOptInfo*rel,
3838
Indexrti,RangeTblEntry*rte,
3939
List*inheritlist);
40+
staticvoidset_subquery_pathlist(Query*root,RelOptInfo*rel,
41+
Indexrti,RangeTblEntry*rte);
4042
staticRelOptInfo*make_one_rel_by_joins(Query*root,intlevels_needed,
4143
List*initial_rels);
4244

@@ -101,94 +103,7 @@ set_base_rel_pathlists(Query *root)
101103
if (rel->issubquery)
102104
{
103105
/* Subquery --- generate a separate plan for it */
104-
105-
/*
106-
* If there are any restriction clauses that have been
107-
* attached to the subquery relation, consider pushing them
108-
* down to become HAVING quals of the subquery itself.(Not
109-
* WHERE clauses, since they may refer to subquery outputs
110-
* that are aggregate results. But planner.c will transfer
111-
* them into the subquery's WHERE if they do not.) This
112-
* transformation is useful because it may allow us to
113-
* generate a better plan for the subquery than evaluating all
114-
* the subquery output rows and then filtering them.
115-
*
116-
* Currently, we do not push down clauses that contain
117-
* subselects, mainly because I'm not sure it will work
118-
* correctly (the subplan hasn't yet transformed sublinks to
119-
* subselects). Also, if the subquery contains set ops
120-
* (UNION/INTERSECT/EXCEPT) we do not push down any qual
121-
* clauses, since the planner doesn't support quals at the top
122-
* level of a setop. (With suitable analysis we could try to
123-
* push the quals down into the component queries of the
124-
* setop, but getting it right is not trivial.)
125-
* Non-pushed-down clauses will get evaluated as qpquals of
126-
* the SubqueryScan node.
127-
*
128-
* XXX Are there any cases where we want to make a policy
129-
* decision not to push down, because it'd result in a worse
130-
* plan?
131-
*/
132-
if (rte->subquery->setOperations==NULL)
133-
{
134-
/* OK to consider pushing down individual quals */
135-
List*upperrestrictlist=NIL;
136-
List*lst;
137-
138-
foreach(lst,rel->baserestrictinfo)
139-
{
140-
RestrictInfo*rinfo= (RestrictInfo*)lfirst(lst);
141-
Node*clause= (Node*)rinfo->clause;
142-
143-
if (contain_subplans(clause))
144-
{
145-
/* Keep it in the upper query */
146-
upperrestrictlist=lappend(upperrestrictlist,rinfo);
147-
}
148-
else
149-
{
150-
151-
/*
152-
* We need to replace Vars in the clause (which
153-
* must refer to outputs of the subquery) with
154-
* copies of the subquery's targetlist
155-
* expressions. Note that at this point, any
156-
* uplevel Vars in the clause should have been
157-
* replaced with Params, so they need no work.
158-
*/
159-
clause=ResolveNew(clause,rti,0,
160-
rte->subquery->targetList,
161-
CMD_SELECT,0);
162-
rte->subquery->havingQual=
163-
make_and_qual(rte->subquery->havingQual,
164-
clause);
165-
166-
/*
167-
* We need not change the subquery's hasAggs or
168-
* hasSublinks flags, since we can't be pushing
169-
* down any aggregates that weren't there before,
170-
* and we don't push down subselects at all.
171-
*/
172-
}
173-
}
174-
rel->baserestrictinfo=upperrestrictlist;
175-
}
176-
177-
/* Generate the plan for the subquery */
178-
rel->subplan=subquery_planner(rte->subquery,
179-
-1.0/* default case */ );
180-
181-
/* Copy number of output rows from subplan */
182-
rel->tuples=rel->subplan->plan_rows;
183-
184-
/* Mark rel with estimated output rows, width, etc */
185-
set_baserel_size_estimates(root,rel);
186-
187-
/* Generate appropriate path */
188-
add_path(rel,create_subqueryscan_path(rel));
189-
190-
/* Select cheapest path (pretty easy in this case...) */
191-
set_cheapest(rel);
106+
set_subquery_pathlist(root,rel,rti,rte);
192107
}
193108
elseif ((inheritlist=expand_inherted_rtentry(root,rti, true))
194109
!=NIL)
@@ -353,6 +268,106 @@ set_inherited_rel_pathlist(Query *root, RelOptInfo *rel,
353268
set_cheapest(rel);
354269
}
355270

271+
/*
272+
* set_subquery_pathlist
273+
*Build the (single) access path for a subquery RTE
274+
*/
275+
staticvoid
276+
set_subquery_pathlist(Query*root,RelOptInfo*rel,
277+
Indexrti,RangeTblEntry*rte)
278+
{
279+
Query*subquery=rte->subquery;
280+
281+
/*
282+
* If there are any restriction clauses that have been attached to the
283+
* subquery relation, consider pushing them down to become HAVING quals
284+
* of the subquery itself. (Not WHERE clauses, since they may refer to
285+
* subquery outputs that are aggregate results. But planner.c will
286+
* transfer them into the subquery's WHERE if they do not.) This
287+
* transformation is useful because it may allow us to generate a better
288+
* plan for the subquery than evaluating all the subquery output rows
289+
* and then filtering them.
290+
*
291+
* There are several cases where we cannot push down clauses:
292+
*
293+
* 1. If the subquery contains set ops (UNION/INTERSECT/EXCEPT) we do not
294+
* push down any qual clauses, since the planner doesn't support quals at
295+
* the top level of a setop. (With suitable analysis we could try to push
296+
* the quals down into the component queries of the setop, but getting it
297+
* right seems nontrivial. Work on this later.)
298+
*
299+
* 2. If the subquery has a LIMIT clause we must not push down any quals,
300+
* since that could change the set of rows returned.
301+
*
302+
* 3. We do not push down clauses that contain subselects, mainly because
303+
* I'm not sure it will work correctly (the subplan hasn't yet transformed
304+
* sublinks to subselects).
305+
*
306+
* Non-pushed-down clauses will get evaluated as qpquals of the
307+
* SubqueryScan node.
308+
*
309+
* XXX Are there any cases where we want to make a policy decision not to
310+
* push down, because it'd result in a worse plan?
311+
*/
312+
if (subquery->setOperations==NULL&&
313+
subquery->limitOffset==NULL&&
314+
subquery->limitCount==NULL)
315+
{
316+
/* OK to consider pushing down individual quals */
317+
List*upperrestrictlist=NIL;
318+
List*lst;
319+
320+
foreach(lst,rel->baserestrictinfo)
321+
{
322+
RestrictInfo*rinfo= (RestrictInfo*)lfirst(lst);
323+
Node*clause= (Node*)rinfo->clause;
324+
325+
if (contain_subplans(clause))
326+
{
327+
/* Keep it in the upper query */
328+
upperrestrictlist=lappend(upperrestrictlist,rinfo);
329+
}
330+
else
331+
{
332+
/*
333+
* We need to replace Vars in the clause (which must refer to
334+
* outputs of the subquery) with copies of the subquery's
335+
* targetlist expressions. Note that at this point, any
336+
* uplevel Vars in the clause should have been replaced with
337+
* Params, so they need no work.
338+
*/
339+
clause=ResolveNew(clause,rti,0,
340+
subquery->targetList,
341+
CMD_SELECT,0);
342+
subquery->havingQual=make_and_qual(subquery->havingQual,
343+
clause);
344+
/*
345+
* We need not change the subquery's hasAggs or
346+
* hasSublinks flags, since we can't be pushing
347+
* down any aggregates that weren't there before,
348+
* and we don't push down subselects at all.
349+
*/
350+
}
351+
}
352+
rel->baserestrictinfo=upperrestrictlist;
353+
}
354+
355+
/* Generate the plan for the subquery */
356+
rel->subplan=subquery_planner(subquery,
357+
-1.0/* default case */ );
358+
359+
/* Copy number of output rows from subplan */
360+
rel->tuples=rel->subplan->plan_rows;
361+
362+
/* Mark rel with estimated output rows, width, etc */
363+
set_baserel_size_estimates(root,rel);
364+
365+
/* Generate appropriate path */
366+
add_path(rel,create_subqueryscan_path(rel));
367+
368+
/* Select cheapest path (pretty easy in this case...) */
369+
set_cheapest(rel);
370+
}
356371

357372
/*
358373
* make_fromexpr_rel

0 commit comments

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