Date: Thu, 29 Nov 2001 14:13:13 -0600 (CST)
From: Brian McCane <bmccane@mccons.net>
To: <pgsql-admin@postgresql.org>
Subject: Re: Vacuum 
In-Reply-To: <19565.1007058635@sss.pgh.pa.us>
Message-ID: <20011129134155.W29836-200000@fw.mccons.net>
MIME-Version: 1.0
Content-Type: MULTIPART/MIXED; BOUNDARY="0-947532855-1007064793=:29836"

  This message is in MIME format.  The first part should be readable text,
  while the remaining parts are likely unreadable without MIME-aware tools.
  Send mail to mime@docserver.cac.washington.edu for more info.

--0-947532855-1007064793=:29836
Content-Type: TEXT/PLAIN; charset=US-ASCII

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"

--0-947532855-1007064793=:29836
Content-Type: TEXT/PLAIN; charset=US-ASCII; name="fixtable.pl"
Content-Transfer-Encoding: BASE64
Content-ID: <20011129141313.B29836@fw.mccons.net>
Content-Description: 
Content-Disposition: attachment; filename="fixtable.pl"

IyEgL3Vzci9iaW4vcGVybA0KDQpyZXF1aXJlICdnZXRvcHRzLnBsJyA7DQoN
Cm15ICRhbGxJbmRleGVzID0gMCA7DQpteSAkYWxsVHJpZ2dlcnMgPSAwIDsN
Cm15ICR0YWJsZSA9ICIiIDsNCm15ICRpbmRleCA9ICIiIDsNCm15ICRub3Ry
ZWFsbHkgPSAwIDsNCkdldG9wdHMoJ0lUdDppOm4nKSA7DQokYWxsSW5kZXhl
cyA9ICRvcHRfSSBpZiAkb3B0X0kgOw0KJGFsbFRyaWdnZXJzID0gJG9wdF9U
IGlmICRvcHRfVCA7DQokdGFibGUgPSAkb3B0X3QgaWYgJG9wdF90IDsNCiRp
bmRleCA9ICRvcHRfaSBpZiAkb3B0X2kgOw0KJG5vdHJlYWxseSA9ICRvcHRf
biBpZiAkb3B0X24gOw0KDQp1c2Ugc3RyaWN0IDsNCg0KbXkgJFVTQUdFID0g
PDxVU0FHRSA7DQokMCAtIFBvc3RncmVzcWwgdGFibGUgdG9vbA0KDQogICQw
IC10IDx0YWJsZT4gWy1pIDxpbmRleD5dIFstSV0gWy1UXSA8ZGF0YWJhc2U+
DQoNCglPcHRpb25zOg0KCQktdCA8dGFibGU+CVRhYmxlIHRvIHdvcmsgb24N
CgkJLWkgPGluZGV4PglJbmRleCB0byByZWJ1aWxkDQoJCS1JCQlSZWJ1aWxk
IGFsbCB0cmlnZ2Vycw0KCQktVAkJRG8gYWxsIHRyaWdnZXJzDQoJWW91IG11
c3QgY2hvb3NlIGF0IGxlYXN0IG9uZSBvZiAtaSwgLUkgYW5kIC1UDQpVU0FH
RQ0KDQpkaWUgJFVTQUdFIGlmICgkdGFibGUgZXEgIiIpIDsNCmRpZSAiWW91
IGNhbiBvbmx5IHVzZSBvbmUgb2YgJy1JJyBhbmQgJy1pJyBvcHRpb25zIg0K
CWlmICgkaW5kZXggbmUgIiIgJiYgJGFsbEluZGV4ZXMpIDsNCmRpZSAkVVNB
R0UgaWYgKCRpbmRleCBlcSAiIiAmJiAhICRhbGxJbmRleGVzICYmICEgJGFs
bFRyaWdnZXJzKSA7DQoNCnVzZSBEQkkgOw0KDQpteSAkZGJuYW1lID0gc2hp
ZnQgQEFSR1YgOw0KJCQkJCQkJCQkIERFTEVURSBUSElTIExJTkUgQU5EIEZJ
WCBORVhUIFRIUkVFIFZBTFVFUyAkJCQkJCQkJCQNCm15ICR1c2VyID0gJ1VT
RVJOQU1FJyA7DQpteSAkcGFzc3dkID0gJ1BBU1NXT1JEJyA7DQpteSAkaG9z
dG5hbWUgPSAnSE9TVE5BTUUnIDsNCg0KbXkgJGRiaCA9IERCSS0+Y29ubmVj
dCgiZGJpOlBnOmRibmFtZT0kZGJuYW1lO2hvc3Q9JGhvc3RuYW1lIiwkdXNl
ciwkcGFzc3dkKSA7DQoNCm15ICVpbmRleGVzIDsNCm15IEB0cmlnZ2VycyA7
DQpteSAldW5pcXVlIDsNCg0Kb3BlbiBQR0RVTVAsICJwZ19kdW1wIC1zIC10
ICR0YWJsZSAkZGJuYW1lIHwiIDsNCndoaWxlICg8UEdEVU1QPikNCnsNCiAg
Y2hvbXAgOw0KICBuZXh0IGlmICEgbS9eQ1JFQVRFLyA7DQoNCiAgaWYgKG0v
XkNSRUFURVxzKyguKilccypJTkRFWFxzKyIoW14iXSopIi4qKFwoLipcKSkv
KQ0KICB7DQogICAgJGluZGV4ZXN7JDJ9ID0gJDMgOw0KICAgICRpbmRleGVz
eyQyID0+ICJVTklRVUUifSA9ICgkMSBuZSAiIikgOw0KDQogICAgbmV4dCA7
DQogIH0NCiAgaWYgKCEgZGVmaW5lZCAkdW5pcXVleyRffSkNCiAgew0KICAg
ICR1bmlxdWV7JF99ID0gMSA7DQogICAgaWYgKG0vXkNSRUFURVxzK0NPTlNU
UkFJTlRccytUUklHR0VSXHMrIihbXiJdKikiLykNCiAgICB7DQogICAgICBw
dXNoIChAdHJpZ2dlcnMsICRfKSA7DQogICAgICBuZXh0IDsNCiAgICB9DQog
IH0NCiAgZWxzZQ0KICB7DQogICAgbmV4dCA7DQogIH0NCn0NCmNsb3NlIFBH
RFVNUCA7DQoNCmlmICgkYWxsVHJpZ2dlcnMpDQp7DQogIG15ICRjbWQgPSAi
U0VMRUNUIG9pZCBmcm9tIHBnX2NsYXNzIHdoZXJlIHJlbG5hbWUgPSAnJHRh
YmxlJyIgOw0KICBteSAkcmVsaWQgOw0KICBpZiAobXkgJHN0aCA9ICRkYmgt
PnByZXBhcmUoJGNtZCkpDQogIHsNCiAgICBpZiAoJHN0aC0+ZXhlY3V0ZSkN
CiAgICB7DQogICAgICBpZiAobXkgJHJvdyA9ICRzdGgtPmZldGNocm93X2Fy
cmF5cmVmKQ0KICAgICAgew0KCSRyZWxpZCA9ICQkcm93WzBdIDsNCiAgICAg
IH0NCiAgICB9DQogIH0NCg0KICAkY21kID0gIlNFTEVDVCB0Z25hbWUgRlJP
TSBwZ190cmlnZ2VyIHdoZXJlIHRncmVsaWQgPSAkcmVsaWQiIDsNCiAgaWYg
KG15ICRzdGggPSAkZGJoLT5wcmVwYXJlKCRjbWQpKQ0KICB7DQogICAgaWYg
KCRzdGgtPmV4ZWN1dGUpDQogICAgew0KICAgICAgd2hpbGUgKG15ICRyb3cg
PSAkc3RoLT5mZXRjaHJvd19hcnJheXJlZikNCiAgICAgIHsNCgkkcmVsaWQg
PSAkJHJvd1swXSA7DQoJcHJpbnQgIkRST1AgVFJJR0dFUiAkJHJvd1swXSBP
TiAkdGFibGU7XG4iIDsNCiAgICAgIH0NCiAgICB9DQogIH0NCg0KICBmb3Ig
bXkgJHRyaWdnZXIgKEB0cmlnZ2VycykNCiAgew0KICAgIHByaW50ICR0cmln
Z2VyIC4gIlxuIiA7DQogIH0NCn0NCg0KZGllICJJbmRleCAkaW5kZXggZG9l
cyBub3QgZXhpc3Qgb24gdGFibGUgJHRhYmxlIg0KICAgICAgaWYgKCRpbmRl
eCBuZSAiIiAmJiAhIGRlZmluZWQgJGluZGV4ZXN7JGluZGV4fSkgOw0KDQpp
ZiAoJGluZGV4KQ0Kew0KICBteSAkY21kID0gIkNSRUFURSIgOw0KICAkY21k
Lj0gIiBVTklRVUUiIGlmICRpbmRleGVzeyRpbmRleCA9PiAiVU5JUVVFIn0g
PT0gMSA7DQogICRjbWQuPSAiIElOREVYICRpbmRleCBPTiAkdGFibGUgJGlu
ZGV4ZXN7JGluZGV4fTsiIDsNCiAgcHJpbnQgIiRjbWRcbiIgOw0KICBteSAk
Y21kID0gIkRST1AgSU5ERVggJGluZGV4OyIgOw0KICBwcmludCAiJGNtZFxu
IiA7DQogICRjbWQgPSAiQUxURVIgVEFCTEUgIi4kaW5kZXguIl9uZXciIDsN
CiAgJGNtZC49ICIgUkVOQU1FIFRPICRpbmRleDsiIDsNCiAgcHJpbnQgIiRj
bWRcbiIgOw0KfQ0KZWxzaWYgKCRhbGxJbmRleGVzKQ0Kew0KICBmb3IgbXkg
JGluZGV4IChrZXlzICVpbmRleGVzKQ0KICB7DQogICAgbmV4dCBpZiAhIGRl
ZmluZWQgJGluZGV4ZXN7JGluZGV4ID0+ICJVTklRVUUifSA7DQoNCiAgICBt
eSAkY21kID0gIkNSRUFURSIgOw0KICAgICRjbWQuPSAiIFVOSVFVRSIgaWYg
JGluZGV4ZXN7JGluZGV4ID0+ICJVTklRVUUifSA9PSAxIDsNCiAgICAkY21k
Lj0gIiBJTkRFWCAiLiRpbmRleC4iX25ldyBPTiAkdGFibGUgJGluZGV4ZXN7
JGluZGV4fTsiIDsNCiAgICBwcmludCAiJGNtZFxuIiA7DQogICAgJGNtZCA9
ICJEUk9QIElOREVYICRpbmRleDsiIDsNCiAgICBwcmludCAiJGNtZFxuIiA7
DQogICAgJGNtZCA9ICJBTFRFUiBUQUJMRSAiLiRpbmRleC4iX25ldyIgOw0K
ICAgICRjbWQuPSAiIFJFTkFNRSBUTyAkaW5kZXg7IiA7DQogICAgcHJpbnQg
IiRjbWRcbiIgOw0KICB9DQp9DQoNCiRkYmgtPmRpc2Nvbm5lY3QgOw0KDQpf
X0VORF9fDQo=

--0-947532855-1007064793=:29836--