Skip site navigation (1) Skip section navigation (2)

Re: Performance degradation, index bloat and planner estimates

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Performance degradation, index bloat and planner estimates
Date: 2010-09-22 04:09:40
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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 
FSM issue.

> 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.

Craig Ringer

Tech-related writing at

In response to


pgsql-performance by date

Next:From: Tom LaneDate: 2010-09-22 04:20:09
Subject: Re: Performance degradation, index bloat and planner estimates
Previous:From: Tom LaneDate: 2010-09-21 23:30:32
Subject: Re: Query much faster with enable_seqscan=0

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group