The above shows that the indexes contained 10M rows and 160M of dead space each. That means you weren't vacuuming nearly enough.
How is it that a row in the table can grow to a size far exceeding the sum of the maximum sized of the fields it consists of?
13M dead rows, and the table is 1.4M pages, or 11GB. No wonder it's slow.
I had a look at the disk files and they are HUGE indeed.
You need to run a vacuum full, and then you need to vacuum far more often. If you're running 8.1, turn on autovacuum and cut each default scale factor in half, to 0.2 and 0.1.
I'm not runing 8.1. Is there a way of doing this in 8.0 or do I need to write a shell script + cron job?
Regards, Bealach
