Message-ID: <40AE2174.7060107@archonet.com>
Date: Fri, 21 May 2004 16:34:12 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 0.6 (X11/20040502)
MIME-Version: 1.0
To: Vitaly Belman <vitalib@012.net.il>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: PostgreSQL caching
References: <40782517171.20040521174209@012.net.il>
In-Reply-To: <40782517171.20040521174209@012.net.il>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Vitaly Belman wrote:
> Hello,
> 
> I have the following problem:
> 
> When I run some query after I just run the Postmaster, it takse
> several seconds to execute (sometimes more than 10), if I rerun it
> again afterwards, it takes mere milliseconds.
> 
> So, I guess it has to do with PostgreSQL caching.. But how exactly
> does it work? What does it cache? And how can I control it?

There are two areas of cache - PostgreSQL's shared buffers and the 
operating system's disk-cache. You can't directly control what data is 
cached, it just keeps track of recently used data. It sounds like PG 
isn't being used for a while so your OS decides to use its cache for 
webserver files.

> I would like to load selected information in the memory before a user
> runs the query. Can I do it somehow? As PostgreSQL is used in my case
> as webserver, it isn't really helping if the user has to wait 10
> seconds every time he goes to a new page (even if refreshing the page
> would be really quick, sine Postgre already loaded the data to
> memory).

If you could "pin" data in the cache it would run quicker, but at the 
cost of everything else running slower.

Suggested steps:
1. Read the configuration/tuning guide at:
   http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
2. Post a sample query/explain analyse that runs very slowly when not 
cached.
3. If needs be, you can write a simple timed script that performs a 
query. Or, the autovacuum daemon might be what you want.


-- 
   Richard Huxton
   Archonet Ltd