Re: Should I CLUSTER on PRIMARY KEY

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(at)[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(at)[local]:5432) 11:20:14 [test]
# drop table a;
DROP TABLE
Time: 5.166 ms
(csmith(at)[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(at)[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(at)[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/