Thanks very much for the information. It would appear that our best option might be to vacuum analyze these tables in our application at a point in time when they contain rows instead of doing it at night. Needlesst to say, it would nice to have an option to analyze with a target number of rows instead of the number presently in the table. I suppose another option would be to keep a small number of rows permanently in these tables. In my testing, 100 rows (94 to be exact) did the trick. Is this number going to vary from table to table? Thanks again for your help. Mark
________________________________ From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Wed 11/24/2004 1:26 AM To: Mark Dexter Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] VACUUM and ANALYZE With Empty Tables Mark Dexter wrote: > We use a development environment that works with Postgres via ODBC and > uses cursors to insert and update rows in Postgres tables. I'm using > Postgres version 7.4.5. > A. If I TRUNCATE or DELETE all of the rows in the table and then run > VACUUM or ANALYZE on the empty table, the test program takes over 15 > minutes to complete (i.e., 15X performance drop). > If we routinely run VACUUM or VACUUM ANALYZE (e.g., nightly), these work > tables will normally be empty when the VACUUM is run. So it would > appear from the testing above that they will experience performance > problems when inserting large numbers of rows through our application. Yep - it's a known issue. The analyse is doing what you asked, it's just not what you want. > Is there some easy way around this problem? If there a way to force > VACUUM or ANALYZE to optimize for a set number of rows even if the table > is empty when it is run? Thanks for your help. Mark There are only two options I know of: 1. Vaccum analyse each table separately (tedious, I know) 2. Try pg_autovacuum in the contrib/ directory The autovacuum utility monitors activity for you and targets tables when they've seen a certain amount of activity. Even if it hasn't got the tunability you need, it should be a simple patch to add a list of "excluded" tables. -- Richard Huxton Archonet Ltd ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
