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)


Mon, 12 Aug 2002 16:50:56 -0400 (CLT)
Alvaro Herrera <alvherre@atentus.com>
CLUSTER all tables at once?
> 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.


12 Aug 2002 16:58:57 -0400
Rod Taylor <rbt@zort.ca>
Re: CLUSTER all tables at once?
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

Mon, 12 Aug 2002 18:14:41 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: CLUSTER all tables at once? 
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, Pennsylvania 19073

Wed, 14 Aug 2002 01:45:45 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: CLUSTER all tables at once?