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@xxxxxxxxxx> 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@xxxxxxxxxxxxxx
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
---
Janine Sisk
President/CEO of furfly, LLC
503-693-6407
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
