Re: Optimize SQL

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