Message-Id: <5.1.0.14.2.20040920231140.00ae4828@imaps.mailpen.net>
Date: Mon, 20 Sep 2004 23:24:39 -0700
To: pgsql-sql@postgresql.org
From: "Dean Gibson (DB Administrator)" <postgresql3@ultimeth.com>
Subject: Re: JOIN performance 
Cc: pgsql-sql@postgresql.org
In-Reply-To: <25938.1095743973@sss.pgh.pa.us>
References: <5.1.0.14.2.20040920213442.00ae4450@imaps.mailpen.net>
	<5.1.0.14.2.20040920182022.00adf5e8@imaps.mailpen.net>
	<5.1.0.14.2.20040920164348.00a99868@imaps.mailpen.net>
	<5.1.0.14.2.20040920121606.00a89c68@imaps.mailpen.net>
	<414F02FF.8000603@numerixtechnology.de>
	<414F02FF.8000603@numerixtechnology.de>
	<5.1.0.14.2.20040920121606.00a89c68@imaps.mailpen.net>
	<5.1.0.14.2.20040920164348.00a99868@imaps.mailpen.net>
	<5.1.0.14.2.20040920182022.00adf5e8@imaps.mailpen.net>
	<5.1.0.14.2.20040920213442.00ae4450@imaps.mailpen.net>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed

Tom Lane wrote on 2004-09-20 22:19:
>In 7.3 only a view whose targetlist consists of simple column references 
>can be pulled up into the nullable side of an outer join.

OK, well you dragged me kicking and screaming into writing and 'end_date' 
function (IMMUTABLE but non-STRICT).  I then use that in the JOINed VIEW 
(and other strategic places) rather than in the subject of the LEFT JOIN, 
and that works with no performance problems.

Since we mentioned PostgreSQL versions, one of the things I'd like to see 
in v8.0 is support for "DELETE FROM ... AS ..." and "UPDATE ... AS 
...".  For example, I need to do things like:

DELETE FROM xxx AS x WHERE field > xxx.field;

In other words, a DELETE that has criteria similar to a self JOIN.

Right now, I do this via:

CREATE VIEW yyy AS SELECT * FROM xxx;

DELETE FROM xxx WHERE field > yyy.field;

And that works fine.

But it's probably too late for v8.0 ...

-- Dean