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
=


=0A=0A
Neve= r miss an email again!
Yahoo! Toolbar alerts you the instant new Mai= l arrives. Check it out. --0-777126573-1168962158=:83800--