Re: Assert failure in try_nestloop_path()

On Tue, Sep 29, 2026 at 4:20 PM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
> I think we need to do something to avoid generating duplicate EC
> members in add_child_join_rel_equivalences().

After looking into this more, I don't think that is enough. The
problem is not specific to GEQO.

For instance, the plan of an existing query in partition_join.sql
already enforces the same condition twice:

-> Nested Loop
Join Filter: (((COALESCE(t3_3.c, t4_3.c)))::text = (t1_3.c)::text)
-> Index Scan using prt3_p3_a_idx on prt3_p3 t3_3
Index Cond: ((a = t4_3.a) AND (a IS NOT NULL))
-> Index Only Scan using prt4_p3_c_a_idx on prt4_p3 t1_3
Index Cond: ((c = ((COALESCE(t3_3.c, t4_3.c)))::text)
AND (a = t3_3.a))

And the same assertion can be hit without GEQO:

set enable_partitionwise_join to on;

explain (costs off)
select t1.a, t1.c, t3.c, t5.f1 from prt4 t1 join prt3 t2 on t1.a = t2.a
join prt1 t3 on t2.a = t3.a, int4_tbl t5
where t1.c = coalesce(t3.c, t5.f1::text);

Here the child joins {t1, t3}, {t2, t3} and {t1, t2, t3} each add
their own child version of the same EC member, and they get equated
to each other when we build the clauses for the three-way child join
parameterized by t5.

What these cases have in common is that the EC member references a rel
outside the child join. add_child_join_rel_equivalences() builds a
child version of any multi-relation member that overlaps the join's
parent relids. But child-join members are there for matching pathkeys
of the child join, and a member that references an outside rel cannot
be computed at the join, so it is of no use for that. Its only effect
is on the clauses we generate for parameterized child-join paths,
which is where the duplicates come from.

So I think the fix is to not build such members in the first place, by
considering only members that can be computed at the parent join.
This is what add_child_rel_equivalences() has been doing for base rels
since 2489d76c4.

Attached is a patch for that. The existing query above loses its
redundant join filter.

Thoughts?

- Richard