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@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026