Re: Index vacuum improvements

On Wed, 2006-03-29 at 21:48 +0300, Heikki Linnakangas wrote:

> As we know, index vacuum could be sped up significantly if it didn't
> have 
> to lock every page in left to right direction because of the integrity
> issue 
> described in nbtree/README. We could then scan the index in physical 
> order, and AFAICS combine the btbulkdelete and btvacuumcleanup logic
> to 
> just one scan.

First off, we need some good timings that show this effect. I believe
it, but we need some publicly discussable performance test cases to show
the effect and then show how much we've improved upon it, repeatably.

Initially, I'd suggest just trying to improve this situation by
pre-scanning the physical index files into OS filesystem cache (only) -
i.e. dont lock the files at all. That way, all I/O is sequential into
memory and then after that all random I/O will be logical. But it would
*all* need to fit in cache.

We might be able to improve the index FSM allocation algorithm so that
we improve the locality of logically adjacent blocks. That way a larger
than memory index would be able to be read with a limited cache. We
could then replace the full pre-read with just a limited sequential scan
ahead.

Maybe effective_cache_size could be a real parameter after all?

The existing FSM allocation scheme provides this for certain kinds of
tables, but not others.

Best Regards, Simon Riggs