Date: Fri, 13 Jan 2006 18:17:58 -0600
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Alessandro Baretta <a.baretta@barettadeit.com>
Cc: Ott? Havasv?lgyi <havasvolgyi.otto@gmail.com>,
	pgsql-performance@postgresql.org
Subject: Re: Throwing unnecessary joins away
Message-ID: <20060114001758.GO9017@pervasive.com>
References: <34608c0c0601120418n1af567b9n@mail.gmail.com>
	<43C64CFB.9030504@barettadeit.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <43C64CFB.9030504@barettadeit.com>
User-Agent: Mutt/1.5.11

On Thu, Jan 12, 2006 at 01:35:07PM +0100, Alessandro Baretta wrote:
> Ott? Havasv?lgyi wrote:
> >Hi all,
> > 
> >Is PostgreSQL able to throw unnecessary joins?
> >For example I have two tables, and I join then with their primary keys, 
> >say type of bigint . In this case if I don't reference to one of the 
> >tables anywhere except the join condition, then the join can be eliminated.
> >Or if I do a "table1 left join table2 (table1.referer=table2.id)"  (N : 
> >1 relationship), and I don't reference table2 anywhere else, then it is 
> >unnecessary.
> 
> It cannot possibly remove "unnecessary joins", simply because the join 
> influences whether a tuple in the referenced table gets selected and how 
> many times.

It can remove them if it's an appropriate outer join, or if there is
appropriate RI that proves that the join won't change what data is
selected.

A really common example of this is creating views that pull in tables
that have text names to go with id's, ie:

CREATE TABLE bug_status(
    bug_status_id       serial  PRIMARY KEY
    , bug_status_name   text    NOT NULL UNIQUE
);

CREATE TABLE bug(
    ...
    , bug_status_id     int     REFERENCES bug_status(bug_status_id)
);

CREATE VIEW bug_v AS
    SELECT b.*, bs.bug_status_name FROM bug b JOIN bug_status NATURAL
;

If you have a bunch of cases like that and start building views on views
it's very easy to end up in situations where you don't have any need of
bug_status_name at all. And because of the RI, you know that removing
the join can't possibly change the bug.* portion of that view.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461