Search Postgresql Archives

Re: Clustered indexes - When to use them?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
       message can get through to the mailing list cleanly

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux