Thread: CLUSTER all tables at once?
Hello In TODO there is an item that says "cluster all tables at once". Might I ask, how is the system supposed to know on which indexes does it have to cluster each table? Maybe if some index had the indisclustered bit set one could select that; but is it possible for some table to have more than one index with it? Intuition (but no code observation) says no. And what happens with those tables that do not have any such index? -- Alvaro Herrera (<alvherre[a]atentus.com>) "Oh, great altar of passive entertainment, bestow upon me thy discordant images at such speed as to render linear thought impossible" (Calvin a la TV)
> Maybe if some index had the indisclustered bit set one could select > that; but is it possible for some table to have more than one index with > it? Intuition (but no code observation) says no. And what happens with > those tables that do not have any such index? The bool marker sounds useful. Falling back on the primary key is probably the most appropriate.
Alvaro Herrera <alvherre@atentus.com> writes:
> Maybe if some index had the indisclustered bit set one could select
> that; but is it possible for some table to have more than one index with
> it? Intuition (but no code observation) says no.
At the moment that bit will never be set at all, unless you were to
reach in and set it with a manual "UPDATE pg_index" command.
It would probably be feasible for the CLUSTER code to update the system
catalogs to set the bit on the index used for the clustering (and clear
it from any others it might be set on). Then indisclustered would have
the semantics of "the index most recently used in CLUSTERing its table",
which seems pretty reasonable. And it'd fit in nicely as the control
bit for an auto-CLUSTER command.
> And what happens with those tables that do not have any such index?
Nothing, would be my vote. You'd just re-CLUSTER all tables that have
been clustered before, the same way they were last clustered.
(I'm not actually convinced that this behavior is worth the code space
it'd take to implement, btw.)
regards, tom lane
Tom Lane wrote: > Alvaro Herrera <alvherre@atentus.com> writes: > > Maybe if some index had the indisclustered bit set one could select > > that; but is it possible for some table to have more than one index with > > it? Intuition (but no code observation) says no. > > At the moment that bit will never be set at all, unless you were to > reach in and set it with a manual "UPDATE pg_index" command. > > It would probably be feasible for the CLUSTER code to update the system > catalogs to set the bit on the index used for the clustering (and clear > it from any others it might be set on). Then indisclustered would have > the semantics of "the index most recently used in CLUSTERing its table", > which seems pretty reasonable. And it'd fit in nicely as the control > bit for an auto-CLUSTER command. Added to TODO: o Cluster all tables at once using pg_index.indisclustered or primary key > > And what happens with those tables that do not have any such index? > > Nothing, would be my vote. You'd just re-CLUSTER all tables that have > been clustered before, the same way they were last clustered. > > (I'm not actually convinced that this behavior is worth the code space > it'd take to implement, btw.) I was thinking of a shell script for this, not something in the backend. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
> Added to TODO: > > o Cluster all tables at once using pg_index.indisclustered or primary key > > > > And what happens with those tables that do not have any such index? > > > > Nothing, would be my vote. You'd just re-CLUSTER all tables that have > > been clustered before, the same way they were last clustered. I second Tom's opinion. If the table was not clustered before leave it as is. Thus the TODO should imho (if at all :-) read:o Cluster all tables at once that have a pg_index.indisclustered Andreas
Sounds good to me. TODO updated:
o Cluster all tables at once using pg_index.indisclustered set during previous CLUSTER
---------------------------------------------------------------------------
Zeugswetter Andreas SB SD wrote:
>
> > Added to TODO:
> >
> > o Cluster all tables at once using pg_index.indisclustered or primary key
> >
> > > > And what happens with those tables that do not have any such index?
> > >
> > > Nothing, would be my vote. You'd just re-CLUSTER all tables that have
> > > been clustered before, the same way they were last clustered.
>
> I second Tom's opinion. If the table was not clustered before leave it as is.
>
> Thus the TODO should imho (if at all :-) read:
> o Cluster all tables at once that have a pg_index.indisclustered
>
> Andreas
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073