To: Brandon Casci <brandon@loudcaster.com>
cc: pgsql-performance@postgresql.org
Subject: Re: help with understanding EXPLAIN and boosting performance 
In-reply-to: <AANLkTi=ru==ezfZb5OMDsdZaD++Spx58WbrF0ykM5Qtj@mail.gmail.com> 
References: <AANLkTi=ru==ezfZb5OMDsdZaD++Spx58WbrF0ykM5Qtj@mail.gmail.com>
Comments: In-reply-to Brandon Casci <brandon@loudcaster.com>
	message dated "Wed, 13 Oct 2010 18:06:44 -0400"
Date: Fri, 15 Oct 2010 14:35:03 -0400
Message-ID: <7191.1287167703@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Brandon Casci <brandon@loudcaster.com> writes:
> I have an application hosted on Heroku. They use postgres. It's more or less
> abstracted away, but I can get some performance data from New Relic. For the
> most part, performance is ok, but now and then some queries take a few
> seconds, and spike up to 15 or even 16 seconds! Ouch!

The particular query you're showing here doesn't look terribly
expensive.  Are you sure this is one that took that long?

If you're seeing identical queries take significantly different times,
I'd wonder about what else is happening on the server.  The most obvious
explanation for that type of behavior is that everything is swapped into
RAM when it's fast, but has to be read from disk when it's slow.  If
that's what's happening you should consider buying more RAM or
offloading some activities to other machines, so that there's not so
much competition for memory space.

If specific queries are consistently slow then EXPLAIN might give some
useful info about those.  It's unlikely to tell you much about
non-reproducible slowdowns, though.

			regards, tom lane