On Wed, Jan 10, 2007 at 04:48:42PM -0500, Jeremy Haile wrote: > > BTW, that's the default values for analyze... the defaults for vacuum > > are 2x that. > > Yeah - I was actually more concerned that tables would need to be > analyzed more often than I was about vacuuming too often, so I used > analyze as the example. Since my app is inserting constantly throughout > the day and querying for "recent" data - I want to make sure the query > planner realizes that there are lots of rows with new timestamps on > them. In other words, if I run a query "select * from mytable where > timestamp > '9:00am'" - I want to make sure it hasn't been a day since > the table was analyzed, so the planner thinks there are zero rows > greater than 9:00am today. Well, analyze is pretty cheap. At most it'll read only 30,000 pages, which shouldn't take terribly long on a decent system. So you can be a lot more aggressive with it. > > What's more important > > is to make sure critical tables (such as queue tables) are getting > > vacuumed frequently so that they stay small. > > Is the best way to do that usually to lower the scale factors? Is it > ever a good approach to lower the scale factor to zero and just set the > thresholds to a pure number of rows? (when setting it for a specific > table) The problem is what happens if autovac goes off and starts vacuuming some large table? While that's going on your queue table is sitting there bloating. If you have a separate cronjob to handle the queue table, it'll stay small, especially in 8.2. -- Jim Nasby jim@xxxxxxxxx EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
