Re: Heavily modified big table bloat even in auto vacuum is running

On 22 October 2013 10:15 Amit Kapila wrote:
>>On Mon, Oct 21, 2013 at 10:54 AM, Haribabu kommi <haribabu(dot)kommi(at)huawei(dot)com> wrote:
>>
>> Yes, it's correct. "nkeep" counter have the dead tuples which are recently dead and are not vacuumed. The removal of tuples vacuumed from dead tuples should be the same as "nkeep" counter.
>> So if we remove the nkeep from num_tuples which gives us the proper live tuples. How about following statement at the end scan for all blocks.
>>
>> num_tuples -= nkeep;
>
>Actually what I had in mind was to use nkeep to estimate n_dead_tuples similar to how num_tuples is used to estimate n_live_tuples. I think it will match what Tom had pointed in his response (>>>>What
>>would make more sense to me is for VACUUM to estimate the number
>>>>>of remaining dead tuples somehow and send that in its message.
>>>>>However, since the whole point here is that we aren't accounting for
>>>>>transactions that commit while VACUUM runs, it's not very clear how
>>>>>to do that.)

I changed the patch as passing the "nkeep" counter data as the new dead tuples in the relation to stats like the new_rel_tuples.
The "nkeep" counter is an approximation of dead tuples data of a relation.
Instead of resetting dead tuples stats as zero, used this value to set n_dead_tuples same as n_live_tuples.

Patch is attached in the mail. Please let me know if any changes are required.

Regards,
Hari Babu.