Re: [PERFORM] CLUSTER command - Mailing list pgsql-general

From Charles H. Woloszynski
Subject Re: [PERFORM] CLUSTER command
Date
Msg-id 3DF9329B.1020908@clearmetrix.com
Whole thread Raw
In response to Re: [PERFORM] CLUSTER command  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
List pgsql-general
I think Oracle does something like this with its clustering.  You set a
%fill and Oracle uses this when doing inserts into a segment and when to
add a new one.  There is also some control over the grouping of data
within a page.  I don't have an Oracle manual present, but I think the
clustering works on a specific index.

I agree that adding auto-clustering would be a very good thing and that
we can learn about functionality by studying what other applications
have already done and if/how those strategies were successful.

Charlie


Alvaro Herrera wrote:

>On Thu, Dec 12, 2002 at 04:03:47PM -0800, Stephan Szabo wrote:
>
>
>>On Thu, 12 Dec 2002, johnnnnnn wrote:
>>
>>
>>
>>>I think the code changes would be complicated. Just at a 30-second
>>>consideration, this would need to touch:
>>>- all sql (selects, inserts, updates, deletes)
>>>- vacuuming
>>>- indexing
>>>- statistics gathering
>>>- existing clustering
>>>
>>>
>>I think his idea was to treat it similarly to the way that the
>>system treats tables >2G with .N files.  The only thing is that
>>I believe the code that deals with that wouldn't be particularly
>>easy to change to do it though, but I've only taken a cursory look at
>>what I think is the place that does that(storage/smgr/md.c). Some sort of
>>good partitioning system would be nice though.
>>
>>
>
>I don't think this is doable without a huge amount of work.  The storage
>manager doesn't know anything about what is in a page, let alone a
>tuple.  And it shouldn't, IMHO.  Upper levels don't know how are pages
>organized in disk; they don't know about .1 segments and so on, and they
>shouldn't.
>
>I think this kind of partition doesn't buy too much.  I would really
>like to have some kind of auto-clustering, but it should be implemented
>in some upper level; e.g., by leaving some empty space in pages for
>future tuples, and arranging the whole heap again when it runs out of
>free space somewhere.  Note that this is very far from the storage
>manager.
>
>
>

--


Charles H. Woloszynski

ClearMetrix, Inc.
115 Research Drive
Bethlehem, PA 18015

tel: 610-419-2210 x400
fax: 240-371-3256
web: www.clearmetrix.com






pgsql-general by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: [PERFORM] CLUSTER command
Next
From: Medi Montaseri
Date:
Subject: statement timeout test case