Re: again on index usage

On Fri, Jan 11, 2002 at 11:42:43AM -0500, Bruce Momjian wrote:
> Don Baccus wrote:
> > Zeugswetter Andreas SB SD wrote:
> >
> >
> > > This is one of the main problems of the current optimizer which imho rather
> > > aggressively chooses seq scans over index scans. During high load this does
> > > not pay off.
> >
> >
> > Bingo ... dragging huge tables through the buffer cache via a sequential
> > scan guarantees that a) the next query sequentially scanning the same
> > table will have to read every block again (if the table's longer than
> > available PG and OS cache) b) on a high-concurrency system other queries
> > end up doing extra I/O, too.
> >
> > Oracle partially mitigates the second effect by refusing to trash its
> > entire buffer cache on any given sequential scan. Or so I've been told
> > by people who know Oracle well. A repeat of the sequential scan will
> > still have to reread the entire table but that's true anyway if the
> > table's at least one block longer than available cache.
>
> That is on our TODO list, at least.
>

Hmm, on Linux this sort of behavior (skip the pg buffers for sequential
scans) would have interesting load senstive behavior: since Linux uses
all not-otherwise in use RAM as buffer cache, if you've got a low-load
system, even very large tables will be cached. Once other processes start
competing for RAM, your buffers go away. Bruce, what does xBSD do?

I like it, since anything that needs to be sensitive to system wide
information, like the total load on the machine, should be handled by
the system, not a particular app.

Ross