From: Arjen van der Meijden <acmmailing@tweakers.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Pallav Kalva <pkalva@livedatagroup.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimize SQL
Date: Fri, 15 Sep 2006 18:22:55 +0200
Message-ID: <450AD35F.3070305@tweakers.net> (raw)
In-Reply-To: <2066.1158335599@sss.pgh.pa.us>
References: <450AC22F.9040004@livedatagroup.com>
<2066.1158335599@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: acmmailing@tweakers.net, tgl@sss.pgh.pa.us, pkalva@livedatagroup.com
Subject: Re: Optimize SQL
In-Reply-To: <450AD35F.3070305@tweakers.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox