Re: Bug: Buffer cache is not scan resistant

Luke Lonergan wrote:
> The Postgres shared buffer cache algorithm appears to have a bug.  When
> there is a sequential scan the blocks are filling the entire shared
> buffer cache.  This should be "fixed".
> 
> My proposal for a fix: ensure that when relations larger (much larger?)
> than buffer cache are scanned, they are mapped to a single page in the
> shared buffer cache.

It's not that simple. Using the whole buffer cache for a single seqscan 
is ok, if there's currently no better use for the buffer cache. Running 
a single select will indeed use the whole cache, but if you run any 
other smaller queries, the pages they need should stay in cache and the 
seqscan will loop through the other buffers.

In fact, the pages that are left in the cache after the seqscan finishes 
would be useful for the next seqscan of the same table if we were smart 
enough to read those pages first. That'd make a big difference for 
seqscanning a table that's say 1.5x your RAM size. Hmm, I wonder if 
Jeff's sync seqscan patch adresses that.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com