On Mon, Nov 4, 2019 at 3:38 PM Scott Rankin <srankin@motus.com> wrote:
Definitely no long-running transactions on this table;
Any long running transactions at all? The lock on the table is only necessary to explain why the problem would have gone away at the same time as the reindex finished. If there is a long running transaction which doesn't touch this table, it would still cause the problem. It is just that the reindinex would not solve the problem (because the not-entirely-dead-yet tuples would have to be copied into the new index), and with no lock there is no reason for them to be correlated in time, other than sheer dumb luck.
Does another reindex solve the problem again?
> in fact, this table is pretty infrequently updated – on the order of a few tens of rows updated per day.
That would seem to argue against this explanations, but all the others ones too I think. But a few tens of rows per day and a transaction left open for a few tens of days, and you could get enough zombie tuples to add up to trouble. Particularly if there is one row (as defined by prog.id) which is seeing both most of those updates, an most of the index-scan activity.
But now I am curious, if it is a small table and the index scan is going to be invoked 21,956 times in one query, it seems like it should hash it instead. Does it misestimate how often that index scan is going to get invoked? (assuming the index scan is the 2nd child of a nested loop, what is the expected and actual row count of the 1st child of that loop?)