From: Jim C. Nasby <jnasby@pervasive.com>
To: Alessandro Baretta <a.baretta@barettadeit.com>
Cc: Ott? Havasv?lgyi <havasvolgyi.otto@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Throwing unnecessary joins away
Date: Fri, 13 Jan 2006 18:17:58 -0600
Message-ID: <20060114001758.GO9017@pervasive.com> (raw)
In-Reply-To: <43C64CFB.9030504@barettadeit.com>
References: <34608c0c0601120418n1af567b9n@mail.gmail.com>
<43C64CFB.9030504@barettadeit.com>
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
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: jnasby@pervasive.com, a.baretta@barettadeit.com, havasvolgyi.otto@gmail.com
Subject: Re: Throwing unnecessary joins away
In-Reply-To: <20060114001758.GO9017@pervasive.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