Hi,
When I proposed v3 for the original wrong-result issue [1], it
recorded the relevant deferred outer joins in a new RestrictInfo
field. As noted in the review:
> However, your v3 adds a new field to RestrictInfo, which breaks ABI
> and can't be back-patched.
That concern prevented us from using the v3 approach.
Commit f4002899 instead fixed the correctness issue conservatively by
retaining every constant-propagated clone clause and marking it with
norm_selec greater than 1 so that it contributes no additional
selectivity.
The attached patch revisits the optimization without adding any field
to RestrictInfo.
Consider an outer-join clause:
OUTERVAR = INNERVAR
If OUTERVAR is known to equal a constant,
reconsider_outer_join_clauses() derives:
INNERVAR = CONSTANT
The original equality is then redundant as long as OUTERVAR has not
been null-extended before the clause is evaluated.
If a lower outer join has already null-extended OUTERVAR, however, the
original strict clause is still required to prevent an inner row
satisfying INNERVAR = CONSTANT from matching the null-extended row.
Normally, incompatible_relids is sufficient to reject a clone at such
an evaluation level, because the lower outer join's relid appears in
the available relids.
The exception is a pushed-down outer join that has already been
physically performed but whose OJ relid is still deferred until its
commuting lower outer join completes.
This is the case addressed by f4002899.
The patch uses the existing norm_selec marker to identify clone
clauses made redundant by constant propagation.
When selecting clauses for a particular join or parameterized path, it
checks whether such a deferred outer join may already have
null-extended the clause's outer operand.
It derives this entirely from the existing RestrictInfo and
SpecialJoinInfo fields, including incompatible_relids, outer_relids,
min_lefthand, min_righthand, syn_righthand, and commute_below_l.
If such an outer join may have been performed, the original clause is
retained. Otherwise, it is omitted as redundant.
The clause is not removed during reconsider_outer_join_clauses(). It
is still distributed to the relations and remains available during
join-order search.
It is omitted only later, when constructing the restriction list for a
specific join or parameterized path, after it has served its
join-search purpose.
At that point, omitting it is equivalent to using the constant-TRUE
replacement for that particular placement.
The regression test changes are plan-only changes. The new plans
choose join orders in which the propagated constant restrictions make
the clone clauses redundant, while clauses needed to reject
null-extended rows remain in place.
Thus, this patch restores the elimination of redundant outer-join
clone clauses while preserving the correctness fix from f4002899 and
without changing the RestrictInfo ABI.
[1]
https://www.postgresql.org/message-id/19765-5778b18918040583%40postgresql.org
--
Thanks,
Tender Wang
From 20be17ddbe09fbffe0ff10bbbec303e365d210c6 Mon Sep 17 00:00:00 2001
From: Tender Wang <[email protected]>
Date: Mon, 9 Nov 2026 19:28:10 +0800
Subject: [PATCH] Remove redundant constant-propagated outer-join clauses
reconsider_outer_join_clauses() can derive an inner-side restriction
INNERVAR = CONSTANT when the outer operand of an outer-join clause is
known equal to the same constant. The original outer-join clause is
then redundant as long as the outer operand has not been null-extended.
Clone clauses cannot always be replaced with constant TRUE, because a
pushed-down outer join may have already null-extended the outer operand
while its OJ relid is still absent from the joinrel. In that case the
original strict clause is needed to reject the null-extended row.
Commit f4002899 therefore conservatively retained all such clone
clauses, which can leave unnecessary join filters in other join orders.
Use the existing norm_selec > 1 marker to identify clone clauses made
redundant by constant propagation. When selecting clauses for a join
or parameterized path, check whether an incompatible outer join that
can null the outer operand has both minimum inputs available while its
OJ relid is still deferred. Retain the clause in that case; otherwise
omit it as redundant.
This restores removal of unnecessary join filters without adding
additional state to RestrictInfo.
---
src/backend/optimizer/util/relnode.c | 90 ++++++++++++++++++++++++++++
src/test/regress/expected/join.out | 41 ++++++-------
2 files changed, 108 insertions(+), 23 deletions(-)
diff --git a/src/backend/optimizer/util/relnode.c
b/src/backend/optimizer/util/relnode.c
index 90a687c32da..f15658aabce 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -66,6 +66,9 @@ static List *build_joinrel_restrictlist(PlannerInfo *root,
#ifdef USE_ASSERT_CHECKING
static bool no_duplicate_clause_serials(List *clauses);
#endif
+static bool clone_clause_is_still_needed(PlannerInfo *root,
+
RestrictInfo *rinfo,
+
Relids available_relids);
static void build_joinrel_joinlist(RelOptInfo *joinrel,
RelOptInfo
*outer_rel,
RelOptInfo
*inner_rel);
@@ -1453,6 +1456,82 @@ no_duplicate_clause_serials(List *clauses)
}
#endif
+/*
+ * clone_clause_is_still_needed
+ * Check whether a clone clause made redundant by constant
propagation
+ * is still needed at this join level.
+ *
+ * reconsider_outer_join_clauses() marks such a clause with norm_selec > 1.
+ * Usually it can be omitted because the derived constant restrictions imply
+ * it. However, it is still needed if its outer operand has already been
+ * null-extended by a pushed-down outer join whose OJ relid has not yet been
+ * added to the joinrel. In that case the strict original clause must reject
+ * the null-extended row.
+ */
+static bool
+clone_clause_is_still_needed(PlannerInfo *root, RestrictInfo *rinfo,
+ Relids
available_relids)
+{
+ Relids left_baserels;
+ Relids right_baserels;
+ Relids outer_baserels;
+
+ Assert(rinfo->norm_selec > 1.0);
+ Assert(rinfo->has_clone || rinfo->is_clone);
+
+ /*
+ * Identify the relids used by the clause's outer operand. Strip OJ
+ * relids first, since clone variants can carry different nulling bits.
+ */
+ left_baserels = bms_intersect(rinfo->left_relids, root->all_baserels);
+ right_baserels = bms_intersect(rinfo->right_relids, root->all_baserels);
+ if (bms_is_subset(left_baserels, rinfo->outer_relids) &&
+ !bms_overlap(right_baserels, rinfo->outer_relids))
+ outer_baserels = left_baserels;
+ else if (bms_is_subset(right_baserels, rinfo->outer_relids) &&
+ !bms_overlap(left_baserels, rinfo->outer_relids))
+ outer_baserels = right_baserels;
+ else
+ {
+ bms_free(left_baserels);
+ bms_free(right_baserels);
+ return true; /* be conservative */
+ }
+
+ foreach_node(SpecialJoinInfo, sjinfo, root->join_info_list)
+ {
+ /*
+ * This OJ is interesting only if it can null the outer
operand, is
+ * incompatible with this clone variant, and can have its relid
+ * withheld after it has been physically performed.
+ */
+ if (sjinfo->ojrelid == 0 ||
+ !bms_is_member(sjinfo->ojrelid,
rinfo->incompatible_relids) ||
+ !bms_overlap(sjinfo->syn_righthand, outer_baserels) ||
+ bms_is_empty(sjinfo->commute_below_l))
+ continue;
+
+ /*
+ * Both minimum inputs are present, so this OJ has physically
+ * null-extended its RHS. Its relid is nevertheless absent
because
+ * a commuting lower outer join has not been completed yet.
+ */
+ if (!bms_is_member(sjinfo->ojrelid, available_relids) &&
+ bms_is_subset(sjinfo->min_lefthand, available_relids) &&
+ bms_is_subset(sjinfo->min_righthand, available_relids)
&&
+ !bms_is_subset(sjinfo->commute_below_l,
available_relids))
+ {
+ bms_free(left_baserels);
+ bms_free(right_baserels);
+ return true;
+ }
+ }
+
+ bms_free(left_baserels);
+ bms_free(right_baserels);
+ return false;
+}
+
/*
* build_joinrel_restrictlist
* build_joinrel_joinlist
@@ -1585,6 +1664,11 @@ subbuild_joinrel_restrictlist(PlannerInfo *root,
continue;
if (bms_overlap(rinfo->incompatible_relids,
both_input_relids))
continue;
+ /* Drop a redundant clone unless a deferred OJ
still needs it. */
+ if (rinfo->norm_selec > 1.0 &&
+ !clone_clause_is_still_needed(root,
rinfo,
+
both_input_relids))
+ continue;
}
else
{
@@ -1827,6 +1911,9 @@ get_baserel_parampathinfo(PlannerInfo *root, RelOptInfo
*baserel,
continue;
if (bms_is_member(rinfo->rinfo_serial, pserials))
continue;
+ if (rinfo->norm_selec > 1.0 &&
+ !clone_clause_is_still_needed(root, rinfo,
joinrelids))
+ continue;
}
pclauses = lappend(pclauses, rinfo);
@@ -1973,6 +2060,9 @@ get_joinrel_parampathinfo(PlannerInfo *root, RelOptInfo
*joinrel,
continue;
if (bms_is_member(rinfo->rinfo_serial, pserials))
continue;
+ if (rinfo->norm_selec > 1.0 &&
+ !clone_clause_is_still_needed(root, rinfo,
join_and_req))
+ continue;
}
pclauses = lappend(pclauses, rinfo);
diff --git a/src/test/regress/expected/join.out
b/src/test/regress/expected/join.out
index 7773852e61b..a7c0c80db2f 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4303,22 +4303,20 @@ select * from
left join zt3 on (f2 = f3)
left join zt1 zt1b on (f3 = zt1b.f1)
where zt1.f1 = 53;
- QUERY PLAN
----------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------
Nested Loop Left Join
- Join Filter: (zt3.f3 = zt1b.f1)
+ -> Index Only Scan using zt1_pkey on zt1
+ Index Cond: (f1 = 53)
-> Nested Loop Left Join
- Join Filter: (zt2.f2 = zt3.f3)
+ -> Index Only Scan using zt2_pkey on zt2
+ Index Cond: (f2 = 53)
-> Nested Loop Left Join
- -> Index Only Scan using zt1_pkey on zt1
+ -> Index Only Scan using zt3_pkey on zt3
+ Index Cond: (f3 = 53)
+ -> Index Only Scan using zt1_pkey on zt1 zt1b
Index Cond: (f1 = 53)
- -> Index Only Scan using zt2_pkey on zt2
- Index Cond: (f2 = 53)
- -> Index Only Scan using zt3_pkey on zt3
- Index Cond: (f3 = 53)
- -> Index Only Scan using zt1_pkey on zt1 zt1b
- Index Cond: (f1 = 53)
-(13 rows)
+(11 rows)
select * from
zt1 left join zt2 on (zt1.f1 = f2)
@@ -4335,23 +4333,21 @@ explain (costs off)
select * from
zt1 left join zt1 zt1b on (zt1.f1 = zt1b.f1 and zt1b.f1 = 53)
left join (zt3 left join zt2 on (f3 = f2)) on (zt1b.f1 = f3);
- QUERY PLAN
---------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------
Hash Left Join
Hash Cond: (zt1.f1 = zt1b.f1)
-> Seq Scan on zt1
-> Hash
-> Nested Loop Left Join
- Join Filter: (zt3.f3 = zt2.f2)
+ -> Index Only Scan using zt1_pkey on zt1 zt1b
+ Index Cond: (f1 = 53)
-> Nested Loop Left Join
- Join Filter: (zt1b.f1 = zt3.f3)
- -> Index Only Scan using zt1_pkey on zt1 zt1b
- Index Cond: (f1 = 53)
-> Index Only Scan using zt3_pkey on zt3
Index Cond: (f3 = 53)
- -> Index Only Scan using zt2_pkey on zt2
- Index Cond: (f2 = 53)
-(14 rows)
+ -> Index Only Scan using zt2_pkey on zt2
+ Index Cond: (f2 = 53)
+(12 rows)
select * from
zt1 left join zt1 zt1b on (zt1.f1 = zt1b.f1 and zt1b.f1 = 53)
@@ -7080,7 +7076,6 @@ where tt1.f1 = ss1.c0;
Output: tt4.f1
-> Nested Loop Left Join
Output: tt4.f1
- Join Filter: (tt3.f1 = tt4.f1)
-> Seq Scan on public.text_tbl tt3
Output: tt3.f1
Filter: (tt3.f1 = 'foo'::text)
@@ -7098,7 +7093,7 @@ where tt1.f1 = ss1.c0;
Output: (tt4.f1)
-> Seq Scan on public.text_tbl tt5
Output: tt4.f1
-(33 rows)
+(32 rows)
select 1 from
text_tbl as tt1
--
2.43.0
