Re: CLUSTERing on Insert

I believe there's a TODO item for index-organized tables/clustered
tables. If not, there's certainly been discussion about it on the
-hackers list.

On Sun, Sep 17, 2006 at 10:21:27PM -0700, CG wrote:
> As I'm waiting for a CLUSTER operation to finish, it occurs to me that in a lot of cases, the performance benefits to
havingone's data stored on disk in index order can outweigh the overhead involved in inserting data on-disk in index
order....Just an idea I thought I'd throw out. :)  
>
> Also, the CLUSTER operation is about as straight forward as one can get. It basically reads each row, one-by-one, in
theindex order over to the new table, reindexes, then renames the new table to preserve references. I've been thinking
abouthow to speed up the copy process. Perhaps taking contiguous blocks of data and moving them into place would save
someI/O time. Locking the table is another problem. Would it be impossible to perform the CLUSTER within the context of
aREAD COMMITTED transaction, and then pick up the leftover CRUD rows and put them at the end of the file. The existing
codemakes some assumptions that the table was not altered. There would be no more assumptions.  
>
> I'm sure I'm not the first person to scratch his head thinking about CLUSTER. Maybe I just don't really understand
thelimitations that are out there preventing these things from being created. But, what else is there to do at 1AM on a
Sundaynight waiting for a 500MB table to CLUSTER? :) 
>
>
> CG
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>

--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)