Re: When does CLUSTER occur?

From: Igor Neyman <ineyman(at)perceptron(dot)com>
To: Mike Christensen <mike(at)kitchenpc(dot)com>, "sthomas(at)optionshouse(dot)com" <sthomas(at)optionshouse(dot)com>
Cc: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, "Schnabel, Robert D(dot)" <schnabelr(at)missouri(dot)edu>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: When does CLUSTER occur?
Date: 2012-11-29 19:41:00
Message-ID: A76B25F2823E954C9E45E32FA49D70EC08F06967@mail.corp.perceptron.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


From: Mike Christensen [mailto:mike(at)kitchenpc(dot)com]
Sent: Thursday, November 29, 2012 1:35 PM
To: sthomas(at)optionshouse(dot)com
Cc: Jeff Janes; Schnabel, Robert D.; pgsql-general(at)postgresql(dot)org
Subject: Re: When does CLUSTER occur?

On Thu, Nov 29, 2012 at 10:28 AM, Shaun Thomas <sthomas(at)optionshouse(dot)com> wrote:
On 11/29/2012 12:20 PM, Jeff Janes wrote:
It would maintain an imperfect clustering, but still much better than
current behavior.

I thought about that, too. The "imperfect clustering" made me erase everything I'd written. If the clustering is imperfect, it's not really clustering. It would mean less random reads to restart the seek chain though, so it would be a perceptible gain. But it's still not real clustering until the order is maintained indefinitely.

So far as I know, that simply can't be done with MVCC. Especially on an insert-only table that's clustered on a column unrelated to insert order.

How is this implemented in MS SQL then?  Obviously, MS SQL supports real clustering and has MVCC..

--

The way MVCC implemented in Postgres (not allowing "in place" updates) is obviously complicates things in regards to clustering.

As for MS SQL, here is how it implements clustering:
The index on which the table is clustered actually becomes a "physical holder" (for the lack of better term) of the table, meaning the complete table tuples are stored in the leaf blocks of the index it is clustered on. There is no other place in the database where they are stored physically.

Regards,
Igor Neyman

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Serge Fonville 2012-11-29 19:53:50 Re: When does CLUSTER occur?
Previous Message Serge Fonville 2012-11-29 19:26:51 Re: When does CLUSTER occur?