From: Mario Behring <mariobehring@yahoo.com>
To: Alvaro Herrera <alvherre@commandprompt.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Droping indexes
Date: Tue, 16 Jan 2007 07:42:38 -0800 (PST)
Message-ID: <40081.83800.qm@web30012.mail.mud.yahoo.com> (raw)
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@commandprompt.com>
To: Mario Behring <mariobehring@yahoo.com>
Cc: Tomas Vondra <tv@fuzzy.cz>; pgsql-sql@postgresql.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
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, alvherre@commandprompt.com
Subject: Re: Droping indexes
In-Reply-To: <40081.83800.qm@web30012.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