On 9/18/07, Tom Lane <[EMAIL PROTECTED]> wrote: > > I wrote: > > * The patch makes undocumented changes that cause autovacuum's decisions > > to be driven by total estimated dead space rather than total number of > > dead tuples. Do we like this? > > No one seems to have picked up on this point, but after reflection > I think there's actually a pretty big problem here. Per-page pruning > is perfectly capable of keeping dead space in check. In a system with > HOT running well, the reasons to vacuum a table will be: > > 1. Remove dead index entries. > 2. Remove LP_DEAD line pointers. > 3. Truncate off no-longer-used end pages. > 4. Transfer knowledge about free space into FSM. > > Pruning cannot accomplish #1, #2, or #3, and without significant changes > in the FSM infrastructure it has no hope about #4 either.
I guess we already have mechanism to remove dead index entries outside vacuum. So my take would be handle vacuum based on dead index entries separately. May be we can track number of dead index entries and trigger vacuum on the base relation if it goes beyond a threshold. For LP_DEAD line pointers, with some adjustments to the patch, we can make it track dead space in a page by accounting for the the LP_DEAD pointers. So if there is a bloat because of LP_DEAD pointers, that will be reflected in the dead space and help us trigger vacuum on the table. > I'm inclined to think that we should continue to drive autovac off a > count of dead rows If we do that, I guess it would make sense to count only non-HOT dead tuples because HOT tuples neither create LP_DEAD line pointers nor cause index bloats. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com