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"?
Date: Thu, 27 Mar 2008 13:07:44 -0400
Message-ID: <20080327130744.9bef536b.wmoran@collaborativefusion.com> (raw)
In-Reply-To: <17799.195.41.66.226.1206632068.squirrel@mail.jabbernet.dk>
References: <17799.195.41.66.226.1206632068.squirrel@mail.jabbernet.dk>
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 ...)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: wmoran@collaborativefusion.com, jesper@krogh.cc
Subject: Re: "Slow" query or just "Bad hardware"?
In-Reply-To: <20080327130744.9bef536b.wmoran@collaborativefusion.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox