Re: index growth problem

So I guess any changes that were made to make VACUUM and FSM include
indexes
does not remove the necessity to reindex (as long as we don't want index
sizes to bloat and grow larger than they need be).
Is that correct?

Graham.

Jim C. Nasby wrote:

>On Wed, Oct 18, 2006 at 03:20:19PM -0700, Graham Davis wrote:
>
>
>>I have a question about index growth.
>>
>>The way I understand it, dead tuples in indexes were not reclaimed by
>>VACUUM commands in the past. However, I've read in a few forum posts
>>that this was changed somewhere between 7.4 and 8.0.
>>
>>
>
>There was a change to indexes that made vacuum more effective; I don't
>remember the details off-hand.
>
>
>
>>I'm having an issue where my GIST indexes are growing quite large, and
>>running a VACUUM doesn't appear to remove the dead tuples. For example,
>>if I check out the size an index before running any VACUUM :
>>
>>select pg_relation_size('asset_positions_position_idx');
>>pg_relation_size
>>------------------
>> 11624448
>>(1 row)
>>
>>The size is about 11Mb. If I run a VACUUM command in verbose, I see
>>this about the index:
>>
>>INFO: index "asset_positions_position_idx" now contains 4373 row
>>versions in 68 pages
>>DETAIL: 0 index pages have been deleted, 0 are currently reusable.
>>CPU 0.00s/0.00u sec elapsed 0.16 sec.
>>
>>When I run the same command to find the size after the VACUUM, it hasn't
>>changed. However, if I drop and then recreate this index, the size
>>becomes much smaller (almost half the size):
>>
>>drop index asset_positions_position_idx;
>>DROP INDEX
>>
>>CREATE INDEX asset_positions_position_idx ON asset_positions USING GIST
>>(position GIST_GEOMETRY_OPS);
>>CREATE INDEX
>>
>>select pg_relation_size('asset_positions_position_idx');
>>pg_relation_size
>>------------------
>> 6225920
>>(1 row)
>>
>>Is there something I am missing here, or is the reclaiming of dead
>>tuples for these indexes just not working when I run a VACUUM? Is it
>>suppose to work?
>>
>>
>
>That's not really a useful test to see if VACUUM is working. VACUUM can
>only trim space off the end of a relation (index or table), where by
>'end' I mean the end of the last file for that relation on the
>filesystem. This means it's pretty rare for VACUUM to actually shrink
>files on-disk for tables. This can be even more difficult for indexes (I
>think it's virtually impossible to shrink a B-tree index file).
>
>

--
Graham Davis
Refractions Research Inc.
gdavis(at)refractions(dot)net