More performance questions

Vivek,
Thanks for your kind reply.

VIVEK> Do you vacuum analyze or just vacuum?

We do vacuum verbose analyze. This shows you what it’s doing - it also
shows you that it deletes obsolete tuples (removes old data).

VIVEK> Have you ever run a “vaccum full” to clear out old data?
No, there doesn’t seem to be such a command in postgres 7.1.2. But vacuum
analyze is supposed to do that.

VIVEK> Did you tune postgres from the defaults? The defaults are pretty
damned lame for anything but toy DBs.

Kind of. I increased the shared bufferes and the sort memory. Maybe you can
e-mail me your postgresql.conf file (or whatever optimizations you like to
make) I would appreciate it.

VIVEK> One more thing to try after a vacuum full is to reindex all the
VIVEK> indices. At the psql prompt when connected to the rt2 database, type
VIVEK> “\di” to list them, then run “reindex index XXX” for each one listed.

VIVEK> When you have a lot of churn in your database the indexes tend to
VIVEK> grow. This is being worked on for the next release of PG, I hear.

I did this, but it didn’t make any difference. I also believe (but am not
positive) that a vacuum verbose analyze will do this as well.

Any other ideas?

Thanks!
Nick
Nicolae P. Costescu, Ph.D. / Senior Developer
Stronghold Technologies
46040 Center Oak Plaza, Suite 160 / Sterling, Va 20166
Tel: 571-434-1472 / Fax: 571-434-1478