Re: Droping indexes - Mailing list pgsql-sql
| From | Mario Behring |
|---|---|
| Subject | Re: Droping indexes |
| Date | |
| Msg-id | 754782.38721.qm@web30006.mail.mud.yahoo.com Whole thread Raw |
| In response to | Droping indexes (Mario Behring <mariobehring@yahoo.com>) |
| Responses |
Re: Droping indexes
(Alvaro Herrera <alvherre@commandprompt.com>)
|
| List | pgsql-sql |
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
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
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!
>
> 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!
8:00? 8:25? 8:40? Find a flick in no time
with theYahoo! Search movie showtime shortcut.