On Sat, 27 Aug 2005 21:28:57 +0530 (IST) <[EMAIL PROTECTED]> threw this fish to the penguins:
> Hello Friends, > We were having a database in pgsql7.4. The database was responding very > slowly even after full vacuum (select > count(*) from some_table_having_18000_records was taking 18 Sec). One comment here: "select count(*)" may seem like a good benchmark, but it's not generally. If your application really depends on this number, fine. Otherwise, you should measure performance with a real query from your application. The "select count(*)" can be very slow because it does not use indexes. > We took a backup of that db and restored it back. Now the same db on > same PC is responding fast (same query is taking 18 ms). This sounds like some index is getting gooped up. If you do a lot of deleting from tables, your indexes can collect dead space that vacuum can not reclaim. Try in sql "reindex table my_slow_table" for a suspect table. In the contrib directory of the postgresql distribution there is a script called "reindexdb". You can run this to reindex your whole database. I also wonder about file system slowdowns. What hardware/OS/filesystem are you using? -- George -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL) ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
