Matthew T. O'Connor wrote: > Alvaro Herrera wrote: > >Jim C. Nasby wrote: > >>FWIW, I normally go with the 8.2 defaults, though I could see dropping > >>vacuum_scale_factor down to 0.1 or 0.15. I also think the thresholds > >>could be decreased further, maybe divide by 10. > > > >How about pushing thresholds all the way down to 0? > > As long as it handles small (or zero row) tables ok then yes. The > base_threshold in the originial contrib autovacuum was just an easy way > to not vacuum really small tables too often. If a table has only 10 > rows, it's going to get vacuumed every time one row is updated. I guess > that's not a big problem with a table that small but still seems excessive.
Well, if a table has 10 rows, and we keep the current threshold of 1000 rows, then this table must have 1002 dead tuples (99% dead tuples, 1002 dead + 10 live) before being vacuumed. This seems wasteful because there are 500 dead tuples on it and only 10 live tuples. So each scan must wade through all the dead tuples. Another small table with 100 tuples will be vacuumed on every iteration as well, even if there are just two dead tuples. So you are right -- maybe dropping it all the way to 0 is too much. But a small value of 10 is reasonable? That will make the 10 tuple table be vacuumed when there are 10 dead tuples (50% of dead tuples), and the 100 tuple table when there are 11 (11% of dead tuples). It decreases quickly to the scale factor (2%, or do we want to decrease it to 1%?) Does this sound acceptable? -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J "I am amazed at [the pgsql-sql] mailing list for the wonderful support, and lack of hesitasion in answering a lost soul's question, I just wished the rest of the mailing list could be like this." (Fotis) (http://archives.postgresql.org/pgsql-sql/2006-06/msg00265.php) ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match