> > >The "this day and age" argument isn't very convincing. Hard drive
> > >capacity growth has far outstripped hard drive seek time
> and bandwidth improvements.
> > >Random access has more penalty than ever.
> >
> > In point of fact, there haven't been noticeable seek time
> improvements
> > for years. Transfer rates, on the other hand, have gone
> through the roof.
>
> Er, yeah. I stated it wrong. The real ratio here is between
> seek time and throughput.
>
> Typical 7200RPM drives have average seek times are in the
> area of 10ms.
> Typical sustained transfer rates are in the range of 40Mb/s.
> Postgres reads 8kB blocks at a time.
>
> So 800kB/s for random access reads. And 40Mb/s for sequential
> reads. That's a factor of 49. I don't think anyone wants
> random_page_cost to be set to 50 though.
>
> For a high end 15k drive I see average seek times get as low
> as 3ms. And sustained transfer rates get as high as 100Mb/s.
> So about 2.7Mb/s for random access reads or about a
> random_page_cost of 37. Still pretty extreme.
>
> So what's going on with the empirically derived value of 4?
> Perhaps this is because even though Postgres is reading an
> entire table sequentially it's unlikely to be the only I/O
> consumer? The sequential reads would be interleaved
> occasionally by some other I/O forcing a seek to continue.
What about the cache memory on the disk? Even IDE disks have some 8Mb
cache today, which makes a lot of difference for fairly short scans.
Even if it's just read cache. That'll bring the speed of random access
down to a 1=1 relationship with sequential access, assuming all fits in
the cache.
//Magnus