Date: Wed, 8 Feb 2006 07:46:39 -0800 (PST)
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
In-Reply-To: <1139406891.11389.60.camel@localhost.localdomain>
Message-ID: <20060208073955.S43207@megazone.bigpanda.com>
References: <1139406891.11389.60.camel@localhost.localdomain>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

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.