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: 4C998184.2070402@postnewspapers.com.au (view raw or flat)
Thread:
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.

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.

> 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 http://soapyfrogs.blogspot.com/

In response to

Responses

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-2014 The PostgreSQL Global Development Group