fk_join_est_fix_2017-05-23.patch

diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index cdb18d9..09858be 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -4308,7 +4308,6 @@ get_foreign_key_join_selectivity(PlannerInfo *root,
 	{
 		ForeignKeyOptInfo *fkinfo = (ForeignKeyOptInfo *) lfirst(lc);
 		bool		ref_is_outer;
-		bool		use_smallest_selectivity = false;
 		List	   *removedlist;
 		ListCell   *cell;
 		ListCell   *prev;
@@ -4429,10 +4428,6 @@ get_foreign_key_join_selectivity(PlannerInfo *root,
 		 * be double-counting the null fraction, and (2) it's not very clear
 		 * how to combine null fractions for multiple referencing columns.
 		 *
-		 * In the use_smallest_selectivity code below, null derating is done
-		 * implicitly by relying on clause_selectivity(); in the other cases,
-		 * we do nothing for now about correcting for nulls.
-		 *
 		 * XXX another point here is that if either side of an FK constraint
 		 * is an inheritance parent, we estimate as though the constraint
 		 * covers all its children as well.  This is not an unreasonable
@@ -4443,30 +4438,17 @@ get_foreign_key_join_selectivity(PlannerInfo *root,
 		 * work, it is uncommon in practice to have an FK referencing a parent
 		 * table.  So, at least for now, disregard inheritance here.
 		 */
-		if (ref_is_outer && jointype != JOIN_INNER)
-		{
-			/*
-			 * When the referenced table is on the outer side of a non-inner
-			 * join, knowing that each inner row has exactly one match is not
-			 * as useful as one could wish, since we really need to know the
-			 * fraction of outer rows with a match.  Still, we can avoid the
-			 * folly of multiplying the per-column estimates together.  Take
-			 * the smallest per-column selectivity, instead.  (This should
-			 * correspond to the FK column with the most nulls.)
-			 */
-			use_smallest_selectivity = true;
-		}
-		else if (jointype == JOIN_SEMI || jointype == JOIN_ANTI)
+
+		if (jointype == JOIN_SEMI || jointype == JOIN_ANTI)
 		{
 			/*
-			 * For JOIN_SEMI and JOIN_ANTI, the selectivity is defined as the
-			 * fraction of LHS rows that have matches.  The referenced table
-			 * is on the inner side (we already handled the other case above),
-			 * so the FK implies that every LHS row has a match *in the
-			 * referenced table*.  But any restriction or join clauses below
-			 * here will reduce the number of matches.
+			 * For JOIN_SEMI and JOIN_ANTI, when the referenced table is on
+			 * the inner side, the selectivity is defined as the fraction of
+			 * LHS rows that have matches.  Here the FK implies that every LHS
+			 * row has a match *in the referenced table*.  But any restriction
+			 * or join clauses below here will reduce the number of matches.
 			 */
-			if (bms_membership(inner_relids) == BMS_SINGLETON)
+			if (!ref_is_outer && bms_membership(inner_relids) == BMS_SINGLETON)
 			{
 				/*
 				 * When the inner side of the semi/anti join is just the
@@ -4483,11 +4465,12 @@ get_foreign_key_join_selectivity(PlannerInfo *root,
 				/*
 				 * When the inner side of the semi/anti join is itself a join,
 				 * it's hard to guess what fraction of the referenced table
-				 * will get through the join.  But we still don't want to
-				 * multiply per-column estimates together.  Take the smallest
-				 * per-column selectivity, instead.
+				 * will get through the join. We'll simply fall back on
+				 * clauselist_selectivity allowing it to provide the
+				 * estimation.
 				 */
-				use_smallest_selectivity = true;
+				fkselec *= clauselist_selectivity(root, removedlist,
+										  0, jointype, sjinfo);
 			}
 		}
 		else
@@ -4502,26 +4485,6 @@ get_foreign_key_join_selectivity(PlannerInfo *root,
 
 			fkselec *= 1.0 / ref_tuples;
 		}
-
-		/*
-		 * Common code for cases where we should use the smallest selectivity
-		 * that would be computed for any one of the FK's clauses.
-		 */
-		if (use_smallest_selectivity)
-		{
-			Selectivity thisfksel = 1.0;
-
-			foreach(cell, removedlist)
-			{
-				RestrictInfo *rinfo = (RestrictInfo *) lfirst(cell);
-				Selectivity csel;
-
-				csel = clause_selectivity(root, (Node *) rinfo,
-										  0, jointype, sjinfo);
-				thisfksel = Min(thisfksel, csel);
-			}
-			fkselec *= thisfksel;
-		}
 	}
 
 	*restrictlist = worklist;
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index d08b1e1..9e6e23d 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -5691,3 +5691,35 @@ where exists (select 1 from j3
 (13 rows)
 
 drop table j3;
+--
+-- Test Foreign key join estimation performs sanely for outer joins
+--
+begin work;
+create table fkest (a int, b int, c int unique, primary key(a,b));
+create table fkest1 (a int, b int, primary key(a,b));
+insert into fkest select x/10,x%10, x from generate_Series(1,400) x;
+insert into fkest1 select x/10,x%10 from generate_Series(1,400) x;
+alter table fkest1 add constraint fkest1_a_b_fkey foreign key (a,b) references fkest;
+analyze fkest;
+analyze fkest1;
+explain (costs off) select * from fkest f
+left join fkest1 f1 on f.a = f1.a and f.b = f1.b
+left join fkest1 f2 on f.a = f2.a and f.b = f2.b
+left join fkest1 f3 on f.a = f3.a and f.b = f3.b
+where f.c = 1;
+                            QUERY PLAN                            
+------------------------------------------------------------------
+ Nested Loop Left Join
+   ->  Nested Loop Left Join
+         ->  Nested Loop Left Join
+               ->  Seq Scan on fkest f
+                     Filter: (c = 1)
+               ->  Index Only Scan using fkest1_pkey on fkest1 f1
+                     Index Cond: ((a = f.a) AND (b = f.b))
+         ->  Index Only Scan using fkest1_pkey on fkest1 f2
+               Index Cond: ((a = f.a) AND (b = f.b))
+   ->  Index Only Scan using fkest1_pkey on fkest1 f3
+         Index Cond: ((a = f.a) AND (b = f.b))
+(11 rows)
+
+rollback;
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index c3994ea..5c79d74 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1878,3 +1878,28 @@ where exists (select 1 from j3
       and t1.unique1 < 1;
 
 drop table j3;
+
+--
+-- Test Foreign key join estimation performs sanely for outer joins
+--
+
+begin work;
+
+create table fkest (a int, b int, c int unique, primary key(a,b));
+create table fkest1 (a int, b int, primary key(a,b));
+
+insert into fkest select x/10,x%10, x from generate_Series(1,400) x;
+insert into fkest1 select x/10,x%10 from generate_Series(1,400) x;
+
+alter table fkest1 add constraint fkest1_a_b_fkey foreign key (a,b) references fkest;
+
+analyze fkest;
+analyze fkest1;
+
+explain (costs off) select * from fkest f
+left join fkest1 f1 on f.a = f1.a and f.b = f1.b
+left join fkest1 f2 on f.a = f2.a and f.b = f2.b
+left join fkest1 f3 on f.a = f3.a and f.b = f3.b
+where f.c = 1;
+
+rollback;