Re: Clustered indexes - When to use them?

From: MaXX <bs139412(at)skynet(dot)be>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Clustered indexes - When to use them?
Date: 2005-11-02 21:55:36
Message-ID: dkbcks$2fhu$1@talisker.lacave.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sven Willenberger 2005-11-02 22:02:16 Function to insert entire row%ROWTYPE into other table
Previous Message James Thompson 2005-11-02 21:54:37 CLI Reporting Tool Was: Re: Oracle 10g Express - any danger for Postgres?