Re: Throwing unnecessary joins away

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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461