Thread: Droping indexes

Droping indexes

From
Mario Behring
Date:
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.

Thanks in advance.

Mario Behring


Looking for earth-friendly autos?
Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center.

Re: Droping indexes

From
Mario Behring
Date:
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!



8:00? 8:25? 8:40? Find a flick in no time
with theYahoo! Search movie showtime shortcut.

Re: Droping indexes

From
Alvaro Herrera
Date:
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?
Pleasekeep 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


Re: Droping indexes

From
Mario Behring
Date:
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



Never miss an email again!
Yahoo! Toolbar
alerts you the instant new Mail arrives. Check it out.

Re: Droping indexes

From
Erik Jones
Date:
Mario Behring wrote:
> 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??
Have you read the documentation?

http://www.postgresql.org/docs/8.2/interactive/sql-createindex.html

-- 
erik jones <erik@myemma.com>
software development
emma(r)



Re: Droping indexes

From
Scott Marlowe
Date:
On Tue, 2007-01-16 at 07:51, Mario Behring wrote:
> 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?

The bad news:  No.  Once you drop an index it's just gone.
More bad news:  If it's a primary key index, you probably can't drop it
either.

Very good news:  PostgreSQL stores an index definition for easy index
recreation.  Let's say your tablename is "mytable"

This query will get you all the index creation statements for mytable:

select indexdef from pg_indexes where tablename='mytable';
CREATE UNIQUE INDEX mytable_pkey ON mytable USING btree (id)CREATE UNIQUE INDEX mytable_i1_key ON mytable USING btree
(i1)CREATEINDEX mytable_i1_i2 ON mytable USING btree (i1, i2)
 

Note that if you're using a schema other than the public one, you might
need to add 

tablespace='tablespacename' to the where clause.


Re: Droping indexes

From
Frank Bax
Date:
At 10:42 AM 1/16/07, Mario Behring wrote:
>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??


This might provide a clue:

pg_dump -t events | grep ^CREATE



Re: Droping indexes

From
Frank Bax
Date:
At 11:27 AM 1/16/07, Frank Bax wrote:
>At 10:42 AM 1/16/07, Mario Behring wrote:
>>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??
>
>
>This might provide a clue:
>
>pg_dump -t events | grep ^CREATE


Sorry, perhaps this would be better

pg_dump -s -t events | grep ^CREATE