public inbox for [email protected]  

help / color / mirror / Atom feed

From: Greg Sabino Mullane <[email protected]>
To: Xavier Solomon <[email protected]>
Cc: [email protected]
Subject: Re: Using left joins instead of inner joins as an optimization
Date: Fri, 6 Sep 2024 09:50:04 -0400
Message-ID: <CAKAnmmLR0cGVWvkqXbPXu+ZAq0Vye=36VsnY5Qniy-gH4pZR1g@mail.gmail.com> (raw)
In-Reply-To: <CAHT4OD0Ng_1WZUgBnBNU=o_cs77n6k+YkksB95Bd4xu5CwagqQ@mail.gmail.com>
References: <CAHT4OD0Ng_1WZUgBnBNU=o_cs77n6k+YkksB95Bd4xu5CwagqQ@mail.gmail.com>

On Fri, Sep 6, 2024 at 7:05 AM Xavier Solomon <[email protected]>
wrote:

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

- Is it a bad idea to use left joins to optimize this even if semantically
> 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

view thread (3+ messages)

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], [email protected]
  Subject: Re: Using left joins instead of inner joins as an optimization
  In-Reply-To: <CAKAnmmLR0cGVWvkqXbPXu+ZAq0Vye=36VsnY5Qniy-gH4pZR1g@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