Hannu Krosing <hannu@skype.net> writes:
> My wild guess is that deleting all index pointers for a removed index is
> more-or-less the same cost as creating new ones for inserted/updated
> page.
Only if you are willing to make the removal process recalculate the
index keys from looking at the deleted tuple. This opens up a ton of
gotchas for user-defined index functions, particularly for doing it in
the bgwriter which is not really capable of running transactions.
Removing index entries also requires writing WAL log records, which
is something we probably want to minimize in the bgwriter to avoid
contention issues.
> It is often more agreeable to take a continuous up-to-2X performance hit
> than an unpredictable hit at unknown (or even at a known) time.
Well, you can have that sort of tradeoff today, by running autovacuum
continuously with the right delay parameters.
The only vacuum optimization idea I've heard that makes any sense to me
is the one about keeping a bitmap of changed pages so that vacuum need
not read in pages that have not changed since last time. Everything
else is just shuffling the same work around, and in most cases doing it
less efficiently than we do now and in more performance-critical places.
regards, tom lane