Search Postgresql Archives

Re: Clustered indexes - When to use them?

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

 



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@xxxxxxxxxxxxx
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[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