Re: Vacuum

On Thu, 29 Nov 2001, Tom Lane wrote:

> bangh <banghe@baileylink.net> writes:
> > However I noticed the index grows also very fast. Does anyone have a way to
> > compress the index?
>
> REINDEX, or just drop and recreate the indexes.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Okay, after some more testing and cussing/discussing with myself, I have
decided that my "reindex" perl script is ready for public consumption.

To use the attached script, you will need to put your username, password
and hostname into the program (just look for the comment).  If you run it
without any parameters, it will give you some simple instructions.  I use
it something like:

    $ fixtable.pl -I -t relevance kids

The program will then dump out SQL code to re-create ALL indexes (-I) for
the 'relevance' table (-t) in my 'kids' database.  You can feed the output
of the script to 'psql', or look at it first (I am paranoid) and then
cut-n-paste it to 'psql' yourself.

The SQL code creates a new index for each index on a table, drops the
original index, renames the new index to the old name, and then repeats
for the next index on the table.  This means that the user you login as
when you run 'psql' must own the indexes (otherwise the DROP INDEX fails).
If the drop fails for any reason, you will end up with TWO (2) identical
indexes which will almost certainly hurt performance on inserts.  Also,
you need to have enough free space to have a second copy of your largest
index on the table or the CREATE INDEX will fail.

NOTE:  This script DOES NOT DO ANYTHING with permissions because I don't
use them.  If someone would like to give me some SQL code that will return
the old permissions, and set them on the new index, I would be happy to
add this functionality.

Finally.  I am providing this script because I am a nice guy.  If your
machine explodes or any table gets injured by your use of this script I
cannot and will not be held responsible.  I wrote this script for my own
use, and the only time it has ever failed for me was when I had duplicated
values in a UNIQUE index field.  Once I corrected the data corruption, the
script worked correctly.  So, at least at first, I recommend that you look
at the SQL that is generated, and make sure you understand what it is
trying to do BEFORE you use it.

- brian

Wm. Brian McCane                    | Life is full of doors that won't open
Search http://recall.maxbaud.net/   | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"