On Mon, Nov 4, 2019 at 11:56 AM Justin Pryzby <pryzby@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