Re: Clustered indexes - When to use them?

On Wed, Nov 02, 2005 at 08:50:45PM +0100, MaXX wrote:
> Ok thank you,
> so I can consider using clustered indexes when I need to 'reorder' random
> data to improve the speed of a particular query...
>
> In simple words:
> Clustered indexes are like the alphabetical index in a book, where term are
> randomly distibuted in the book and regular indexes are more like the table
> of content...
> Right?

You have that backwards. The TOC matches the ordering of the book
(table). Think of it as the book is clustered on the TOC. Stuff from the
index appears all over; it's not clustered.

Keep in mind that for PostgreSQL it's simply a matter of correlation.
You can actually see correlation in one of system views. The higher the
correlation between an index and the table, the more efficient index
scans will be.

For some other databases, when you cluster on an index the table
actually *becomes an index*. This means that doing an index scan is
actually the same as a table/sequential scan, except that you can easily
find an exact place to start. Because of this, a 'clustered table' (or
an Index Organized Table as Oracle calls it) can be extremely fast for
certain operations.

In any case, remember the first rule of all performance tuning: don't.
And the second rule: if you're going to, you better have metrics to
measure your tuning with to make sure it's worth it.

Feel free to call me at work if you still have questions.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461