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@xxxxxxxxxxxxxxx
