Re: Remove inner joins based on foreign keys

Richard Guo <guofenglinux(at)gmail(dot)com> 于2026年5月1日周五 16:25写道：
> Because the clause "n.id = o.id" is an outer join ON clause that is
> non-degenerate (one that references the non-nullable side of the join)
> and we need to force it to be evaluated exactly at the level of the
> outer join. To handle that, we add the join's minimum input relid set
> to required_relids. That's why it appears in users' joininfo without
> referencing users.

Thanks for the explanation.

>
> Attached patch relaxes the check against ref_rel->joininfo. Nothing
> else has changed.

CREATE TABLE users (id int primary key, name text);
CREATE TABLE orders (id int primary key, user_id int not null
references users(id), amount int);
CREATE TABLE nation (id int primary key, name text);
postgres=# explain select n.* from nation n left join (orders o join
users u on o.user_id = u.id) on n.id = o.id;
QUERY PLAN
-------------------------------------------------------------------------
Hash Right Join (cost=38.58..74.34 rows=1270 width=36)
Hash Cond: (o.id = n.id)
-> Seq Scan on orders o (cost=0.00..30.40 rows=2040 width=8)
-> Hash (cost=22.70..22.70 rows=1270 width=36)
-> Seq Scan on nation n (cost=0.00..22.70 rows=1270 width=36)
(5 rows)

postgres=# explain select n.* from nation n left join orders o on n.id = o.id;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on nation n (cost=0.00..22.70 rows=1270 width=36)
(1 row)

Recently, I encountered the two plans above. The first plan can
continue to transform into the second plan after inner-join removing.
But in current logic, we cannot do this. Because we do left-join
removable first. Then we do other join(semi-join,
self-join,inner-join) removable.
We can only remove outer-join, whose min-righthand is single.
Maybe we can call remove_useless_joins() again to remove the outer
join that the function couldn't remove in the first call.
I'm not sure it is worth doing this.
Any thoughts?