On K, 2005-08-31 at 12:23 -0400, Tom Lane wrote:
> 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.
Would it be OK in non-functional index case ?
> Removing index entries also requires writing WAL log records, which
> is something we probably want to minimize in the bgwriter to avoid
> contention issues.
but the WAL log records have to be written at some point anyway, so this
should not increase the general load.
> > 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.
Not really, I was aiming at the case where the old and new *index*
entries are also on the same page (quite likely after an update of a
non-index field, or only one of the indexed fields). I this case we are
possibly shuffling around the CPU work, but we have a good chance of
avoiding I/O work. This is similar to placing the updated heap tuple on
the same page as old one to avoid extra page writes.
Another interesting idea is to have a counter in heap tuple for "index
entries pointing to this tuple", so that instead of setting the too-old-
to-be-visible bit, we could just remove the index entry, and decrease
that counter, and remove the counter when it's zero.
--
Hannu Krosing <hannu@skype.net>