Re: PostgreSQL caching

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