REINDEX : new parameter to preserve current average leaf density asnew implicit FILLFACTOR
От | John Lumby |
---|---|
Тема | REINDEX : new parameter to preserve current average leaf density asnew implicit FILLFACTOR |
Дата | |
Msg-id | DM6PR06MB5562CE0EB876CD6C30430D36A3E30@DM6PR06MB5562.namprd06.prod.outlook.com обсуждение исходный текст |
Ответы |
Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR
Re: REINDEX : new parameter to preserve current average leaf densityas new implicit FILLFACTOR |
Список | pgsql-general |
Background : For some workloads involving high volume of INSERT/UPDATE/DELETE, It is sometimes beneficial to schedule regular REINDEX of high-activity indexes, so as to improve performance, or restore performance levels backto what it was earlier, by removing dead keys etc. This can result in the average page density of these indexesfluctuating up and down in a saw-tooth fashion, REINDEX causing large increase in density (large drop in total numberof pages) and the workload gradually decreasing density back to some "steady-state". 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. This would avoid the saw-tooth effect on number of pages, and also reduce the number of index page-splits which occur duringthe period immediately following a REINDEX done with default FILLFACTOR of 90%. In effect, it lessens the need forthe physical reorganization aspect of REINDEX and focusses more on the function of removing dead keys. An admin do this for themselves by monitoring index page density and setting the FILLFACTOR to the current density beforeeach REINDEX (and may find that this doesn't change much if the workload is truly steady-state), but I wonder ifthis community would agree that it would provide a useful automation of the process. Cheers, John Lumby
В списке pgsql-general по дате отправления: