On Mon, 2007-09-24 at 10:02 +0100, Heikki Linnakangas wrote: > Alvaro Herrera wrote: > > Log Message: > > ----------- > > Reduce the size of memory allocations by lazy vacuum when processing a small > > table, by allocating just enough for a hardcoded number of dead tuples per > > page. The current estimate is 200 dead tuples per page. > > 200 sounds like a badly chosen value. With a 8k block size, that's a bit > less than MaxHeapTuplesPerPage, which means that in the worst case you > don't allocate enough space to hold all dead tuples, and you end up > doing 2 index cleanups, no matter how large you set > maintenance_work_mem.
Agreed. Tables with 2-4 columns easily fall into that category. Assoication tables are often like this and can have 2 indexes on them. > Note that as the patch stands, the capping is not limited to small > tables. Doing extra index passes on a relatively big table with lots of > indexes might be cause a lot of real extra I/O. > > How about just using MaxHeapTuplesPerPage? With the default 8K block > size, it's not that much more than 200, but makes the above gripes > completely go away. That seems like the safest option at this point. It would be much better to use a value for each table. Any constant value will be sub-optimal in many cases. Let's use our knowledge of the table to calculate a sensible value. We often have average row length available from last VACUUM, don't we? Use that, plus 10%. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
