Switch the planner over to treating qualifications of a JOIN_SEMI join as

though it is an inner rather than outer join type.  This essentially means
that we don't bother to separate "pushed down" qual conditions from actual
join quals at a semijoin plan node; which is okay because the restrictions of
SQL syntax make it impossible to have a pushed-down qual that references the
inner side of a semijoin.  This allows noticeably better optimization of
IN/EXISTS cases than we had before, since the equivalence-class machinery can
now use those quals.  Also fix a couple of other mistakes that had essentially
disabled the ability to unique-ify the inner relation and then join it to just
a subset of the left-hand relations.  An example case using the regression
database is

select * from tenk1 a, tenk1 b
where (a.unique1,b.unique2) in (select unique1,unique2 from tenk1 c);

which is planned reasonably well by 8.3 and earlier but had been forcing a
cartesian join of a/b in CVS HEAD.

1 files changed, 17 insertions, 11 deletions

diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index d1b590d0631..1764656da38 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/optimizer/plan/initsplan.c,v 1.144 2008/10/25 19:51:32 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/optimizer/plan/initsplan.c,v 1.145 2008/11/22 22:47:06 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -851,16 +851,11 @@ distribute_qual_to_rels(PlannerInfo *root, Node *clause,
 		maybe_equivalence = false;
 		maybe_outer_join = false;
 	}
-	else if (bms_overlap(relids, outerjoin_nonnullable) &&
-			 (jointype != JOIN_SEMI ||
-			  bms_nonempty_difference(relids, outerjoin_nonnullable)))
+	else if (bms_overlap(relids, outerjoin_nonnullable))
 	{
 		/*
 		 * The qual is attached to an outer join and mentions (some of the)
-		 * rels on the nonnullable side, so it's not degenerate.  (For a
-		 * JOIN_SEMI qual, we consider it non-degenerate only if it mentions
-		 * both sides of the join --- if it mentions only one side, it can
-		 * be pushed down.)
+		 * rels on the nonnullable side, so it's not degenerate.
 		 *
 		 * We can't use such a clause to deduce equivalence (the left and
 		 * right sides might be unequal above the join because one of them has
@@ -1062,6 +1057,7 @@ distribute_sublink_quals_to_rels(PlannerInfo *root,
 	SpecialJoinInfo *sjinfo;
 	Relids		qualscope;
 	Relids		ojscope;
+	Relids		outerjoin_nonnullable;
 	ListCell   *l;
 
 	/*
@@ -1076,17 +1072,27 @@ distribute_sublink_quals_to_rels(PlannerInfo *root,
 								fslink->jointype,
 								quals);
 
+	/* Treat as inner join if SEMI, outer join if ANTI */
 	qualscope = bms_union(sjinfo->syn_lefthand, sjinfo->syn_righthand);
-	ojscope = bms_union(sjinfo->min_lefthand, sjinfo->min_righthand);
+	if (fslink->jointype == JOIN_SEMI)
+	{
+		ojscope = outerjoin_nonnullable = NULL;
+	}
+	else
+	{
+		Assert(fslink->jointype == JOIN_ANTI);
+		ojscope = bms_union(sjinfo->min_lefthand, sjinfo->min_righthand);
+		outerjoin_nonnullable = fslink->lefthand;
+	}
 
-	/* Distribute the join quals much as for a regular LEFT JOIN */
+	/* Distribute the join quals much as for a regular JOIN node */
 	foreach(l, quals)
 	{
 		Node   *qual = (Node *) lfirst(l);
 
 		distribute_qual_to_rels(root, qual,
 								false, below_outer_join, fslink->jointype,
-								qualscope, ojscope, fslink->lefthand);
+								qualscope, ojscope, outerjoin_nonnullable);
 	}
 
 	/* Now we can add the SpecialJoinInfo to join_info_list */