Re: Remove inner joins based on foreign keys

Hi all,

Richard Guo <guofenglinux(at)gmail(dot)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