Skip site navigation (1) Skip section navigation (2)

Re: Optimize SQL

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Pallav Kalva <pkalva(at)livedatagroup(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Optimize SQL
Date: 2006-09-15 15:53:19
Message-ID: 2066.1158335599@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Joost KraaijeveldDate: 2006-09-15 16:05:09
Subject: Re: Why the difference in plans ??
Previous:From: Luke LonerganDate: 2006-09-15 15:42:10
Subject: Re: RAID 0 not as fast as expected

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group