Brian Cox <brian.cox@ca.com> writes:
> I have a largish (pg_dump output is 4G) database. The query:
> select count(*) from some-table
> was taking 120 secs to report that there were 151,000+ rows.
> This seemed very slow. This db gets vacuum'd regularly (at least once
> per day). I also did a manual 'vacuum analyze', but after it completed,
> the query ran no faster. However, after dumping the database and
> recreating it from the backup, the same query takes 2 secs.
> Why the dramatic decrease?
Presumably, the table was really bloated (lots of unused space).
> Would 'vacuum full' have achieved the
> same performance improvements?
It would've compacted the table all right, but probably left the indexes
worse off.
> Is there anything else that needs to be done
> regularly to prevent this performance degradation?
I suspect that your FSM settings are too low, causing free space found
by VACUUM to be forgotten about. You might also need to consider
vacuuming more than once a day (there's a tradeoff between how often
you vacuum and how much FSM space you need).
regards, tom lane
