Re: What are the benefits of using a clustered index?

Mike Christensen wrote:

> I'm wondering what the performance advantage is to using a clustered
> index.

In Postgres you don't cluster indexes. You cluster tables. It only
means that the table is written from scratch, following the index order.
So what it gives you is locality of access for queries that follow
ranges of that index, nothing more. It seems very obvious that in this
implementation a new tuple is not going to follow the index order; it'll
just be stored wherever there is free space. If you run CLUSTER again,
they'll be put in the right place.

(There was a patch to enhance this so that a new insertion would instead
use space closer to where the tuple would be if it followed the order.
But it was only a hint; if there wasn't enough free space in the right
spot, it would be stored elsewhere. Still, the patch was not
committed.)

> I'm quite sure SQL Server doesn't work this way and I'm not sure about
> Oracle. Can someone enlighten me on the exact benefit of this? Thanks!!

Yeah, they use a completely different definition of "clustered index"
from ours.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support