iterative_elimination.diff

diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index 7b8dc7a2b7..f7ccda5231 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -2298,6 +2298,7 @@ remove_self_joins_recurse(PlannerInfo *root, List *joinlist, Relids toRemove)
 			{
 				/* Create group of relation indexes with the same oid */
 				Relids		group = NULL;
+				Relids		removed;
 
 				while (i < j)
 				{
@@ -2306,8 +2307,21 @@ remove_self_joins_recurse(PlannerInfo *root, List *joinlist, Relids toRemove)
 				}
 
 				relids = bms_del_members(relids, group);
-				toRemove = bms_add_members(toRemove,
-										remove_self_joins_one_group(root, group));
+
+				/*
+				 * Try to remove self-joins from a group of identical entries.
+				 * Make next attempt iteratively - if something is deleted from
+				 * a group, changes in clauses and equivalence classes can give
+				 * us a chance to find more candidates.
+				 */
+				do {
+					Assert(!bms_overlap(group, toRemove));
+					removed = remove_self_joins_one_group(root, group);
+					toRemove = bms_add_members(toRemove, removed);
+					group = bms_del_members(group, removed);
+				} while (!bms_is_empty(removed) &&
+						 bms_membership(group) == BMS_MULTIPLE);
+				bms_free(removed);
 				bms_free(group);
 			}
 			else
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 12a90bd42e..cb2429645c 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -6786,6 +6786,36 @@ SELECT * FROM emp1 e1, emp1 e2 WHERE e1.id = e2.id AND e2.code <> e1.code;
    Filter: ((e2.id IS NOT NULL) AND (e2.code <> e2.code))
 (3 rows)
 
+-- Shuffle self-joined relations. Only in the case of iterative deletion
+-- attempts explains of these queries will be identical.
+CREATE UNIQUE INDEX ON emp1((id*id));
+explain SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3
+WHERE c1.id=c2.id AND c1.id*c2.id=c3.id*c3.id;
+                           QUERY PLAN                            
+-----------------------------------------------------------------
+ Aggregate  (cost=43.84..43.85 rows=1 width=8)
+   ->  Seq Scan on emp1 c3  (cost=0.00..38.25 rows=2237 width=0)
+         Filter: ((id IS NOT NULL) AND ((id * id) IS NOT NULL))
+(3 rows)
+
+explain SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3
+WHERE c1.id=c3.id AND c1.id*c3.id=c2.id*c2.id;
+                           QUERY PLAN                            
+-----------------------------------------------------------------
+ Aggregate  (cost=43.84..43.85 rows=1 width=8)
+   ->  Seq Scan on emp1 c3  (cost=0.00..38.25 rows=2237 width=0)
+         Filter: ((id IS NOT NULL) AND ((id * id) IS NOT NULL))
+(3 rows)
+
+explain SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3
+WHERE c3.id=c2.id AND c3.id*c2.id=c1.id*c1.id;
+                           QUERY PLAN                            
+-----------------------------------------------------------------
+ Aggregate  (cost=43.84..43.85 rows=1 width=8)
+   ->  Seq Scan on emp1 c3  (cost=0.00..38.25 rows=2237 width=0)
+         Filter: ((id IS NOT NULL) AND ((id * id) IS NOT NULL))
+(3 rows)
+
 -- We can remove the join even if we find the join can't duplicate rows and
 -- the base quals of each side are different.  In the following case we end up
 -- moving quals over to s1 to make it so it can't match any rows.
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 4d49c0767a..55147263ca 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -2576,6 +2576,16 @@ CREATE TABLE emp1 ( id SERIAL PRIMARY KEY NOT NULL, code int);
 explain (verbose, costs off)
 SELECT * FROM emp1 e1, emp1 e2 WHERE e1.id = e2.id AND e2.code <> e1.code;
 
+-- Shuffle self-joined relations. Only in the case of iterative deletion
+-- attempts explains of these queries will be identical.
+CREATE UNIQUE INDEX ON emp1((id*id));
+explain SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3
+WHERE c1.id=c2.id AND c1.id*c2.id=c3.id*c3.id;
+explain SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3
+WHERE c1.id=c3.id AND c1.id*c3.id=c2.id*c2.id;
+explain SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3
+WHERE c3.id=c2.id AND c3.id*c2.id=c1.id*c1.id;
+
 -- We can remove the join even if we find the join can't duplicate rows and
 -- the base quals of each side are different.  In the following case we end up
 -- moving quals over to s1 to make it so it can't match any rows.