> What bothers me about the TODO item is that if we have to sequentially
> scan indexes, are we really gaining much by not having to sequentially
> scan the heap? If the heap is large enough to gain from a bitmap, the
> index is going to be large too. Is disabling per-index cleanout for
> expression indexes the answer?
I guess you're saying that full index scan should only be done when the
index is a functional one, and use index lookup for safe indexes ? That
would be a huge win for most of my vacuum-problematic tables, as I don't
have any functional indexes. But I guess full index scan would still be
faster if the percentage of pages changed is more than some threshold.
On the other hand it would allow very frequent vacuuming even for huge
tables so that situation should not occur. Autovacuum thresholds could
be lowered drastically in that case...
> Anyway, as I remember, if you have a 20gig table, a vacuum / sequential
> scan is painful, but if we have to sequential scan the all indexes, that
> is probably just as painful. If we can't make headway there and we
> can't cleanout indexes without an sequential index scan, I think we
> should just remove the TODO item and give up on improving vacuum
> performance.
>From my POV, there must be a way to speed up vacuums on huge tables and
small percentage of to-be-vacuumed tuples... a 200 million rows table
with frequent updates of the _same_ record is causing me some pain right
now. I would like to have that table vacuumed as often as possible, but
right now it only works to do it once per week due to load problems on
long-running transactions preventing vacuuming other tables.
Cheers,
Csaba.