Re: [HACKERS] Index recreation in vacuum

[Charset iso-8859-1 unsupported, filtering to ASCII...]
> Hi all,
>
> I'm trying to implement REINDEX command.
>
> REINDEX operation itself is available everywhere and
> I've thought about applying it to VACUUM.

That is a good idea. Vacuuming of indexes can be very slow.

> .
> My plan is as follows.
>
> Add a new option to force index recreation in vacuum
> and if index recreation is specified.

Couldn't we auto-recreate indexes based on the number of tuples moved by
vacuum, or do we update indexes as we move them?

>
> 1) invalidate all indexes of the target table
> 2) vacuum the target table(heap table only)
> 3) internal commit and truncation
> 4) recreate and validate all indexes of the table.
>
> The problem is how to invalidate/validate indexes.
> Of cource natural way is to drop/create indexes but the
> definition of indexes would be lost in case of abort/crash.

My idea would be to create a new index that is a random index name.
Then, do rename(), which is an atomic OS operation putting the new index
file in place of the old name. Seems that would work well.

> Now I'm inclined to use relhasindex of pg_class to
> validate/invalidate indexes of a table at once.

There are a few calls to CatalogIndexInsert() that know the system table they
are using and know it has indexes, so it does not check that field. You
could add cases for that.

--
Bruce Momjian | http://www.op.net/~candle
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026