Seqscan buffer promotion (was: reindex/vacuum locking/performance?)

On Mon, 2003-10-06 at 05:15, Andrew Sullivan wrote:
> There's plenty of academic work which purports to show that LRU is
> far from the best choice. Just in principle, it seems obvious that a
> single-case seqscan-type operation (such as vacuum does) is a good
> way to lose your cache for no real gain.

Traditionally, seqscan type operations are accommodated in LRU type
managers by having multiple buffer promotion policies, primarily because
it is simple to implement. For example, if you are doing a seqscan, a
buffer loaded from disk is never promoted to the top of the LRU.
Instead it is only partially promoted (say, halfway for example) toward
the top of the buffer list. A page that is already in the buffer is
promoted either to the halfway point or top depending on where it was
found. There are numerous variations on the idea, some being more
clever and complex than others.

The point of this being that a pathological or rare sequential scan can
never trash more than a certain percentage of the cache, while not
significantly impacting the performance of a sequential scan. The
primary nuisance is that it slightly increases the API complexity. I'll
add that I don't know what PostgreSQL actually does in this regard, but
from the thread it appears as though seqscans are handled like the
default case.

Cheers,

-James Rogers
jamesr(at)best(dot)com