On 20/09/2010 7:59 PM, Daniele Varrazzo wrote:
> Does anybody have some information about where the bloat is coming
> from and what is the best way to get rid of it? Would a vacuum full
> fix this kind of problem? Is there a way to fix it without taking the
> system offline?
It's hard to know where the index bloat comes from. The usual cause I
see reported here is with regular VACUUM FULL use, which doesn't seem to
be a factor in your case.
A VACUUM FULL will not address index bloat; it's more likely to add to
it. You'd want to use CLUSTER instead, but that'll still require an
exclusive lock that takes the table offline for some time. Your current
solution - a concurrent reindex - is your best bet for a workaround
until you find out what's causing the bloat.
If the bloat issue were with relations rather than indexes I'd suspect
free space map issues as you're on 8.3.
My (poor) understanding is that index-only bloat probably won't be an
> 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
> Any idea of where the 20M record estimate is coming from? Isn't the
> size of the partial index taken into account in the estimate?
I'd really help to have EXPLAIN ANALYZE output here.
Tech-related writing at http://soapyfrogs.blogspot.com/
In response to
pgsql-performance by date
|Next:||From: Tom Lane||Date: 2010-09-22 04:20:09|
|Subject: Re: Performance degradation, index bloat and planner estimates |
|Previous:||From: Tom Lane||Date: 2010-09-21 23:30:32|
|Subject: Re: Query much faster with enable_seqscan=0 |