This is the critical point. You have this line:
There were 132969 unused item pointers.
Which says there's 132k or so dead rows in your table. Which means
vacuum / autovacuum isn't keeping up. Did you try and stop the update
several times? Each time it starts then gets killed it creates dead
rows.
Try to run just ANALYZE on the table and then run the
SELECT relpages, reltuples FROM pg_class WHERE relname = 'table'
again. It should report about 20k of pages, i.e. 160MB. That might slow
the things down ;-)
Try doing a vacuum full followed by a reindex OR a cluster on this
table and see if that helps.
Well, maybe the vacuum will fix the problem - have you executed the
query that took 167ms (according to the explain analyze output posted by
you) over a clean table? But I doubt the growth from 6.000 to 20.000
alone might cause degradation from 170ms to several minutes ...
regards
Tomas
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
