Thread: CLUSTER command
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
Rick Dicaire <kritek@gmail.com> wrote: > Hi folks...pgsql 8.1 (we're migrating to 9.1 later this year). Good idea. > 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.) > 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
On Wed, Jan 18, 2012 at 10:08 AM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: >> 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?
Rick Dicaire <kritek@gmail.com> writes:
> 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
On Jan 18, 2012, at 11:59 AM, Tom Lane wrote: > 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 notdocumented, how do I now find out what that index was". -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice
On Wed, Jan 18, 2012 at 2:17 PM, Scott Ribe <scott_ribe@elevated-dev.com> wrote: > On Jan 18, 2012, at 11:59 AM, Tom Lane wrote: > >> 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 wasnot 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!
On Jan 18, 2012, at 1:09 PM, Rick Dicaire wrote: > 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 scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice