Re: Vacuum

From: Brian McCane <bmccane(at)mccons(dot)net>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Vacuum
Date: 2001-11-29 20:13:13
Message-ID: 20011129134155.W29836-200000@fw.mccons.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-hackers pgsql-novice

On Thu, 29 Nov 2001, Tom Lane wrote:

> bangh <banghe(at)baileylink(dot)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"

Attachment Content-Type Size
fixtable.pl text/plain 3.1 KB

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Jodi Kanter 2001-11-29 20:25:48 Visio
Previous Message bangh 2001-11-29 19:30:32 Re: Vaccum

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2001-11-29 20:31:08 Re: Call for platform testing
Previous Message Joe Conway 2001-11-29 20:07:28 Re: Second call for platform testing

Browse pgsql-novice by date

  From Date Subject
Next Message Horst Herb 2001-11-29 21:51:06 prevent psql script execution from stopping
Previous Message Tom Lane 2001-11-29 18:30:35 Re: Vacuum