> 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