Re: remove_useless_joins vs. bug #19560

On Mon, Jul 27, 2026 at 2:35 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Drat, failed to hit control-C control-E. Now with patches attached,
> for sure.

Thanks for the patchset. Nice cleanup:

911 insertions(+), 1670 deletions(-)

I noticed that the call to remove_rels_from_query_tree() at the end of
remove_useless_joins() performs a full traversal over the query tree
to delete the removed relids. I wonder if this is a bit too
brute-force, as AFAICS only PHVs can still contain references to the
removed relids. If there were any other remaining references,
join_is_removable() wouldn't have considered the join removable in the
first place.

If that's correct, perhaps we can simplify
remove_rels_from_query_tree() to something lighter, similar to
remove_result_refs().

Also, the issue with duplicate clauses after self-join elimination
reported in bug #19435 is still present in v4. It would be great if
we could address that in passing as well.

Here is a simplified repro:

create table t (a int unique, b int);

explain (costs off)
select * from t t1 left join
(t t2 join t t3 on t2.a = t3.a) on t3.a is not null;
QUERY PLAN
-------------------------------------------------------------
Nested Loop Left Join
-> Seq Scan on t t1
-> Materialize
-> Seq Scan on t t3
Filter: ((a IS NOT NULL) AND (a IS NOT NULL))
(5 rows)

- Richard