Re: Pointers needed on optimizing slow SQL statements

Ok, I will look into gathering better statistics.  This is the first
time I've had a significant problem with a PG database, so this is
uncharted territory for me.

If there is more info I could give that would help, please be more
specific about what you need and I will attempt to do so.

Thanks!

janine

On Jun 3, 2009, at 2:42 PM, Tom Lane wrote:

> Janine Sisk <janine@furfly.net> writes:
>> I've been Googling for SQL tuning help for Postgres but the pickings
>> have been rather slim.  Maybe I'm using the wrong search terms.  I'm
>> trying to improve the performance of the following query and would be
>> grateful for any hints, either directly on the problem at hand, or to
>> resources I can read to find out more about how to do this.  In the
>> past I have fixed most problems by adding indexes to get rid of
>> sequential scans, but in this case it appears to be the hash join and
>> the nested loops that are taking up all the time and I don't really
>> know what to do about that.  In Google I found mostly references from
>> people wanting to use a hash join to *fix* a performance problem, not
>> deal with it creating one...
>
> The hashjoin isn't creating any problem that I can see.  What's
> hurting you is the nestloops above it, which need to be replaced with
> some other join technique.  The planner is going for a nestloop
> because
> it expects only one row out of the hashjoin, which is off by more than
> three orders of magnitude :-(.  So in short, your problem is poor
> estimation of the selectivity of this condition:
>
>>                                  Join Filter: ((ci.live_revision =
>> cr.revision_id) OR ((ci.live_revision IS NULL) AND (cr.revision_id =
>> content_item__get_latest_revision(ci.item_id))))
>
> It's hard to tell why the estimate is so bad, though, since you didn't
> provide any additional information.  Perhaps increasing the statistics
> target for these columns (or the whole database) would help.
>
>             regards, tom lane
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

---
Janine Sisk
President/CEO of furfly, LLC
503-693-6407