Take OUTER JOIN semantics into account when estimating the size of join

relations.  It's not very bright, but at least it now knows that
A LEFT JOIN B must produce at least as many rows as are in A ...

5 files changed, 47 insertions, 11 deletions

diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index cc9e49b9e22..8e88e46d571 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -41,7 +41,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/optimizer/path/costsize.c,v 1.67 2001/02/15 17:46:40 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/optimizer/path/costsize.c,v 1.68 2001/02/16 00:03:07 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -837,11 +837,12 @@ void
 set_joinrel_size_estimates(Query *root, RelOptInfo *rel,
 						   RelOptInfo *outer_rel,
 						   RelOptInfo *inner_rel,
+						   JoinType jointype,
 						   List *restrictlist)
 {
 	double		temp;
 
-	/* cartesian product */
+	/* Start with the Cartesian product */
 	temp = outer_rel->rows * inner_rel->rows;
 
 	/*
@@ -855,12 +856,42 @@ set_joinrel_size_estimates(Query *root, RelOptInfo *rel,
 									 0);
 
 	/*
+	 * If we are doing an outer join, take that into account: the output
+	 * must be at least as large as the non-nullable input.  (Is there any
+	 * chance of being even smarter?)
+	 */
+	switch (jointype)
+	{
+		case JOIN_INNER:
+			break;
+		case JOIN_LEFT:
+			if (temp < outer_rel->rows)
+				temp = outer_rel->rows;
+			break;
+		case JOIN_RIGHT:
+			if (temp < inner_rel->rows)
+				temp = inner_rel->rows;
+			break;
+		case JOIN_FULL:
+			if (temp < outer_rel->rows)
+				temp = outer_rel->rows;
+			if (temp < inner_rel->rows)
+				temp = inner_rel->rows;
+			break;
+		default:
+			elog(ERROR, "set_joinrel_size_estimates: unsupported join type %d",
+				 (int) jointype);
+			break;
+	}
+
+	/*
 	 * Force estimate to be at least one row, to make explain output look
 	 * better and to avoid possible divide-by-zero when interpolating
 	 * cost.
 	 */
 	if (temp < 1.0)
 		temp = 1.0;
+
 	rel->rows = temp;
 
 	/*
diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c
index 2a59ed43e48..2492f17ea9b 100644
--- a/src/backend/optimizer/path/joinrels.c
+++ b/src/backend/optimizer/path/joinrels.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/optimizer/path/joinrels.c,v 1.50 2001/01/24 19:42:58 momjian Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/optimizer/path/joinrels.c,v 1.51 2001/02/16 00:03:07 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -394,7 +394,7 @@ make_join_rel(Query *root, RelOptInfo *rel1, RelOptInfo *rel2,
 	 * Find or build the join RelOptInfo, and compute the restrictlist
 	 * that goes with this particular joining.
 	 */
-	joinrel = get_join_rel(root, rel1, rel2, &restrictlist);
+	joinrel = get_join_rel(root, rel1, rel2, jointype, &restrictlist);
 
 	/*
 	 * Consider paths using each rel as both outer and inner.
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index 57827c813f3..b4764ab6f8c 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/optimizer/util/relnode.c,v 1.31 2001/01/24 19:43:00 momjian Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/optimizer/util/relnode.c,v 1.32 2001/02/16 00:03:08 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -152,6 +152,7 @@ find_join_rel(Query *root, Relids relids)
  *
  * 'outer_rel' and 'inner_rel' are relation nodes for the relations to be
  *		joined
+ * 'jointype': type of join (inner/outer)
  * 'restrictlist_ptr': result variable.  If not NULL, *restrictlist_ptr
  *		receives the list of RestrictInfo nodes that apply to this
  *		particular pair of joinable relations.
@@ -163,6 +164,7 @@ RelOptInfo *
 get_join_rel(Query *root,
 			 RelOptInfo *outer_rel,
 			 RelOptInfo *inner_rel,
+			 JoinType jointype,
 			 List **restrictlist_ptr)
 {
 	List	   *joinrelids;
@@ -252,7 +254,7 @@ get_join_rel(Query *root,
 	 * Set estimates of the joinrel's size.
 	 */
 	set_joinrel_size_estimates(root, joinrel, outer_rel, inner_rel,
-							   restrictlist);
+							   jointype, restrictlist);
 
 	/*
 	 * Add the joinrel to the query's joinrel list.
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index c6dfcac39c9..5caa576f0c0 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: cost.h,v 1.37 2001/01/24 19:43:26 momjian Exp $
+ * $Id: cost.h,v 1.38 2001/02/16 00:03:05 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -70,6 +70,7 @@ extern void set_baserel_size_estimates(Query *root, RelOptInfo *rel);
 extern void set_joinrel_size_estimates(Query *root, RelOptInfo *rel,
 						   RelOptInfo *outer_rel,
 						   RelOptInfo *inner_rel,
+						   JoinType jointype,
 						   List *restrictlist);
 
 /*
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index b4e239aa8f7..7e000034761 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: pathnode.h,v 1.33 2001/01/24 19:43:26 momjian Exp $
+ * $Id: pathnode.h,v 1.34 2001/02/16 00:03:06 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -66,8 +66,10 @@ extern HashPath *create_hashjoin_path(RelOptInfo *joinrel,
  */
 extern RelOptInfo *get_base_rel(Query *root, int relid);
 extern RelOptInfo *make_base_rel(Query *root, int relid);
-extern RelOptInfo *get_join_rel(Query *root, RelOptInfo *outer_rel,
-			 RelOptInfo *inner_rel,
-			 List **restrictlist_ptr);
+extern RelOptInfo *get_join_rel(Query *root,
+								RelOptInfo *outer_rel,
+								RelOptInfo *inner_rel,
+								JoinType jointype,
+								List **restrictlist_ptr);
 
 #endif	 /* PATHNODE_H */