Martin Boese <boesemar(at)gmx(dot)de> wrote:
> The table has only ~1400 rows. A count(*) takes more than 70
> seconds. Other tables are fast as usual.
>
> When this happens I can also see my system's disks are suffering.
> 'systat -vm' shows 100% disk load at ~4MB/sec data rates.
>
> A simple VACUUM does *not* fix it, a VACUUM FULL however does. See
> the textfile attached.
This is almost certainly a result of bloat on this table.
Autovacuum should normally protect you from that, but there are a
few things which can prevent it from doing so, like long-running
transactions or repeated updates against the entire table in a short
time. There has also been a bug found recently which, as I
understand it, can cause autovacuum to become less aggressive over
time, which might possibly contribute to this sort of problem.
You appear to have snipped the portion of the vacuum output which
might have confirmed and quantified the problem. If you get into
this state again, the entire output of this would be informative:
VACUUM VERBOSE public.circuit;
The goal would be to try to prevent the bloat in the first place so
that you don't need to use aggressive maintenance like VACUUM FULL
to recover. Manual vacuums or tweaking the autovacuum parameters
may help. Also, keep an eye out for maintenance releases for 9.0;
there's likely to be a fix coming which will help you with this.
-Kevin