Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR

On Mon, Jul 8, 2019 at 9:23 AM John Lumby <johnlumby(at)hotmail(dot)com> wrote:
> Overall, pg-12beta2 yielded a 6.7% reduction in sizes (total pages) of indexes, which was most noticable with the 6 non-unique ones.
> In fact the primary-key index was larger with pg-12.

The avg_leaf_density was actually higher for the primary key index, so
it looks like it really came out slightly ahead on v12. Perhaps you
didn't take deleted_pages into account -- there must be free space
that is reusable by the index that has yet to be reused. It would
probably make sense to subtract that across the board.

> Would you have expected better than 6.7%?

I don't think that a test case that runs VACUUM when there are only
4300 deletions and 4300 insertions is particularly realistic, in
general. You might see a larger difference if there was more churn
between each VACUUM run.

> Although a welcome improvement, I think it is not enough to justify stopping use of setting a lower explicit FILLFACTOR. Which then brings me back to thinking there is a case for the subject of this thread, an automatic way to preserve density.

I don't think that such an option would make much sense. The "waves of
misery" paper is about smoothing out the frequency of page splits
following bulk loading and a CREATE INDEX. It is not about making
splits occur less often. It's well understood that a certain amount of
free space is the overhead of B-Tree indexes, albeit an overhead that
can be avoided in certain specific instances.

> And one question :
> I notice that in some pg-11 release, a new config parameter appeared :
> vacuum_cleanup_index_scale_factor

> I have not researched this at all and nor did I set it to anything for my pg-12beta2 run, but it sounds as though maybe it could be relevant to this kind of workload - Is that so?

You seem to be worried about keeping indexes as small as possible.
vacuum_cleanup_index_scale_factor won't help with that.

--
Peter Geoghegan