MIME-Version: 1.0
From: Xavier Solomon <xavier.solomon515@gmail.com>
Date: Thu, 5 Sep 2024 18:58:53 +0200
Message-ID: <CAHT4OD0Ng_1WZUgBnBNU=o_cs77n6k+YkksB95Bd4xu5CwagqQ@mail.gmail.com>
Subject: Using left joins instead of inner joins as an optimization
To: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="0000000000003e7021062162359f"
Archived-At: <https://www.postgresql.org/message-id/CAHT4OD0Ng_1WZUgBnBNU%3Do_cs77n6k%2BYkksB95Bd4xu5CwagqQ%40mail.gmail.com>
Precedence: bulk

--0000000000003e7021062162359f
Content-Type: text/plain; charset="UTF-8"

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!

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

<div dir=3D"ltr">Hi All!<br><br>I&#39;m currently writing a view that joins=
 many tables and I was wondering how=C2=A0PostgreSQL optimizes<br><div>proj=
ections on such a view. In particular I was wondering if it is a correct an=
d valid optimization</div><div>technique to use left joins when they are eq=
uivalent to an inner join.</div><br>I have created a minimal example. Suppo=
se we have two tables:<br><br>&gt; create table a(a_id int primary key gene=
rated always as identity, a_data text);<br>&gt; create table b(b_id int pri=
mary key generated always as identity, a_id int not null references a(a_id)=
, b_data text);<br><br>Then the query<br>&gt; explain select b_id from b na=
tural left join a;<br>results in a `Seq Scan on b`. Whereas the query<br>&g=
t; explain select b_id from b natural join a;<br>results in a join with seq=
uential scans on both a and b.<br><br>I believe because b.a_id is not null =
and references a.a_id a left and an inner join are exactly equivalent.<br><=
br>My questions are:<br>- Am I wrong that in such a situation a left and in=
ner join are equivalent?<br><div>- Why does PostgreSQL not automatically op=
timize this?</div><div>- Is it a bad idea to use left joins to optimize thi=
s even if semantically an inner join would be correct?</div><div><br></div>=
<div>Thank you for your help!<br></div></div>

--0000000000003e7021062162359f--