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

Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> writes:
> On Sun, Jan 19, 2014 at 5:59 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> It strikes me that there may be an obvious way to improve the number
>> further, based on the observation in this thread that nkeep doesn't need
>> to be scaled up because VACUUM should have scanned every page that could
>> contain dead tuples. Namely, that we're arriving at new_rel_tuples by
>> scaling up num_tuples linearly --- but perhaps we should only scale up
>> the live-tuples fraction of that count, not the dead-tuples fraction.
>> By scaling up dead tuples too, we are presumably still overestimating
>> new_rel_tuples somewhat, and the behavior that I'm seeing with this test
>> script seems to confirm that.

> After reading your analysis, first thought occurred to me is that we can
> directly subtract nkeep from num_tuples to account for better scaling
> of live tuples, but I think the scaling routine vac_estimate_reltuples()
> is expecting scanned_tuples and this routine is shared by both
> Analyze and Vacuum where the mechanism to calculate the live
> and dead tuples seems to be bit different, so may be directly passing
> a subtract of num_tuples and nkeep to this routine might create some
> problem. However I think this idea is definitely worth pursuing to
> improve the estimates of live tuples in Vacuum.

Yeah, it seemed like it would require some rethinking of the way
vac_estimate_reltuples() works. It's probably not that hard, but it
looked like it'd require more thought than I wanted to put into it on
a Saturday ;-)

regards, tom lane