Martin Boese <boesemar@xxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
