Thank each of you for your replies.  I'm just beginning to understand the 
scope of my opportunities.

Someone (I apologize, I forgot who) recently posted this query:
    SELECT oid::regclass, reltuples, relpages
    FROM pg_class

Though the application is a relatively low-volume TP system, it is 
structured a lot like a data warehouse with one primary table that 
everything else hangs off.  What the query above shows is that my largest 
table, at 34 million rows, takes almost 1.4 million pages or 10+ Gb if my 
math is good.  The same table has 14 indexes, totaling another 12Gb.  All 
this is running on a box with 4Gb of memory.

So what I believe I see happening is that almost every query is clearing out 
memory to load the particular index it needs.  Hence my "first queries are 
the fastest" observation at the beginning of this thread.

There are certainly design improvements to be done, but I've already started 
the process of getting the memory increased on our production db server.  We 
are btw running 8.1 beta 3.

""Steinar H. Gunderson"" <[EMAIL PROTECTED]> wrote in message 
> On Mon, Oct 24, 2005 at 11:09:55AM -0400, Alex Turner wrote:
>> Just to play devils advocate here for as second, but if we have an 
>> algorithm
>> that is substational better than just plain old LRU, which is what I 
>> believe
>> the kernel is going to use to cache pages (I'm no kernel hacker), then 
>> why
>> don't we apply that and have a significantly larger page cache a la 
>> Oracle?
> There have (AFAIK) been reports of setting huge amounts of shared_buffers
> (close to the total amount of RAM) performing much better in 8.1 than in
> earlier versions, so this might actually be okay these days.
> I haven't heard of anybody reporting increase setting such values, though.
> /* Steinar */
> -- 
> Homepage:
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [EMAIL PROTECTED] so that your
       message can get through to the mailing list cleanly

Reply via email to