Michael Fuhr <mike@xxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
