When can joins be avoided?

[Stefan Fehrenbach <stefan.fehrenbach@xxxxxxxxx>]

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