On Tue, Jul 9, 2019 at 3:18 PM John Lumby <johnlumby@xxxxxxxxxxx> wrote: > > Or, it could be that range scan performance benefitted from reduced fragmentation, > > > > Yes, I think so. 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 There have been numerous reports from users with problems involving low cardinality indexes that gradually became less correlated with the underlying table over time. At least a couple of these users found that a periodic REINDEX temporarily fixed the problem -- see the first thread for an example. Postgres 12 maintains the heap/table sort order among duplicates by treating heap TID as a tiebreaker column, which may make REINDEXing totally unnecessary for you. It's harder to model this issue because the problem with heap TID order will only be seen when there is at least a moderate amount of churn. -- Peter Geoghegan
