I have always been frustrated by the wildly erratic performance of our
postgresql 8 server. We run aprogram that does heavy data importing via a
heuristics-based import program. Sometime records being imported would just
fly by, sometimes they would crawl. The import program imports records from
a flat table and uses heuristics to normalise and dedupe. This is done via a
sequence of updates and inserts bracketed by a start-end transaction.
At a certain checkpoint representing about 1,000,000 rows read and imported,
I ran a vacuum/analyze on all of the tables in the target schema. To my
horror, performance reduced to less than TEN percent of what it was befor
the vacuum/analyze. I thought that turning autovacuum off and doing my own
vacuuming would improve performance, but it seems to be killing it.
I have since turned autovacuum on and am tearing my hair out wathcing the
imported records crawl by. I have tried vacuuming the entire DB as well as
rebuilding indexes. Nothing. Any ideas what could have happened? What is the
right thing to do?
Carlo