On Tue, Mar 11, 2014 at 2:59 PM, Amit Kapila <amit.kapil...@gmail.com> wrote: > On Mon, Mar 10, 2014 at 1:13 PM, Haribabu Kommi > <kommi.harib...@gmail.com> wrote: >> On Mon, Mar 10, 2014 at 4:24 PM, Amit Kapila <amit.kapil...@gmail.com> wrote: >>> On Mon, Mar 10, 2014 at 5:58 AM, Wang, Jing <ji...@fast.au.fujitsu.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
