On 28/07/2026 10:13, Andrei Lepikhov wrote:
Just a rebase onto current master
--
regards, Andrei Lepikhov,
pgEdge
From d586afe7821fd2b676d4553bf582d849fa8f3583 Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <[email protected]>
Date: Wed, 15 Jul 2026 12:06:36 +1000
Subject: [PATCH v2 1/2] Gate the NestLoop inner side with outer-only join
clauses
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
In some cases, a LEFT JOIN’s NestLoop might have a clause that depends only on
the outer side of the join. In such a case, if the outer tuple evaluates this
clause as false, the result of the inner side scan operation will not impact
the join’s output: inner side will always be replaced with NULL values.
Treat such an outer-side clause as a ‘gating’ clause and push it into the inner
side as a Result node. It will reduce the number of rescan operations to a bare
minimum when the the gating clause evaluates to a true.
This operation effectively forces parameterisation of the NestLoop JOIN, even if
it was originally plain. To identify such cases, the NestLoop struct was
supplemented with a flag that allows the executor to distinguish a ‘gated’ join
from the parameterised NestLoop and to set the REWIND flag correctly.
No costing change is made: the gating Result inherits its child's cost fields,
and final_cost_nestloop() does not model the skipped inner rescans.
---
.../postgres_fdw/expected/postgres_fdw.out | 14 +-
src/backend/executor/nodeNestloop.c | 12 +-
src/backend/optimizer/plan/createplan.c | 93 +++-
src/include/nodes/plannodes.h | 2 +
src/test/regress/expected/create_index.out | 21 +-
src/test/regress/expected/join.out | 412 ++++++++++--------
src/test/regress/expected/predicate.out | 113 ++---
src/test/regress/expected/subselect.out | 17 +-
src/test/regress/sql/join.sql | 27 ++
9 files changed, 459 insertions(+), 252 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out
b/contrib/postgres_fdw/expected/postgres_fdw.out
index 517d15cf1fa..ac6d8d851a1 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -2532,16 +2532,18 @@ SELECT q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 =
13) q(a) RIGHT JOIN ft2 O
---------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join
Output: (13), ft2.c1
- Join Filter: (13 = ft2.c1)
-> Foreign Scan on public.ft2
Output: ft2.c1
Remote SQL: SELECT "C 1" FROM "S 1"."T 1" WHERE (("C 1" >= 10)) AND
(("C 1" <= 15)) ORDER BY "C 1" ASC NULLS LAST
- -> Materialize
+ -> Result
Output: (13)
- -> Foreign Scan on public.ft1
- Output: 13
- Remote SQL: SELECT NULL FROM "S 1"."T 1" WHERE (("C 1" = 13))
-(11 rows)
+ One-Time Filter: (13 = ft2.c1)
+ -> Materialize
+ Output: (13)
+ -> Foreign Scan on public.ft1
+ Output: 13
+ Remote SQL: SELECT NULL FROM "S 1"."T 1" WHERE (("C 1" =
13))
+(13 rows)
SELECT q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2
ON (q.a = ft2.c1) WHERE ft2.c1 BETWEEN 10 AND 15;
a | c1
diff --git a/src/backend/executor/nodeNestloop.c
b/src/backend/executor/nodeNestloop.c
index da48c9b204b..7da6eb49fc1 100644
--- a/src/backend/executor/nodeNestloop.c
+++ b/src/backend/executor/nodeNestloop.c
@@ -263,12 +263,16 @@ ExecInitNestLoop(NestLoop *node, EState *estate, int
eflags)
*
* If we have no parameters to pass into the inner rel from the outer,
* tell the inner child that cheap rescans would be good. If we do have
- * such parameters, then there is no point in REWIND support at all in
the
- * inner child, because it will always be rescanned with fresh parameter
- * values.
+ * such parameters, there is normally no point in REWIND support in the
+ * inner child, because it will be rescanned with fresh parameter
values.
+ *
+ * The exception is a 'gated' nested loop join, where the only
parameters
+ * feed a gating Result that caps the inner subtree and is re-checked
once
+ * per outer tuple. The plan below that gate is parameter-independent,
so
+ * we keep the REWIND flag.
*/
outerPlanState(nlstate) = ExecInitNode(outerPlan(node), estate, eflags);
- if (node->nestParams == NIL)
+ if (node->nestParams == NIL || node->keep_inner_rewind)
eflags |= EXEC_FLAG_REWIND;
else
eflags &= ~EXEC_FLAG_REWIND;
diff --git a/src/backend/optimizer/plan/createplan.c
b/src/backend/optimizer/plan/createplan.c
index 02a888c5996..50e7e71370b 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -4200,8 +4200,11 @@ create_nestloop_plan(PlannerInfo *root,
Plan *inner_plan;
Relids outerrelids;
Relids ojrelids;
+ Relids joinrelids = best_path->jpath.path.parent->relids;
List *tlist = build_path_tlist(root, &best_path->jpath.path);
List *joinrestrictclauses = best_path->jpath.joinrestrictinfo;
+ List *gating_clauses = NIL;
+ bool keep_inner_rewind = false;
List *joinclauses;
List *otherclauses;
List *nestParams;
@@ -4247,8 +4250,24 @@ create_nestloop_plan(PlannerInfo *root,
if (IS_OUTER_JOIN(best_path->jpath.jointype))
{
extract_actual_join_clauses(joinrestrictclauses,
-
best_path->jpath.path.parent->relids,
+
joinrelids,
&joinclauses, &otherclauses);
+
+ /*
+ * Collect the join clauses that reference only the outer rel:
they are
+ * constant for a given outer tuple, so the loop further down
can gate
+ * the inner side with them instead of re-checking them per
inner row.
+ */
+ foreach_node(RestrictInfo, rinfo, joinrestrictclauses)
+ {
+ if (bms_is_empty(rinfo->clause_relids) ||
+ !bms_is_subset(rinfo->clause_relids,
outerrelids))
+ continue;
+
+ Assert(!RINFO_IS_PUSHED_DOWN(rinfo, joinrelids));
+
+ gating_clauses = lappend(gating_clauses, rinfo->clause);
+ }
}
else
{
@@ -4257,6 +4276,14 @@ create_nestloop_plan(PlannerInfo *root,
otherclauses = NIL;
}
+ /*
+ * Pull the outer-only clauses out of joinclauses; they become a gating
+ * qual on the inner side below. Do this before parameterization, while
+ * the expressions are still un-parameterized.
+ */
+ if (gating_clauses != NIL)
+ joinclauses = list_difference_ptr(joinclauses, gating_clauses);
+
/* Replace any outer-relation variables with nestloop params */
if (best_path->jpath.path.param_info)
{
@@ -4271,6 +4298,68 @@ create_nestloop_plan(PlannerInfo *root,
bms_union(best_path->jpath.outerjoinpath->parent->relids,
best_path->jpath.innerjoinpath->parent->relids));
+ /*
+ * The gating clauses must be parameterized so the gating Result can
+ * evaluate them against the current outer tuple.
+ */
+ if (gating_clauses != NIL)
+ {
+ Relids tmpOuterRels = root->curOuterRels;
+ Plan *subplan = inner_plan;
+
+ /*
+ * Keep the inner side rewindable across rescans, but only when
this
+ * gate is the sole source of nestloop parameters.
+ */
+ keep_inner_rewind =
+
!bms_overlap(PATH_REQ_OUTER(best_path->jpath.innerjoinpath),
+
outerrelids);
+
+ Assert(bms_is_subset(pull_varnos(root, (Node *) gating_clauses),
+ outerrelids));
+
+ /*
+ * replace_nestloop_params only converts Vars in curOuterRels,
which
+ * was restored above and no longer covers this join's outer
relids,
+ * so re-add them across the call.
+ */
+ root->curOuterRels = bms_union(root->curOuterRels, outerrelids);
+ gating_clauses = (List *)
+ replace_nestloop_params(root, (Node *) gating_clauses);
+ bms_free(root->curOuterRels);
+ root->curOuterRels = tmpOuterRels;
+
+ /*
+ * Avoid stacking Result nodes. If the inner plan is already a
Result,
+ * merge our parameterized clauses into its resconstantqual.
This
+ * mirrors create_gating_plan()'s logic.
+ */
+ if (IsA(subplan, Result))
+ {
+ Result *existing = (Result *) subplan;
+ List *clauses = (List *) existing->resconstantqual;
+
+ Assert(clauses == NULL || IsA(clauses, List));
+
+ clauses = list_concat(gating_clauses, clauses);
+ clauses = order_qual_clauses(root, clauses);
+ existing->resconstantqual = (Node *) clauses;
+
+ /* Gating quals could be unsafe, so use the Path's
safety flag */
+ existing->plan.parallel_safe =
best_path->jpath.path.parallel_safe;
+ }
+ else
+ {
+ inner_plan = (Plan *)
make_gating_result(subplan->targetlist,
+
(Node *) gating_clauses,
+
subplan);
+ copy_plan_costsize(inner_plan, subplan);
+
+ /* Gating quals could be unsafe, so use the Path's
safety flag */
+ inner_plan->parallel_safe =
best_path->jpath.path.parallel_safe;
+ }
+ }
+
/*
* Identify any nestloop parameters that should be supplied by this join
* node, and remove them from root->curOuterParams.
@@ -4345,6 +4434,8 @@ create_nestloop_plan(PlannerInfo *root,
ojrelids,
best_path->jpath.inner_unique);
+ join_plan->keep_inner_rewind = keep_inner_rewind;
+
copy_generic_path_info(&join_plan->join.plan, &best_path->jpath.path);
return join_plan;
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 2fe6b61afaf..dce46966685 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -1010,6 +1010,8 @@ typedef struct NestLoop
Join join;
/* list of NestLoopParam nodes */
List *nestParams;
+ /* keep inner side rewindable: gate is the sole source of nestloop
params */
+ bool keep_inner_rewind;
} NestLoop;
typedef struct NestLoopParam
diff --git a/src/test/regress/expected/create_index.out
b/src/test/regress/expected/create_index.out
index 7b2640f0e04..f9db7416306 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2260,19 +2260,20 @@ EXPLAIN (COSTS OFF)
SELECT count(*) FROM tenk1 LEFT JOIN tenk2 ON
tenk1.hundred = 42 AND (tenk2.thousand = 42 OR tenk2.thousand = 41 OR
tenk2.tenthous = 2) AND
tenk2.hundred = tenk1.hundred;
- QUERY PLAN
-------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------
Aggregate
-> Nested Loop Left Join
- Join Filter: (tenk1.hundred = 42)
-> Index Only Scan using tenk1_hundred on tenk1
- -> Memoize
- Cache Key: tenk1.hundred
- Cache Mode: logical
- -> Index Scan using tenk2_hundred on tenk2
- Index Cond: (hundred = tenk1.hundred)
- Filter: ((thousand = 42) OR (thousand = 41) OR (tenthous
= 2))
-(10 rows)
+ -> Result
+ One-Time Filter: (tenk1.hundred = 42)
+ -> Memoize
+ Cache Key: tenk1.hundred
+ Cache Mode: logical
+ -> Index Scan using tenk2_hundred on tenk2
+ Index Cond: (hundred = tenk1.hundred)
+ Filter: ((thousand = 42) OR (thousand = 41) OR
(tenthous = 2))
+(11 rows)
--
-- Check behavior with duplicate index column contents
diff --git a/src/test/regress/expected/join.out
b/src/test/regress/expected/join.out
index db4fcc5a5a0..43e73268328 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2424,21 +2424,22 @@ from int4_tbl t1, int4_tbl t2
left join int4_tbl t3 on t3.f1 > 0
left join int4_tbl t4 on t3.f1 > 1
where t4.f1 is null;
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------
Nested Loop
-> Nested Loop Left Join
Filter: (t4.f1 IS NULL)
-> Seq Scan on int4_tbl t2
-> Materialize
-> Nested Loop Left Join
- Join Filter: (t3.f1 > 1)
-> Seq Scan on int4_tbl t3
Filter: (f1 > 0)
- -> Materialize
- -> Seq Scan on int4_tbl t4
+ -> Result
+ One-Time Filter: (t3.f1 > 1)
+ -> Materialize
+ -> Seq Scan on int4_tbl t4
-> Seq Scan on int4_tbl t1
-(12 rows)
+(13 rows)
select t1.f1
from int4_tbl t1, int4_tbl t2
@@ -2454,21 +2455,23 @@ select *
from int4_tbl t1 left join int4_tbl t2 on true
left join int4_tbl t3 on t2.f1 > 0
left join int4_tbl t4 on t3.f1 > 0;
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------
Nested Loop Left Join
-> Seq Scan on int4_tbl t1
-> Materialize
-> Nested Loop Left Join
- Join Filter: (t3.f1 > 0)
-> Nested Loop Left Join
- Join Filter: (t2.f1 > 0)
-> Seq Scan on int4_tbl t2
+ -> Result
+ One-Time Filter: (t2.f1 > 0)
+ -> Materialize
+ -> Seq Scan on int4_tbl t3
+ -> Result
+ One-Time Filter: (t3.f1 > 0)
-> Materialize
- -> Seq Scan on int4_tbl t3
- -> Materialize
- -> Seq Scan on int4_tbl t4
-(12 rows)
+ -> Seq Scan on int4_tbl t4
+(14 rows)
explain (costs off)
select * from onek t1
@@ -2525,7 +2528,6 @@ select * from int4_tbl t1
QUERY PLAN
-------------------------------------------------
Nested Loop Left Join
- Join Filter: (t2.f1 = t3.f1)
-> Nested Loop Left Join
-> Nested Loop Left Join
-> Seq Scan on int4_tbl t1
@@ -2533,9 +2535,11 @@ select * from int4_tbl t1
-> Seq Scan on int4_tbl t2
-> Materialize
-> Seq Scan on int4_tbl t3
- -> Materialize
- -> Seq Scan on int4_tbl t4
-(11 rows)
+ -> Result
+ One-Time Filter: (t2.f1 = t3.f1)
+ -> Materialize
+ -> Seq Scan on int4_tbl t4
+(12 rows)
explain (costs off)
select * from int4_tbl t1
@@ -2563,23 +2567,25 @@ select * from int4_tbl t1
left join (int4_tbl t2 left join int4_tbl t3 on t2.f1 > 0) on t2.f1 > 1
left join int4_tbl t4 on t2.f1 > 2 and t3.f1 > 3
where t1.f1 = coalesce(t2.f1, 1);
- QUERY PLAN
-----------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------
Nested Loop Left Join
- Join Filter: ((t2.f1 > 2) AND (t3.f1 > 3))
-> Nested Loop Left Join
- Join Filter: (t2.f1 > 0)
-> Nested Loop Left Join
Filter: (t1.f1 = COALESCE(t2.f1, 1))
-> Seq Scan on int4_tbl t1
-> Materialize
-> Seq Scan on int4_tbl t2
Filter: (f1 > 1)
+ -> Result
+ One-Time Filter: (t2.f1 > 0)
+ -> Materialize
+ -> Seq Scan on int4_tbl t3
+ -> Result
+ One-Time Filter: ((t2.f1 > 2) AND (t3.f1 > 3))
-> Materialize
- -> Seq Scan on int4_tbl t3
- -> Materialize
- -> Seq Scan on int4_tbl t4
-(14 rows)
+ -> Seq Scan on int4_tbl t4
+(16 rows)
explain (costs off)
select * from int4_tbl t1
@@ -2588,22 +2594,24 @@ select * from int4_tbl t1
where t3.f1 is null) s
left join tenk1 t4 on s.f1 > 1)
on s.f1 = t1.f1;
- QUERY PLAN
--------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------
Hash Right Join
Hash Cond: (t2.f1 = t1.f1)
-> Nested Loop Left Join
- Join Filter: (t2.f1 > 1)
-> Nested Loop Left Join
- Join Filter: (t2.f1 > 0)
Filter: (t3.f1 IS NULL)
-> Seq Scan on int4_tbl t2
- -> Materialize
- -> Seq Scan on int4_tbl t3
- -> Seq Scan on tenk1 t4
+ -> Result
+ One-Time Filter: (t2.f1 > 0)
+ -> Materialize
+ -> Seq Scan on int4_tbl t3
+ -> Result
+ One-Time Filter: (t2.f1 > 1)
+ -> Seq Scan on tenk1 t4
-> Hash
-> Seq Scan on int4_tbl t1
-(13 rows)
+(15 rows)
explain (costs off)
select * from int4_tbl t1
@@ -2615,20 +2623,22 @@ select * from int4_tbl t1
QUERY PLAN
-----------------------------------------------------------------
Nested Loop Left Join
- Join Filter: (t2.f1 > 1)
-> Hash Right Join
Hash Cond: (t2.f1 = t1.f1)
-> Nested Loop Left Join
- Join Filter: (t2.f1 > 0)
Filter: (t2.f1 <> COALESCE(t3.f1, '-1'::integer))
-> Seq Scan on int4_tbl t2
- -> Materialize
- -> Seq Scan on int4_tbl t3
+ -> Result
+ One-Time Filter: (t2.f1 > 0)
+ -> Materialize
+ -> Seq Scan on int4_tbl t3
-> Hash
-> Seq Scan on int4_tbl t1
- -> Materialize
- -> Seq Scan on tenk1 t4
-(14 rows)
+ -> Result
+ One-Time Filter: (t2.f1 > 1)
+ -> Materialize
+ -> Seq Scan on tenk1 t4
+(16 rows)
explain (costs off)
select * from onek t1
@@ -2680,17 +2690,18 @@ select * from int8_tbl t1
left join lateral
(select * from int8_tbl t3 where t3.q1 = t2.q1 offset 0) s
on t2.q1 = 1;
- QUERY PLAN
--------------------------------------------
+ QUERY PLAN
+--------------------------------------------------
Nested Loop Left Join
-> Seq Scan on int8_tbl t1
-> Materialize
-> Nested Loop Left Join
- Join Filter: (t2.q1 = 1)
-> Seq Scan on int8_tbl t2
- -> Seq Scan on int8_tbl t3
- Filter: (q1 = t2.q1)
-(8 rows)
+ -> Result
+ One-Time Filter: (t2.q1 = 1)
+ -> Seq Scan on int8_tbl t3
+ Filter: (q1 = t2.q1)
+(9 rows)
explain (costs off)
select * from int8_tbl t1
@@ -2698,16 +2709,17 @@ select * from int8_tbl t1
left join lateral
(select * from generate_series(t2.q1, 100)) s
on t2.q1 = 1;
- QUERY PLAN
-----------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------
Nested Loop Left Join
-> Seq Scan on int8_tbl t1
-> Materialize
-> Nested Loop Left Join
- Join Filter: (t2.q1 = 1)
-> Seq Scan on int8_tbl t2
- -> Function Scan on generate_series
-(7 rows)
+ -> Result
+ One-Time Filter: (t2.q1 = 1)
+ -> Function Scan on generate_series
+(8 rows)
explain (costs off)
select * from int8_tbl t1
@@ -2715,16 +2727,17 @@ select * from int8_tbl t1
left join lateral
(select t2.q1 from int8_tbl t3) s
on t2.q1 = 1;
- QUERY PLAN
--------------------------------------------
+ QUERY PLAN
+--------------------------------------------------
Nested Loop Left Join
-> Seq Scan on int8_tbl t1
-> Materialize
-> Nested Loop Left Join
- Join Filter: (t2.q1 = 1)
-> Seq Scan on int8_tbl t2
- -> Seq Scan on int8_tbl t3
-(7 rows)
+ -> Result
+ One-Time Filter: (t2.q1 = 1)
+ -> Seq Scan on int8_tbl t3
+(8 rows)
explain (costs off)
select * from onek t1
@@ -2732,20 +2745,21 @@ select * from onek t1
left join lateral
(select * from onek t3 where t3.two = t2.two offset 0) s
on t2.unique1 = 1;
- QUERY PLAN
---------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------
Nested Loop Left Join
-> Seq Scan on onek t1
-> Materialize
-> Nested Loop Left Join
- Join Filter: (t2.unique1 = 1)
-> Seq Scan on onek t2
- -> Memoize
- Cache Key: t2.two
- Cache Mode: binary
- -> Seq Scan on onek t3
- Filter: (two = t2.two)
-(11 rows)
+ -> Result
+ One-Time Filter: (t2.unique1 = 1)
+ -> Memoize
+ Cache Key: t2.two
+ Cache Mode: binary
+ -> Seq Scan on onek t3
+ Filter: (two = t2.two)
+(12 rows)
--
-- check a case where we formerly got confused by conflicting sort orders
@@ -5404,11 +5418,12 @@ select unique1, x from tenk1 left join
f_immutable_int4(1) x on unique1 = x;
QUERY PLAN
----------------------------------------------------
Nested Loop Left Join
- Join Filter: (tenk1.unique1 = 1)
-> Index Only Scan using tenk1_unique1 on tenk1
- -> Materialize
- -> Result
-(5 rows)
+ -> Result
+ One-Time Filter: (tenk1.unique1 = 1)
+ -> Materialize
+ -> Result
+(6 rows)
explain (costs off)
select unique1, x from tenk1 right join f_immutable_int4(1) x on unique1 = x;
@@ -5455,17 +5470,17 @@ from nt3 as nt3
) as ss2
on ss2.id = nt3.nt2_id
where nt3.id = 1 and ss2.b3;
- QUERY PLAN
-----------------------------------------------
+ QUERY PLAN
+-------------------------------------------------
Nested Loop Left Join
Filter: ((nt2.b1 OR ((0) = 42)))
-> Index Scan using nt3_pkey on nt3
Index Cond: (id = 1)
-> Nested Loop Left Join
- Join Filter: (0 = nt2.nt1_id)
-> Index Scan using nt2_pkey on nt2
Index Cond: (id = nt3.nt2_id)
-> Result
+ One-Time Filter: (0 = nt2.nt1_id)
(9 rows)
drop function f_immutable_int4(int);
@@ -5705,7 +5720,6 @@ select count(*) from
-------------------------------------------------------------------------
Aggregate
-> Nested Loop Left Join
- Join Filter: (a.unique2 = b.unique1)
-> Nested Loop
-> Nested Loop
-> Seq Scan on int4_tbl
@@ -5715,9 +5729,11 @@ select count(*) from
Index Cond: (thousand = int4_tbl.f1)
-> Index Scan using tenk1_unique1 on tenk1 a
Index Cond: (unique1 = b.unique2)
- -> Index Only Scan using tenk1_thous_tenthous on tenk1 c
- Index Cond: (thousand = a.thousand)
-(14 rows)
+ -> Result
+ One-Time Filter: (a.unique2 = b.unique1)
+ -> Index Only Scan using tenk1_thous_tenthous on tenk1 c
+ Index Cond: (thousand = a.thousand)
+(15 rows)
select count(*) from
tenk1 a join tenk1 b on a.unique1 = b.unique2
@@ -5742,7 +5758,6 @@ select b.unique1 from
-> Nested Loop Left Join
-> Seq Scan on int4_tbl i2
-> Nested Loop Left Join
- Join Filter: (b.unique1 = 42)
-> Nested Loop
-> Nested Loop
-> Seq Scan on int4_tbl i1
@@ -5750,9 +5765,11 @@ select b.unique1 from
Index Cond: ((thousand = i1.f1) AND (tenthous
= i2.f1))
-> Index Scan using tenk1_unique1 on tenk1 a
Index Cond: (unique1 = b.unique2)
- -> Index Only Scan using tenk1_thous_tenthous on tenk1 c
- Index Cond: (thousand = a.thousand)
-(15 rows)
+ -> Result
+ One-Time Filter: (b.unique1 = 42)
+ -> Index Only Scan using tenk1_thous_tenthous on tenk1 c
+ Index Cond: (thousand = a.thousand)
+(16 rows)
select b.unique1 from
tenk1 a join tenk1 b on a.unique1 = b.unique2
@@ -6051,39 +6068,41 @@ select t1.* from
on (t1.f1 = b1.d1)
left join int4_tbl i4
on (i8.q2 = i4.f1);
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------
Hash Left Join
Output: t1.f1
Hash Cond: (i8.q2 = i4.f1)
-> Nested Loop Left Join
Output: t1.f1, i8.q2
- Join Filter: (t1.f1 = '***'::text)
-> Seq Scan on public.text_tbl t1
Output: t1.f1
- -> Materialize
+ -> Result
Output: i8.q2
- -> Hash Right Join
+ One-Time Filter: (t1.f1 = '***'::text)
+ -> Materialize
Output: i8.q2
- Hash Cond: ((NULL::integer) = i8b1.q2)
- -> Hash Join
- Output: i8.q2, (NULL::integer)
- Hash Cond: (i8.q1 = i8b2.q1)
- -> Seq Scan on public.int8_tbl i8
- Output: i8.q1, i8.q2
+ -> Hash Right Join
+ Output: i8.q2
+ Hash Cond: ((NULL::integer) = i8b1.q2)
+ -> Hash Join
+ Output: i8.q2, (NULL::integer)
+ Hash Cond: (i8.q1 = i8b2.q1)
+ -> Seq Scan on public.int8_tbl i8
+ Output: i8.q1, i8.q2
+ -> Hash
+ Output: i8b2.q1, (NULL::integer)
+ -> Seq Scan on public.int8_tbl i8b2
+ Output: i8b2.q1, NULL::integer
-> Hash
- Output: i8b2.q1, (NULL::integer)
- -> Seq Scan on public.int8_tbl i8b2
- Output: i8b2.q1, NULL::integer
- -> Hash
- Output: i8b1.q2
- -> Seq Scan on public.int8_tbl i8b1
Output: i8b1.q2
+ -> Seq Scan on public.int8_tbl i8b1
+ Output: i8b1.q2
-> Hash
Output: i4.f1
-> Seq Scan on public.int4_tbl i4
Output: i4.f1
-(30 rows)
+(32 rows)
select t1.* from
text_tbl t1
@@ -6112,43 +6131,45 @@ select t1.* from
on (t1.f1 = b1.d1)
left join int4_tbl i4
on (i8.q2 = i4.f1);
- QUERY PLAN
-----------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------
Hash Left Join
Output: t1.f1
Hash Cond: (i8.q2 = i4.f1)
-> Nested Loop Left Join
Output: t1.f1, i8.q2
- Join Filter: (t1.f1 = '***'::text)
-> Seq Scan on public.text_tbl t1
Output: t1.f1
- -> Materialize
+ -> Result
Output: i8.q2
- -> Hash Right Join
+ One-Time Filter: (t1.f1 = '***'::text)
+ -> Materialize
Output: i8.q2
- Hash Cond: ((NULL::integer) = i8b1.q2)
-> Hash Right Join
- Output: i8.q2, (NULL::integer)
- Hash Cond: (i8b2.q1 = i8.q1)
- -> Nested Loop
- Output: i8b2.q1, NULL::integer
- -> Seq Scan on public.int8_tbl i8b2
- Output: i8b2.q1, i8b2.q2
- -> Materialize
- -> Seq Scan on public.int4_tbl i4b2
- -> Hash
- Output: i8.q1, i8.q2
- -> Seq Scan on public.int8_tbl i8
+ Output: i8.q2
+ Hash Cond: ((NULL::integer) = i8b1.q2)
+ -> Hash Right Join
+ Output: i8.q2, (NULL::integer)
+ Hash Cond: (i8b2.q1 = i8.q1)
+ -> Nested Loop
+ Output: i8b2.q1, NULL::integer
+ -> Seq Scan on public.int8_tbl i8b2
+ Output: i8b2.q1, i8b2.q2
+ -> Materialize
+ -> Seq Scan on public.int4_tbl
i4b2
+ -> Hash
Output: i8.q1, i8.q2
- -> Hash
- Output: i8b1.q2
- -> Seq Scan on public.int8_tbl i8b1
+ -> Seq Scan on public.int8_tbl i8
+ Output: i8.q1, i8.q2
+ -> Hash
Output: i8b1.q2
+ -> Seq Scan on public.int8_tbl i8b1
+ Output: i8b1.q2
-> Hash
Output: i4.f1
-> Seq Scan on public.int4_tbl i4
Output: i4.f1
-(34 rows)
+(36 rows)
select t1.* from
text_tbl t1
@@ -6178,46 +6199,48 @@ select t1.* from
on (t1.f1 = b1.d1)
left join int4_tbl i4
on (i8.q2 = i4.f1);
- QUERY PLAN
-----------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------
Hash Left Join
Output: t1.f1
Hash Cond: (i8.q2 = i4.f1)
-> Nested Loop Left Join
Output: t1.f1, i8.q2
- Join Filter: (t1.f1 = '***'::text)
-> Seq Scan on public.text_tbl t1
Output: t1.f1
- -> Materialize
+ -> Result
Output: i8.q2
- -> Hash Right Join
+ One-Time Filter: (t1.f1 = '***'::text)
+ -> Materialize
Output: i8.q2
- Hash Cond: ((NULL::integer) = i8b1.q2)
-> Hash Right Join
- Output: i8.q2, (NULL::integer)
- Hash Cond: (i8b2.q1 = i8.q1)
- -> Hash Join
- Output: i8b2.q1, NULL::integer
- Hash Cond: (i8b2.q1 = i4b2.f1)
- -> Seq Scan on public.int8_tbl i8b2
- Output: i8b2.q1, i8b2.q2
- -> Hash
- Output: i4b2.f1
- -> Seq Scan on public.int4_tbl i4b2
+ Output: i8.q2
+ Hash Cond: ((NULL::integer) = i8b1.q2)
+ -> Hash Right Join
+ Output: i8.q2, (NULL::integer)
+ Hash Cond: (i8b2.q1 = i8.q1)
+ -> Hash Join
+ Output: i8b2.q1, NULL::integer
+ Hash Cond: (i8b2.q1 = i4b2.f1)
+ -> Seq Scan on public.int8_tbl i8b2
+ Output: i8b2.q1, i8b2.q2
+ -> Hash
Output: i4b2.f1
- -> Hash
- Output: i8.q1, i8.q2
- -> Seq Scan on public.int8_tbl i8
+ -> Seq Scan on public.int4_tbl
i4b2
+ Output: i4b2.f1
+ -> Hash
Output: i8.q1, i8.q2
- -> Hash
- Output: i8b1.q2
- -> Seq Scan on public.int8_tbl i8b1
+ -> Seq Scan on public.int8_tbl i8
+ Output: i8.q1, i8.q2
+ -> Hash
Output: i8b1.q2
+ -> Seq Scan on public.int8_tbl i8b1
+ Output: i8b1.q2
-> Hash
Output: i4.f1
-> Seq Scan on public.int4_tbl i4
Output: i4.f1
-(37 rows)
+(39 rows)
select t1.* from
text_tbl t1
@@ -6311,8 +6334,8 @@ select 1 from
join int4_tbl i42 on ss1.a is null or i8.q1 <> i8.q2
right join (select 2 as b) ss2
on ss2.b < i4.f1;
- QUERY PLAN
------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------
Nested Loop Left Join
-> Result
-> Nested Loop
@@ -6320,16 +6343,17 @@ select 1 from
Join Filter: NULL::boolean
Filter: (((1) IS NULL) OR (i8.q1 <> i8.q2))
-> Nested Loop Left Join
- Join Filter: (i4.f1 IS NOT NULL)
-> Seq Scan on int4_tbl i4
Filter: (2 < f1)
- -> Materialize
- -> Seq Scan on int8_tbl i8
+ -> Result
+ One-Time Filter: (i4.f1 IS NOT NULL)
+ -> Materialize
+ -> Seq Scan on int8_tbl i8
-> Result
One-Time Filter: false
-> Materialize
-> Seq Scan on int4_tbl i42
-(16 rows)
+(17 rows)
--
-- test for appropriate join order in the presence of lateral references
@@ -6990,7 +7014,6 @@ from int8_tbl t1
QUERY PLAN
-------------------------------------------------
Nested Loop Left Join
- Join Filter: (t2.q2 < t3.unique2)
-> Nested Loop Left Join
Join Filter: (t2.q1 > t3.unique1)
-> Hash Left Join
@@ -7000,9 +7023,11 @@ from int8_tbl t1
-> Seq Scan on int8_tbl t2
-> Materialize
-> Seq Scan on onek t3
- -> Materialize
- -> Seq Scan on onek t4
-(13 rows)
+ -> Result
+ One-Time Filter: (t2.q2 < t3.unique2)
+ -> Materialize
+ -> Seq Scan on onek t4
+(14 rows)
-- bug #19460: we need to clean up RestrictInfos more than we had been doing
explain (costs off)
@@ -7287,11 +7312,12 @@ select 1 from a t1
-> Seq Scan on a t1
-> Materialize
-> Nested Loop Left Join
- Join Filter: (t2.id = 1)
-> Index Only Scan using a_pkey on a t2
Index Cond: (id = 1)
- -> Seq Scan on a t3
-(8 rows)
+ -> Result
+ One-Time Filter: (t2.id = 1)
+ -> Seq Scan on a t3
+(9 rows)
-- check join removal works when uniqueness of the join condition is enforced
-- by a UNION
@@ -8871,14 +8897,15 @@ explain (costs off)
select * from emp1 t1
inner join emp1 t2 on t1.id = t2.id
left join emp1 t3 on t1.id > 1 and t1.id < 2;
- QUERY PLAN
-----------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------
Nested Loop Left Join
- Join Filter: ((t2.id > 1) AND (t2.id < 2))
-> Seq Scan on emp1 t2
- -> Materialize
- -> Seq Scan on emp1 t3
-(5 rows)
+ -> Result
+ One-Time Filter: ((t2.id > 1) AND (t2.id < 2))
+ -> Materialize
+ -> Seq Scan on emp1 t3
+(6 rows)
-- Check that SJE doesn't replace the target relation
EXPLAIN (COSTS OFF)
@@ -8899,14 +8926,16 @@ EXPLAIN (COSTS OFF)
SELECT * FROM emp1 t1
INNER JOIN emp1 t2 ON t1.id = t2.id
LEFT JOIN emp1 t3 ON t1.code = 1 AND (t2.code = t3.code OR t2.code = 1);
- QUERY PLAN
----------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------
Nested Loop Left Join
- Join Filter: ((t2.code = 1) AND ((t2.code = t3.code) OR (t2.code = 1)))
+ Join Filter: ((t2.code = t3.code) OR (t2.code = 1))
-> Seq Scan on emp1 t2
- -> Materialize
- -> Seq Scan on emp1 t3
-(5 rows)
+ -> Result
+ One-Time Filter: (t2.code = 1)
+ -> Materialize
+ -> Seq Scan on emp1 t3
+(7 rows)
INSERT INTO emp1 VALUES (1, 1), (2, 1);
WITH t1 AS (SELECT * FROM emp1)
@@ -9081,11 +9110,12 @@ SELECT 1 AS c1 FROM sl sl1 LEFT JOIN (sl AS sl2 NATURAL
JOIN sl AS sl3)
Nested Loop Left Join
-> Seq Scan on sl sl1
-> Nested Loop Left Join
- Join Filter: sl3.bool_col
-> 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) AND bool_col)
- -> Seq Scan on sl sl4
-(7 rows)
+ -> Result
+ One-Time Filter: sl3.bool_col
+ -> Seq Scan on sl sl4
+(8 rows)
-- Check that quals of a jointree node that becomes empty when the self-join
-- is removed are not lost, and that they don't migrate above an outer join
@@ -11108,3 +11138,43 @@ SELECT COUNT(*) FROM onek t1 LEFT JOIN tenk1 t2
19000
(1 row)
+-- Outer-only ON-clauses become a gating Result on the inner side; for a
+-- parameter-independent inner, NestLoop.keep_inner_rewind retains REWIND so
+-- a Materialize below the gate replays its buffer across rescans rather than
+-- rebuilding it for every outer tuple that passes the gate.
+create function platform_independent_explain(query text) returns setof text
+language plpgsql as
+$$
+declare
+ ln text;
+begin
+ for ln in
+ execute format('explain (analyze, costs off, summary off, timing off,
buffers off) %s', query)
+ loop
+ ln := regexp_replace(ln, 'Maximum Storage: \d+', 'Maximum Storage: N');
+ return next ln;
+ end loop;
+end;
+$$;
+set enable_hashjoin = off;
+set enable_mergejoin = off;
+select platform_independent_explain('
+ select count(*) from onek t1 left join int4_tbl t2
+ on (t1.unique1 = t2.f1 and t1.hundred in (1, 2))');
+ platform_independent_explain
+----------------------------------------------------------------------------
+ Aggregate (actual rows=1.00 loops=1)
+ -> Nested Loop Left Join (actual rows=1000.00 loops=1)
+ Join Filter: (t1.unique1 = t2.f1)
+ Rows Removed by Join Filter: 1000
+ -> Seq Scan on onek t1 (actual rows=1000.00 loops=1)
+ -> Result (actual rows=1.00 loops=1000)
+ One-Time Filter: (t1.hundred = ANY ('{1,2}'::integer[]))
+ -> Materialize (actual rows=5.00 loops=200)
+ Storage: Memory Maximum Storage: NkB
+ -> Seq Scan on int4_tbl t2 (actual rows=5.00 loops=1)
+(10 rows)
+
+reset enable_hashjoin;
+reset enable_mergejoin;
+drop function platform_independent_explain(text);
diff --git a/src/test/regress/expected/predicate.out
b/src/test/regress/expected/predicate.out
index feae77cb840..357c1ad1e08 100644
--- a/src/test/regress/expected/predicate.out
+++ b/src/test/regress/expected/predicate.out
@@ -114,10 +114,9 @@ EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t1
FULL JOIN pred_tab t2 ON t1.a = t2.a
LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL;
- QUERY PLAN
--------------------------------------------
+ QUERY PLAN
+---------------------------------------------
Nested Loop Left Join
- Join Filter: (t2.a IS NOT NULL)
-> Merge Full Join
Merge Cond: (t1.a = t2.a)
-> Sort
@@ -126,9 +125,11 @@ SELECT * FROM pred_tab t1
-> Sort
Sort Key: t2.a
-> Seq Scan on pred_tab t2
- -> Materialize
- -> Seq Scan on pred_tab t3
-(12 rows)
+ -> Result
+ One-Time Filter: (t2.a IS NOT NULL)
+ -> Materialize
+ -> Seq Scan on pred_tab t3
+(13 rows)
-- Ensure the IS_NULL qual is reduced to constant-FALSE, since a) it's on a NOT
-- NULL column, and b) its Var is not nullable by any outer joins
@@ -151,18 +152,20 @@ EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t1
LEFT JOIN pred_tab t2 ON t1.a = 1
LEFT JOIN pred_tab t3 ON t2.a IS NULL;
- QUERY PLAN
--------------------------------------------
+ QUERY PLAN
+-------------------------------------------------
Nested Loop Left Join
- Join Filter: (t2.a IS NULL)
-> Nested Loop Left Join
- Join Filter: (t1.a = 1)
-> Seq Scan on pred_tab t1
+ -> Result
+ One-Time Filter: (t1.a = 1)
+ -> Materialize
+ -> Seq Scan on pred_tab t2
+ -> Result
+ One-Time Filter: (t2.a IS NULL)
-> Materialize
- -> Seq Scan on pred_tab t2
- -> Materialize
- -> Seq Scan on pred_tab t3
-(9 rows)
+ -> Seq Scan on pred_tab t3
+(11 rows)
--
-- Tests for OR clauses in join clauses
@@ -185,10 +188,9 @@ EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t1
FULL JOIN pred_tab t2 ON t1.a = t2.a
LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL OR t2.b = 1;
- QUERY PLAN
----------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------
Nested Loop Left Join
- Join Filter: ((t2.a IS NOT NULL) OR (t2.b = 1))
-> Merge Full Join
Merge Cond: (t1.a = t2.a)
-> Sort
@@ -197,9 +199,11 @@ SELECT * FROM pred_tab t1
-> Sort
Sort Key: t2.a
-> Seq Scan on pred_tab t2
- -> Materialize
- -> Seq Scan on pred_tab t3
-(12 rows)
+ -> Result
+ One-Time Filter: ((t2.a IS NOT NULL) OR (t2.b = 1))
+ -> Materialize
+ -> Seq Scan on pred_tab t3
+(13 rows)
-- Ensure the OR clause is reduced to constant-FALSE when all OR branches are
-- provably false
@@ -222,18 +226,20 @@ EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t1
LEFT JOIN pred_tab t2 ON t1.a = 1
LEFT JOIN pred_tab t3 ON t2.a IS NULL OR t2.c IS NULL;
- QUERY PLAN
----------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------
Nested Loop Left Join
- Join Filter: ((t2.a IS NULL) OR (t2.c IS NULL))
-> Nested Loop Left Join
- Join Filter: (t1.a = 1)
-> Seq Scan on pred_tab t1
+ -> Result
+ One-Time Filter: (t1.a = 1)
+ -> Materialize
+ -> Seq Scan on pred_tab t2
+ -> Result
+ One-Time Filter: ((t2.a IS NULL) OR (t2.c IS NULL))
-> Materialize
- -> Seq Scan on pred_tab t2
- -> Materialize
- -> Seq Scan on pred_tab t3
-(9 rows)
+ -> Seq Scan on pred_tab t3
+(11 rows)
--
-- Tests for NullTest reduction in EXISTS sublink
@@ -244,26 +250,27 @@ SELECT * FROM pred_tab t1
LEFT JOIN pred_tab t2 ON EXISTS
(SELECT 1 FROM pred_tab t3, pred_tab t4, pred_tab t5, pred_tab t6
WHERE t1.a = t3.a AND t6.a IS NOT NULL);
- QUERY PLAN
----------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------
Nested Loop Left Join
- Join Filter: EXISTS(SubPlan exists_1)
-> Seq Scan on pred_tab t1
- -> Materialize
- -> Seq Scan on pred_tab t2
- SubPlan exists_1
- -> Nested Loop
+ -> Result
+ One-Time Filter: EXISTS(SubPlan exists_1)
+ -> Materialize
+ -> Seq Scan on pred_tab t2
+ SubPlan exists_1
-> Nested Loop
-> Nested Loop
- -> Seq Scan on pred_tab t4
+ -> Nested Loop
+ -> Seq Scan on pred_tab t4
+ -> Materialize
+ -> Seq Scan on pred_tab t3
+ Filter: (t1.a = a)
-> Materialize
- -> Seq Scan on pred_tab t3
- Filter: (t1.a = a)
+ -> Seq Scan on pred_tab t5
-> Materialize
- -> Seq Scan on pred_tab t5
- -> Materialize
- -> Seq Scan on pred_tab t6
-(17 rows)
+ -> Seq Scan on pred_tab t6
+(18 rows)
-- Ensure the IS_NULL qual is reduced to constant-FALSE
EXPLAIN (COSTS OFF)
@@ -354,15 +361,16 @@ SELECT * FROM pred_tab t1
-> Seq Scan on pred_tab t1
-> Materialize
-> Nested Loop Left Join
- Join Filter: (t3.b IS NOT NULL)
-> Nested Loop Left Join
Join Filter: (t2.a = t3.a)
-> Seq Scan on pred_tab t2
-> Materialize
-> Seq Scan on pred_tab_notnull t3
- -> Materialize
- -> Seq Scan on pred_tab t4
-(12 rows)
+ -> Result
+ One-Time Filter: (t3.b IS NOT NULL)
+ -> Materialize
+ -> Seq Scan on pred_tab t4
+(13 rows)
SELECT * FROM pred_tab t1
LEFT JOIN pred_tab t2 ON TRUE
@@ -384,21 +392,22 @@ SELECT * FROM pred_tab t1
LEFT JOIN pred_tab t2 ON TRUE
LEFT JOIN pred_tab_notnull t3 ON t2.a = t3.a
LEFT JOIN pred_tab t4 ON t3.b IS NULL AND t3.a IS NOT NULL;
- QUERY PLAN
---------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------
Nested Loop Left Join
-> Seq Scan on pred_tab t1
-> Materialize
-> Nested Loop Left Join
- Join Filter: ((t3.b IS NULL) AND (t3.a IS NOT NULL))
-> Nested Loop Left Join
Join Filter: (t2.a = t3.a)
-> Seq Scan on pred_tab t2
-> Materialize
-> Seq Scan on pred_tab_notnull t3
- -> Materialize
- -> Seq Scan on pred_tab t4
-(12 rows)
+ -> Result
+ One-Time Filter: ((t3.b IS NULL) AND (t3.a IS NOT NULL))
+ -> Materialize
+ -> Seq Scan on pred_tab t4
+(13 rows)
SELECT * FROM pred_tab t1
LEFT JOIN pred_tab t2 ON TRUE
diff --git a/src/test/regress/expected/subselect.out
b/src/test/regress/expected/subselect.out
index ce0ff764417..20ffdf01982 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -3789,16 +3789,17 @@ EXPLAIN (COSTS OFF)
SELECT * FROM not_null_tab t1
LEFT JOIN not_null_tab t2
ON t1.id NOT IN (SELECT id FROM not_null_tab);
- QUERY PLAN
-------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------
Nested Loop Left Join
- Join Filter: (NOT (ANY (t1.id = (hashed SubPlan any_1).col1)))
-> Seq Scan on not_null_tab t1
- -> Materialize
- -> Seq Scan on not_null_tab t2
- SubPlan any_1
- -> Seq Scan on not_null_tab
-(7 rows)
+ -> Result
+ One-Time Filter: (NOT (ANY (t1.id = (hashed SubPlan any_1).col1)))
+ -> Materialize
+ -> Seq Scan on not_null_tab t2
+ SubPlan any_1
+ -> Seq Scan on not_null_tab
+(8 rows)
-- ANTI JOIN: outer side is defined NOT NULL and is not nulled by outer join,
-- inner side is defined NOT NULL
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 9533af8656e..2194d5df64e 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -4271,3 +4271,30 @@ SELECT COUNT(*) FROM onek t1 LEFT JOIN tenk1 t2
ON (t2.thousand = t1.tenthous OR t2.thousand = t1.thousand);
SELECT COUNT(*) FROM onek t1 LEFT JOIN tenk1 t2
ON (t2.thousand = t1.tenthous OR t2.thousand = t1.thousand);
+
+-- Outer-only ON-clauses become a gating Result on the inner side; for a
+-- parameter-independent inner, NestLoop.keep_inner_rewind retains REWIND so
+-- a Materialize below the gate replays its buffer across rescans rather than
+-- rebuilding it for every outer tuple that passes the gate.
+create function platform_independent_explain(query text) returns setof text
+language plpgsql as
+$$
+declare
+ ln text;
+begin
+ for ln in
+ execute format('explain (analyze, costs off, summary off, timing off,
buffers off) %s', query)
+ loop
+ ln := regexp_replace(ln, 'Maximum Storage: \d+', 'Maximum Storage: N');
+ return next ln;
+ end loop;
+end;
+$$;
+set enable_hashjoin = off;
+set enable_mergejoin = off;
+select platform_independent_explain('
+ select count(*) from onek t1 left join int4_tbl t2
+ on (t1.unique1 = t2.f1 and t1.hundred in (1, 2))');
+reset enable_hashjoin;
+reset enable_mergejoin;
+drop function platform_independent_explain(text);
--
2.55.0
From e0d0dc940ad78d3a92810cbaa83abacd9bc3bb50 Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <[email protected]>
Date: Fri, 3 Jul 2026 11:58:04 +0200
Subject: [PATCH v2 2/2] pg_plan_advice: look through gating Results on the
NestLoop inner side
The preceding commit introduced a new plan shape: the inner side of a
NestLoop may now be capped by a gating Result that evaluates outer-only
join clauses as a one-time filter, sitting above any Material or Memoize
node.
pgpa_decompose_join() was unaware of this.
Teach the NestLoop case to descend through a gating Result before the strategy
checks. This restores the node ordering the rest of the function
expects and classifies the strategy as MATERIALIZE/MEMOIZE again. A
childless Result is deliberately not descended: it is itself the scan
and carries its own RTIs.
---
contrib/pg_plan_advice/pgpa_join.c | 10 ++++++++++
1 file changed, 10 insertions(+)
diff --git a/contrib/pg_plan_advice/pgpa_join.c
b/contrib/pg_plan_advice/pgpa_join.c
index e69c25551a0..f3f2ad62f7c 100644
--- a/contrib/pg_plan_advice/pgpa_join.c
+++ b/contrib/pg_plan_advice/pgpa_join.c
@@ -388,6 +388,16 @@ pgpa_decompose_join(pgpa_plan_walker_context *walker, Plan
*plan,
case T_NestLoop:
+ /*
+ * create_nestloop_plan() may have capped the inner
side with a
+ * gating Result that evaluates outer-only join clauses
as a
+ * one-time filter. It sits above any Material or
Memoize node,
+ * so look through it before determining the join
strategy.
+ */
+ if (elidedinner == NULL &&
is_result_node_with_child(innerplan) &&
+ ((Result *) innerplan)->resconstantqual != NULL)
+ elidedinner = pgpa_descend_node(pstmt,
&innerplan);
+
/*
* The planner may have chosen to place a Material or
Memoize node
* on the inner side of the NestLoop; if this is
present, we
--
2.55.0
