Re: Pointers needed on optimizing slow SQL statements

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Janine Sisk <janine(at)furfly(dot)net>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Pointers needed on optimizing slow SQL statements
Date: 2009-06-03 21:42:59
Message-ID: 25715.1244065379@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Janine Sisk 2009-06-03 22:04:47 Re: Pointers needed on optimizing slow SQL statements
Previous Message Tom Lane 2009-06-03 21:15:23 Re: degenerate performance on one server of 3