In response to "Carlo Stonebanks" <stonec.register@xxxxxxxxxxxx>: > >> indexes. I don't know whether autovacuum will also analyze tables > >> for you automagically, but it would be a good idea to analyze the table > > > > It does. > > So, I have checked my log and I see an autovacuum running once every minute > on our various databases being hosted on the server - once every minute! > > From what I can see, autovacuum is hitting the db's in question about once > every five minutes. Does this imply an ANALYZE is being done automatically > that would meet the requirements we are talking about here? Is there any > benefit ot explicitly performing an ANALYZE? > > (Or does this go hand-in-and with turning off autovacuum...?) It's only checking to see if vacuum/analyze needs done every 5 minutes. It may or may not do any actual work at that time, based on how much the tables have changed. See: http://www.postgresql.org/docs/8.1/interactive/maintenance.html#AUTOVACUUM This is a case, during your bulk loads, where autovacuum might actually hurt you. How many records are you inserting/updating in 5 minutes? You may be exceeding autovacuum's ability to keep things clean. I can't say for sure, but I would suspect that you'd be better off not using autovacuum until after the initial data loads are done. My guess is that you'll get better performance if you disable autovac and write manual vacuum/analyze into your load scripts. Exactly how often to have your script do it is something that will require testing to figure out, but probably starting with every 100 or so, then adjust it up and down and see what works best. Explicitly performing a vacuum or analyze can be very beneficial, especially if you know what kind of changes your creating in the data. (Now that I think of it, there's no reason to disable autovac, as it will notice if you've just manually vacuumed a table and not do it again.) If you know that you're radically changing the kind of data in a table, manually running analyze is a good idea. If you know that you're creating a lot of dead tuples, manually vacuuming is a good idea. Especially during a big data load where these changes might be taking place faster than autovac notices. -- Bill Moran Collaborative Fusion Inc.
