Hi Tomas,

Thank you.

Please help me here.....I am not a data= base guy...........how do I use this CLUSTER command and what does it do? P= lease keep in mind that I do not have disk space left.........

Also,= please check the table I am talking about below:

opennms=3D# \d events
   &= nbsp;           &nbs= p;        Tabela "public.events"<= br style=3D"font-family: courier,monaco,monospace,sans-serif;">      =    Colunm          | = ;           Type &n= bsp;          | Modifyers
------------------= -------+-----------------------------+---------------
eventid   &nbs= p;             = | integer           =           | not null eventuei   &nbs= p;            | char= acter varying(256)      | not null
nodeid   = ;            &n= bsp; | integer         &= nbsp;           |<= br style=3D"font-family: courier,monaco,monospace,sans-serif;"> eventtime = ;            &n= bsp; | timestamp without time zone | not null
eventhost   &nb= sp;           | character= varying(256)      |
eventsource    &n= bsp;        | character varying(128)&nbs= p;     | not null
ipaddr      &nb= sp;           | character= varying(16)       |
eventdpname   &= nbsp;         | character varying(1= 2)       | not null
eventsnmphost   &n= bsp;       | character varying(256) &nbs= p;    |
serviceid        &nb= sp;      | integer     &n= bsp;            = ;   |
eventsnmp   &nb= sp;           | character= varying(256)      |
eventparms    &nb= sp;         | text   = ;            &n= bsp;        |
eventcreatetime   &= nbsp;     | timestamp without time zone | not null
eventdescr   &n= bsp;          | character vary= ing(4000)     |
eventloggroup     &nbs= p;     | character varying(32)    &= nbsp; |
= eventlogmsg         &nbs= p;   | character varying(256)      |
eventseveri= ty           | integer           &= nbsp;         | not null
eventpathoutage&nb= sp;        | character varying(1024)&nbs= p;    |
eventcorrelation        |= character varying(1024)     |
eventsuppressedcount  = | integer          = ;           |
eventoperinstruct   &nbs= p;   | character varying(1024)     | eventautoactio= n         | character varying(256)&= nbsp;     |
eventoperaction      =    | character varying(256)      |
eventoperacti= onmenutext | character varying(64)       |
eventnotification   &nbs= p;   | character varying(128)      |
eventtticke= t            | chara= cter varying(128)      |
eventtticketstate  &nbs= p;    | integer       &nb= sp;            = |
eventforward   =          | character varying(256)&n= bsp;     |
eventmouseovertext      | c= haracter varying(64)       |
eventlog  &nb= sp;            = | character(1)          =       | not null
eventdisplay   =          | character(1)  =             &nb= sp; | not null
eventackuser         &n= bsp; | character varying(256)      |<= br style=3D"font-family: courier,monaco,monospace,sans-serif;"> ndices:ktime&n= bsp;           | timestam= p without time zone |
    "pk_eventid"primary key, btree (eventid)<= br style=3D"font-family: courier,monaco,monospace,sans-serif;">    "events_acktime_idx" btree= (eventacktime)

    "events= _display_idx" btree (eventdisplay)
    "events_ipaddr_idx" btree (ipaddr)<= /span>
  &n= bsp; "events_log_idx" btree (eventlog)
    "events_nodeid_idx" btree = (nodeid)
= ;   "events_serviceid_idx" btree (serviceid)
    "events_severit= y_idx" btree (eventseverity)
    "events_time_idx" btree (eventtime)
    = "events_uei_idx" btree (eventuei)
Restrictions of foreing key:
    "fk_nodeid= 6" FOREIGN KEY (nodeid) REFERENCES node(nodeid) ON DELETE CASCADE

Thanks.

Mario Behring

----- = Original Message ----
From: Tomas Vondra <tv@fuzzy.cz>
To: Mari= o Behring <mariobehring@yahoo.com>
Sent: Tuesday, January 16, 2007= 12:29:59 PM
Subject: Re: [SQL] Droping indexes

> Hi all,=
>
> Please, if I drop all indexes from a table, can I recreat= e them after
> performing a vacuum full at this table? I mean, I do = not know details
> about the indexes, so what I am asking is if I is= sue a REINDEX on this
> table, will it create the proper indexes again?
>= ;
> Please advise.

No, if you drop them they're lost and you'= ll have to recreate them using
CREATE INDEX. Being in your situation, I= 'd use CLUSTER instead of VACUUM
+ REINDEX, as it basically does the sa= me thing, plus it has several
advantages related to performance.
Just think carefully which index would you use to cluster the table -
= the index associated with the primary key is generally a good choice.
Tomas

PS: Don't forget to analyze the table after that!

<= /div>

=0A=0A

8:00? 8:25? 8:40? Find a= flick in no time
with theYahoo! Search movie showtime shortcut= . --0-1265797147-1168958609=:38721-- From alvherre@commandprompt.com Tue Jan 16 15:09:13 2007 Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 2BFD89FB1F9 for ; Tue, 16 Jan 2007 11:09:13 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 96354-06 for ; Tue, 16 Jan 2007 11:09:00 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from lists.commandprompt.com (host-254.commandprompt.net [207.173.203.254]) by postgresql.org (Postfix) with ESMTP id 443009FB1DC for ; Tue, 16 Jan 2007 11:09:00 -0400 (AST) Received: from perhan.alvh.no-ip.org (200-126-100-202.bk7-dsl.surnet.cl [200.126.100.202]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id l0GF8vYo029648; Tue, 16 Jan 2007 07:08:58 -0800 Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id 7B54B32A05; Tue, 16 Jan 2007 12:08:55 -0300 (CLST) Date: Tue, 16 Jan 2007 12:08:55 -0300 From: Alvaro Herrera To: Mario Behring Cc: Tomas Vondra , pgsql-sql@postgresql.org Subject: Re: Droping indexes Message-ID: <20070116150855.GB24671@alvh.no-ip.org> Mail-Followup-To: Mario Behring , Tomas Vondra , pgsql-sql@postgresql.org References: <754782.38721.qm@web30006.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <754782.38721.qm@web30006.mail.mud.yahoo.com> User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: ClamAV version 0.88.5, clamav-milter version 0.88.5 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Tue, 16 Jan 2007 07:08:58 -0800 (PST) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/54 X-Sequence-Number: 27206 Mario Behring wrote: > Hi Tomas, > > Thank you. > > Please help me here.....I am not a database guy...........how do I use this CLUSTER command and what does it do? Please keep in mind that I do not have disk space left......... If the disk is full you cannot use CLUSTER anyway. Suggestion: make a note of the indexes that exist. Drop them. Do a VACUUM FULL of the table. Create the indexes again. After that's done: 1. read the CLUSTER documentation, as it may help you in the future 2. consider restructuring the table so that redundant information is kept only on one place (for example, the eventackuser could probably be put on a separate table and on this one store just an integer ID). This will make the table and the index on that column a lot smaller. 3. add more disks to your installation 4. research a more effective VACUUM policy -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support From mariobehring@yahoo.com Tue Jan 16 15:43:19 2007 Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 6A01F9FB2F1 for ; Tue, 16 Jan 2007 11:43:19 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 26030-01 for ; Tue, 16 Jan 2007 11:42:45 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from web30012.mail.mud.yahoo.com (web30012.mail.mud.yahoo.com [209.191.69.29]) by postgresql.org (Postfix) with SMTP id AB63A9FB2EF for ; Tue, 16 Jan 2007 11:42:50 -0400 (AST) Received: (qmail 85765 invoked by uid 60001); 16 Jan 2007 15:42:39 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:MIME-Version:Content-Type:Message-ID; b=PnM0v5KdqQb3eiImGp1pKwgGJbBs/tKOWU/7R5RPk1mm/0w9JuB8F3clDkvs87bIJsGxQVctgcoDZ8cRL9z/w42Ric0HaULyDJvnOD1Bnp8RWlmJwrdYlMOTZ8Zy3t6eRQ0og5umlAGNRXO3USexnqLXAo3FVnKfvyLxrDbwUkQ=; X-YMail-OSG: GYNm3dYVM1lnqPjTpxpAPT.KwaOPIZk1Edm4SD2SZDtsExMZFaGy0AGF6u9WQU5gRGodps9sdQBnZVOFHDtIyNL.0RebsFIzzCTjvPIOHUuPMJa0QTzj4LFSitqdI8VJ260jib96SaJ3tyE- Received: from [200.161.5.135] by web30012.mail.mud.yahoo.com via HTTP; Tue, 16 Jan 2007 07:42:39 PST X-Mailer: YahooMailRC/368.3 YahooMailWebService/0.6.132.7 Date: Tue, 16 Jan 2007 07:42:38 -0800 (PST) From: Mario Behring Subject: Re: Droping indexes To: Alvaro Herrera Cc: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-777126573-1168962158=:83800" Message-ID: <40081.83800.qm@web30012.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/55 X-Sequence-Number: 27207 --0-777126573-1168962158=:83800 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hi Alvaro,=0A=0AThank you for your advise..............I was thinking about= doing exactly that, I wasn't sure on how to do it though, meaning, conside= ring the info below, how should I use the CREATE INDEX command to create th= ese indexes??=0A=0Aopennms=3D# \d events=0A Tabela "= public.events"=0A Colunm | Type | M= odifyers=0A-------------------------+-----------------------------+--------= -------=0A eventid | integer | not null= =0A eventuei | character varying(256) | not null=0A nod= eid | integer |=0A eventtime = | timestamp without time zone | not null=0A eventhost | = character varying(256) |=0A eventsource | character varyin= g(128) | not null=0A ipaddr | character varying(16) = |=0A eventdpname | character varying(12) | not null= =0A eventsnmphost | character varying(256) |=0A serviceid = | integer |=0A eventsnmp | ch= aracter varying(256) |=0A eventparms | text = |=0A eventcreatetime | timestamp without time zone | not = null=0A eventdescr | character varying(4000) |=0A eventlog= group | character varying(32) |=0A eventlogmsg = | character varying(256) |=0A eventseverity |=0A integer = | not null=0A eventpathoutage | character varying(= 1024) |=0A eventcorrelation | character varying(1024) |=0A e= ventsuppressedcount | integer |=0A eventoperinstruct= | character varying(1024) |=0A eventautoaction | charact= er varying(256) |=0A eventoperaction | character varying(256) = |=0A eventoperactionmenutext | character varying(64) |=0A eventn= otification | character varying(128) |=0A eventtticket = | character varying(128) |=0A eventtticketstate | integer = |=0A eventforward | character varying(256) = |=0A eventmouseovertext | character varying(64) |=0A eventlog = | character(1) | not null=0A eventdisplay = | character(1) | not null=0A eventackuser |= character varying(256) |=0A ndices:ktime | timestamp witho= ut time zone |=0A "pk_eventid"primary key, btree (eventid)=0A "events= _acktime_idx" btree (eventacktime)=0A "events_ackuser_idx" btree (eventa= ckuser)=0A "events_display_idx" btree (eventdisplay)=0A "events_ipadd= r_idx" btree (ipaddr)=0A "events_log_idx" btree (eventlog)=0A "events= _nodeid_idx" btree (nodeid)=0A "events_serviceid_idx" btree (serviceid)= =0A "events_severity_idx" btree (eventseverity)=0A "events_time_idx" = btree (eventtime)=0A "events_uei_idx" btree (eventuei)=0ARestrictions of= foreing key:=0A "fk_nodeid6" FOREIGN KEY (nodeid) REFERENCES node(nodei= d) ON DELETE CASCADE=0A=0A=0AThank you.=0A=0ABest regards,=0A=0AMario Behri= ng=0A=0A=0A----- Original Message ----=0AFrom: Alvaro Herrera =0ATo: Mario Behring =0ACc: Tomas Vo= ndra ; pgsql-sql@postgresql.org=0ASent: Tuesday, January 16, 2= 007 1:08:55 PM=0ASubject: Re: [SQL] Droping indexes=0A=0AMario Behring wrot= e:=0A> Hi Tomas,=0A> =0A> Thank you.=0A> =0A> Please help me here.....I am = not a database guy...........how do I use this CLUSTER command and what doe= s it do? Please keep in mind that I do not have disk space left.........=0A= =0AIf the disk is full you cannot use CLUSTER anyway. Suggestion: make a= =0Anote of the indexes that exist. Drop them. Do a VACUUM FULL of the=0At= able. Create the indexes again.=0A=0AAfter that's done:=0A=0A1. read the C= LUSTER documentation, as it may help you in the future=0A=0A2. consider res= tructuring the table so that redundant information is=0Akept only on one pl= ace (for example, the eventackuser could probably be=0Aput on a separate ta= ble and on this one store just an integer ID). This=0Awill make the table = and the index on that column a lot smaller.=0A=0A3. add more disks to your = installation=0A=0A4. research a more effective VACUUM policy=0A=0A-- =0AAlv= aro Herrera http://www.CommandPrompt.com/=0A= PostgreSQL Replication, Consulting, Custom Development, 24x7 support=0A=0A= =0A=0A=0A=0A=0A=0A =0A_____________________________________________________= _______________________________=0AWant to start your own business?=0ALearn = how on Yahoo! Small Business.=0Ahttp://smallbusiness.yahoo.com/r-index --0-777126573-1168962158=:83800 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable

Hi Alvaro,

Thank you for your advise..............I was thinking ab= out doing exactly that, I wasn't sure on how to do it though, meaning, cons= idering the info below, how should I use the CREATE INDEX command to create= these indexes??

opennms=3D# \d events
        &nbs= p;            &= nbsp; Tabela "public.events"
      =    Colunm          | = ;           Type &n= bsp;          | Modifyers
------------------= -------+-----------------------------+---------------
eventid   &nbs= p;             = | integer           =           | not null eventuei   &nbs= p;            | char= acter varying(256)      | not null
nodeid   = ;            &n= bsp; | integer         &= nbsp;           |<= br style=3D"font-family: courier,monaco,monospace,sans-serif;"> eventtime = ;            &n= bsp; | timestamp without time zone | not null
eventhost   &nb= sp;           | character= varying(256)      |
eventsource    &n= bsp;        | character varying(128)&nbs= p;     | not null
ipaddr      &nb= sp;           | character= varying(16)       |
eventdpname   &= nbsp;         | character varying(1= 2)       | not null
eventsnmphost   &n= bsp;       | character varying(256) &nbs= p;    |
serviceid        &nb= sp;      | integer     &n= bsp;            = ;   |
eventsnmp   &nb= sp;           | character= varying(256)      |
eventparms    &nb= sp;         | text   = ;            &n= bsp;        |
eventcreatetime   &= nbsp;     | timestamp without time zone | not null
eventdescr   &n= bsp;          | character vary= ing(4000)     |
eventloggroup     &nbs= p;     | character varying(32)    &= nbsp; |
= eventlogmsg         &nbs= p;   | character varying(256)      |
eventseveri= ty           |=0A integer=             &nb= sp;        | not null
eventpathoutage  = ;       | character varying(1024)  =    |
eventcorrelation        | charact= er varying(1024)     |
eventsuppressedcount    |= integer           &= nbsp;         |
eventoperinstruct   &nbs= p;   | character varying(1024)     | eventautoactio= n         | character varying(256)&= nbsp;     |
eventoperaction      =    | character varying(256)      |
eventoperacti= onmenutext | character varying(64)       |
eventnotification   &nbs= p;   | character varying(128)      |
eventtticke= t            | chara= cter varying(128)      |
eventtticketstate  &nbs= p;    | integer       &nb= sp;            = |
eventforward   =          | character varying(256)&n= bsp;     |
eventmouseovertext      | c= haracter varying(64)       |
eventlog  &nb= sp;            = | character(1)          =       | not null
eventdisplay   =          | character(1)  =             &nb= sp; | not null
eventackuser         &n= bsp; | character varying(256)      |<= br style=3D"font-family: courier,monaco,monospace,sans-serif;"> ndices:ktime&n= bsp;           | timestam= p without time zone |
    "pk_eventid"primary key, btree (eventid)<= br style=3D"font-family: courier,monaco,monospace,sans-serif;">    "events_acktime_idx" btree= (eventacktime)

    "events= _display_idx" btree (eventdisplay)
    "events_ipaddr_idx" btree (ipaddr)<= /span>
  &n= bsp; "events_log_idx" btree (eventlog)
    "events_nodeid_idx" btree = (nodeid)
= ;   "events_serviceid_idx" btree (serviceid)
    "events_severit= y_idx" btree (eventseverity)
    "events_time_idx" btree (eventtime)
    = "events_uei_idx" btree (eventuei)
Restrictions of foreing key:
    "fk_nodeid= 6" FOREIGN KEY (nodeid) REFERENCES node(nodeid) ON DELETE CASCADE

Thank you.

Best regards,

Mario Behring

----- Original Message ----
From: Alvaro Herrera <alvherre@comm= andprompt.com>
To: Mario Behring <mariobehring@yahoo.com>
Cc= : Tomas Vondra <tv@fuzzy.cz>; pgsql-sql@postgresql.org
Sent: Tuesd= ay, January 16, 2007 1:08:55 PM
Subject: Re: [SQL] Droping indexes

Mario Behring wrote:
> Hi Tomas,
>
> Thank you.>
> Please help me here.....I am not a database guy...........h= ow do I use this CLUSTER command and what does it do? Please keep in mind that I do not hav= e disk space left.........

If the disk is full you cannot use CLUSTE= R anyway.  Suggestion: make a
note of the indexes that exist.&= nbsp; Drop them.  Do a VACUUM FULL of the
table. &nb= sp;Create the indexes again.

After that's done:

1. read the C= LUSTER documentation, as it may help you in the future

2. consider r= estructuring the table so that redundant information is
kept only on one= place (for example, the eventackuser could probably be
put on a separat= e table and on this one store just an integer ID).  This
will = make the table and the index on that column a lot smaller.

3. add mo= re disks to your installation

4. research a more effective VACUUM po= licy

--
Alvaro Herrera           &= nbsp;           &nbs= p;        http://www.CommandPrompt.com/
Pos= tgreSQL Replication, Consulting, Custom Development, 24x7 support