> -----Original Message-----
> From: owner-pgsql-hackers@postgresql.org
> [mailto:owner-pgsql-hackers@postgresql.org]On Behalf Of Tom Lane
>
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Conclusions:
> > o indexes never get smaller
>
> Which we knew...
>
> > o drop/recreate index is slower than vacuum of indexes
>
> Quite a few people have reported finding the opposite in practice.
> You should probably try vacuuming after deleting or updating some
> fraction of the rows, rather than just the all or none cases.
>
Vacuum after delelting all rows isn't a worst case.
There's no moving in that case and vacuum doesn't need to call
index_insert() corresponding to the moving of heap tuples.
Vacuum after deleting half of rows may be one of the worst case.
In this case,index_delete() is called as many times as 'delete all'
case and expensive index_insert() is called for moved_in tuples.
Regards.
Hiroshi Inoue
Inoue@tpf.co.jp