Re: Index vacuum improvements

On Wed, 29 Mar 2006, Tom Lane wrote:

> Heikki Linnakangas <hlinnaka(at)iki(dot)fi> writes:
>> 1. Instead of stopping on the first matching tuple, scan the whole index
>> page for all matching entries at once.
>
> That loses the ability to reflect tuple deadness back into LP_DELETE
> flags, no? Which is a problem already for bitmap indexscans, but I
> don't wish to give it up for regular indexscans too. With a solution
> for that it might be workable, but I don't see what we do about that.

At first glance, it doesn't look so hard. index_getmulti could mark
those tids that are dead, and btgetmulti would rescan the index page and
set LP_DELETE on all tuples that are still there.

We don't have to care about splits; if the index tuple is no longer where
it used to be, just ignore it. Right, no?

>> 2. Alternatively, the index scan could store the location of the last
>> known non-deletable tuple it has encountered, in addition to the tuple it
>> stops on. When a stopped scan continues, it checks if the tuple it was
>> stopped on is still on the same page. If it's not, instead of moving
>> right to find it, relocate the last known non-deletable tuple and
>> continue the scan from there. There can't be any visible tuples between
>> the tuple it stopped on and the last known non-deletable tuple, because
>> we would have encountered it before, and would know by now that it's
>> non-deletable.
>
> This one appears to be assuming MVCC visibility semantics, which means
> it will break system catalog operations, and probably foreign-key checks
> too.

True. Of course, we could special-case system catalogs. I don't know about
foreign keys, though. I've never looked at how it works.

- Heikki