Re: Droping indexes

Hi Alvaro,

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

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

Thank you.

Best regards,

Mario Behring

----- Original Message ----
From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Mario Behring <mariobehring(at)yahoo(dot)com>
Cc: Tomas Vondra <tv(at)fuzzy(dot)cz>; pgsql-sql(at)postgresql(dot)org
Sent: Tuesday, 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...........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

____________________________________________________________________________________
Want to start your own business?
Learn how on Yahoo! Small Business.
http://smallbusiness.yahoo.com/r-index