Re: Importance of re-index

From: Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net>
To: pgsql general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Importance of re-index
Date: 2006-08-03 22:56:33
Message-ID: 44D27F21.4060206@cox.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

-----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-----

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2006-08-03 23:03:12 Re: Importance of re-index
Previous Message gustavo halperin 2006-08-03 22:51:19 Create function problem