Re: Clustered indexes - When to use them?

On Wed, Nov 02, 2005 at 10:55:36PM +0100, MaXX wrote:
> 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...

That's because unfortunately PostgreSQL only keeps statistics on
individual columns. There's no stats kept on multi-column indexes; the
best the planner can do is use the stats for the first column.
--
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