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 having one'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 the index order over to the new table, reindexes, then renames the new table to preserve references. I've been thinking about how to speed up the copy process. Perhaps taking contiguous blocks of data and moving them into place would save some I/O time. Locking the table is another problem. Would it be impossible to perform the CLUSTER within the context of a READ COMMITTED transaction, and then pick up the leftover CRUD rows and put them at the end of the file. The existing code makes 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 the limitations that are out there preventing these things from being created. But, what else is there to do at 1AM on a Sunday night 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(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)