From: Tom Lane <tgl@sss.pgh.pa.us>
To: Pallav Kalva <pkalva@livedatagroup.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimize SQL
Date: Fri, 15 Sep 2006 11:53:19 -0400
Message-ID: <2066.1158335599@sss.pgh.pa.us> (raw)
In-Reply-To: <450AC22F.9040004@livedatagroup.com>
References: <450AC22F.9040004@livedatagroup.com>
Pallav Kalva <pkalva@livedatagroup.com> writes:
> select listing0_.listingid as col_0_0_,
> getmaxdate(listing0_.lastupdate, max(addressval2_.createdate)) as col_1_0_
> from listing.listing listing0_
> left outer join listing.address listingadd1_
> on listing0_.fkbestaddressid=listingadd1_.addressid
> left outer join listing.addressvaluation addressval2_
> on listingadd1_.addressid=addressval2_.fkaddressid
> where listing0_.lastupdate>'2006-09-15 08:31:26.927'
> and listing0_.lastupdate<=current_timestamp
> or addressval2_.createdate>'2006-09-15 08:31:26.927' and
> addressval2_.createdate<=current_timestamp
> group by listing0_.listingid , listing0_.lastupdate
> order by getmaxdate(listing0_.lastupdate, max(addressval2_.createdate))
> asc limit 10;
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?
One thing that might help a bit is to change the join order:
from listing.listing listing0_
left outer join listing.addressvaluation addressval2_
on listing0_.fkbestaddressid=addressval2_.fkaddressid
left outer join listing.address listingadd1_
on listing0_.fkbestaddressid=listingadd1_.addressid
so that at least the WHERE clause can be applied before having joined to
listingadd1_. The semantics of your ON clauses are probably wrong anyway
--- did you think twice about what happens if there's no matching
listingadd1_ entry?
regards, tom lane
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: tgl@sss.pgh.pa.us, pkalva@livedatagroup.com
Subject: Re: Optimize SQL
In-Reply-To: <2066.1158335599@sss.pgh.pa.us>
* 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