Re: Overriding the optimizer

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