John Lumby <johnlumby@hotmail.com> writes:
> Suggestion : it would be useful if REINDEX could , when some new parameter is set , first determine current average
leafpage density in the index to be rebuilt, (e.g. the value of pgstatindex().avg_leaf_density from the
pgstattupleextension ), 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
occurduring the period immediately following a REINDEX done with default FILLFACTOR of 90%. In effect, it lessens
theneed 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