Re: autovacumm not working ?

Tomasz Rakowski <mourawi(at)yahoo(dot)com> writes:
> The strange thing is that number of pages allocated for "t_ais_position" table and "t_ais_position_pkey" index haven't changed
> (so autovacuum works ok on them) , but the number of pages allocated to "ix_t_ais_position_update_time" index increased
> from 250 to 2218 (x 9 times).

> "ix_t_ais_position_update_time" index is created as "create index ix_t_ais_position_update_time on t_ais_position(update_time)", so it is suppose to be updated very frequently (as main table). The other index "t_ais_position_pkey" is on primary key and values doesn't change at all ....

I think you may be running into one of the usage patterns that btree
vacuuming doesn't clean up very well. Do you initially create a lot of
rows with nearby update_times, and eventually remove all but a few?
The worst case is where you're leaving only one live row per index page.
The current VACUUM code can only recycle index pages that have become
totally empty ...

regards, tom lane