On Thu, 15 Dec 2005, Craig A. James wrote:
The example I raised in a previous thread, of irregular usage, is the same: I have a particular query that I *always* want to be fast even if it's only used rarely, but the system swaps its tables out of the file-system cache, based on "low usage", even though the "high usage" queries are low priority. How can Postgres know such things when there's no way for me to tell it?
actually, postgres doesn't manage the file-system cache, it deliberatly leaves that up to the OS it is running on to do that job.
one (extremely ugly) method that you could use would be to have a program that looks up what files are used to store your high priority tables and then write a trivial program to keep those files in memory (it may be as simple as mmaping the files and then going to sleep, or you may have to read various points through the file to keep them current in the cache, it WILL vary depending on your OS and filesystem in use)
oracle goes to extremes with this sort of control, I'm actually mildly surprised that they still run on a host OS and haven't completely taken over the machine (I guess they don't want to have to write device drivers, that's about the only OS code they really want to use, they do their own memory management, filesystem, and user systems), by avoiding areas like this postgres sacrafices a bit of performance, but gains a much broader set of platforms (hardware and OS) that it can run on. and this by itself can result in significant wins (does oracle support Opteron CPU's in 64 bit mode yet? as of this summer it just wasn't an option)
David Lang
