Re: issue log message to suggest VACUUM FULL if a table is nearly empty

On Mon, Mar 10, 2014 at 1:13 PM, Haribabu Kommi
<kommi(dot)haribabu(at)gmail(dot)com> wrote:
> On Mon, Mar 10, 2014 at 4:24 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>> On Mon, Mar 10, 2014 at 5:58 AM, Wang, Jing <jingw(at)fast(dot)au(dot)fujitsu(dot)com> wrote:
>> > Enclosed is the patch to implement the requirement that issue log message to
>> > suggest VACUUM FULL if a table is nearly empty.
>> >
>> > The requirement comes from the Postgresql TODO list.
>> >
>> I think it would be better if we can use some existing stats to issue warning
>> message rather than traversing the FSM for all pages. For example after
>> vacuuming page in lazy_scan_heap(), we update the freespace for page.
>> You can refer below line in lazy_scan_heap().
>> freespace = PageGetHeapFreeSpace(page);
>>
>> Now it might be possible that we might not get freespace info easily as
>> it is not accumulated for previous vacuum's. Incase there is no viable
>> way to get it through vacuum stats, we are already updating fsm after
>> vacuum by FreeSpaceMapVacuum(), where I think it should be possible
>> to get freespace.
>
> yes this way it works without extra penalty. But the problem is how to calculate
> the free space which is left in the skipped pages because of visibility bit.

One way could be by extrapolating (vac_estimate_reltuples) like we do for
some other stats, but not sure if we can get the correct estimates. The
main reason is that if you observe that code path, all the decisions are
mainly done on the basis of vacrelstats. I have not checked in detail if by
using any other stats, this purpose can be achieved, may be once you can
look into it.

By the way have you checked if FreeSpaceMapVacuum() can serve your
purpose, because this call already traverses FSM in depth-first order to
update the freespace. So may be by using this call or wrapper on this
such that it returns total freespace as well apart from updating freespace
can serve the need.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com