DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=ejRdLdNbV1WHiwdrf3LmFt+Ic2HBrm9RUOHpHx/HXS3n8nEiaA7wuuheczwxbOuJ4vyjea/RK3jo8+oj8x8pqnYEjuQBnbKG+BMZMQbjs8Kde+kbYPRhYFJw5QCapk1dwCsELvbyWL7MkQaRRNuRi2dZYXh/cK9AicEsPL97vTs=
Message-ID: <34608c0c0601121051r52c707f6l@mail.gmail.com>
Date: Thu, 12 Jan 2006 19:51:22 +0100
From: =?ISO-8859-1?Q?Ott=F3_Havasv=F6lgyi?= <havasvolgyi.otto@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Throwing unnecessary joins away
In-Reply-To: <1137085382.3959.16.camel@state.g2switchworks.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
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=3Dperson.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=3D2;

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=F3 Havasv=F6lgyi 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=3Db.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...
>