Re: Remove inner joins based on foreign keys

On Mon, Jun 22, 2026 at 7:42 PM Tender Wang <tndrwang@gmail.com> wrote:
> 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?

Removing the inner join is safe in this specific case, but I don't
plan to support it, at least not in the initial version.  Supporting
it would require restructuring the OJ, and LEFT JOIN ... ON true looks
unusual in user-written SQL.  If it turns out to be common in
practice, it can be added as a self-contained follow-up patch.

For now I'd like to keep the focus on the trigger gap issue.  I'd
particularly appreciate input on the menu in section 2 of my previous
email before investing more in any specific direction.  That's the
real blocker for this feature to land.

- Richard