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, Pennsylvania 19073
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
Import Notes
Resolved by subject fallback
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.indisclusteredAndreas
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
--
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