Re: DB page cache/query performance

On May 14, 2008, at 6:18 PM, Greg Smith wrote:
>> Also, how long should pages stay in the cache? (Assuming I have
>> way more
>> memory than the total size of all the tables/indexes.) Is there any
>> time-based expiration (in addition to LRU-based, which in my case
>> should
>> never be resorted to)?
>
> Forever, at least as far as the PostgreSQL one is concerned. They
> only get evicted when a new buffer needs to be allocated and
> there's none available. It's not quite a LRU mechanism either. If
> you visit http://www.westnet.com/~gsmith/content/postgresql/
> there's a presentation called "Inside the PostgreSQL Buffer Cache"
> that goes over how that part works. You'd probably find a look
> inside your system using pg_buffercache informative. Some of the
> sample queries I give only work on 8.3, but the "Buffer contents
> summary, with percentages" one should be OK on 8.1.

Hrm... don't seqscans use a separate set of buffers in 8.3? While
technically those won't be evicted until needed, you're unlikely to
find stuff hanging around there for terribly long...
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828