Re: Problem with VACUUM after very large delete?

On Tue, 2002-12-17 at 17:47, Doug Fields wrote:
> Hello all,
>
> (7.2.1 on Debian Woody 3.0 with various 7.2.3 patches backported)
>
> Earlier today I deleted about 31 million records from one of our tables
> with a very complex query which required a full table scan. This query
> took quite a few hours to run (in fact, overnight).
>
> Later, I did a VACUUM and then an ANALYZE.
>
> However, it does not appear that either:
>
> 1) The records were actually deleted or
>
> 2) The VACUUM is working properly
>
> Why? The VACUUM doesn't seem to be vacuuming the two indexes on the
> table. I'm not sure what's going on, but if you could make any
> suggestions, I'd appreciate it.
>

While vacuum will delete dead index entries, it doesn't collapse out
empty pages in the indexes. This causes indexes to never get smaller
and, in cases where the range of the index continually expands, causes
indexes to grow larger and larger. The generally accepted way to fix
this is via reindexing. (note this was not fixed in 7.3)

Robert Treat