It seems that your problem with toast tables indexes growing due to the
tree not collapsing when the growth is all on one side, and Scott
Marlowe's problem of booleans not collapsing because too many values are
the same are closely related, and very connected. Has anyone with these
problems tried dropping and re-creating the index? This would have some
impact, but no worse than the vacuum, and it would not require downtime,
just may cause poor performance for any hits during the re-build.
Questions for a guru (probably Tom Lane)
1) Are statistics generated at index creation time, or just vacuum
analyze?
2) Does vacuum analyze cause less locking than vacuum full?
3) Can index creation and dropping be isolated in a transaction?
4) Putting those considerations together, what is the best order of doing
things to recover the space (or at least make it reuseable to stop growth)
with the least impact for the users?