Message-ID: <450AD35F.3070305@tweakers.net>
Date: Fri, 15 Sep 2006 18:22:55 +0200
From: Arjen van der Meijden <acmmailing@tweakers.net>
User-Agent: Thunderbird 1.5.0.5 (Windows/20060719)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Pallav Kalva <pkalva@livedatagroup.com>, pgsql-performance@postgresql.org
Subject: Re: Optimize SQL
References: <450AC22F.9040004@livedatagroup.com>
	<2066.1158335599@sss.pgh.pa.us>
In-Reply-To: <2066.1158335599@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 15-9-2006 17:53 Tom Lane wrote:
> If that WHERE logic is actually what you need, then getting this query
> to run quickly seems pretty hopeless.  The database must form the full
> outer join result: it cannot discard any listing0_ rows, even if they
> have lastupdate outside the given range, because they might join to
> addressval2_ rows within the given createdate range.  And conversely
> it can't discard any addressval2_ rows early.  Is there any chance
> that you wanted AND not OR there?

Couldn't it also help to do something like this?

SELECT ..., (SELECT MAX(createdate) FROM addressval ...)
FROM listing l
   LEFT JOIN address ...
WHERE l.id IN (SELECT id FROM listing WHERE lastupdate ...
       UNION
         SELECT id FROM listing JOIN addressval a ON ... WHERE 
a.createdate ...)


Its not pretty, but looking at the explain only a small amount of 
records match both clauses. So this should allow the use of indexes for 
both the createdate-clause and the lastupdate-clause.

Best regards,

Arjen