Re: index bloat WAS: reindexing pg_shdepend

On Thu, Aug 02, 2007 at 10:40:24PM -0400, Joseph S wrote:
> Tom Lane wrote:
> >Heavy use of temp tables would expand pg_class, pg_type, and especially
> >pg_attribute, but as long as you have a decent vacuuming regimen (do you
> >use autovac?) they shouldn't get out of hand.
>
> I do use autovac.  Like I said they don't get really out of hand, only
> up to 20 megs or so before I noticed that it was weird.  The large
> indexes are what tipped me off that something strange was going on.

Unexpected bloat in pg_shdepend led me to discover a problem with
statistics for shared tables a couple of months ago:

http://archives.postgresql.org/pgsql-hackers/2007-06/msg00190.php
http://archives.postgresql.org/pgsql-hackers/2007-06/msg00245.php

> I only noticed this because I was making an effort to monitor index
> bloat on my regular tables.  It could be there are a lot of people out
> there who are experiencing this but don't notice because 20 megs here
> and there don't cause any noticeable problems.

Anybody making heavy use of temporary tables and relying on autovacuum
is probably suffering bloat in pg_shdepend because no released
version of PostgreSQL has the fix for the statistics bug (it has
been fixed in CVS, however).  As I mention in the second message
above, vacuuming pg_shdepend resulted in an immediate performance
improvement in an application I was investigating.

--
Michael Fuhr