From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Joseph S <jks(at)selectacast(dot)net> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: index bloat WAS: reindexing pg_shdepend |
Date: | 2007-08-03 14:27:27 |
Message-ID: | 20070803142727.GA14996@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Markus Schiltknecht | 2007-08-03 14:28:36 | Re: invalid page header |
Previous Message | A.M. | 2007-08-03 14:12:27 | timezone + DST handling |