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