Re: Huge shared hit for small table

On Mon, Nov 4, 2019 at 3:38 PM Scott Rankin <srankin(at)motus(dot)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?)

Cheers,

Jeff

>