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.
> 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.
I assume that for a vacuum that only hit pages indicated in the bitmap,
it would still be necessary to do an index scan to remove the heap
pointers in the index, right?
I have added the last sentence to the TODO entry:* Create a bitmap of pages that need vacuuming Instead of
sequentiallyscanning the entire table, have the background writer or some other process record pages that have expired
rows,then VACUUM can look at just those pages rather than the entire table. In the event of a system crash, the
bitmapwould probably be invalidated. One complexity is that index entries still have to be vacuumed, and doing this
withoutan index scan (by using the heap values to find the index entry) might be slow and unreliable, especially for
user-defined index functions.
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073