Hi,
I think we should continue this discussion.
Andreas' idea for a gating Result node seems more interesting than the original
approach and is also looks less invasive.
Here is an updated patch that outlines the solution. It still needs to fix some
issues, benchmarking, and write more tests, but the results so far look
promising. For example, in the reproduction script provided in this thread
earlier, it greatly cuts down the number of inner table scans:
Nested Loop Left Join (actual rows=10036.00 loops=1)
-> Nested Loop Left Join (actual rows=10036.00 loops=1)
-> Seq Scan on products p (actual rows=10036.00 loops=1)
Filter: (price > '0.9'::double precision)
Rows Removed by Filter: 89964
-> Result (actual rows=0.50 loops=10036)
One-Time Filter: (p.type = 'p'::text)
-> Index Scan using phones_pkey on phones ph
(actual rows=1.00 loops=5026)
Index Cond: (id = p.id)
Index Searches: 5026
-> Result (actual rows=0.50 loops=10036)
One-Time Filter: (p.type = 'v'::text)
-> Index Scan using vehicles_pkey on vehicles v
(actual rows=1.00 loops=5010)
Index Cond: (id = p.id)
Index Searches: 5010
The main question for me now is: is it possible to introduce gating into the
optimisation stage and let the planner pick a 'gated' nest loop based on cost
estimates? To do that, we need to introduce one more node, such as GatingPath,
or change ProjectionPath slightly. As this is a more invasive approach, I prefer
the current one unless evidence emerges that a Path machinery change would be
beneficial for something else.
--
regards, Andrei Lepikhov,
pgEdgeFrom 2b7795cf1496f20ba7b2edf5cdd1dc6ba2c69af3 Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <[email protected]>
Date: Tue, 26 May 2026 15:53:04 +0200
Subject: [PATCH] Gate the NestLoop inner side with outer-only join clauses
When a NestLoop's joinrestrictinfo holds clauses that reference only the outer
relation, evaluating them as a Join Filter is wasteful: the entire inner
subplan is re-executed for every outer tuple and every resulting row is
discarded whenever the clause is false. The inner scan does no useful work in
those iterations and need not be started at all.
create_nestloop_path() collects the qualifying RestrictInfos into a new
JoinPath field, outer_clauses. Only genuine join quals referencing solely
the outer rel are eligible; pseudoconstant, pushed-down and no-relids
clauses are skipped, since those are post-join filters with different
outer-join semantics. create_nestloop_plan() removes the collected clauses
from joinclauses, parameterizes them with replace_nestloop_params(), and
wraps the inner plan in a make_gating_result() Result carrying them as
resconstantqual.
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,
so this is a runtime-only win the planner does not yet credit.
---
.../postgres_fdw/expected/postgres_fdw.out | 14 +-
src/backend/optimizer/plan/createplan.c | 89 ++++-
src/test/regress/expected/create_index.out | 21 +-
src/test/regress/expected/join.out | 375 ++++++++++--------
src/test/regress/expected/predicate.out | 113 +++---
src/test/regress/expected/subselect.out | 17 +-
6 files changed, 380 insertions(+), 249 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out
b/contrib/postgres_fdw/expected/postgres_fdw.out
index e90289e4ab1..7aa86c057d5 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -2457,16 +2457,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/optimizer/plan/createplan.c
b/src/backend/optimizer/plan/createplan.c
index de6a183da79..a08660ccd14 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -4191,8 +4191,10 @@ create_nestloop_plan(PlannerInfo *root,
Plan *outer_plan;
Plan *inner_plan;
Relids outerrelids;
+ 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;
List *joinclauses;
List *otherclauses;
List *nestParams;
@@ -4238,8 +4240,29 @@ 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.
+ *
+ * Take only genuine join quals (non-pushed-down); a
pushed-down filter
+ * has different outer-join semantics and must never move into
the gate.
+ * Kept beside extract_actual_join_clauses() so both apply the
same
+ * joinrelids and cannot disagree on what is pushed down.
Inner and
+ * semi joins never arrive here with such a clause: it would be
a
+ * single-rel restriction already pushed down to the outer scan.
+ */
+ foreach_node(RestrictInfo, rinfo, joinrestrictclauses)
+ {
+ if (!rinfo->pseudoconstant &&
+ !RINFO_IS_PUSHED_DOWN(rinfo, joinrelids) &&
+ !bms_is_empty(rinfo->clause_relids) &&
+ bms_is_subset(rinfo->clause_relids,
outerrelids))
+ gating_clauses = lappend(gating_clauses,
rinfo->clause);
+ }
}
else
{
@@ -4248,6 +4271,15 @@ 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. Match by Expr pointer (shared, since
both
+ * lists derive from the same RestrictInfos), and do it 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)
{
@@ -4257,6 +4289,61 @@ create_nestloop_plan(PlannerInfo *root,
replace_nestloop_params(root, (Node *) otherclauses);
}
+ /*
+ * Outer-only clauses are constant across the inner scan for a given
outer
+ * tuple, so applying them as a per-rescan gate on the inner side is
+ * equivalent to the usual per-row join filter: when the gate is false
the
+ * inner side yields no rows, which for an outer join is the same
+ * null-extended result, but skips the inner scan entirely.
+ *
+ * The clauses must be parameterized (outer Vars -> NestLoop params) so
the
+ * gating Result can evaluate them against the current outer tuple.
+ */
+ if (gating_clauses != NIL)
+ {
+ Relids tmpOuterRels = root->curOuterRels;
+ Plan *subplan = inner_plan;
+
+ /*
+ * Every Var in the gating qual must belong to this nestloop's
outer
+ * side; otherwise replace_nestloop_params() would strand an
outer Var
+ * on the inner side or mint a param no nestloop supplies.
Re-checks
+ * the clause_relids test from the collection loop above.
+ */
+ Assert(bms_is_subset(pull_varnos(root, (Node *) gating_clauses),
+ outerrelids));
+
+ /*
+ * Unlike the joinclauses above, this runs regardless of
param_info:
+ * the inner side always needs the outer Vars as params.
+ *
+ * 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;
+
+ /*
+ * resconstantqual takes the clause list directly:
ExecInitResult feeds
+ * it to ExecInitQual, which reads an implicit-AND list.
+ *
+ * copy_plan_costsize() carries the child's costs and
parallel-safety
+ * onto the Result unchanged: final_cost_nestloop() does not
model the
+ * skipped inner rescans, so the gate is a runtime-only win the
planner
+ * does not credit (cf. create_gating_plan()).
+ */
+ inner_plan = (Plan *) make_gating_result(subplan->targetlist,
+
(Node *) gating_clauses,
+
subplan);
+ copy_plan_costsize(inner_plan, subplan);
+ }
+
/*
* Identify any nestloop parameters that should be supplied by this join
* node, and remove them from root->curOuterParams.
diff --git a/src/test/regress/expected/create_index.out
b/src/test/regress/expected/create_index.out
index 55538c4c41e..71fef89354a 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2250,19 +2250,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 78bf022f7b4..22c58664a73 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
@@ -4630,11 +4644,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;
@@ -4681,18 +4696,19 @@ 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
-(9 rows)
+ One-Time Filter: (0 = nt2.nt1_id)
+ -> Result
+(10 rows)
drop function f_immutable_int4(int);
-- test inlining when function returns composite
@@ -4931,7 +4947,6 @@ select count(*) from
-------------------------------------------------------------------------
Aggregate
-> Nested Loop Left Join
- Join Filter: (a.unique2 = b.unique1)
-> Nested Loop
-> Nested Loop
-> Seq Scan on int4_tbl
@@ -4941,9 +4956,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
@@ -4968,7 +4985,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
@@ -4976,9 +4992,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
@@ -5277,39 +5295,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
@@ -5338,43 +5358,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
@@ -5404,46 +5426,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
@@ -5537,8 +5561,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
@@ -5546,16 +5570,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
@@ -6216,7 +6241,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
@@ -6226,9 +6250,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)
@@ -6480,11 +6506,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
@@ -7931,14 +7958,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)
@@ -7959,14 +7987,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)
@@ -8141,11 +8171,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: (bool_col AND (a IS NOT NULL) AND (b IS NOT NULL) AND
(c IS NOT NULL) AND (bool_col IS NOT NULL))
- -> Seq Scan on sl sl4
-(7 rows)
+ -> Result
+ One-Time Filter: sl3.bool_col
+ -> Seq Scan on sl sl4
+(8 rows)
-- Check optimization disabling if it will violate special join conditions.
-- Two identical joined relations satisfies self join removal conditions but
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 a3778c23c34..06013614868 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -3575,16 +3575,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
--
2.54.0
