On Sat, May 05, 2007 at 09:52:56PM -0700, Yudhvir Singh Sidhu wrote: > Here is what I think the story is: > a. Large amounts of rows are added to and deleted from a table - daily. > With this much activity, the statistics get out of whack easily. That's > where ANALYZE or VACUUM ANALYZE would help with query speed. You are still confusing ANALYZE and VACUUM. Those are distinct operations, and help for different reasons. Deleting rows leaves "dead rows" -- for various reasons, Postgres can't actually remove them from disk at the DELETE point. VACUUM scans through the disk, searching for dead rows, and actually marks them as removed. This results in faster query times since there will be less data overall to search for. ANALYZE updates the statistics, as mentioned. Yes, by adding or deleting a lot of data, the estimates can get out of whack, leading to bad query plans. > b. If ANALYZE does not have a direct impact on vacuum times, what does? > Meaning what in this EXPLAIN/ANALYZE and Indexing world would have a > direct impact? Improving your vacuum speed is overall not that easy (although there are options you can tweak, and you can of course improve your hardware). The simplest thing to do is simply to vacuum more often, as there will be less work to do each time. It's a bit like cleaning your house -- it might be less work to clean it once a year, but it sure is a better idea in the long run to clean a bit every now and then. :-) /* Steinar */ -- Homepage: http://www.sesse.net/
