Обсуждение: CLUSTER command

Поиск
Список
Период
Сортировка

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

Re: CLUSTER command

От
"Kevin Grittner"
Дата:
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

Re: CLUSTER command

От
Rick Dicaire
Дата:
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?

Re: CLUSTER command

От
Tom Lane
Дата:
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

Re: CLUSTER command

От
Scott Ribe
Дата:
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





Re: CLUSTER command

От
Rick Dicaire
Дата:
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!

Re: CLUSTER command

От
Scott Ribe
Дата:
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