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

From: Xavier Solomon <[email-masked]>
Date: Thu, 5 Sep 2024 18:58:53 +0200

Hi All!

I'm currently writing a view that joins many tables and I was wondering
how PostgreSQL optimizes
projections on such a view. In particular I was wondering if it is a
correct and valid optimization
technique to use left joins when they are equivalent to an inner join.

I have created a minimal example. Suppose we have two tables:

> 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.

My questions are:
- Am I wrong that in such a situation a left and inner join are equivalent?
- Why does PostgreSQL not automatically optimize this?
- Is it a bad idea to use left joins to optimize this even if semantically
an inner join would be correct?

Thank you for your help!

---

From: David Rowley <[email-masked]>
Date: Sat, 7 Sep 2024 01:24:45 +1200

On Fri, 6 Sept 2024 at 23:05, Xavier Solomon
<[email-masked]> 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
> 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.

> - 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

---

From: Greg Sabino Mullane <[email-masked]>
Date: Fri, 6 Sep 2024 09:50:04 -0400

> results in a join with sequential scans on both a and b.

I think your example is a little too contrived. Try explaining
select * from b natural left join a;
and you should see the plans become equivalent again.

I would expect a query that left joins but only pulls data from one table
to be not feasible in real life. Yes, in an ideal world the non-left join
would be smart enough to not even do the scan on a, but it's kind of a moot
point outside of odd select clauses.

> an inner join would be correct?

Not at all - if it works for you, go ahead. But I'm dubious you will gain
much for queries that actually make use of the left join, at least for
relatively simply selects.

Cheers,
Greg