Re: What gets cached?

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
ORDER BY 3 DESC

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"" <sgunderson(at)bigfoot(dot)com> wrote in message
news:20051024153248(dot)GA24601(at)samfundet(dot)no(dot)(dot)(dot)
> 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: http://www.sesse.net/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>