Thread

Re: Remove inner joins based on foreign keys

Tender Wang <tndrwang@gmail.com> — 2026-05-06T12:57:57Z

Richard Guo <guofenglinux@gmail.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?