From: Kevin Ricords <kevin@silverback.com>
To: pgsql-admin@postgresql.org
Subject: index bloat on partial index 8.4
Date: Mon, 3 Oct 2011 14:12:19 -0400
Message-ID: <4E89FB03.8010407@silverback.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: kevin@silverback.com
Subject: Re: index bloat on partial index 8.4
In-Reply-To: <4E89FB03.8010407@silverback.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox