Tom Lane wrote:
> What I think you are saying is that when there's lots of competing work,
> seqscans have less advantage over indexscans because the
> sequential-access locality advantage is lost when the disk drive has to
> go off and service some other request. If that's the mechanism, I think
> that the appropriate answer is just to reduce random_page_cost. It's
> true that the current default of 4.0 was chosen using measurements on
> otherwise-unloaded systems. If you assume that the system is (a) too
> busy to do read-aheads for you, and (b) has to move the disk arm to
> service other requests between each of your requests, then it's not
> clear that sequential reads have any performance advantage at all :-(.
> I don't think I'd go as far as to lower random_page_cost to 1.0, but
> certainly there's a case for using an intermediate value.
It is even more interesting than simple sequential vs random scan
thinking. Depending on the maker of the drive, even an unloaded system
will move the head randomly. Modern drives have almost no resemblance to
their predecessors. Sectors are mapped however the OEM sees fit. A
numerically sequential read from a hard disk may have the drive heads
bouncing all over the disk because the internal configuration of the
disk has almost nothing to do with the external representation.
Think about a RAID device. What does a sequential scan mean to a RAID
system? Very little depending on how the image is constructed. Storage
devices are now black boxes. The only predictable advantage a
"sequential scan" can have on a modern computer is OS level caching.