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
