Thread: Importance of re-index
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. John
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.
-----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-----
Scott Marlowe <smarlowe@g2switchworks.com> writes:
> 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.
That was once true but these days reindex is perfectly crash-safe. The
only case where it's not is where you want to reindex a shared catalog's
index (eg one of pg_database's), and we don't let you do that in
multiuser mode anyway.
regards, tom lane
On Thu, 2006-08-03 at 18:03, Tom Lane wrote: > Scott Marlowe <smarlowe@g2switchworks.com> writes: > > 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. > > That was once true but these days reindex is perfectly crash-safe. The > only case where it's not is where you want to reindex a shared catalog's > index (eg one of pg_database's), and we don't let you do that in > multiuser mode anyway. Oh cool! nice to know that's since been fixed.
On Thu, Aug 03, 2006 at 05:46:44PM -0500, 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. And if you're vacuuming frequently enough, there shouldn't be that much need to reindex. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Jim C. Nasby wrote: > On Thu, Aug 03, 2006 at 05:46:44PM -0500, Scott Marlowe wrote: >> On Thu, 2006-08-03 at 17:44, John Sidney-Woollett wrote: [snip] > And if you're vacuuming frequently enough, there shouldn't be > that much need to reindex. How aggressively does PostgreSQL keep b-trees in balance? Inserting the range [1..10000000] should result in a right- unbalanced tree. - -- 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 iD8DBQFE2nZQS9HxQb37XmcRAh4jAJ0bCnk4GOxIt9gUZh9hujDBi/PjwwCfUndS EB9GruGxVJ9Ja0avpurTKwA= =g+1r -----END PGP SIGNATURE-----
Disagree. We only apply reindex on tables that see lots of updates... With our 7.4.x databases we vacuum each day, but we see real performance gains after re-indexing too - we see lower load averages and no decrease in responsiveness over time. Plus we have the benefit of reduced disk space usage. I think that the two things go hand in hand, although vacuum is the most important. John Jim C. Nasby wrote: > And if you're vacuuming frequently enough, there shouldn't be that much > need to reindex.
On Aug 10, 2006, at 1:57 AM, John Sidney-Woollett wrote: > Disagree. > > We only apply reindex on tables that see lots of updates... > > With our 7.4.x databases we vacuum each day, but we see real > performance gains after re-indexing too - we see lower load > averages and no decrease in responsiveness over time. Plus we have > the benefit of reduced disk space usage. You may be getting temporary performance gains by shrinking the indexes to a level that's un-sustainable. As you update the table, it needs to create new index keys, which have to go somewhere. Also, if I had a dollar for everytime someone thought they were safe from bloat because they were vacuuming once a day, I'd be living on a beach somewhere. There's very few databases I've seen where vacuuming once a day is sufficient, so it's very likely that you are suffering fromm bloat. > I think that the two things go hand in hand, although vacuum is the > most important. > > John > > Jim C. Nasby wrote: >> And if you're vacuuming frequently enough, there shouldn't be that >> much >> need to reindex. > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
2006/8/10, Ron Johnson <ron.l.johnson@cox.net>: > How aggressively does PostgreSQL keep b-trees in balance? > > Inserting the range [1..10000000] should result in a right- > unbalanced tree. Are you talking about a tree that is unbalanced regarding its height (ie, has some leaves that are further away from the root than others), or regarding the space occupation within the leafs (ie, some leaves are almost empty, while others are not)? In the former case I think you should read up some on B+trees, since they are by design always perfectly height-balanced. Note that the B does not stand for "binary". In the latter: It won't, because the splitting mechanism will never result in an almost-empty leaf. That can only be caused by deletions. greetings, Nicolas -- Nicolas Barbier http://www.gnu.org/philosophy/no-word-attachments.html