Re: Re: Loading optimization

Ian Harding <iharding(at)pakrat(dot)com> writes:
> Tom Lane wrote:
>> The CLUSTER implementation is so shoddy at the moment that I'm hesitant
>> to encourage people to use it anyway :-(. We've got to rewrite it so
>> that it doesn't drop other indexes, lose constraints, break foreign
>> key and inheritance relationships, etc etc.

> Are the problems with CLUSTER isolated to the creation of the clustering,
> or the maintenance of it?

I guess you could consider it a bug that the clustered order is not
preserved by subsequent inserts/updates, but I don't. Otherwise the
problem is just with creation. That effectively does something like

SELECT * INTO temp_NNN FROM your_table ORDER BY index_var;

and then drops your_table and renames temp_NNN into place. So all
that's copied are the column types; you lose all other auxiliary info
about the table.

Now that I look at the code, it'd be very easy to preserve constraints
(a small change in the code would allow copying them to the new table)
so maybe we should do that. But the other issues like inheritance
relationships can't be fixed without a fundamentally different
implementation method, one that preserves the identity (OID) of the
table. You can find past discussions about how to do this in the
pghackers archives; it seems within reach given the changes made for
7.1, so perhaps someone will get to it in 7.2 or so.

regards, tom lane