pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCLUSTER command
7+ messages / 4 participants
[nested] [flat]
* CLUSTER command
@ 2012-01-18 14:51 Rick Dicaire <kritek@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Rick Dicaire @ 2012-01-18 14:51 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: CLUSTER command
@ 2012-01-18 15:08 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Rick Dicaire <kritek@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Kevin Grittner @ 2012-01-18 15:08 UTC (permalink / raw)
To: Rick Dicaire <kritek@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: CLUSTER command
@ 2012-01-18 18:54 Rick Dicaire <kritek@gmail.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 7+ messages in thread
From: Rick Dicaire @ 2012-01-18 18:54 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-admin
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?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: CLUSTER command
@ 2012-01-18 18:59 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rick Dicaire <kritek@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2012-01-18 18:59 UTC (permalink / raw)
To: Rick Dicaire <kritek@gmail.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: CLUSTER command
@ 2012-01-18 19:17 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Scott Ribe @ 2012-01-18 19:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Rick Dicaire <kritek@gmail.com>; pgsql-admin
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 not documented, 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: CLUSTER command
@ 2012-01-18 20:09 Rick Dicaire <kritek@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 7+ messages in thread
From: Rick Dicaire @ 2012-01-18 20:09 UTC (permalink / raw)
To: pgsql-admin
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 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!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: CLUSTER command
@ 2012-01-18 20:17 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Rick Dicaire <kritek@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Scott Ribe @ 2012-01-18 20:17 UTC (permalink / raw)
To: Rick Dicaire <kritek@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2012-01-18 20:17 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-01-18 14:51 CLUSTER command Rick Dicaire <kritek@gmail.com>
2012-01-18 15:08 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-01-18 18:54 ` Rick Dicaire <kritek@gmail.com>
2012-01-18 18:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-01-18 19:17 ` Scott Ribe <scott_ribe@elevated-dev.com>
2012-01-18 20:09 ` Rick Dicaire <kritek@gmail.com>
2012-01-18 20:17 ` Scott Ribe <scott_ribe@elevated-dev.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox