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
