Re: remove_useless_joins vs. bug #19560

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 于2026年7月28日周二 02:21写道：
>
> I think it's still open to debate how far back we want to go.
> In principle we could make these changes in v14/v15, but with no known
> bugs manifesting in those branches, the risk/reward ratio seems poor,
> so I didn't make patches for those. I think we definitely want to do
> this in v18, since aside from #19560 that branch has the SJE bug that
> Jacob B. reported at [1] (which is a distinct problem, per Alexander's
> analysis). v16 and v17 seem like a gray area, but we do know that
> they have #19560. Even if we limit our ambition in those branches
> to fixing that problem, I don't see a simple localized fix for it.
>
> Thoughts?
>

My question is unrelated to back-patching, so apologies if this is not
the right thread.

When I first saw your proposal to rewrite analyzejoins.c, I wondered
whether it might also address the remaining SJE issue discussed in
[1].

However, after applying v5 on top of HEAD, the issue still seems to be
present. In particular, the resulting plan retains a redundant filter
after SJE:

CREATE TABLE pg_table_a (
id INTEGER PRIMARY KEY,
col_bool BOOLEAN
);

INSERT INTO pg_table_a (id, col_bool)
VALUES (5, TRUE);

EXPLAIN
SELECT 1 AS c1
FROM (
pg_table_a AS tom0
RIGHT JOIN (
(pg_table_a AS tom1 NATURAL JOIN pg_table_a AS tom2)
RIGHT JOIN pg_table_a AS tom3
ON tom1.col_bool IS NOT NULL
)
ON tom1.col_bool
);

The relevant part of the plan is:

Seq Scan on pg_table_a tom2
Filter: ((col_bool IS NOT NULL) AND (col_bool IS NOT NULL))

That duplicate filter appears to be left behind after the join
involving tom1 is removed.

I think this issue is still worth fixing. The discussion in [1] has
been inactive for about two months, so I wanted to ask whether you
intend to address it as part of the analyzejoins.c rewrite, or whether
it would be better for me to resume work on [1] once the rewrite has
been committed.

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

--
Thanks,
Tender Wang