Claudio Freire <klaussfreire@gmail.com> wrote:
> Forgive the naive question...
> but...
>
> Aren't all index scans, forward or backward, random IO?
No. Some could approach that; but, for example, an index scan
immediately following a CLUSTER on the index would be totally
sequential on the heap file access and would tend to be fairly close
to sequential on the index itself. It would certainly trigger OS
level read-ahead for the heap, and quite possibly for the index. So
for a lot of pages, the difference might be between copying a page
from the OS cache to the database cache versus a random disk seek.
To a lesser degree than CLUSTER you could get some degree of
sequencing from a bulk load or even from normal data insert
patterns. Consider a primary key which is sequentially assigned, or
a timestamp column, or receipt numbers, etc.
As Tom points out, some usage patterns may scramble this natural
order pretty quickly. Some won't.
-Kevin
