From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Jacob Costello <jake@suntradingllc.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: optimizing away join when querying view
Date: Wed, 8 Feb 2006 07:46:39 -0800 (PST)
Message-ID: <20060208073955.S43207@megazone.bigpanda.com> (raw)
In-Reply-To: <1139406891.11389.60.camel@localhost.localdomain>
References: <1139406891.11389.60.camel@localhost.localdomain>
On Wed, 8 Feb 2006, Jacob Costello wrote:
> Postgres doesn't seem to optimize away unnecessary joins in a view
> definition when the view is queried in such a way that the join need not
> be executed. In the example below, I define two tables, foo and bar,
> with a foreign key on bar referencing foo, and a view on the natural
> join of the tables. The tables are defined so that the relationship
> from bar to foo is allowed to be many to one, with the column of bar
> referencing foo (column a) set NOT NULL, so that there must be exactly
> one foo record for every bar record. I then EXPLAIN selecting the "b"
> column from bar, through the view and from bar directly. The tables
> have been ANALYZEd but have no data. EXPLAIN shows the join actually
> occurring when selecting b from the view quux. If I understand
> correctly (maybe I don't), this is guaranteed to be exactly the same as
> the selecting b directly from the bar table.
AFAIK there are periods in which a foreign key does not guarantee that
there's one foo record for every bar record between an action and the
constraint check for that action at statement end so you'd probably have
to be careful in any case.
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: sszabo@megazone.bigpanda.com, jake@suntradingllc.com
Subject: Re: optimizing away join when querying view
In-Reply-To: <20060208073955.S43207@megazone.bigpanda.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