RE: [HACKERS] Index recreation in vacuum

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman(at)candle(dot)pha(dot)pa(dot)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(at)tpf(dot)co(dot)jp