pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

When can joins be avoided?
2+ messages / 2 participants
[nested] [flat]

* When can joins be avoided?
@ 2022-11-16 10:39 Stefan Fehrenbach <stefan.fehrenbach@gmail.com>
  2022-11-17 14:49 ` Re: When can joins be avoided? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Stefan Fehrenbach @ 2022-11-16 10:39 UTC (permalink / raw)
  To: pgsql-performance@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: When can joins be avoided?
  2022-11-16 10:39 When can joins be avoided? Stefan Fehrenbach <stefan.fehrenbach@gmail.com>
@ 2022-11-17 14:49 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2022-11-17 14:49 UTC (permalink / raw)
  To: Stefan Fehrenbach <stefan.fehrenbach@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org

Stefan Fehrenbach <stefan.fehrenbach@gmail.com> writes:
> 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?

src/backend/optimizer/plan/analyzejoins.c

> They don't seem to match my intuition about which joins
> could be avoided.

I believe only left joins to single tables can be elided ATM.
It's too hard to prove uniqueness of the join key in more-
complicated cases.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2022-11-17 14:49 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-16 10:39 When can joins be avoided? Stefan Fehrenbach <stefan.fehrenbach@gmail.com>
2022-11-17 14:49 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox