Re: Remove inner joins based on foreign keys

On Mon, Jun 22, 2026 at 7:42 PM Tender Wang <tndrwang(at)gmail(dot)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