short circuit joins

From: Marcus Engene <mengpg2(at)engene(dot)se>
To: POSTGRESQL <pgsql-general(at)postgresql(dot)org>
Subject: short circuit joins
Date: 2009-08-30 22:54:35
Message-ID: 4A9B032B.8070904@engene.se
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

-- a
select
...
from
base_table bt
left outer join sub_table_1 st1 on (st1.base_table = bt.id)
left outer join sub_table_2 st1 on (st2.base_table = bt.id)

-- b
select
...
from
base_table bt
left outer join sub_table_1 st1 on (bt.objecttype = 1 AND
st1.base_table = bt.id)
left outer join sub_table_2 st1 on (bt.objecttype = 2 AND
st2.base_table = bt.id)

Pretend this is some stupid way of object orientation. base_table is the
base class and sub_table_x are subclasses. base_table.objecttype tells
which instance it is. Just for the sake of discussion, it could also be
like "bt.is_married_monogamous = 1 AND wife.husband = bt.id" for example.

In case b, does Postgres avoid to unnecessarily try look for respective
sub_table ? Is it worthwhile to have?

I'm on 8.3 presently. Still curious if newer versions have some
optimization here.

Best regards,
Marcus

Browse pgsql-general by date

  From Date Subject
Next Message bilal ghayyad 2009-08-30 23:13:18 Re: $Body$
Previous Message David Fetter 2009-08-30 22:46:46 Re: Using WITH queries on VIEWs