MIME-Version: 1.0
References: <CAHT4OD0Ng_1WZUgBnBNU=o_cs77n6k+YkksB95Bd4xu5CwagqQ@mail.gmail.com>
In-Reply-To: <CAHT4OD0Ng_1WZUgBnBNU=o_cs77n6k+YkksB95Bd4xu5CwagqQ@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Fri, 6 Sep 2024 09:50:04 -0400
Message-ID: <CAKAnmmLR0cGVWvkqXbPXu+ZAq0Vye=36VsnY5Qniy-gH4pZR1g@mail.gmail.com>
Subject: Re: Using left joins instead of inner joins as an optimization
To: Xavier Solomon <xavier.solomon515@gmail.com>
Cc: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="0000000000007ceb1d062173b172"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmLR0cGVWvkqXbPXu%2BZAq0Vye%3D36VsnY5Qniy-gH4pZR1g%40mail.gmail.com>
Precedence: bulk

--0000000000007ceb1d062173b172
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, Sep 6, 2024 at 7:05=E2=80=AFAM Xavier Solomon <xavier.solomon515@gm=
ail.com>
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

--0000000000007ceb1d062173b172
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Sep 6, 2024 at 7:05=E2=80=AFAM Xa=
vier Solomon &lt;<a href=3D"mailto:xavier.solomon515@gmail.com">xavier.solo=
mon515@gmail.com</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockq=
uote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1p=
x solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">&gt; explain se=
lect b_id from b natural left join a;<br>results in a `Seq Scan on b`. Wher=
eas the query<br>&gt; explain select b_id from b natural join a;<br>results=
 in a join with sequential scans on both a and b.<br></div></blockquote><di=
v><br></div><div>I think your example is a little too contrived. Try explai=
ning=C2=A0</div><div>select * from b natural left join a;</div><div>and you=
 should see the plans become equivalent again.</div><div><br></div><div>I w=
ould expect a query that left joins but only pulls data from one table to b=
e 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&#39;s kind of a moot =
point outside of odd select clauses.</div><div><br></div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div>- Is it a bad idea t=
o use left joins to optimize this even if semantically an inner join would =
be correct?</div></div></blockquote><div><br></div><div>Not at all - if it =
works for you, go ahead. But I&#39;m dubious you will gain much for queries=
 that actually make use of the left join, at least for relatively simply se=
lects.</div><div><br></div><div>Cheers,</div><div>Greg</div><div>=C2=A0<br>=
</div></div></div>

--0000000000007ceb1d062173b172--