Re: pgstattuple extension for indexes

Satoshi Nagayasu <nagayasus(at)nttdata(dot)co(dot)jp> wrote:

> > I hope you to write how to interpret the framgentation (and other) info
> > in README. In my understanding, I'll write "You'd better do REINDEX when
> > you see the fragmentation is greater than 50%" under the present
> > calculation method.
>
> I can't understand why you want to make such decision, because you're
> thinking the fragmentation information is not the most important for
> the users, aren't you?

Suppose a simple update case, for example, the accounts table in pgbench.
The default fillfactor of btree indexes is 90%, so the leaf pages are
fully split after we update 10-20% of tuples. But pgstatindex reports
the fragmentation is 50% in such condition, but I think we should do
REINDEX then. My decision came from this.

The setting fillfactor=50% is better than the case with high fillfactor
but all pages have split once, even if sizes of the indexes are same.
I worry that users will misunderstand the 50% of fragmentation -- if the
report says 100%, they'll consider to do REINDEX. But 50%, the necessity
is unclear.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center