Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 737CD9FB34E for ; Tue, 16 Jan 2007 10:43:53 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 98768-08 for ; Tue, 16 Jan 2007 10:43:31 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from web30006.mail.mud.yahoo.com (web30006.mail.mud.yahoo.com [209.191.69.23]) by postgresql.org (Postfix) with SMTP id 529409FB350 for ; Tue, 16 Jan 2007 10:43:31 -0400 (AST) Received: (qmail 39287 invoked by uid 60001); 16 Jan 2007 14:43:29 -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=ZltQsiGg+CnXQ/t2WCmvY+v0q6pzEjedsnMAN9IQDNs9cu50gP2GXVgDZZm30CHtIP0LHKlvaZiGAqb9Y3yw+pVzWnkxoeQh6AN5sOQFAailPgN8cBlVsZ9ebHRomsNMu8Tcj+Ru5VG9tbzzijHjrFCeB3rits8uqFQ7rcng0l4=; X-YMail-OSG: YFd5oaQVM1nqElVl_VxqFFuaIAi0eYA_i0CcKgJUXJK6bMLd75SmmSme0nqqnmbhuUEKDSVe2R7fTx04Sh7tNasApMAAqz.PpImzUPG71KsvpjqVPez4gw-- Received: from [200.161.5.135] by web30006.mail.mud.yahoo.com via HTTP; Tue, 16 Jan 2007 06:43:29 PST X-Mailer: YahooMailRC/368.3 YahooMailWebService/0.6.132.7 Date: Tue, 16 Jan 2007 06:43:29 -0800 (PST) From: Mario Behring Subject: Re: Droping indexes To: Tomas Vondra Cc: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1265797147-1168958609=:38721" Message-ID: <754782.38721.qm@web30006.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/53 X-Sequence-Number: 27205 --0-1265797147-1168958609=:38721 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hi Tomas,=0A=0AThank you.=0A=0APlease 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.........=0A=0AAlso, please= check the table I am talking about below:=0A=0Aopennms=3D# \d events=0A = Tabela "public.events"=0A Colunm | = Type | Modifyers=0A-------------------------+---------= --------------------+---------------=0A eventid | integer = | not null=0A eventuei | character varyin= g(256) | not null=0A nodeid | integer = |=0A eventtime | timestamp without time zone | not null= =0A eventhost | character varying(256) |=0A eventsource = | character varying(128) | not null=0A ipaddr = | character varying(16) |=0A eventdpname | character= varying(12) | not null=0A eventsnmphost | character varyin= g(256) |=0A serviceid | integer |=0A= eventsnmp | character varying(256) |=0A eventparms = | text |=0A eventcreatetime | times= tamp without time zone | not null=0A eventdescr | character va= rying(4000) |=0A eventloggroup | character varying(32) = |=0A eventlogmsg | character varying(256) |=0A eventseveri= ty | integer | not null=0A eventpathoutage = | character varying(1024) |=0A eventcorrelation | characte= r varying(1024) |=0A eventsuppressedcount | integer = |=0A eventoperinstruct | character varying(1024) |=0A eventau= toaction | character varying(256) |=0A eventoperaction = | character varying(256) |=0A eventoperactionmenutext | character var= ying(64) |=0A eventnotification | character varying(128) |= =0A eventtticket | character varying(128) |=0A eventtticket= state | integer |=0A eventforward | ch= aracter 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 without time zone |=0A "pk_eventid"primary key, btr= ee (eventid)=0A "events_acktime_idx" btree (eventacktime)=0A "events_= ackuser_idx" btree (eventackuser)=0A "events_display_idx" btree (eventdi= splay)=0A "events_ipaddr_idx" btree (ipaddr)=0A "events_log_idx" btre= e (eventlog)=0A "events_nodeid_idx" btree (nodeid)=0A "events_service= id_idx" btree (serviceid)=0A "events_severity_idx" btree (eventseverity)= =0A "events_time_idx" btree (eventtime)=0A "events_uei_idx" btree (ev= entuei)=0ARestrictions of foreing key:=0A "fk_nodeid6" FOREIGN KEY (node= id) REFERENCES node(nodeid) ON DELETE CASCADE=0A=0A=0AThanks.=0A=0AMario Be= hring=0A=0A=0A=0A=0A=0A----- Original Message ----=0AFrom: Tomas Vondra =0ATo: Mario Behring =0ASent: Tuesday, Ja= nuary 16, 2007 12:29:59 PM=0ASubject: Re: [SQL] Droping indexes=0A=0A> Hi a= ll,=0A> =0A> Please, if I drop all indexes from a table, can I recreate the= m after =0A> performing a vacuum full at this table? I mean, I do not know = details =0A> about the indexes, so what I am asking is if I issue a REINDEX= on this =0A> table, will it create the proper indexes again?=0A> =0A> Ple= ase advise.=0A=0ANo, if you drop them they're lost and you'll have to recre= ate them using =0ACREATE INDEX. Being in your situation, I'd use CLUSTER in= stead of VACUUM =0A+ REINDEX, as it basically does the same thing, plus it = has several =0Aadvantages related to performance.=0A=0AJust think carefully= which index would you use to cluster the table - =0Athe index associated w= ith the primary key is generally a good choice.=0A=0ATomas=0A=0APS: Don't f= orget to analyze the table after that!=0A=0A=0A=0A=0A=0A=0A=0A=0A =0A______= ___________________________________________________________________________= ___=0ANeed Mail bonding?=0AGo to the Yahoo! Mail Q&A for great tips from Ya= hoo! Answers users.=0Ahttp://answers.yahoo.com/dir/?link=3Dlist&sid=3D39654= 6091 --0-1265797147-1168958609=:38721 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
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--