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

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Mike Christensen <imaudi(at)comcast(dot)net>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: What are the benefits of using a clustered index?
Date: 2009-03-17 13:16:39
Message-ID: 20090317131638.GA4202@alvh.no-ip.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2009-03-17 13:18:37 Re: PostgreSQL versus MySQL for GPS Data
Previous Message Daniel Verite 2009-03-17 12:48:18 Re: Escaping special characters