Обсуждение: Should I CLUSTER on PRIMARY KEY

Robert James wrote:
> I would like to CLUSTER a table on its PRIMARY KEY.  Now, I haven't
> explicitly defined and named an index for this table - but the primary
> key defines one.   How can I tell Postgres to CLUSTER on it?

Get the index name:

\d tablename

Right at the bottom it will have the index names:

Indexes:
     "a_pkey" PRIMARY KEY, btree (a)


then cluster:

# cluster tablename using a_pkey;
CLUSTER


> Also: If I define an index on a PK, will Postgres make a second one, or
> realize its redundnant?

Depends how you define it (I think). What's your create table statement
look like?

--
Postgresql & php tutorials
http://www.designmagick.com/

Robert James wrote:
> Thanks, Chris.  Is there a way to do this deterministically, or at least
> programatically? I have code to create the tables and cluster them
> automatically?

 From a quick test, it seems the naming convention is 'tablename_pkey':

# create table a(blah text primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey"
for table "a"
CREATE TABLE
Time: 12.336 ms
(csmith@[local]:5432) 11:20:08 [test]
# \d a
      Table "public.a"
  Column | Type | Modifiers
--------+------+-----------
  blah   | text | not null
Indexes:
     "a_pkey" PRIMARY KEY, btree (blah)

(csmith@[local]:5432) 11:20:14 [test]
# drop table a;
DROP TABLE
Time: 5.166 ms
(csmith@[local]:5432) 11:20:25 [test]
# create table a(c float primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey"
for table "a"
CREATE TABLE
Time: 1.624 ms
(csmith@[local]:5432) 11:20:36 [test]
# \d a
            Table "public.a"
  Column |       Type       | Modifiers
--------+------------------+-----------
  c      | double precision | not null
Indexes:
     "a_pkey" PRIMARY KEY, btree (c)

(csmith@[local]:5432) 11:20:36 [test]


Though I'd hesitate to automatically do a cluster on all of your tables.

http://www.postgresql.org/docs/current/static/sql-cluster.html

When a table is being clustered, an ACCESS EXCLUSIVE lock is acquired on
it. This prevents any other database operations (both reads and writes)
from operating on the table until the CLUSTER is finished.

This could take quite a while if you have a large table.

--
Postgresql & php tutorials
http://www.designmagick.com/

On Sun, Jul 19, 2009 at 08:56:08PM -0400, Robert James wrote:
> Thanks, Chris.  Is there a way to do this deterministically, or at least
> programatically? I have code to create the tables and cluster them
> automatically?

As Chris said, the index used by the primary key uses the table name
with "_pkey" append on the end.  If you want to figure out how psql is
figuring this out, you can pass it -E when you connect.

Why do you care though?  CLUSTERing is a rather limited fix for a rather
specialized type of problem, it's not something that's worth setting
for every table and almost certainly not just blindly doing it on the
table's primary key.

--
  Sam  http://samason.me.uk/