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

Commitf18c57f

Browse files
committed
Fix planner to do the right thing when a degenerate outer join (one whose
joinclause doesn't use any outer-side vars) requires a "bushy" plan to becreated. The normal heuristic to avoid joins with no joinclause has to beoverridden in that case. Problem is new in 8.2; before that we forced theouter join order anyway. Per example from Teodor.
1 parent0afabec commitf18c57f

File tree

4 files changed

+52
-11
lines changed

4 files changed

+52
-11
lines changed

‎src/backend/optimizer/geqo/geqo_eval.c‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
9-
* $PostgreSQL: pgsql/src/backend/optimizer/geqo/geqo_eval.c,v 1.81 2006/10/24 17:50:22 tgl Exp $
9+
* $PostgreSQL: pgsql/src/backend/optimizer/geqo/geqo_eval.c,v 1.82 2006/12/12 21:31:02 tgl Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -258,7 +258,7 @@ desirable_join(PlannerInfo *root,
258258
/*
259259
* Join if there is an applicable join clause.
260260
*/
261-
if (have_relevant_joinclause(outer_rel,inner_rel))
261+
if (have_relevant_joinclause(root,outer_rel,inner_rel))
262262
return true;
263263

264264
/*

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

Lines changed: 10 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/path/joinrels.c,v 1.81 2006/10/24 17:50:22 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/path/joinrels.c,v 1.82 2006/12/12 21:31:02 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -147,8 +147,13 @@ make_rels_by_joins(PlannerInfo *root, int level, List **joinrels)
147147
ListCell*other_rels;
148148
ListCell*r2;
149149

150-
if (old_rel->joininfo==NIL)
151-
continue;/* we ignore clauseless joins here */
150+
/*
151+
* We can ignore clauseless joins here, *except* when there are
152+
* outer joins --- then we might have to force a bushy outer
153+
* join. See have_relevant_joinclause().
154+
*/
155+
if (old_rel->joininfo==NIL&&root->oj_info_list==NIL)
156+
continue;
152157

153158
if (k==other_level)
154159
other_rels=lnext(r);/* only consider remaining rels */
@@ -166,7 +171,7 @@ make_rels_by_joins(PlannerInfo *root, int level, List **joinrels)
166171
* pair of rels. Do so if there is at least one usable
167172
* join clause.
168173
*/
169-
if (have_relevant_joinclause(old_rel,new_rel))
174+
if (have_relevant_joinclause(root,old_rel,new_rel))
170175
{
171176
RelOptInfo*jrel;
172177

@@ -270,7 +275,7 @@ make_rels_by_clause_joins(PlannerInfo *root,
270275
RelOptInfo*other_rel= (RelOptInfo*)lfirst(l);
271276

272277
if (!bms_overlap(old_rel->relids,other_rel->relids)&&
273-
have_relevant_joinclause(old_rel,other_rel))
278+
have_relevant_joinclause(root,old_rel,other_rel))
274279
{
275280
RelOptInfo*jrel;
276281

‎src/backend/optimizer/util/joininfo.c‎

Lines changed: 37 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/util/joininfo.c,v 1.44 2006/03/05 15:58:31 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/util/joininfo.c,v 1.45 2006/12/12 21:31:02 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -24,7 +24,8 @@
2424
*the two given relations.
2525
*/
2626
bool
27-
have_relevant_joinclause(RelOptInfo*rel1,RelOptInfo*rel2)
27+
have_relevant_joinclause(PlannerInfo*root,
28+
RelOptInfo*rel1,RelOptInfo*rel2)
2829
{
2930
boolresult= false;
3031
Relidsjoin_relids;
@@ -53,6 +54,40 @@ have_relevant_joinclause(RelOptInfo *rel1, RelOptInfo *rel2)
5354
}
5455
}
5556

57+
/*
58+
* It's possible that the rels correspond to the left and right sides
59+
* of a degenerate outer join, that is, one with no joinclause mentioning
60+
* the non-nullable side. The above scan will then have failed to locate
61+
* any joinclause indicating we should join, but nonetheless we must
62+
* allow the join to occur.
63+
*
64+
* Note: we need no comparable check for IN-joins because we can handle
65+
* sequential buildup of an IN-join to multiple outer-side rels; therefore
66+
* the "last ditch" case in make_rels_by_joins() always succeeds. We
67+
* could dispense with this hack if we were willing to try bushy plans
68+
* in the "last ditch" case, but that seems too expensive.
69+
*/
70+
if (!result)
71+
{
72+
foreach(l,root->oj_info_list)
73+
{
74+
OuterJoinInfo*ojinfo= (OuterJoinInfo*)lfirst(l);
75+
76+
/* ignore full joins --- other mechanisms handle them */
77+
if (ojinfo->is_full_join)
78+
continue;
79+
80+
if ((bms_is_subset(ojinfo->min_lefthand,rel1->relids)&&
81+
bms_is_subset(ojinfo->min_righthand,rel2->relids))||
82+
(bms_is_subset(ojinfo->min_lefthand,rel2->relids)&&
83+
bms_is_subset(ojinfo->min_righthand,rel1->relids)))
84+
{
85+
result= true;
86+
break;
87+
}
88+
}
89+
}
90+
5691
bms_free(join_relids);
5792

5893
returnresult;

‎src/include/optimizer/joininfo.h‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/optimizer/joininfo.h,v 1.31 2006/03/05 15:58:57 momjian Exp $
10+
* $PostgreSQL: pgsql/src/include/optimizer/joininfo.h,v 1.32 2006/12/12 21:31:02 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -17,7 +17,8 @@
1717
#include"nodes/relation.h"
1818

1919

20-
externboolhave_relevant_joinclause(RelOptInfo*rel1,RelOptInfo*rel2);
20+
externboolhave_relevant_joinclause(PlannerInfo*root,
21+
RelOptInfo*rel1,RelOptInfo*rel2);
2122

2223
externvoidadd_join_clause_to_rels(PlannerInfo*root,
2324
RestrictInfo*restrictinfo,

0 commit comments

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