0001-Improve-RestrictInfo-deduplication-after-self-join-e.patch

From 01b9dc8b728bef49104fe1652841770eddb0c45c Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <lepihov@gmail.com>
Date: Thu, 26 Mar 2026 12:15:01 +0100
Subject: [PATCH] Improve RestrictInfo deduplication after self-join
 elimination

After self-join elimination rewrites varnos, two RestrictInfos can
end up with identical clauses but different metadata (outer_relids,
rinfo_serial).  The previous restrict_infos_logically_equal()
compared full RestrictInfo structs, missing these duplicates and
leaving redundant filter conditions in the plan.

For base restrictions (singleton required_relids), compare only the
clause expression, which is sufficient since these are pushed-down
filters that don't carry join-semantic meaning.  Retain the full
struct comparison for join clauses (non-singleton required_relids)
to avoid incorrectly merging clauses at different join levels.

Also check security_level to prevent merging an RLS policy qual with a
user-written ON clause that happen to look identical after SJE.

Author: Tender Wang <tndrwang@gmail.com>
Author: Alexander Korotkov <aekorotkov@gmail.com>
Reviewed-by: Andrei Lepikhov <lepihov@gmail.com>
Discussion: https://www.postgresql.org/message-id/19435-3cc1a87f291129f1%40postgresql.org
---
 src/backend/optimizer/plan/analyzejoins.c | 16 ++++-
 src/test/regress/expected/join.out        | 78 +++++++++++++++++++++++
 src/test/regress/sql/join.sql             | 48 ++++++++++++++
 3 files changed, 139 insertions(+), 3 deletions(-)

diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index 12e9ed0d0c7..ed35c51a38f 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -1635,9 +1635,19 @@ restrict_infos_logically_equal(RestrictInfo *a, RestrictInfo *b)
 	int			saved_rinfo_serial = a->rinfo_serial;
 	bool		result;
 
-	a->rinfo_serial = b->rinfo_serial;
-	result = equal(a, b);
-	a->rinfo_serial = saved_rinfo_serial;
+	if (bms_membership(a->required_relids) == BMS_SINGLETON &&
+		a->security_level == b->security_level)
+	{
+		Assert(a->is_pushed_down && b->is_pushed_down);
+
+		result = equal(a->clause, b->clause);
+	}
+	else
+	{
+		a->rinfo_serial = b->rinfo_serial;
+		result = equal(a, b);
+		a->rinfo_serial = saved_rinfo_serial;
+	}
 
 	return result;
 }
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 84872c6f04e..88766e5e626 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -8108,6 +8108,84 @@ SELECT 1 AS c1 FROM sl sl1 LEFT JOIN (sl AS sl2 NATURAL JOIN sl AS sl3)
          ->  Seq Scan on sl sl4
 (7 rows)
 
+-- SJE: after self-join removal merges sl7 and sl6, the JOIN
+-- produces a pushed-down (bool_col IS NOT NULL) that duplicates the ON
+-- clause.  Verify that clause deduplication removes the duplicate, leaving
+-- a single Filter condition.
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+  sl AS sl5 LEFT JOIN (sl AS sl6 NATURAL JOIN sl AS sl7)
+    ON sl6.bool_col IS NOT NULL
+  LEFT JOIN sl AS sl8
+    ON sl6.bool_col;
+                                                 QUERY PLAN                                                 
+------------------------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+   ->  Seq Scan on sl sl5
+   ->  Nested Loop Left Join
+         Join Filter: sl7.bool_col
+         ->  Seq Scan on sl sl7
+               Filter: ((bool_col IS NOT NULL) AND (a IS NOT NULL) AND (b IS NOT NULL) AND (c IS NOT NULL))
+         ->  Seq Scan on sl sl8
+(7 rows)
+
+-- SJE: multi-relation clause deduplication after self-join removal.
+-- After SJE removes sl4, both ON clauses collapse to the same expression
+-- but with non-singleton required_relids (referencing sl2 and the kept rel).
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+  sl sl1 LEFT JOIN sl sl2
+    LEFT JOIN (sl AS sl4 NATURAL JOIN sl AS sl3)
+      ON (sl4.b + sl2.b) IS NOT NULL
+    ON (sl3.b + sl2.b) IS NOT NULL;
+                                                 QUERY PLAN                                                 
+------------------------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+   ->  Seq Scan on sl sl1
+   ->  Nested Loop
+         Join Filter: (((sl3.b + sl2.b) IS NOT NULL) AND ((sl3.b + sl2.b) IS NOT NULL))
+         ->  Seq Scan on sl sl2
+         ->  Seq Scan on sl sl3
+               Filter: ((a IS NOT NULL) AND (b IS NOT NULL) AND (c IS NOT NULL) AND (bool_col IS NOT NULL))
+(7 rows)
+
+-- SJE: clause deduplication must not merge clauses with different
+-- security_level values. An RLS policy qual and a
+-- user-written ON clause may look identical after SJE
+-- rewrites varnos, but removing the RLS qual would break the security
+-- barrier guarantee.
+ALTER TABLE sl ENABLE ROW LEVEL SECURITY;
+ALTER TABLE sl FORCE ROW LEVEL SECURITY;
+CREATE POLICY sl_policy ON sl USING (bool_col IS NOT NULL);
+CREATE ROLE regress_sje_user LOGIN;
+GRANT SELECT ON sl TO regress_sje_user;
+SET ROLE regress_sje_user;
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+  sl sl1 LEFT JOIN (
+    (sl AS sl2 NATURAL JOIN sl AS sl3)
+    LEFT JOIN sl sl4
+    ON sl2.bool_col IS NOT NULL
+  ) ON sl2.bool_col IS NOT NULL;
+                                                              QUERY PLAN                                                               
+---------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+   ->  Seq Scan on sl sl1
+         Filter: (bool_col IS NOT NULL)
+   ->  Nested Loop Left Join
+         Join Filter: (sl3.bool_col IS NOT NULL)
+         ->  Seq Scan on sl sl3
+               Filter: ((bool_col IS NOT NULL) AND (bool_col IS NOT NULL) AND (a IS NOT NULL) AND (b IS NOT NULL) AND (c IS NOT NULL))
+         ->  Seq Scan on sl sl4
+               Filter: (bool_col IS NOT NULL)
+(9 rows)
+
+RESET ROLE;
+DROP POLICY sl_policy ON sl;
+ALTER TABLE sl DISABLE ROW LEVEL SECURITY;
+ALTER TABLE sl NO FORCE ROW LEVEL SECURITY;
+REVOKE SELECT ON sl FROM regress_sje_user;
+DROP ROLE regress_sje_user;
 -- Check optimization disabling if it will violate special join conditions.
 -- Two identical joined relations satisfies self join removal conditions but
 -- stay in different special join infos.
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 30b479dda7c..864d95694f9 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -3162,6 +3162,54 @@ EXPLAIN (COSTS OFF)
 SELECT 1 AS c1 FROM sl sl1 LEFT JOIN (sl AS sl2 NATURAL JOIN sl AS sl3)
   ON sl2.bool_col LEFT JOIN sl AS sl4 ON sl2.bool_col;
 
+-- SJE: after self-join removal merges sl7 and sl6, the JOIN
+-- produces a pushed-down (bool_col IS NOT NULL) that duplicates the ON
+-- clause.  Verify that clause deduplication removes the duplicate, leaving
+-- a single Filter condition.
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+  sl AS sl5 LEFT JOIN (sl AS sl6 NATURAL JOIN sl AS sl7)
+    ON sl6.bool_col IS NOT NULL
+  LEFT JOIN sl AS sl8
+    ON sl6.bool_col;
+
+-- SJE: multi-relation clause deduplication after self-join removal.
+-- After SJE removes sl4, both ON clauses collapse to the same expression
+-- but with non-singleton required_relids (referencing sl2 and the kept rel).
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+  sl sl1 LEFT JOIN sl sl2
+    LEFT JOIN (sl AS sl4 NATURAL JOIN sl AS sl3)
+      ON (sl4.b + sl2.b) IS NOT NULL
+    ON (sl3.b + sl2.b) IS NOT NULL;
+
+-- SJE: clause deduplication must not merge clauses with different
+-- security_level values. An RLS policy qual and a
+-- user-written ON clause may look identical after SJE
+-- rewrites varnos, but removing the RLS qual would break the security
+-- barrier guarantee.
+ALTER TABLE sl ENABLE ROW LEVEL SECURITY;
+ALTER TABLE sl FORCE ROW LEVEL SECURITY;
+CREATE POLICY sl_policy ON sl USING (bool_col IS NOT NULL);
+CREATE ROLE regress_sje_user LOGIN;
+GRANT SELECT ON sl TO regress_sje_user;
+SET ROLE regress_sje_user;
+
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+  sl sl1 LEFT JOIN (
+    (sl AS sl2 NATURAL JOIN sl AS sl3)
+    LEFT JOIN sl sl4
+    ON sl2.bool_col IS NOT NULL
+  ) ON sl2.bool_col IS NOT NULL;
+
+RESET ROLE;
+DROP POLICY sl_policy ON sl;
+ALTER TABLE sl DISABLE ROW LEVEL SECURITY;
+ALTER TABLE sl NO FORCE ROW LEVEL SECURITY;
+REVOKE SELECT ON sl FROM regress_sje_user;
+DROP ROLE regress_sje_user;
+
 -- Check optimization disabling if it will violate special join conditions.
 -- Two identical joined relations satisfies self join removal conditions but
 -- stay in different special join infos.
-- 
2.51.0