[ADMIN] CLUSTER command

[Rick Dicaire]

Hi folks...pgsql 8.1 (we're migrating to 9.1 later this year).

Two things:

When running CLUSTER with no args, on tables with multiple indexes, do
I understand correctly that the tables' primary key is the default
index used?

Also, I've been tasked with finding and listing all the tables that
get CLUSTER'd when CLUSTER with no args is executed, and in what order
they're CLUSTER'd. Where is information regarding a tables CLUSTER
status/index used/etc stored?

Thanks

[Kevin Grittner]

Post by Rick Dicaire
Hi folks...pgsql 8.1 (we're migrating to 9.1 later this year).

Good idea.

Post by Rick Dicaire
When running CLUSTER with no args, on tables with multiple
indexes, do I understand correctly that the tables' primary key is
the default index used?

To quote the fine documentations at:

www.postgresql.org/docs/8.1/interactive/sql-cluster.html

| When a table is clustered, PostgreSQL remembers on which index it
| was clustered. The form CLUSTER tablename reclusters the table on
| the same index that it was clustered before.
|
| CLUSTER without any parameter reclusters all the tables in the
| current database that the calling user owns, or all tables if
| called by a superuser. (Never-clustered tables are not included.)

Post by Rick Dicaire
Also, I've been tasked with finding and listing all the tables
that get CLUSTER'd when CLUSTER with no args is executed,

http://www.postgresql.org/docs/8.1/interactive/catalog-pg-index.html

-Kevin

[Rick Dicaire]

On Wed, Jan 18, 2012 at 10:08 AM, Kevin Grittner

Post by Kevin Grittner

Post by Rick Dicaire
Also, I've been tasked with finding and listing all the tables
that get CLUSTER'd when CLUSTER with no args is executed,

http://www.postgresql.org/docs/8.1/interactive/catalog-pg-index.html

select relname as table from pg_class join pg_index on pg_class.oid =
pg_index.indrelid where indisclustered = 't';
select relname as index from pg_class join pg_index on pg_class.oid =
pg_index.indexrelid where indisclustered = 't';

Thanks, this gets me the list.

How do I determine in what order tables are clustered when CLUSTER is
executed with no args?

[Tom Lane]

Post by Rick Dicaire
How do I determine in what order tables are clustered when CLUSTER is
executed with no args?

AFAIR there is no specified order. It probably just seqscans pg_class,
so whatever physical order those tuples happen to have today is it.

regards, tom lane

[Scott Ribe]

Post by Tom Lane
AFAIR there is no specified order. It probably just seqscans pg_class,
so whatever physical order those tuples happen to have today is it.

I'm pretty sure he meant: "in the case when CLUSTER was issued in the past to cluster the table on some index, but was not documented, how do I now find out what that index was".
--
Scott Ribe
***@elevated-dev.com
http://www.elevated-dev.com/
[phone] voice

[Rick Dicaire]

Post by Scott Ribe

AFAIR there is no specified order.  It probably just seqscans pg_class,
so whatever physical order those tuples happen to have today is it.

I'm pretty sure he meant: "in the case when CLUSTER was issued in the past to cluster the table on some index, but was not documented, how do I now find out what that index was".

No, I know what the indexes are. The scenario is there's 3 tables in
the db that get clustered. Wanted to know in what order those 3 tables
are reclustered when CLUSTER is exec'd with no args.

Thank you all for your help!

[Scott Ribe]

Post by Rick Dicaire
No, I know what the indexes are. The scenario is there's 3 tables in
the db that get clustered. Wanted to know in what order those 3 tables
are reclustered when CLUSTER is exec'd with no args.

Ah, I see now. Sorry for the noise.
--
Scott Ribe
***@elevated-dev.com
http://www.elevated-dev.com/
[phone] voice