Skip site navigation (1) Skip section navigation (2)

Re: JOIN performance

From: "Dean Gibson (DB Administrator)" <postgresql3(at)ultimeth(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: JOIN performance
Date: 2004-09-21 06:24:39
Message-ID: 5.1.0.14.2.20040920231140.00ae4828@imaps.mailpen.net (view raw or flat)
Thread:
Lists: pgsql-sql
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


In response to

pgsql-sql by date

Next:From: Dean Gibson (DB Administrator)Date: 2004-09-21 07:38:59
Subject: Difference in DATEs
Previous:From: birdy edwardsDate: 2004-09-21 05:55:56
Subject: [ocpfree] Info Needed about OCA

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group