Michael Fuhr <mike@fuhr.org> writes:
> On Thu, Aug 02, 2007 at 10:40:24PM -0400, Joseph S wrote:
>> 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
Hmm ... that problem would have caused autovac to mostly ignore the
shared tables, but in such a scenario you'd expect the table itself
and both indexes to all be bloated. The thing that struck me about
Joseph's report was that the one index was so much more bloated than
the other. The index entries are only slightly larger (3 OIDs not 2)
so there's no obvious reason for this.
The fact that the indexes are bloated and the table itself not can be
explained by a history of manual VACUUM FULLs, but that should have
had similar effects on both indexes.
We know that vacuum's inability to merge mostly-but-not-entirely-empty
index pages can lead to index bloat given a sufficiently unfriendly
usage pattern, and I think that must be what happened here, but I'm not
clear what that usage pattern is. If we had those details we could
possibly work around it by changing the column ordering in the index
--- AFAIR there isn't any particular reason for
pg_shdepend_depender_index to have one column order rather than another.
regards, tom lane