Re: remove_useless_joins vs. bug #19560

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 于2026年7月21日周二 02:52写道：
>
> Anyway, what to do? I imagine we could write some code in
> analyzejoins.c to consider whether ECs give rise to base restriction
> clauses that they didn't before. But that seems ugly and fragile.
> More generally, there is nothing that is not ugly and fragile about
> analyzejoins.c's relation removal logic, and the recent addition of
> self-join elimination made that situation even worse. I think we
> have a permanent maintenance gotcha there.

Yes, recently, there have been a few bug fixes on HEAD related to
outer-join removal.
And there is still a bug that is not finished for self-join
elimination, for example: [1]
In the future, more kinds of join removal will be added. We should
think about maintenance now.

>
> I have a modest proposal to make instead: let's nuke all that logic
> from orbit. Revise analyzejoins.c so that it does join removals
> working strictly on the jointree representation, which is simpler
> and far more stable than any of the planner's derived data. Then,
> if we successfully removed any joins, throw away all the derived
> data and loop back around within query_planner() to redo
> deconstruct_jointree and all the rest of it.

Current join removals based on the planner's derived data are not complete.
What I mean by complete is: for example, foo left join (bar t1 inner
join bar t2)
If "bar t1 inner join bar t2", this inner join can be removed according to SJE.
But we have no chance to perform left-join removals. We first do
left-join removals in current logic,
But the right side is not a single rel, so remove_useless_joins()
returns directly.

Can working strictly on the jointree representation handle the above case?

>
> I'm not sure offhand what the implications would be for planning
> speed. It's possible that this'd actually be faster, considering
> what a mess the relation-removal logic is. But in any case, we
> have a bug-prone maintenance nightmare there, and it will get
> worse not better as people add more stuff to the planner. I think
> the current approach is unsustainable.

Agree
In the future, inner join removal may be added, and
remove_rel_from_query() will consider 3 types of join removals at that
time.
I don't think it is easy to maintain.

[1] https://www.postgresql.org/message-id/CAHewXN%3D7kDJjUcgEm%2B6qhaKOXuqzvhRqAAKdafNCRgn0yH7BGg%40mail.gmail.com

--
Thanks,
Tender Wang