Re: Droping indexes - Mailing list pgsql-sql
| From | Mario Behring |
|---|---|
| Subject | Re: Droping indexes |
| Date | |
| Msg-id | 40081.83800.qm@web30012.mail.mud.yahoo.com Whole thread Raw |
| In response to | Droping indexes (Mario Behring <mariobehring@yahoo.com>) |
| Responses |
Re: Droping indexes
(Erik Jones <erik@myemma.com>)
Re: Droping indexes (Frank Bax <fbax@sympatico.ca>) |
| List | pgsql-sql |
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
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
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
> 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
Never miss an email again!
Yahoo! Toolbar alerts you the instant new Mail arrives. Check it out.