Re: again on index usage

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

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
measurementson 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