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

John Lumby <johnlumby(at)hotmail(dot)com> writes:
> Suggestion : it would be useful if REINDEX could ,   when some new parameter is set , first determine current average leaf page density in the index to be rebuilt,    (e.g. the value of pgstatindex().avg_leaf_density from the   pgstattuple extension ),  and then adopt this density as the temporary override FILLFACTOR while rebuilding index pages,  as to to minimize change in density.

Um ... why bother reindexing at all, then?

> This would avoid the saw-tooth effect on number of pages,   and also reduce the number of index page-splits which occur during the period immediately following a REINDEX done with default FILLFACTOR of 90%.   In effect,  it lessens the need for the physical reorganization aspect of REINDEX and focusses more on the function of removing dead  keys.

I think you've confused REINDEX with VACUUM. It seems like a pretty poor
substitute for that --- it's much more expensive and has worse locking
requirements.

regards, tom lane