To: Harry Jackson <harryjackson@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: CPU and RAM
References: <45b42ce40512211720t164ea389wa5bfd0cbae6c80e2@mail.gmail.com>
In-Reply-To: <45b42ce40512211720t164ea389wa5bfd0cbae6c80e2@mail.gmail.com>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 22 Dec 2005 22:52:54 -0500
Message-ID: <878xuc8o6h.fsf@stark.xeocode.com>
Lines: 26
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Harry Jackson <harryjackson@gmail.com> writes:

> At the moment everything is working OK but I am noticing an almost
> linear increase in time to retrieve data from the database as the data
> set increases in size. Clustering knocks the access times down by 25%
> but it also knocks users off the website and can take up to 30 minutes
> which is hardly an ideal scenario. 

If the whole database is in RAM I wouldn't expect clustering to have any
effect. Either you're doing a lot of merge joins or a few other cases where
clustering might be helping you, or the cluster is helping you keep more of
the database in ram avoiding the occasional disk i/o.

That said, I would agree with the others to not assume the plans for every
query is ok. It's easy when the entire database fits in RAM to be fooled into
thinking plans are ok because they're running quite fast but in fact have
problems.

In particular, if you have a query doing a sequential scan of some moderately
large table (say a few thousand rows) then you may find the query executes
reasonably fast when tested on its own but consumes enough cpu and memory
bandwidth that when it's executed frequently in an OLTP setting it pegs the
cpu at 100%.

-- 
greg