Re: Optimize SQL - Mailing list pgsql-performance

From Tom Lane
Subject Re: Optimize SQL
Date
Msg-id 2066.1158335599@sss.pgh.pa.us
Whole thread Raw
In response to Optimize SQL  (Pallav Kalva <pkalva@livedatagroup.com>)
Responses Re: Optimize SQL  (Arjen van der Meijden <acmmailing@tweakers.net>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: "Luke Lonergan"
Date:
Subject: Re: RAID 0 not as fast as expected
Next
From: Joost Kraaijeveld
Date:
Subject: Re: Why the difference in plans ??