Re: Index Scans become Seq Scans after VACUUM ANALYSE

Adrian 'Dagurashibanipal' von Bidder wrote:
>
> On Wed, 2002-04-17 at 19:43, Tom Lane wrote:
> > Hannu Krosing <hannu(at)tm(dot)ee> writes:
> > > OTOH, it is also important where the file is on disk. As seen from disk
> > > speed test graphs on http://www.tomshardware.com , the speed difference
> > > of sequential reads is 1.5 to 2.5 between inner and outer tracks.
> >
> > True. But if we use the same test file for both the sequential and
> > random-access timings, hopefully the absolute speed of access will
> > cancel out. (Again, it's the sort of thing that could use some
> > real-world testing...)
>
> Not so sure about that. Random access basically measures latency,
> sequential access measures transfer speed. I'd argue that latency is
> more or less constant across the disk as it depends on head movement and
> the spindle turning.

The days when "head movement" is relevant are long over. Not a single drive
sold today, or in the last 5 years, is a simple spindle/head system. Many are
RLE encoded, some have RAID features across the various platters inside the
drive. Many have dynamic remapping of sectors, all of them have internal
caching, some of them have predictive read ahead, some even use compression.

The assumption that sequentially reading a file from a modern disk drive means
that the head will move less often is largely bogus. Now, factor in a full RAID
system where you have 8 of these disks. Random access of a drive may be slower
than sequential access, but this has less to do with the drive, and more to do
with OS level caching and I/O channel hardware.

Factor in a busy multitasking system, you have no way to really predict the
state of a drive from one read to the next.

(Rotational speed of the drive is still important in that it affects internal
rotational alignment and data transfer.)