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@pakrat.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@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026