cluster index on a table

From: Ibrahim Harrani <ibrahim(dot)harrani(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: cluster index on a table
Date: 2009-06-24 17:32:14
Message-ID: 530068a0906241032v1044638dy5237596421b4fe34@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hello,

I have a table like following. To increase the performance of this
table, I would like to create CLUSTER.
First, Which index should I use on this table for CLUSTER?
Secondly, Can I create multiple CLUSTER on the same table?
I will appreciate, if you can suggest other options to increase the
performance of the table.
I use this table to save metadata of the mails on my system.

mail=# \d maillogs
Table "public.maillogs"
Column | Type |
Modifiers
--------------------+-----------------------------+-------------------------------------------------------
id | bigint | not null default
nextval('maillogs_id_seq'::regclass)
queueid | character varying(255) | not null default
'*'::character varying
recvtime | timestamp without time zone | default now()
remoteip | character varying(128) | not null default
'0.0.0.0'::character varying
relayflag | smallint | not null default
(0)::smallint
retaction | integer |
retval | integer | not null default 0
probspam | double precision | not null default
(0)::double precision
messageid | text |
fromaddress | text | not null
toaddress | text | not null
envelopesender | text |
enveloperecipients | text |
messagesubject | text |
size | bigint |
logstr | character varying(1024) |
destinationaddress | character varying(255) |
quarantinepath | character varying(1024) | not null default
''::character varying
backuppath | character varying(1024) | not null default
''::character varying
quarantineflag | smallint | not null default
(0)::smallint
backupflag | smallint | not null default
(0)::smallint
deletedflag | smallint | not null default 0
profileid | integer | not null default 0
Indexes:
"maillogs_pkey" PRIMARY KEY, btree (id) CLUSTER
"idx_maillogs_backupflag" btree (backupflag)
"idx_maillogs_deletedflag" btree (deletedflag)
"idx_maillogs_enveloperecipients" btree (enveloperecipients)
"idx_maillogs_envelopesender" btree (envelopesender)
"idx_maillogs_messagesubject" btree (messagesubject)
"idx_maillogs_quarantineflag" btree (quarantineflag)
"idx_maillogs_recvtime" btree (recvtime)
"idx_maillogs_remoteip" btree (remoteip)
"idx_maillogs_revtal" btree (retval)
Foreign-key constraints:
"maillogs_profileid_fkey" FOREIGN KEY (profileid) REFERENCES
profiles(profileid)
Triggers:
maillogs_insert AFTER INSERT ON maillogs FOR EACH ROW EXECUTE
PROCEDURE maillogs_insert()

mail=#

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Kenneth Marshall 2009-06-24 17:40:26 Re: cluster index on a table
Previous Message Alvaro Herrera 2009-06-24 16:29:15 Re: tsvector_update_trigger performance?