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

Re: Pointers needed on optimizing slow SQL statements

From: Janine Sisk <janine(at)furfly(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Pointers needed on optimizing slow SQL statements
Date: 2009-06-03 22:04:47
Message-ID: E67D68A6-FA8D-4F1F-8354-3EF7E166E2EC@furfly.net (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)furfly(dot)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(at)postgresql(dot)org 
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

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





In response to

Responses

pgsql-performance by date

Next:From: Greg SmithDate: 2009-06-03 23:01:32
Subject: Re: Best way to load test a postgresql server
Previous:From: Tom LaneDate: 2009-06-03 21:42:59
Subject: Re: Pointers needed on optimizing slow SQL statements

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