postgres.git / summary / log / commit / refs
commit 7c620d2bf87bdb9508dd8cb40377f84388244dca
Author: Richard Guo <rguo@postgresql.org>
Date: Thu Sep 10 01:03:53 2026 +0000
Fix duplicate enforcement of EC-derived conditions
A clause connecting the same two EC members can be requested from the
EC machinery in different contexts. When building parameterized index
paths for a relation, generate_implied_equalities_for_column derives
an indexable clause with parent_ec set, to mark it as redundant with
other join clauses derived from the same EC. Meanwhile, at a join
where the relations of a multi-relation EC member first come together,
generate_join_implied_equalities emits a clause equating the same two
members with parent_ec unset, since that clause is the sole enforcer
of its member there and must not be dropped as redundant. Because
create_join_clause treats parent_ec as part of its cache-lookup key,
such requests can yield two different RestrictInfos for the same
condition, carrying different rinfo_serials.
This breaks the assumption that one condition has exactly one serial
number, which create_nestloop_path relies on to drop join clauses that
are already enforced within a parameterized inner path. As a result,
the same condition could be enforced twice at different plan levels,
wasting execution effort and applying the clause's selectivity twice,
underestimating the join's row count.
To fix, make create_join_clause copy the rinfo_serial from an existing
clause that connects the same two members with the opposite parent_ec
marking, so that the two clauses keep their distinct markings but are
recognized as the same condition.
As a result, a duplicate join filter disappears from one of the
existing regression plans (the "cardinal_number" query in join.sql);
the other plan changes are join-order differences between plans of
essentially equal cost.
Author: Richard Guo <guofenglinux@gmail.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/CAMbWs4-3yZeBMGQDpmYRPdV-sL_=6H2AN=MoeneOT+VUk28PPg@mail.gmail.com
Backpatch-through: 19
src/backend/optimizer/path/equivclass.c | 15 ++++++++
src/test/regress/expected/join.out | 63 ++++++++++++++++++++++++++-------
src/test/regress/sql/join.sql | 24 +++++++++++++
3 files changed, 89 insertions(+), 13 deletions(-)
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 66fe1c24a58..123eaa2c601 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -2040,6 +2040,21 @@ create_join_clause(PlannerInfo *root,
/* If it's a child clause, copy the parent's rinfo_serial */
if (parent_rinfo)
rinfo->rinfo_serial = parent_rinfo->rinfo_serial;
+ else
+ {
+ RestrictInfo *counterpart;
+
+ /*
+ * If a clause comparing the same two EMs already exists with the
+ * opposite parent_ec marking, adopt its rinfo_serial: the two clauses
+ * enforce the same condition, and they must share a serial number
+ * lest we enforce that condition more than once in a plan.
+ */
+ counterpart = ec_search_clause_for_ems(root, ec, leftem, rightem,
+ parent_ec ? NULL : ec);
+ if (counterpart)
+ rinfo->rinfo_serial = counterpart->rinfo_serial;
+ }
/* Mark the clause as redundant, or not */
rinfo->parent_ec = parent_ec;
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index f89ab203fb3..0225aa13254 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2337,17 +2337,17 @@ select a.f1, b.f1, t.thousand, t.tenthous from
(select sum(f1)+1 as f1 from int4_tbl i4a) a,
(select sum(f1) as f1 from int4_tbl i4b) b
where b.f1 = t.thousand and a.f1 = b.f1 and (a.f1+b.f1+999) = t.tenthous;
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------
Nested Loop
+ -> Aggregate
+ -> Seq Scan on int4_tbl i4a
-> Nested Loop
Join Filter: ((sum(i4b.f1)) = ((sum(i4a.f1) + 1)))
- -> Aggregate
- -> Seq Scan on int4_tbl i4a
-> Aggregate
-> Seq Scan on int4_tbl i4b
- -> Index Only Scan using tenk1_thous_tenthous on tenk1 t
- Index Cond: ((thousand = (sum(i4b.f1))) AND (tenthous = ((((sum(i4a.f1) + 1)) + (sum(i4b.f1))) + 999)))
+ -> Index Only Scan using tenk1_thous_tenthous on tenk1 t
+ Index Cond: ((thousand = (sum(i4b.f1))) AND (tenthous = ((((sum(i4a.f1) + 1)) + (sum(i4b.f1))) + 999)))
(9 rows)
select a.f1, b.f1, t.thousand, t.tenthous from
@@ -4087,17 +4087,17 @@ select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from
left join tenk1 t2
on (subq1.y1 = t2.unique1)
where t1.unique2 < 42 and t1.stringu1 > t2.stringu2;
- QUERY PLAN
------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------
Nested Loop
-> Nested Loop
Join Filter: (t1.stringu1 > t2.stringu2)
-> Nested Loop
+ -> Seq Scan on onerow onerow_1
-> Nested Loop
-> Seq Scan on onerow
- -> Seq Scan on onerow onerow_1
- -> Index Scan using tenk1_unique2 on tenk1 t1
- Index Cond: ((unique2 = (11)) AND (unique2 < 42))
+ -> Index Scan using tenk1_unique2 on tenk1 t1
+ Index Cond: ((unique2 = (11)) AND (unique2 < 42))
-> Index Scan using tenk1_unique1 on tenk1 t2
Index Cond: (unique1 = (3))
-> Seq Scan on int4_tbl i1
@@ -4142,7 +4142,6 @@ where t1.unique1 < i4.f1;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop
Output: (64)::information_schema.cardinal_number
- Join Filter: (t1.tenthous = ((64)::information_schema.cardinal_number)::integer)
-> Seq Scan on public.tenk1 t3
Output: t3.unique1, t3.unique2, t3.two, t3.four, t3.ten, t3.twenty, t3.hundred, t3.thousand, t3.twothousand, t3.fivethous, t3.tenthous, t3.odd, t3.even, t3.stringu1, t3.stringu2, t3.string4
Filter: (t3.fivethous < 0)
@@ -4173,7 +4172,7 @@ where t1.unique1 < i4.f1;
-> Seq Scan on public.int8_tbl i8
Output: i8.q1, i8.q2
Filter: (i8.q1 = ((64)::information_schema.cardinal_number)::integer)
-(33 rows)
+(32 rows)
select ss1.d1 from
tenk1 as t1
@@ -6020,6 +6019,44 @@ where t1.unique1 < 1;
Index Cond: (unique1 = t3.unique1)
(15 rows)
+--
+-- check that an EC-derived condition is not enforced twice, both within a
+-- parameterized path and at the join above it
+--
+begin;
+set local from_collapse_limit to 1;
+explain (costs off)
+select count(*) from int4_tbl t1,
+ lateral (select * from tenk1 t2,
+ lateral (select t2.ten as x offset 0) s0
+ join tenk1 t3 on t3.unique2 = t1.f1
+ where t3.unique1 = t2.hundred + s0.x) ss1;
+ QUERY PLAN
+---------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (t3.unique2 = t1.f1)
+ -> Nested Loop
+ -> Seq Scan on tenk1 t2
+ -> Nested Loop
+ -> Result
+ -> Index Scan using tenk1_unique1 on tenk1 t3
+ Index Cond: (unique1 = (t2.hundred + (t2.ten)))
+ -> Materialize
+ -> Seq Scan on int4_tbl t1
+(11 rows)
+
+select count(*) from int4_tbl t1,
+ lateral (select * from tenk1 t2,
+ lateral (select t2.ten as x offset 0) s0
+ join tenk1 t3 on t3.unique2 = t1.f1
+ where t3.unique1 = t2.hundred + s0.x) ss1;
+ count
+-------
+ 0
+(1 row)
+
+rollback;
--
-- test successful handling of full join underneath left join (bug #14105)
--
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 83f9bf84555..7b5c61c3104 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -2157,6 +2157,30 @@ select * from onek t1
and t2.unique2 = t4.unique2
where t1.unique1 < 1;
+--
+-- check that an EC-derived condition is not enforced twice, both within a
+-- parameterized path and at the join above it
+--
+
+begin;
+
+set local from_collapse_limit to 1;
+
+explain (costs off)
+select count(*) from int4_tbl t1,
+ lateral (select * from tenk1 t2,
+ lateral (select t2.ten as x offset 0) s0
+ join tenk1 t3 on t3.unique2 = t1.f1
+ where t3.unique1 = t2.hundred + s0.x) ss1;
+
+select count(*) from int4_tbl t1,
+ lateral (select * from tenk1 t2,
+ lateral (select t2.ten as x offset 0) s0
+ join tenk1 t3 on t3.unique2 = t1.f1
+ where t3.unique1 = t2.hundred + s0.x) ss1;
+
+rollback;
+
--
-- test successful handling of full join underneath left join (bug #14105)
--
[parent: be2602cfcc15]