From: Mario Behring <mariobehring@yahoo.com>
To: Tomas Vondra <tv@fuzzy.cz>
Cc: pgsql-sql@postgresql.org
Subject: Re: Droping indexes
Date: Tue, 16 Jan 2007 06:43:29 -0800 (PST)
Message-ID: <754782.38721.qm@web30006.mail.mud.yahoo.com> (raw)
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.........
Also, please check the table I am talking about below:
opennms=# \d events
Tabela "public.events"
Colunm | Type | Modifyers
-------------------------+-----------------------------+---------------
eventid | integer | not null
eventuei | character varying(256) | not null
nodeid | integer |
eventtime | timestamp without time zone | not null
eventhost | character varying(256) |
eventsource | character varying(128) | not null
ipaddr | character varying(16) |
eventdpname | character varying(12) | not null
eventsnmphost | character varying(256) |
serviceid | integer |
eventsnmp | character varying(256) |
eventparms | text |
eventcreatetime | timestamp without time zone | not null
eventdescr | character varying(4000) |
eventloggroup | character varying(32) |
eventlogmsg | character varying(256) |
eventseverity | integer | not null
eventpathoutage | character varying(1024) |
eventcorrelation | character varying(1024) |
eventsuppressedcount | integer |
eventoperinstruct | character varying(1024) |
eventautoaction | character varying(256) |
eventoperaction | character varying(256) |
eventoperactionmenutext | character varying(64) |
eventnotification | character varying(128) |
eventtticket | character varying(128) |
eventtticketstate | integer |
eventforward | character varying(256) |
eventmouseovertext | character varying(64) |
eventlog | character(1) | not null
eventdisplay | character(1) | not null
eventackuser | character varying(256) |
ndices:ktime | timestamp without time zone |
"pk_eventid"primary key, btree (eventid)
"events_acktime_idx" btree (eventacktime)
"events_ackuser_idx" btree (eventackuser)
"events_display_idx" btree (eventdisplay)
"events_ipaddr_idx" btree (ipaddr)
"events_log_idx" btree (eventlog)
"events_nodeid_idx" btree (nodeid)
"events_serviceid_idx" btree (serviceid)
"events_severity_idx" btree (eventseverity)
"events_time_idx" btree (eventtime)
"events_uei_idx" btree (eventuei)
Restrictions of foreing key:
"fk_nodeid6" FOREIGN KEY (nodeid) REFERENCES node(nodeid) ON DELETE CASCADE
Thanks.
Mario Behring
----- Original Message ----
From: Tomas Vondra <tv@fuzzy.cz>
To: Mario 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 recreate 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 issue 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 same 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!
____________________________________________________________________________________
Need Mail bonding?
Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users.
http://answers.yahoo.com/dir/?link=list&sid=396546091
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mariobehring@yahoo.com, tv@fuzzy.cz
Subject: Re: Droping indexes
In-Reply-To: <754782.38721.qm@web30006.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox