Re: Pointers needed on optimizing slow SQL statements

From: Tom Lane
Subject: Re: Pointers needed on optimizing slow SQL statements
Date: ,
Msg-id: 25715.1244065379@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Pointers needed on optimizing slow SQL statements  (Janine Sisk)
Responses: Re: Pointers needed on optimizing slow SQL statements  (Janine Sisk)
Re: Pointers needed on optimizing slow SQL statements  (Janine Sisk)
List: pgsql-performance


Janine Sisk <> 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


pgsql-performance by date:

From: Reid Thompson
Date:
Subject: Re: degenerate performance on one server of 3
From: Scott Marlowe
Date:
Subject: Re: Pointers needed on optimizing slow SQL statements