Re: Using left joins instead of inner joins as an optimization

On Fri, 6 Sept 2024 at 23:05, Xavier Solomon
<xavier(dot)solomon515(at)gmail(dot)com> wrote:
> > create table a(a_id int primary key generated always as identity, a_data text);
> > create table b(b_id int primary key generated always as identity, a_id int not null references a(a_id), b_data text);
>
> Then the query
> > explain select b_id from b natural left join a;
> results in a `Seq Scan on b`. Whereas the query
> > explain select b_id from b natural join a;
> results in a join with sequential scans on both a and b.
>
> I believe because b.a_id is not null and references a.a_id a left and an inner join are exactly equivalent.

Mostly equivalent, but there are a few corner cases where they're not.

> My questions are:
> - Am I wrong that in such a situation a left and inner join are equivalent?

The foreign key triggers are deferred until at least the end of the
statement, so there are cases where the foreign key can be temporarily
violated. For example, if the outer query is an UPDATE a SET a_id =
... and you have an AFTER UPDATE ON a trigger that runs your left join
query, a_id will be changed but the changes won't have been verified
(or cascaded) in the referencing table.

> - Why does PostgreSQL not automatically optimize this?

We would need to change the way foreign keys work or maybe at least
give the planner more context as to where the query it's planning is
coming from. I think it might be safe to do this optimisation if it's
a top-level query, but not if it's being run from a trigger. Maybe
there are other cases which are safe too.

> - Is it a bad idea to use left joins to optimize this even if semantically an inner join would be correct?

If you only ever do it in top-level statements, then I think it's
safe. If not, you might get wrong results. For deferred foreign key
constraints, the window where the foreign key could be violated lasts
until the end of the transaction, so even top-level queries could see
wrong results if you use left join instead of inner.

David