Message-ID: <4E89EBBA.2070100@silverback.com>
Date: Mon, 3 Oct 2011 13:07:06 -0400
From: Kevin Ricords <kevin@silverback.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:6.0.2) Gecko/20110902 Thunderbird/6.0.2
MIME-Version: 1.0
To: <pgsql-admin@postgresql.org>
Subject: index bloat on partial index 8.4
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
Content-Transfer-Encoding: 7bit

Per documentation 
(http://www.postgresql.org/docs/8.4/static/routine-reindex.html):
In PostgreSQL 7.4 and later, index pages that have become completely 
empty are reclaimed for re-use. There is still a possibility for 
inefficient use of space: if all but a few index keys on a page have 
been deleted, the page remains allocated. So a usage pattern in which 
all but a few keys in each range are eventually deleted will see poor 
use of space. For such usage patterns, periodic reindexing is recommended.

My implementation is a 150 million row table with a partial index on 
newly created rows, where every row will be updated to not match the 
index condition every few minutes.
The index size appears to grow proportional to the number of rows added 
to the table, but doesn't shrink when rows are updated to no longer meet 
the partial index condition.

 >select relname, indexrelname, pg_relation_size(indexrelid) from 
pg_stat_user_indexes where relname = 't';
"t";"t_partial_idx";58064896

 >select count(1) from t where t_summarized=false;
34

Even if the 34 rows were each in different pages with a deep index 
structure, the index size is not justified, if I understand the 
documentation correctly.  I'd guess that 'completely empty' refers to 
whether the row exists, rather than whether the row belongs in the index.

Any ideas on this pattern of index bloat other than reindex/rebuilding 
periodically?  Has this been addressed in a newer release?

-Kevin