public inbox for [email protected]  

help / color / mirror / Atom feed

From: Xavier Solomon <[email protected]>
To: [email protected]
Subject: Using left joins instead of inner joins as an optimization
Date: Thu, 5 Sep 2024 18:58:53 +0200
Message-ID: <CAHT4OD0Ng_1WZUgBnBNU=o_cs77n6k+YkksB95Bd4xu5CwagqQ@mail.gmail.com> (raw)

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!

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Using left joins instead of inner joins as an optimization
  In-Reply-To: <CAHT4OD0Ng_1WZUgBnBNU=o_cs77n6k+YkksB95Bd4xu5CwagqQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox