postgres.git / summary / log / commit / refs

commit    2cbbffff05b853733ae19ca7047cfeb9a346a497
Author:   Tom Lane <tgl@sss.pgh.pa.us>
Date:     Tue Feb 07 16:56:43 2023 +0000
First released in: 16.0

    Remove leftover code in deconstruct_distribute_oj_quals().
    
    The initial "put back OJ relids" adjustment of ojscope was
    incorrect and unnecessary; it seems to be a leftover from
    when I (tgl) was trying to get this function to work at all.
    
    Richard Guo
    
    Discussion: https://postgr.es/m/CAMbWs4-L2C47ZGZPabBAi5oDZsKmsbvhYcGCy5o=gCjsaG_ZQA@mail.gmail.com


 src/backend/optimizer/plan/initsplan.c |  7 -------
 src/test/regress/expected/join.out     | 26 ++++++++++++++++++++++++++
 src/test/regress/sql/join.sql          | 15 +++++++++++++++
 3 files changed, 41 insertions(+), 7 deletions(-)

diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index 2d9aa85e591..904f710d593 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -1903,13 +1903,6 @@ deconstruct_distribute_oj_quals(PlannerInfo *root,
 		int			save_last_rinfo_serial;
 		ListCell   *lc;
 
-		/*
-		 * Put any OJ relids that were removed from min_righthand back into
-		 * ojscope, else distribute_qual_to_rels will complain.
-		 */
-		ojscope = bms_join(ojscope, bms_intersect(sjinfo->commute_below,
-												  sjinfo->syn_righthand));
-
 		/* Identify the outer joins this one commutes with */
 		joins_above = sjinfo->commute_above_r;
 		joins_below = bms_intersect(sjinfo->commute_below,
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 07f5aad5ea4..eea8978fad4 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4982,6 +4982,32 @@ select id from a where id in (
          ->  Seq Scan on b
 (5 rows)
 
+-- check optimization with oddly-nested outer joins
+explain (costs off)
+select a1.id from
+  (a a1 left join a a2 on true)
+  left join
+  (a a3 left join a a4 on a3.id = a4.id)
+  on a2.id = a3.id;
+          QUERY PLAN          
+------------------------------
+ Nested Loop Left Join
+   ->  Seq Scan on a a1
+   ->  Materialize
+         ->  Seq Scan on a a2
+(4 rows)
+
+explain (costs off)
+select a1.id from
+  (a a1 left join a a2 on a1.id = a2.id)
+  left join
+  (a a3 left join a a4 on a3.id = a4.id)
+  on a2.id = a3.id;
+    QUERY PLAN    
+------------------
+ Seq Scan on a a1
+(1 row)
+
 -- check that join removal works for a left join when joining a subquery
 -- that is guaranteed to be unique by its GROUP BY clause
 explain (costs off)
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 7157b5cccca..9d20b88d71d 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1770,6 +1770,21 @@ select id from a where id in (
 	select b.id from b left join c on b.id = c.id
 );
 
+-- check optimization with oddly-nested outer joins
+explain (costs off)
+select a1.id from
+  (a a1 left join a a2 on true)
+  left join
+  (a a3 left join a a4 on a3.id = a4.id)
+  on a2.id = a3.id;
+
+explain (costs off)
+select a1.id from
+  (a a1 left join a a2 on a1.id = a2.id)
+  left join
+  (a a3 left join a a4 on a3.id = a4.id)
+  on a2.id = a3.id;
+
 -- check that join removal works for a left join when joining a subquery
 -- that is guaranteed to be unique by its GROUP BY clause
 explain (costs off)


[parent: aa6954104644]