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

On Tue, Mar 11, 2014 at 2:59 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> 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.

I checked the vac_estimate_reltuples() function, but not able to find
a proper way to identify the free space.

> 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.

Thanks for information. we can get the table free space by writing some wrapper
or modify a little bit of FreeSpaceMapVacuum() function. This way it
will not add
any extra overhead in identifying the table is almost empty or not.

Regards,
Hari Babu
Fujitsu Australia