Re: Importance of re-index

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Scott Marlowe wrote:
> On Thu, 2006-08-03 at 17:44, John Sidney-Woollett wrote:
>> In addition to making sure databases are vacuumed regularly, it is worth
>> running REINDEX on tables that see a lot of updates (or insert/deletes).
>>
>> Running REINDEX on a regular basis will keep the indexes compacted and
>> can noticeably improve the database performance.
>>
>> The other benefit is that the disk space taken by your database can be
>> significantly reduced.
>>
>> This is barely mentioned in the 7.4 docs, and alluded to in the 8.1 docs.
>>
>> FWIW, in my experience it is DEFINITELY worth reindexing regularly.
>
> But note that reindex is one of those "invasive" commands that may cause
> problems for certain types of 24/7 operations, while vacuum is meant to
> run concurrently almost any time of day. Reindex was originally
> designed to fix broken indexes, and, at least in earlier encarnations,
> should something stop it in the middle of reindexing I believe it is
> possible to be left with no index.
>
> It's got its uses, but it's got its issues as well.

It certainly is simpler to write
REINDEX INDEX foo;
than to write
DROP INDEX foo;
CREATE INDEX foo BLAH BLAH BLAH.

- --
Ron Johnson, Jr.
Jefferson LA USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD4DBQFE0n8hS9HxQb37XmcRAvHZAKCf/Orza4TboRxYiqys/VngHzpKCACY8i1t
suyTDb+FMnTN6jL3fi80bg==
=MGqB
-----END PGP SIGNATURE-----