On Tue, Sep 8, 2026 at 3:20 AM Tom Lane <[email protected]> wrote:
> I'm not quite convinced that this is worth spending cycles on.
> We have only two example queries that trigger this case, and in
> neither one does the actually-selected plan change. I think that's
> because the case only occurs with very bizarre join ordering choices
> that will lose on cost grounds anyway.
Hmm, I don't think bizarre join orderings are required. The duplicate
arises whenever the qual pushed down into the parameterized path is
not affected by the commuting outer join, which seems quite a common
case. Here is an example where the duplicate shows up in the selected
plan, with the joins performed just in syntactic order:
explain (costs off)
select * from onek t1
left join onek t2 on t1.unique1 = t2.unique1
left join onek t3 on t2.unique1 = t3.unique1
left join onek t4 on t3.unique1 = t4.unique1 and t3.ten = t4.ten + 0
and t2.unique2 = t4.unique2 + 0
where t1.unique1 < 1;
-> Index Scan using onek_unique1 on onek t4
Index Cond: (unique1 = t3.unique1)
Filter: ((t3.ten = (ten + 0)) AND (t3.ten = (ten + 0)))
Both clone variants of "t3.ten = t4.ten + 0" are enforced at the
scan.
Even when the duplicate is not visible in the final plan, the clause's
selectivity is still applied multiple times, underestimating the row
count, which can easily lead to worse plan choices in bigger queries.
> Also the proposed assertion
> would only catch rather narrow cases where we try to put the same
> clause twice in the same place, but not if we put it in two different
> places in the plan tree.
Right. The assertion is only meant as cheap tripwires at the spots
where we select among clones, not as full coverage.
> It turns out that this one has nothing to do with clone clauses,
> it's that the hacky bit in get_joinrel_parampathinfo to ensure full
> enforcement of equivalence classes (lines 1952-2006 in HEAD, dating to
> commit 207d5a656) isn't being careful not to add duplicate clauses.
> As I said in that commit message, this is a super rare case already,
> so it's not surprising nobody noticed. I made a quick-n-dirty patch
> for it, attached, but I don't have a test case that visibly exposes
> the misbehavior.
Agreed. I had arrived at the same conclusion. Your fix makes sense
and I think we should apply it. I don't have a test case that exposes
the misbehavior either.
- Richard
