Re: VACUUM/ANALYZE counting of in-doubt tuples

On Sat, 2007-11-17 at 12:27 -0500, Tom Lane wrote:

> I feel fairly comfortable with this analysis for ANALYZE, and the
> patch I posted yesterday can easily be adjusted to accommodate it.
> However, what of VACUUM? As that code stands, every non-removable
> tuple (including RECENTLY_DEAD ones) is counted as live, and the
> dead-tuples count gets reset to zero. That seems clearly bogus.
> But the other-transaction-commits-second hypothesis seems a good bit
> more dubious for VACUUM than it is for ANALYZE.
>
> Should we attempt to adjust VACUUM's accounting as well, or leave it
> for 8.4? For that matter, should adjusting ANALYZE be left for 8.4?
> Thoughts?

Some random thoughts:

Question is, what % of table is recently dead? If its zero, then we have
no problem. Only care if its a substantial number.

By fixing ANALYZE we are now also less likely to run a VACUUM while
executing a large COPY or DELETE.

At the end of VACUUM it takes a lock prior to truncation. If it gets
that lock it knows nobody else is locking table. In that case the
recently dead count should be shown as dead, not live.

If we read the stats before VACUUM starts then we can use the number of
non-HOT updates and deletes made between start and finish as the number
of dead rows when VACUUM completes.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com