Re: Heavily modified big table bloat even in auto vacuum is running - Mailing list pgsql-hackers

From Tom Lane
Subject Re: Heavily modified big table bloat even in auto vacuum is running
Date
Msg-id 4446.1390118074@sss.pgh.pa.us
Whole thread Raw
In response to Re: Heavily modified big table bloat even in auto vacuum is running  (Amit Kapila <amit.kapila16@gmail.com>)
List pgsql-hackers
Amit Kapila <amit.kapila16@gmail.com> writes:
> On Sun, Jan 19, 2014 at 5:59 AM, Tom Lane <tgl@sss.pgh.pa.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



pgsql-hackers by date:

Previous
From: Pavel Stehule
Date:
Subject: Re: array_length(anyarray)
Next
From: David Rowley
Date:
Subject: Re: [PATCH] Negative Transition Aggregate Functions (WIP)