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

Commit30a695d

Browse files
committed
Be smart about outer-join qualifications that mention only one side of
the join, per recent discussion on pgsql-sql. Not clear that this willcome up often in real queries, but it's not any more expensive to do itright, so we may as well do it right.
1 parenta1dbe52 commit30a695d

File tree

1 file changed

+89
-62
lines changed

1 file changed

+89
-62
lines changed

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

Lines changed: 89 additions & 62 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/initsplan.c,v 1.84 2003/02/08 20:20:54 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/optimizer/plan/initsplan.c,v 1.85 2003/03/02 23:46:34 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -36,10 +36,10 @@
3636
staticvoidmark_baserels_for_outer_join(Query*root,Relidsrels,
3737
Relidsouterrels);
3838
staticvoiddistribute_qual_to_rels(Query*root,Node*clause,
39-
boolispusheddown,
40-
boolisouterjoin,
41-
boolisdeduced,
42-
Relidsqualscope);
39+
boolispusheddown,
40+
boolisdeduced,
41+
Relidsouterjoin_nonnullable,
42+
Relidsqualscope);
4343
staticvoidadd_vars_to_targetlist(Query*root,List*vars);
4444
staticboolqual_is_redundant(Query*root,RestrictInfo*restrictinfo,
4545
List*restrictlist);
@@ -209,48 +209,53 @@ distribute_quals_to_rels(Query *root, Node *jtnode)
209209
*/
210210
foreach(qual, (List*)f->quals)
211211
distribute_qual_to_rels(root, (Node*)lfirst(qual),
212-
true, false,false,result);
212+
true, false,NULL,result);
213213
}
214214
elseif (IsA(jtnode,JoinExpr))
215215
{
216216
JoinExpr*j= (JoinExpr*)jtnode;
217217
Relidsleftids,
218-
rightids;
219-
boolisouterjoin;
218+
rightids,
219+
nonnullable_rels,
220+
nullable_rels;
220221
List*qual;
221222

222223
/*
223224
* Order of operations here is subtle and critical. First we
224225
* recurse to handle sub-JOINs. Their join quals will be placed
225226
* without regard for whether this level is an outer join, which
226-
* is correct. Then, if we are an outer join, we mark baserels
227-
* contained within the nullable side(s) with our own rel set;
228-
* this will restrict placement of subsequent quals using those
229-
* rels, including our own quals and quals above us in the join
230-
* tree. Finally we place our own join quals.
227+
* is correct. Then we place our own join quals, which are restricted
228+
* by lower outer joins in any case, and are forced to this level if
229+
* this is an outer join and they mention the outer side. Finally, if
230+
* this is an outer join, we mark baserels contained within the inner
231+
* side(s) with our own rel set; this will prevent quals above us in
232+
* the join tree that use those rels from being pushed down below this
233+
* level. (It's okay for upper quals to be pushed down to the outer
234+
* side, however.)
231235
*/
232236
leftids=distribute_quals_to_rels(root,j->larg);
233237
rightids=distribute_quals_to_rels(root,j->rarg);
234238

235239
result=bms_union(leftids,rightids);
236240

237-
isouterjoin=false;
241+
nonnullable_rels=nullable_rels=NULL;
238242
switch (j->jointype)
239243
{
240244
caseJOIN_INNER:
241245
/* Inner join adds no restrictions for quals */
242246
break;
243247
caseJOIN_LEFT:
244-
mark_baserels_for_outer_join(root,rightids,result);
245-
isouterjoin=true;
248+
nonnullable_rels=leftids;
249+
nullable_rels=rightids;
246250
break;
247251
caseJOIN_FULL:
248-
mark_baserels_for_outer_join(root,result,result);
249-
isouterjoin= true;
252+
/* each side is both outer and inner */
253+
nonnullable_rels=result;
254+
nullable_rels=result;
250255
break;
251256
caseJOIN_RIGHT:
252-
mark_baserels_for_outer_join(root,leftids,result);
253-
isouterjoin=true;
257+
nonnullable_rels=rightids;
258+
nullable_rels=leftids;
254259
break;
255260
caseJOIN_UNION:
256261

@@ -269,7 +274,11 @@ distribute_quals_to_rels(Query *root, Node *jtnode)
269274

270275
foreach(qual, (List*)j->quals)
271276
distribute_qual_to_rels(root, (Node*)lfirst(qual),
272-
false,isouterjoin, false,result);
277+
false, false,
278+
nonnullable_rels,result);
279+
280+
if (nullable_rels!=NULL)
281+
mark_baserels_for_outer_join(root,nullable_rels,result);
273282
}
274283
else
275284
elog(ERROR,"distribute_quals_to_rels: unexpected node type %d",
@@ -324,14 +333,16 @@ mark_baserels_for_outer_join(Query *root, Relids rels, Relids outerrels)
324333
* (depending on whether the clause is a join) of each base relation
325334
* mentioned in the clause.A RestrictInfo node is created and added to
326335
* the appropriate list for each rel. Also, if the clause uses a
327-
* mergejoinable operator and is not an outer-join qual, enter the left-
328-
* and right-side expressions into the query's lists of equijoined vars.
336+
* mergejoinable operator and is not delayed by outer-join rules, enter
337+
* the left- and right-side expressions into the query's lists of
338+
* equijoined vars.
329339
*
330340
* 'clause': the qual clause to be distributed
331341
* 'ispusheddown': if TRUE, force the clause to be marked 'ispusheddown'
332342
*(this indicates the clause came from a FromExpr, not a JoinExpr)
333-
* 'isouterjoin': TRUE if the qual came from an OUTER JOIN's ON-clause
334343
* 'isdeduced': TRUE if the qual came from implied-equality deduction
344+
* 'outerjoin_nonnullable': NULL if not an outer-join qual, else the set of
345+
*baserels appearing on the outer (nonnullable) side of the join
335346
* 'qualscope': set of baserels the qual's syntactic scope covers
336347
*
337348
* 'qualscope' identifies what level of JOIN the qual came from. For a top
@@ -341,8 +352,8 @@ mark_baserels_for_outer_join(Query *root, Relids rels, Relids outerrels)
341352
staticvoid
342353
distribute_qual_to_rels(Query*root,Node*clause,
343354
boolispusheddown,
344-
boolisouterjoin,
345355
boolisdeduced,
356+
Relidsouterjoin_nonnullable,
346357
Relidsqualscope)
347358
{
348359
RestrictInfo*restrictinfo=makeNode(RestrictInfo);
@@ -392,63 +403,80 @@ distribute_qual_to_rels(Query *root, Node *clause,
392403
relids=qualscope;
393404

394405
/*
395-
* For an outer-join qual, pretend that the clause references all rels
396-
* appearing within its syntactic scope, even if it really doesn't.
397-
* This ensures that the clause will be evaluated exactly at the level
398-
* of joining corresponding to the outer join.
399-
*
400-
* For a non-outer-join qual, we can evaluate the qual as soon as (1) we
401-
* have all the rels it mentions, and (2) we are at or above any outer
402-
* joins that can null any of these rels and are below the syntactic
403-
* location of the given qual.To enforce the latter, scan the base
404-
* rels listed in relids, and merge their outer-join sets into the
405-
* clause's own reference list. At the time we are called, the
406-
* outerjoinset of each baserel will show exactly those outer
407-
* joins that are below the qual in the join tree.
408-
*
409-
* If the qual came from implied-equality deduction, we can evaluate the
410-
* qual at its natural semantic level.
411-
*
406+
* Check to see if clause application must be delayed by outer-join
407+
* considerations.
412408
*/
413409
if (isdeduced)
414410
{
411+
/*
412+
* If the qual came from implied-equality deduction, we can evaluate
413+
* the qual at its natural semantic level. It is not affected by
414+
* any outer-join rules (else we'd not have decided the vars were
415+
* equal).
416+
*/
415417
Assert(bms_equal(relids,qualscope));
416418
can_be_equijoin= true;
417419
}
418-
elseif (isouterjoin)
420+
elseif (bms_overlap(relids,outerjoin_nonnullable))
419421
{
422+
/*
423+
* The qual is attached to an outer join and mentions (some of the)
424+
* rels on the nonnullable side. Force the qual to be evaluated
425+
* exactly at the level of joining corresponding to the outer join.
426+
* We cannot let it get pushed down into the nonnullable side, since
427+
* then we'd produce no output rows, rather than the intended single
428+
* null-extended row, for any nonnullable-side rows failing the qual.
429+
*
430+
* Note: an outer-join qual that mentions only nullable-side rels can
431+
* be pushed down into the nullable side without changing the join
432+
* result, so we treat it the same as an ordinary inner-join qual.
433+
*/
420434
relids=qualscope;
421435
can_be_equijoin= false;
422436
}
423437
else
424438
{
425-
/* copy to ensure we don't change caller's qualscope set */
426-
Relidsnewrelids=bms_copy(relids);
439+
/*
440+
* For a non-outer-join qual, we can evaluate the qual as soon as
441+
* (1) we have all the rels it mentions, and (2) we are at or above
442+
* any outer joins that can null any of these rels and are below the
443+
* syntactic location of the given qual. To enforce the latter, scan
444+
* the base rels listed in relids, and merge their outer-join sets
445+
* into the clause's own reference list. At the time we are called,
446+
* the outerjoinset of each baserel will show exactly those outer
447+
* joins that are below the qual in the join tree.
448+
*/
449+
Relidsaddrelids=NULL;
427450
Relidstmprelids;
428451
intrelno;
429452

430-
can_be_equijoin= true;
431453
tmprelids=bms_copy(relids);
432454
while ((relno=bms_first_member(tmprelids)) >=0)
433455
{
434456
RelOptInfo*rel=find_base_rel(root,relno);
435457

436-
if (!bms_is_subset(rel->outerjoinset,relids))
437-
{
438-
newrelids=bms_add_members(newrelids,rel->outerjoinset);
439-
440-
/*
441-
* Because application of the qual will be delayed by
442-
* outer join, we mustn't assume its vars are equal
443-
* everywhere.
444-
*/
445-
can_be_equijoin= false;
446-
}
458+
if (rel->outerjoinset!=NULL)
459+
addrelids=bms_add_members(addrelids,rel->outerjoinset);
447460
}
448461
bms_free(tmprelids);
449-
relids=newrelids;
450-
/* Should still be a subset of current scope ... */
451-
Assert(bms_is_subset(relids,qualscope));
462+
463+
if (bms_is_subset(addrelids,relids))
464+
{
465+
/* Qual is not affected by any outer-join restriction */
466+
can_be_equijoin= true;
467+
}
468+
else
469+
{
470+
relids=bms_union(relids,addrelids);
471+
/* Should still be a subset of current scope ... */
472+
Assert(bms_is_subset(relids,qualscope));
473+
/*
474+
* Because application of the qual will be delayed by outer join,
475+
* we mustn't assume its vars are equal everywhere.
476+
*/
477+
can_be_equijoin= false;
478+
}
479+
bms_free(addrelids);
452480
}
453481

454482
/*
@@ -725,8 +753,7 @@ process_implied_equality(Query *root,
725753
* taken for an original JOIN/ON clause.
726754
*/
727755
distribute_qual_to_rels(root, (Node*)clause,
728-
true, false, true,
729-
relids);
756+
true, true,NULL,relids);
730757
}
731758

732759
/*

0 commit comments

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