Re: Clustered indexes - When to use them?

Jim C. Nasby wrote:
> On Wed, Nov 02, 2005 at 08:50:45PM +0100, MaXX wrote:
[...]
>> 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.
pgAdmin shows a correlation value in the statistics panel when I click on a
column... Not sure if it is the right one as all column have it, look more
correlation between values in the column...
Still have a *lot* of things to learn...

> 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.
Ok now I know why I can only have a single clustered index per table...

> 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.
Yeah! EXPLAIN ANALYSE, time, systat and friends...

Thanks again,
--
MaXX