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=#
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
