Date: Thu, 27 Mar 2008 13:07:44 -0400
From: Bill Moran <wmoran@collaborativefusion.com>
To: "Jesper Krogh" <jesper@krogh.cc>
Cc: pgsql-performance@postgresql.org
Subject: Re: "Slow" query or just "Bad hardware"?
Message-Id: <20080327130744.9bef536b.wmoran@collaborativefusion.com>
In-Reply-To: <17799.195.41.66.226.1206632068.squirrel@mail.jabbernet.dk>
References: <17799.195.41.66.226.1206632068.squirrel@mail.jabbernet.dk>
Organization: Collaborative Fusion
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

In response to "Jesper Krogh" <jesper@krogh.cc>:

> Hi
> 
> I have a table with around 10 million entries  The webpage rendered hits
> at most 200 records which are distributed well in the 10m with an average
> of 2 "references" pr. entry.
> 
> Is there anyway to speed this query more up than allready. .. yes running
> it subsequenctly it is blazingly fast, but with view of around 200/10m we
> most
> often dont hit the same query again.

While all the other advice is good, what you really need to do to address
this is figure out what's in your cache and whether it's the right things.
Once you _know_ that (and aren't just speculating) you can start to use
the solutions that others have suggested to improve on the situation. If
you just start trying things at random, you'll probably figure it out
eventually anyway, but I'm assuming you'll want a direct route.

So, I'm going to repeat something that I say on this mailing list about
twice a month: install MRTG or some equivalent and start graphing critical
database statistics.

In your case, install the pg_buffercache addon and use it to track how
much of your shared buffers each table is using.  Based on your
description of the problem, I doubt it will take more than a few days
to have a clear view of exactly what's going on (i.e. you'll probably
see table X clearing table Y out of the buffers or something ...)