Re: Excessive rows/tuples seriously degrading query

Hannu Krosing <hannu(at)tm(dot)ee> writes:
> Chadwick, Russell kirjutas L, 13.12.2003 kell 00:40:
>> Can anyone explain why this table which has never had more than a
>> couple rows in it shows > 500k in the query planner even after running
>> vacuum full.

> It can be that there is an idle transaction somewhere that has locked a
> lot of rows (i.e. all your updates have been running inside the same
> transaction for hour or days)

In fact an old open transaction is surely the issue, given that the
VACUUM report shows a huge number of "kept" tuples:

>> INFO: Pages 4773: Changed 1, reaped 767, Empty 0, New 0; Tup 613737: Vac 57620, Keep/VTL 613735/613713, UnUsed 20652, MinLen 52, MaxLen 52; Re-using: Free/Avail. Space 4322596/4322596; EndEmpty/Avail. Pages 0/4773.
>> CPU 9.11s/13.68u sec elapsed 22.94 sec.

"Keep" is the number of tuples that are committed dead but can't be
removed yet because there is some other open transaction that is old
enough that it should be able to see them if it looks.

Apparently the access pattern on this table is constant updates of the
two logical rows, leaving lots and lots of dead versions. You need to
vacuum it more often to keep down the amount of deadwood, and you need
to avoid having very-long-running transactions open when you vacuum.

regards, tom lane