From: Ottó Havasvölgyi <havasvolgyi.otto@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Throwing unnecessary joins away
Date: Thu, 12 Jan 2006 19:51:22 +0100
Message-ID: <34608c0c0601121051r52c707f6l@mail.gmail.com> (raw)
In-Reply-To: <1137085382.3959.16.camel@state.g2switchworks.com>
References: <34608c0c0601120418n1af567b9n@mail.gmail.com>
<43C64CFB.9030504@barettadeit.com>
<34608c0c0601120541wd910da3s@mail.gmail.com>
<5269.1137081233@sss.pgh.pa.us>
<34608c0c0601120900l7d26073dp@mail.gmail.com>
<1137085382.3959.16.camel@state.g2switchworks.com>
Hi,
If the join is to a primary key or notnull unique column(s), then
inner join is also ok. But of course left join is the simpler case.
An example:
create table person (id serial primary key, name varchar not null);
create table pet (id serial primary key, name varchar not null,
person_id int not null references person(id));
create view v_pet_person as select pet.id as pet_id, pet.name as
pet_name, person_id as person_id, person.name as person_name from pet
join person (pet.person_id=person.id);
At this point we know that optimization may be possible because of the
primary key on person. The optimization depends on the primary key
constraint. Kindof internal dependency.
We can find out that which "from-element" is a given field's source as
far they are simple references. This can be stored.
Then query the view:
select pet_name, person_id from v_pet_person where person_id=2;
In this case we don't need the join.
These queries are usually dynamically generated, the selection list
and the where condition is the dynamic part.
Best Regards,
Otto
2006/1/12, Scott Marlowe <smarlowe@g2switchworks.com>:
> On Thu, 2006-01-12 at 11:00, Ottó Havasvölgyi wrote:
> > Hi,
> >
> > I think it would be sufficient only for views. In other cases the
> > programmer can optimize himself. But a view can be a join of other
> > tables, and it is not sure that all of them are always needed. It all
> > depends on what I select from the view.
>
> The idea that you could throw away joins only works for outer joins.
> I.e. if you did:
>
> select a.x, a.y, a.z from a left join b (on a.id=b.aid)
>
> then you could throw away the join to b. But if it was a regular inner
> join then you couldn't know whether or not you needed to join to b
> without actually joining to b...
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: havasvolgyi.otto@gmail.com
Subject: Re: Throwing unnecessary joins away
In-Reply-To: <34608c0c0601121051r52c707f6l@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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