Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group