Re: Clustered tables improves perfs ?

On 9/13/07, Patrice Castet <pcastet(at)agematis(dot)com> wrote:
> I wonder if clustering a table improves perfs somehow ?

As I understand it, clustering will help cases where you are fetching
data in the same sequence as the clustering order, because adjacent
rows will be located in adjacent pages on disk; this is because hard
drives perform superbly with sequential reads, much less so with
random access.

For example, given a table foo (v integer) populated with a sequence
of integers [1, 2, 3, 4, ..., n], where the column v has an index, and
the table is clustered on that index, a query such as "select v from
foo order by v" will read the data sequentially from disk, since the
data will already be in the correct order.

On the other hand, a query such as "select v from foo order by
random()" will not be able to exploit the clustering. In other words,
clustering is only useful insofar as your access patterns follow the
clustering order.

Alexander.