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

Commit0816fad

Browse files
committed
Undo 8.4-era lobotomization of subquery pullup rules.
After the planner was fixed to convert some IN/EXISTS subqueries intosemijoins or antijoins, we had to prevent it from doing that in somecases where the plans risked getting much worse. The reason the plansgot worse was that in the unoptimized implementation, subqueries couldreference parameters from the outer query at any join level, and sofull table scans could be avoided even if they were one or more levelsof join below where the semi/anti join would be. Now that we havesufficient mechanism in the planner to handle such cases properly,it should no longer be necessary to play dumb here.This reverts commits07b9936 andcd1f0d0. The latter was a stopgapfix that wasn't really sufficiently analyzed at the time. Ratherthan just restricting ourselves to cases where the new join can bestacked on the right-hand input, we should also consider whether itcan be stacked on the left-hand input.
1 parente2fa76d commit0816fad

File tree

1 file changed

+149
-69
lines changed

1 file changed

+149
-69
lines changed

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

Lines changed: 149 additions & 69 deletions
Original file line numberDiff line numberDiff line change
@@ -58,7 +58,8 @@ typedef struct reduce_outer_joins_state
5858
staticNode*pull_up_sublinks_jointree_recurse(PlannerInfo*root,Node*jtnode,
5959
Relids*relids);
6060
staticNode*pull_up_sublinks_qual_recurse(PlannerInfo*root,Node*node,
61-
Relidsavailable_rels,Node**jtlink);
61+
Node**jtlink1,Relidsavailable_rels1,
62+
Node**jtlink2,Relidsavailable_rels2);
6263
staticNode*pull_up_simple_subquery(PlannerInfo*root,Node*jtnode,
6364
RangeTblEntry*rte,
6465
JoinExpr*lowest_outer_join,
@@ -192,8 +193,9 @@ pull_up_sublinks_jointree_recurse(PlannerInfo *root, Node *jtnode,
192193
/* Set up a link representing the rebuilt jointree */
193194
jtlink= (Node*)newf;
194195
/* Now process qual --- all children are available for use */
195-
newf->quals=pull_up_sublinks_qual_recurse(root,f->quals,frelids,
196-
&jtlink);
196+
newf->quals=pull_up_sublinks_qual_recurse(root,f->quals,
197+
&jtlink,frelids,
198+
NULL,NULL);
197199

198200
/*
199201
* Note that the result will be either newf, or a stack of JoinExprs
@@ -237,43 +239,32 @@ pull_up_sublinks_jointree_recurse(PlannerInfo *root, Node *jtnode,
237239
* point of the available_rels machinations is to ensure that we only
238240
* pull up quals for which that's okay.
239241
*
240-
* XXX for the moment, we refrain from pulling up IN/EXISTS clauses
241-
* appearing in LEFT or RIGHT join conditions.Although it is
242-
* semantically valid to do so under the above conditions, we end up
243-
* with a query in which the semijoin or antijoin must be evaluated
244-
* below the outer join, which could perform far worse than leaving it
245-
* as a sublink that is executed only for row pairs that meet the
246-
* other join conditions. Fixing this seems to require considerable
247-
* restructuring of the executor, but maybe someday it can happen.
248-
* (See also the comparable case in pull_up_sublinks_qual_recurse.)
249-
*
250242
* We don't expect to see any pre-existing JOIN_SEMI or JOIN_ANTI
251243
* nodes here.
252244
*/
253245
switch (j->jointype)
254246
{
255247
caseJOIN_INNER:
256248
j->quals=pull_up_sublinks_qual_recurse(root,j->quals,
249+
&jtlink,
257250
bms_union(leftrelids,
258251
rightrelids),
259-
&jtlink);
252+
NULL,NULL);
260253
break;
261254
caseJOIN_LEFT:
262-
#ifdefNOT_USED/* see XXX comment above */
263255
j->quals=pull_up_sublinks_qual_recurse(root,j->quals,
256+
&j->rarg,
264257
rightrelids,
265-
&j->rarg);
266-
#endif
258+
NULL,NULL);
267259
break;
268260
caseJOIN_FULL:
269261
/* can't do anything with full-join quals */
270262
break;
271263
caseJOIN_RIGHT:
272-
#ifdefNOT_USED/* see XXX comment above */
273264
j->quals=pull_up_sublinks_qual_recurse(root,j->quals,
265+
&j->larg,
274266
leftrelids,
275-
&j->larg);
276-
#endif
267+
NULL,NULL);
277268
break;
278269
default:
279270
elog(ERROR,"unrecognized join type: %d",
@@ -303,14 +294,22 @@ pull_up_sublinks_jointree_recurse(PlannerInfo *root, Node *jtnode,
303294
/*
304295
* Recurse through top-level qual nodes for pull_up_sublinks()
305296
*
306-
* jtlink points to the link in the jointree where any new JoinExprs should be
307-
* inserted. If we find multiple pull-up-able SubLinks, they'll get stacked
308-
* there in the order we encounter them. We rely on subsequent optimization
309-
* to rearrange the stack if appropriate.
297+
* jtlink1 points to the link in the jointree where any new JoinExprs should
298+
* be inserted if they reference available_rels1 (i.e., available_rels1
299+
* denotes the relations present underneath jtlink1). Optionally, jtlink2 can
300+
* point to a second link where new JoinExprs should be inserted if they
301+
* reference available_rels2 (pass NULL for both those arguments if not used).
302+
* Note that SubLinks referencing both sets of variables cannot be optimized.
303+
* If we find multiple pull-up-able SubLinks, they'll get stacked onto jtlink1
304+
* and/or jtlink2 in the order we encounter them. We rely on subsequent
305+
* optimization to rearrange the stack if appropriate.
306+
*
307+
* Returns the replacement qual node, or NULL if the qual should be removed.
310308
*/
311309
staticNode*
312310
pull_up_sublinks_qual_recurse(PlannerInfo*root,Node*node,
313-
Relidsavailable_rels,Node**jtlink)
311+
Node**jtlink1,Relidsavailable_rels1,
312+
Node**jtlink2,Relidsavailable_rels2)
314313
{
315314
if (node==NULL)
316315
returnNULL;
@@ -323,45 +322,105 @@ pull_up_sublinks_qual_recurse(PlannerInfo *root, Node *node,
323322
/* Is it a convertible ANY or EXISTS clause? */
324323
if (sublink->subLinkType==ANY_SUBLINK)
325324
{
326-
j=convert_ANY_sublink_to_join(root,sublink,
327-
available_rels);
328-
if (j)
325+
if ((j=convert_ANY_sublink_to_join(root,sublink,
326+
available_rels1))!=NULL)
327+
{
328+
/* Yes; insert the new join node into the join tree */
329+
j->larg=*jtlink1;
330+
*jtlink1= (Node*)j;
331+
/* Recursively process pulled-up jointree nodes */
332+
j->rarg=pull_up_sublinks_jointree_recurse(root,
333+
j->rarg,
334+
&child_rels);
335+
/*
336+
* Now recursively process the pulled-up quals. Any inserted
337+
* joins can get stacked onto either j->larg or j->rarg,
338+
* depending on which rels they reference.
339+
*/
340+
j->quals=pull_up_sublinks_qual_recurse(root,
341+
j->quals,
342+
&j->larg,
343+
available_rels1,
344+
&j->rarg,
345+
child_rels);
346+
/* Return NULL representing constant TRUE */
347+
returnNULL;
348+
}
349+
if (available_rels2!=NULL&&
350+
(j=convert_ANY_sublink_to_join(root,sublink,
351+
available_rels2))!=NULL)
329352
{
330-
/* Yes; recursively process what we pulled up */
353+
/* Yes; insert the new join node into the join tree */
354+
j->larg=*jtlink2;
355+
*jtlink2= (Node*)j;
356+
/* Recursively process pulled-up jointree nodes */
331357
j->rarg=pull_up_sublinks_jointree_recurse(root,
332358
j->rarg,
333359
&child_rels);
334-
/* Any inserted joins get stacked onto j->rarg */
360+
/*
361+
* Now recursively process the pulled-up quals. Any inserted
362+
* joins can get stacked onto either j->larg or j->rarg,
363+
* depending on which rels they reference.
364+
*/
335365
j->quals=pull_up_sublinks_qual_recurse(root,
336366
j->quals,
337-
child_rels,
338-
&j->rarg);
339-
/* Now insert the new join node into the join tree */
340-
j->larg=*jtlink;
341-
*jtlink= (Node*)j;
342-
/* and return NULL representing constant TRUE */
367+
&j->larg,
368+
available_rels2,
369+
&j->rarg,
370+
child_rels);
371+
/* Return NULL representing constant TRUE */
343372
returnNULL;
344373
}
345374
}
346375
elseif (sublink->subLinkType==EXISTS_SUBLINK)
347376
{
348-
j=convert_EXISTS_sublink_to_join(root,sublink, false,
349-
available_rels);
350-
if (j)
377+
if ((j=convert_EXISTS_sublink_to_join(root,sublink, false,
378+
available_rels1))!=NULL)
351379
{
352-
/* Yes; recursively process what we pulled up */
380+
/* Yes; insert the new join node into the join tree */
381+
j->larg=*jtlink1;
382+
*jtlink1= (Node*)j;
383+
/* Recursively process pulled-up jointree nodes */
353384
j->rarg=pull_up_sublinks_jointree_recurse(root,
354385
j->rarg,
355386
&child_rels);
356-
/* Any inserted joins get stacked onto j->rarg */
387+
/*
388+
* Now recursively process the pulled-up quals. Any inserted
389+
* joins can get stacked onto either j->larg or j->rarg,
390+
* depending on which rels they reference.
391+
*/
357392
j->quals=pull_up_sublinks_qual_recurse(root,
358393
j->quals,
359-
child_rels,
360-
&j->rarg);
361-
/* Now insert the new join node into the join tree */
362-
j->larg=*jtlink;
363-
*jtlink= (Node*)j;
364-
/* and return NULL representing constant TRUE */
394+
&j->larg,
395+
available_rels1,
396+
&j->rarg,
397+
child_rels);
398+
/* Return NULL representing constant TRUE */
399+
returnNULL;
400+
}
401+
if (available_rels2!=NULL&&
402+
(j=convert_EXISTS_sublink_to_join(root,sublink, false,
403+
available_rels2))!=NULL)
404+
{
405+
/* Yes; insert the new join node into the join tree */
406+
j->larg=*jtlink2;
407+
*jtlink2= (Node*)j;
408+
/* Recursively process pulled-up jointree nodes */
409+
j->rarg=pull_up_sublinks_jointree_recurse(root,
410+
j->rarg,
411+
&child_rels);
412+
/*
413+
* Now recursively process the pulled-up quals. Any inserted
414+
* joins can get stacked onto either j->larg or j->rarg,
415+
* depending on which rels they reference.
416+
*/
417+
j->quals=pull_up_sublinks_qual_recurse(root,
418+
j->quals,
419+
&j->larg,
420+
available_rels2,
421+
&j->rarg,
422+
child_rels);
423+
/* Return NULL representing constant TRUE */
365424
returnNULL;
366425
}
367426
}
@@ -373,40 +432,59 @@ pull_up_sublinks_qual_recurse(PlannerInfo *root, Node *node,
373432
/* If the immediate argument of NOT is EXISTS, try to convert */
374433
SubLink*sublink= (SubLink*)get_notclausearg((Expr*)node);
375434
JoinExpr*j;
435+
Relidschild_rels;
376436

377437
if (sublink&&IsA(sublink,SubLink))
378438
{
379439
if (sublink->subLinkType==EXISTS_SUBLINK)
380440
{
381-
j=convert_EXISTS_sublink_to_join(root,sublink, true,
382-
available_rels);
383-
if (j)
441+
if ((j=convert_EXISTS_sublink_to_join(root,sublink, true,
442+
available_rels1))!=NULL)
384443
{
444+
/* Yes; insert the new join node into the join tree */
445+
j->larg=*jtlink1;
446+
*jtlink1= (Node*)j;
447+
/* Recursively process pulled-up jointree nodes */
448+
j->rarg=pull_up_sublinks_jointree_recurse(root,
449+
j->rarg,
450+
&child_rels);
385451
/*
386-
* For the moment, refrain from recursing underneath NOT.
387-
* As in pull_up_sublinks_jointree_recurse, recursing here
388-
* would result in inserting a join underneath an ANTI
389-
* join with which it could not commute, and that could
390-
* easily lead to a worse plan than what we've
391-
* historically generated.
452+
* Now recursively process the pulled-up quals. Because
453+
* we are underneath a NOT, we can't pull up sublinks
454+
* that reference the left-hand stuff, but it's still
455+
* okay to pull up sublinks referencing j->rarg.
392456
*/
393-
#ifdefNOT_USED
394-
/* Yes; recursively process what we pulled up */
395-
Relidschild_rels;
396-
457+
j->quals=pull_up_sublinks_qual_recurse(root,
458+
j->quals,
459+
&j->rarg,
460+
child_rels,
461+
NULL,NULL);
462+
/* Return NULL representing constant TRUE */
463+
returnNULL;
464+
}
465+
if (available_rels2!=NULL&&
466+
(j=convert_EXISTS_sublink_to_join(root,sublink, true,
467+
available_rels2))!=NULL)
468+
{
469+
/* Yes; insert the new join node into the join tree */
470+
j->larg=*jtlink2;
471+
*jtlink2= (Node*)j;
472+
/* Recursively process pulled-up jointree nodes */
397473
j->rarg=pull_up_sublinks_jointree_recurse(root,
398474
j->rarg,
399475
&child_rels);
400-
/* Any inserted joins get stacked onto j->rarg */
476+
/*
477+
* Now recursively process the pulled-up quals. Because
478+
* we are underneath a NOT, we can't pull up sublinks
479+
* that reference the left-hand stuff, but it's still
480+
* okay to pull up sublinks referencing j->rarg.
481+
*/
401482
j->quals=pull_up_sublinks_qual_recurse(root,
402483
j->quals,
484+
&j->rarg,
403485
child_rels,
404-
&j->rarg);
405-
#endif
406-
/* Now insert the new join node into the join tree */
407-
j->larg=*jtlink;
408-
*jtlink= (Node*)j;
409-
/* and return NULL representing constant TRUE */
486+
NULL,NULL);
487+
/* Return NULL representing constant TRUE */
410488
returnNULL;
411489
}
412490
}
@@ -427,8 +505,10 @@ pull_up_sublinks_qual_recurse(PlannerInfo *root, Node *node,
427505

428506
newclause=pull_up_sublinks_qual_recurse(root,
429507
oldclause,
430-
available_rels,
431-
jtlink);
508+
jtlink1,
509+
available_rels1,
510+
jtlink2,
511+
available_rels2);
432512
if (newclause)
433513
newclauses=lappend(newclauses,newclause);
434514
}

0 commit comments

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