When can joins be avoided?

From: Stefan Fehrenbach <stefan(dot)fehrenbach(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: When can joins be avoided?
Date: 2022-11-16 10:39:13
Message-ID: CAB9vtGACGyRjg132fu52rTRUu_m1Xz0ezPNdeba76DbC_Nsy3g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hey all!

I'm interested in the rules that Postgres uses to avoid joins. Are
these documented somewhere? If I had to look at the source code, where
would I start? They don't seem to match my intuition about which joins
could be avoided. Then again, it's quite possible that I'm wrong and
misunderstanding the semantics.

Here are some particular examples I'm interested in.
Set up some tables. Intuition: table a contains some info, table b
contains some (optional) extra info, table c contains some more
(optional) extra info.

create table c (
c_id int primary key,
c_foo text );

create table b (
b_id int primary key,
c_id int references c(c_id),
b_bar text );

create table a (
a_id int primary key,
b_id int references b(b_id),
a_baz text );

-- Now some queries (join on b and c in various ways,
-- but only ever select columns from a)

-- This joins on b, as expected.
-- (Because missing rows could reduce cardinality?
-- but making a.b_id NOT NULL doesn't help...)
explain
select a_baz
from a
join b using (b_id);

-- Making it a LEFT join avoids the join on b, as expected
explain
select a_baz
from a
LEFT join b using (b_id);

-- Joins on b and c. This is very strange to me.
-- Whether or not the join on c results in any rows
-- shouldn't make any difference.
explain
select a_baz
from a
left join (select *
from b
join c using (c_id)) bc using (b_id);

-- making the join in the subquery a LEFT join
-- avoids joining entirely (no b or c in the plan)
explain
select a_baz
from a
left join (select *
from b
LEFT join c using (c_id)) bc using (b_id);

If anybody knows why Postgres behaves this way, please let me know :)

Cheers,
Stefan

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2022-11-17 14:49:20 Re: When can joins be avoided?
Previous Message Simon Riggs 2022-11-16 05:27:54 Help needed with perf tests on subtransaction overflow