Restructure planning of nestloop inner indexscans so that the set of usable

joinclauses is determined accurately for each join.  Formerly, the code only
considered joinclauses that used all of the rels from the outer side of the
join; thus for example
	FROM (a CROSS JOIN b) JOIN c ON (c.f1 = a.x AND c.f2 = b.y)
could not exploit a two-column index on c(f1,f2), since neither of the
qual clauses would be in the joininfo list it looked in.  The new code does
this correctly, and also is able to eliminate redundant clauses, thus fixing
the problem noted 24-Oct-02 by Hans-Jürgen Schönig.

1 files changed, 3 insertions, 68 deletions

diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index 8e73bd2f419..ac5d4a72d45 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/optimizer/path/joinpath.c,v 1.71 2002/09/04 20:31:20 momjian Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/optimizer/path/joinpath.c,v 1.72 2002/11/24 21:52:14 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -42,8 +42,6 @@ static void match_unsorted_inner(Query *root, RelOptInfo *joinrel,
 static void hash_inner_and_outer(Query *root, RelOptInfo *joinrel,
 					 RelOptInfo *outerrel, RelOptInfo *innerrel,
 					 List *restrictlist, JoinType jointype);
-static Path *best_innerjoin(List *join_paths, List *outer_relid,
-			   JoinType jointype);
 static List *select_mergejoin_clauses(RelOptInfo *joinrel,
 						 RelOptInfo *outerrel,
 						 RelOptInfo *innerrel,
@@ -351,8 +349,8 @@ match_unsorted_outer(Query *root,
 	 * Get the best innerjoin indexpath (if any) for this outer rel. It's
 	 * the same for all outer paths.
 	 */
-	bestinnerjoin = best_innerjoin(innerrel->innerjoin, outerrel->relids,
-								   jointype);
+	bestinnerjoin = best_inner_indexscan(root, innerrel,
+										 outerrel->relids, jointype);
 
 	foreach(i, outerrel->pathlist)
 	{
@@ -813,69 +811,6 @@ hash_inner_and_outer(Query *root,
 }
 
 /*
- * best_innerjoin
- *	  Find the cheapest index path that has already been identified by
- *	  indexable_joinclauses() as being a possible inner path for the given
- *	  outer relation(s) in a nestloop join.
- *
- * We compare indexpaths on total_cost only, assuming that they will all have
- * zero or negligible startup_cost.  We might have to think harder someday...
- *
- * 'join_paths' is a list of potential inner indexscan join paths
- * 'outer_relids' is the relid list of the outer join relation
- *
- * Returns the pathnode of the best path, or NULL if there's no
- * usable path.
- */
-static Path *
-best_innerjoin(List *join_paths, Relids outer_relids, JoinType jointype)
-{
-	Path	   *cheapest = (Path *) NULL;
-	bool		isouterjoin;
-	List	   *join_path;
-
-	/*
-	 * Nestloop only supports inner and left joins.
-	 */
-	switch (jointype)
-	{
-		case JOIN_INNER:
-			isouterjoin = false;
-			break;
-		case JOIN_LEFT:
-			isouterjoin = true;
-			break;
-		default:
-			return NULL;
-	}
-
-	foreach(join_path, join_paths)
-	{
-		IndexPath  *path = (IndexPath *) lfirst(join_path);
-
-		Assert(IsA(path, IndexPath));
-
-		/*
-		 * If processing an outer join, only use explicit join clauses in
-		 * the inner indexscan.  For inner joins we need not be so picky.
-		 */
-		if (isouterjoin && !path->alljoinquals)
-			continue;
-
-		/*
-		 * path->joinrelids is the set of base rels that must be part of
-		 * outer_relids in order to use this inner path, because those
-		 * rels are used in the index join quals of this inner path.
-		 */
-		if (is_subseti(path->joinrelids, outer_relids) &&
-			(cheapest == NULL ||
-			 compare_path_costs((Path *) path, cheapest, TOTAL_COST) < 0))
-			cheapest = (Path *) path;
-	}
-	return cheapest;
-}
-
-/*
  * select_mergejoin_clauses
  *	  Select mergejoin clauses that are usable for a particular join.
  *	  Returns a list of RestrictInfo nodes for those clauses.