On 01/09/2026 11:16, Andrei Lepikhov wrote:
> On 28/07/2026 10:13, Andrei Lepikhov wrote:
>
> Just a rebase onto current master
>
Another rebase to follow the master branch.
I have also looked at the downside of the opportunistic approach this feature
takes. The pros are obvious: zero planning overhead, no search-space blowup, and
simple code. But what does the worst case look like?
By gating the inner side, we add one more node (Result) and force the gating
qual to be evaluated once per outer row. The vanilla NestLoop sometimes does not
do that: when the inner side returns no tuple, a LEFT join goes straight to the
null-fill path and ExecQual(joinqual) is never reached at all. So the gated plan
pays for the clause on every outer row. I think a parameterised index scan that
finds no match is the common case for this behaviour.
The worst query therefore looks like this:
CREATE TABLE o (a int, t text);
INSERT INTO o SELECT g, 'row'||g FROM generate_series(1,1000000) g;
CREATE TABLE i (a int, b int);
ANALYZE o, i;
SET enable_hashjoin = off;
SET enable_mergejoin = off;
SET max_parallel_workers_per_gather = 0;
\timing on
SELECT count(*) FROM ( SELECT * FROM o
LEFT JOIN i ON (md5(md5(md5(md5(o.t)))) <> 'z')) s;
Making the gate progressively more expensive shows what is going on.
On the current master, I see ~470ms, with the gating clause ~1500ms. This effect
is linear but isn't limited; it depends on the number of evaluations and the
outer size.
The basic case, when each row passes the gate (and correspondent join clause)
doesn't show any degradation, there are no significant difference between gated
and no-gated approach. To reproduce just add one row into the inner table and
re-execute the query above:
INSERT INTO i (a,b) VALUES (1, 1);
My conclusion is this. A gated NestLoop usually behaves like a normal NestLoop
adding benefit of reduced inner scans or nothing. In the degenerate case where
the inner side returns nothing, it adds an overhead proportional to the outer
size times the cost of the gating clause, with no ceiling. Separately, the
Result projection adds a per-joined- tuple cost that grows with the width of the
inner targetlist.
I do not see this as a fundamental problem, except for SubPlan cases - but
SubPlan is a different animal, and every new correlated pull-up technique
attracts complaints of exactly this shape.
So the real question is whether the cost model can account for these two terms
well enough to choose between a gated and a non-gated NestLoop, assuming we turn
this into a cost-based optimisation.
--
regards, Andrei Lepikhov,
pgEdge
From a7954088609ce524998d6fc51a3b1f902c7750c1 Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <[email protected]>
Date: Fri, 11 Sep 2026 16:39:23 +0200
Subject: [PATCH v3 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 | 94 +++-
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, 460 insertions(+), 252 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out
b/contrib/postgres_fdw/expected/postgres_fdw.out
index a6295674daf..8421c37f8de 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 430f6557f1f..958fca137f0 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -4201,8 +4201,11 @@ create_nestloop_plan(PlannerInfo *root,
Relids outerrelids;
Relids req_outer;
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;
@@ -4257,8 +4260,25 @@ 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
{
@@ -4267,6 +4287,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)
{
@@ -4302,6 +4330,68 @@ create_nestloop_plan(PlannerInfo *root,
}
}
+ /*
+ * 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.
@@ -4374,6 +4464,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 051c3539930..74fd18800ff 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
@@ -5465,11 +5479,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;
@@ -5516,17 +5531,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);
@@ -5766,7 +5781,6 @@ select count(*) from
-------------------------------------------------------------------------
Aggregate
-> Nested Loop Left Join
- Join Filter: (a.unique2 = b.unique1)
-> Nested Loop
-> Nested Loop
-> Seq Scan on int4_tbl
@@ -5776,9 +5790,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
@@ -5803,7 +5819,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
@@ -5811,9 +5826,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
@@ -6112,39 +6129,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
@@ -6173,43 +6192,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
@@ -6239,46 +6260,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
@@ -6372,8 +6395,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
@@ -6381,16 +6404,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
@@ -7229,7 +7253,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
@@ -7239,9 +7262,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)
@@ -7526,11 +7551,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
@@ -9110,14 +9136,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)
@@ -9138,14 +9165,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)
@@ -9320,11 +9349,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
@@ -11347,3 +11377,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 cf295d56507..951dc8db589 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -3816,16 +3816,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 c30fd339f13..99ca02bc43d 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -4389,3 +4389,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 65f97b4d8eafea7b0f8a5878f367f8df6188817c Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <[email protected]>
Date: Fri, 11 Sep 2026 16:39:31 +0200
Subject: [PATCH v3 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
