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
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
