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