Re: Throwing unnecessary joins away

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux