Re: Re: Loading optimization

Added to TODO (part of this is reorganization of cluster items):

* CLUSTER
* cluster all tables at once
* prent lose of constraints, indexes, permissions, inheritance
* Automatically keep clustering on a table
* Keep statistics about clustering, perhaps during VACUUM ANALYZE
[optimizer]

> 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
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026