From f18c57fdf1dd255cc26c4b6b3d6716c8a54ab890 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 12 Dec 2006 21:31:02 +0000 Subject: [PATCH] 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 be created. The normal heuristic to avoid joins with no joinclause has to be overridden in that case. Problem is new in 8.2; before that we forced the outer join order anyway. Per example from Teodor. --- src/backend/optimizer/geqo/geqo_eval.c | 4 ++-- src/backend/optimizer/path/joinrels.c | 15 ++++++++----- src/backend/optimizer/util/joininfo.c | 39 ++++++++++++++++++++++++++++++++-- src/include/optimizer/joininfo.h | 5 +++-- 4 files changed, 52 insertions(+), 11 deletions(-) diff --git a/src/backend/optimizer/geqo/geqo_eval.c b/src/backend/optimizer/geqo/geqo_eval.c index 240672edaf..197efac434 100644 --- a/src/backend/optimizer/geqo/geqo_eval.c +++ b/src/backend/optimizer/geqo/geqo_eval.c @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/backend/optimizer/geqo/geqo_eval.c,v 1.81 2006/10/24 17:50:22 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/geqo/geqo_eval.c,v 1.82 2006/12/12 21:31:02 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -258,7 +258,7 @@ desirable_join(PlannerInfo *root, /* * Join if there is an applicable join clause. */ - if (have_relevant_joinclause(outer_rel, inner_rel)) + if (have_relevant_joinclause(root, outer_rel, inner_rel)) return true; /* diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c index 17b5f31915..9801795994 100644 --- a/src/backend/optimizer/path/joinrels.c +++ b/src/backend/optimizer/path/joinrels.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/optimizer/path/joinrels.c,v 1.81 2006/10/24 17:50:22 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/path/joinrels.c,v 1.82 2006/12/12 21:31:02 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -147,8 +147,13 @@ make_rels_by_joins(PlannerInfo *root, int level, List **joinrels) ListCell *other_rels; ListCell *r2; - if (old_rel->joininfo == NIL) - continue; /* we ignore clauseless joins here */ + /* + * We can ignore clauseless joins here, *except* when there are + * outer joins --- then we might have to force a bushy outer + * join. See have_relevant_joinclause(). + */ + if (old_rel->joininfo == NIL && root->oj_info_list == NIL) + continue; if (k == other_level) other_rels = lnext(r); /* only consider remaining rels */ @@ -166,7 +171,7 @@ make_rels_by_joins(PlannerInfo *root, int level, List **joinrels) * pair of rels. Do so if there is at least one usable * join clause. */ - if (have_relevant_joinclause(old_rel, new_rel)) + if (have_relevant_joinclause(root, old_rel, new_rel)) { RelOptInfo *jrel; @@ -270,7 +275,7 @@ make_rels_by_clause_joins(PlannerInfo *root, RelOptInfo *other_rel = (RelOptInfo *) lfirst(l); if (!bms_overlap(old_rel->relids, other_rel->relids) && - have_relevant_joinclause(old_rel, other_rel)) + have_relevant_joinclause(root, old_rel, other_rel)) { RelOptInfo *jrel; diff --git a/src/backend/optimizer/util/joininfo.c b/src/backend/optimizer/util/joininfo.c index 9aab37753d..bd54a1384b 100644 --- a/src/backend/optimizer/util/joininfo.c +++ b/src/backend/optimizer/util/joininfo.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/optimizer/util/joininfo.c,v 1.44 2006/03/05 15:58:31 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/util/joininfo.c,v 1.45 2006/12/12 21:31:02 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -24,7 +24,8 @@ * the two given relations. */ bool -have_relevant_joinclause(RelOptInfo *rel1, RelOptInfo *rel2) +have_relevant_joinclause(PlannerInfo *root, + RelOptInfo *rel1, RelOptInfo *rel2) { bool result = false; Relids join_relids; @@ -53,6 +54,40 @@ have_relevant_joinclause(RelOptInfo *rel1, RelOptInfo *rel2) } } + /* + * It's possible that the rels correspond to the left and right sides + * of a degenerate outer join, that is, one with no joinclause mentioning + * the non-nullable side. The above scan will then have failed to locate + * any joinclause indicating we should join, but nonetheless we must + * allow the join to occur. + * + * Note: we need no comparable check for IN-joins because we can handle + * sequential buildup of an IN-join to multiple outer-side rels; therefore + * the "last ditch" case in make_rels_by_joins() always succeeds. We + * could dispense with this hack if we were willing to try bushy plans + * in the "last ditch" case, but that seems too expensive. + */ + if (!result) + { + foreach(l, root->oj_info_list) + { + OuterJoinInfo *ojinfo = (OuterJoinInfo *) lfirst(l); + + /* ignore full joins --- other mechanisms handle them */ + if (ojinfo->is_full_join) + continue; + + if ((bms_is_subset(ojinfo->min_lefthand, rel1->relids) && + bms_is_subset(ojinfo->min_righthand, rel2->relids)) || + (bms_is_subset(ojinfo->min_lefthand, rel2->relids) && + bms_is_subset(ojinfo->min_righthand, rel1->relids))) + { + result = true; + break; + } + } + } + bms_free(join_relids); return result; diff --git a/src/include/optimizer/joininfo.h b/src/include/optimizer/joininfo.h index 7c4909ec1c..1480f77835 100644 --- a/src/include/optimizer/joininfo.h +++ b/src/include/optimizer/joininfo.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/optimizer/joininfo.h,v 1.31 2006/03/05 15:58:57 momjian Exp $ + * $PostgreSQL: pgsql/src/include/optimizer/joininfo.h,v 1.32 2006/12/12 21:31:02 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -17,7 +17,8 @@ #include "nodes/relation.h" -extern bool have_relevant_joinclause(RelOptInfo *rel1, RelOptInfo *rel2); +extern bool have_relevant_joinclause(PlannerInfo *root, + RelOptInfo *rel1, RelOptInfo *rel2); extern void add_join_clause_to_rels(PlannerInfo *root, RestrictInfo *restrictinfo, -- 2.11.0