Re: Performance degradation, index bloat and planner estimates

Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> writes:
> If the bloat issue were with relations rather than indexes I'd suspect
> free space map issues as you're on 8.3.

> http://www.postgresql.org/docs/8.3/interactive/runtime-config-resource.html

> My (poor) understanding is that index-only bloat probably won't be an
> FSM issue.

Lack of FSM space can hurt indexes too, although I agree that if *only*
indexes are bloating then it's probably not FSM to blame.

>> The indexed condition is a state of the evolution of the records in
>> the table: many records assume that state for some time, then move to
>> a different state no more indexed. Is the continuous addition/deletion
>> of records to the index causing the bloat (which can be then
>> considered limited to the indexes with a similar usage pattern)?

> Personally I don't know enough to answer that. I would've expected that
> proper VACUUMing would address any resulting index bloat, but

Maybe the index fencepost problem? If your usage pattern involves
creating many records and then deleting most of them, but leaving behind
a few records that are regularly spaced in the index ordering, then you
can end up with a situation where many index pages have only a few
entries. An example case is creating daily records indexed by date, and
then deleting all but the last-day-of-the-month entries later. You end
up with index pages only about 1/30th full. The index cannot be shrunk
because no page is completely empty, but it contains much unused space
--- which can never get re-used either, if you never insert any new keys
in those key ranges.

If this is the problem then reindexing is the only fix.

regards, tom lane