On Aug 30, 2005, at 9:05 AM, Akshay Mathur wrote:
We were having a database in pgsql7.4.2 The database was responding very slowly even after full vacuum analyze (select count(*) from some_table_having_18000_records was taking 18 Sec).
On a 7.4.2 db, there should probably be no index bloat, but there could be. Does REINDEX on your tables help? If not, then VACUUM FULL followed by REINDEX may help. The latter should result in nearly the same as your dump+restore. And you need to run vacuum often enough to keep your tables from bloating. How often that is depends on your update/delete rate.
Also, updating to 8.0 may help.
Vivek Khera, Ph.D.
+1-301-869-4449 x806