The key expense in doing an index scan is the amount of randomness involved in reading the base table. If a table is in the same order as the index then reading the base table will be very fast. If the table is in a completely random order compared to an index (it's correlation is low), then an index scan becomes very expensive because every row you read out of the index means seeking to a random page in the table. So, if you do a lot of querying on the table that would work best with an index scan, it's probably worth it to cluster on that index. Note that I'm talking about index *scans* here, where you're pulling a decent number of rows. There's some other considerations as well, but this is probably the biggest one. On Wed, Nov 02, 2005 at 02:04:31PM +0100, MaXX wrote: > Hi, > Is there any "rule of thumb" on when to (not) use clustered indexes? > What appen to the table/index? (any change on the physical organisation?) > I've seen speed improvement on some queries but I'm not sure if I must use > them or not... > > My rows are imported in batch of 100 (once the main script has collected > them, this takes between 1 and 30min), then another script vacuums the > table and aggregate the last imported rows, if I add a column with the > commit timestamp and cluster on it, will I gain some perfs or not? > > Thanks, > -- > MaXX > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > -- 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 6: explain analyze is your friend
