Re: Optimize SQL

Pallav Kalva <pkalva(at)livedatagroup(dot)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