Thread: 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?
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

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)

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.

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

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