Re: cluster index on a table

Clustering reorganizes the layout of a table according to
the ordering of a SINGLE index. This will place items that
are adjacent in the index adjacent in the heap. So you need
to cluster on the index that will help the locality of
reference for the queries which will benefit you the most.
Execution time sensitive queries are a good way to choose.

Cheers,
Ken

On Wed, Jun 24, 2009 at 08:32:14PM +0300, Ibrahim Harrani wrote:
> 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 (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>