Re: again on index usage

>>>Tom Lane said:
> mlw <markw(at)mohawksoft(dot)com> writes:
> > ... Storage
> > devices are now black boxes. The only predictable advantage a
> > "sequential scan" can have on a modern computer is OS level caching.
>
> You mean read-ahead. True enough, but that "only advantage" is very
> significant. The 4.0 number did not come out of the air, it came
> from actual measurements.

On what OS? Linux? Windows? BSD? OSF/1? System V? All these differ
significantly in how buffer cache is managed. For example, the BSD 'soft
updates' will not penalize large directory updates, but not do any good for
sequential reads (considering what was said already about modern disks). SCSI
tag queueing will significantly improve raw disk reads ('sequential' again)
because of the low overhead of host<->SCSI subsystem communication - any
decent SCSI host adapter will do bus-master DMA, without the interference of
the processor (simplified as much as to illustrate it :). Todays IDE drives on
PC hardware don't do that! Which is not to say that only SCSI drive
controllers are intelligent enough - I still remember an older Motorola VME
based UNIX system (that now can only server the purpose of coffee table :),
where an MFM controller board had all the intelligence of the SCSI subsystem,
although it operated with 'dump' MFM disks. So many examples can be given here.

> I think the real point in this thread is that measurements on an idle
> system might not extrapolate very well to measurements on a heavily
> loaded system. I can see the point, but I don't really have time to
> investigate it right now. I'd be willing to reduce the default value of
> random_page_cost to something around 2, if someone can come up with
> experimental evidence justifying it ...

Agreed. My preference would be, that if you have reasonable enough test data,
that can be shared, many people on different platforms can run performance
tests and come up with an array of recommended values for their particular
OS/hardware configuration. I believe these two items are most significant for
the tuning of an installation.

Daniel Kalchev