Re: Remove inner joins based on foreign keys

Hi all,

Richard Guo <guofenglinux@gmail.com> 于2026年5月26日周二 05:01写道：
> (Attached v5 is the split version of v4, plus addressing Alex's two
> comments.  0002 is still the lock-based predicate from v4, posted as
> the concrete reference for option (B); it can be swapped for whichever
> approach the gap-handling discussion settles on.)
>
(The patches need to be rebased due to commit 9a60f295bcb18)

I had a plan as follows:
postgres=# explain  select fk_child.* from fk_parent1 left join t1 on
true inner join fk_child on fk_child.p1_id = fk_parent1.id;
                              QUERY PLAN
----------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.00..55.21 rows=2260 width=84)
   ->  Nested Loop  (cost=0.00..0.01 rows=1 width=84)
         Join Filter: (fk_parent1.id = fk_child.p1_id)
         ->  Seq Scan on fk_parent1  (cost=0.00..0.00 rows=1 width=4)
         ->  Seq Scan on fk_child  (cost=0.00..0.00 rows=1 width=84)
   ->  Seq Scan on t1  (cost=0.00..32.60 rows=2260 width=0)
(6 rows)
The inner join between fk_parent1 and fk_child  can be removed.
But now they are separated by an outer-join boundary, so
inner_join_is_removable() returns false.
If we support this kind of inner-join removal, it needs to remove not
only ref_rel but also the outer-join relid.

Do we plan to support this kind of inner-join removal now?


-- 
Thanks,
Tender Wang