On Tue, Jul 9, 2019 at 9:04 PM Peter Geoghegan <p...@bowt.ie> wrote: > ISTM that the simplest explanation here is that index fragmentation > (and even index size) is a red herring, and the real issue is that > you're suffering from problems similar to those that are described in > these old threads: > > https://www.postgresql.org/message-id/flat/20160524173914.GA11880%40telsasoft.com > https://www.postgresql.org/message-id/flat/520D6610.8040907%40emulex.com
I can imagine why you found you needed to reduce fillfactor to get much of any benefit from a REINDEX. Page splits are inherently expensive, for one thing. Also, in this specific scenario a succession of page splits might hasten the index returning to having little correlation with the underlying table within each large group of duplicates. Splits on fillfactor 90 pages would make new space available for future insertions on earlier duplicate pages, mixing old and new rows together before long. -- Peter Geoghegan
