On Mon, Nov 4, 2019 at 11:56 AM Justin Pryzby <pry...@telsasoft.com> wrote: > I think it's because some heap pages are being visited many times, due to the > index tuples being badly "fragmented". Note, I'm not talking about > fragmentation of index *pages*, which is what pgstattuple reports (which > wouldn't have nearly so detrimental effect). I could probably say that the > index tuples are badly "correlated" with the heap.
But this is a unique index, and Scott indicates that the problem seems to go away for a while following a REINDEX. > In PG v12 you can use REINDEX CONCURRENTLY (but beware there's a crash > affecting its progress reporting, fix to be included in v12.1). PG v12 will store B-Tree duplicates in heap TID order, so if that's the problem then upgrading to v12 (and REINDEXing if the upgrade was performed using pg_upgrade) will fix it for good. -- Peter Geoghegan
