RE: [HACKERS] Index recreation in vacuum

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> 
> [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,

Yes,we could probably do it. But I'm not sure the availability of new
vacuum.

New vacuum would give us a big advantage that
1) Much faster than current if vacuum remove/moves many tuples.
2) Does shrink index files

But in case of abort/crash
1) couldn't choose index scan for the table
2) unique constraints of the table would be lost

I don't know how people estimate this disadvantage. 
> 
> > 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.
>

I think there aren't so many places to check.
I would examine it if my idea is OK.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp