Improve bloat calculation

[phinjensen]

ID: 21 Version: unspecified Date: 2009-12-03 12:57 EST Author: Greg Sabino Mullane ([email protected])

---

The current bloat calculation is very rough and fails to account for many things. Make it more accurate.

[phinjensen]

Author: Martin von Oertzen ([email protected]) Date:2011-06-16 08:50:20 EDT

---

I can confirm, that pg_stattuple reports 1GB less wasted size.

$ ~/check_postgres.pl --action=bloat --db=edarchive --warning='200 MB' -u $USER
POSTGRES_BLOAT WARNING: DB "edarchive" (db edarchive) table public.a10
rows:23984772 pages:3482352 shouldbe:3334154 (1.0X) wasted size:1214038016 (1
GB) | public.a10=1214038016B public.searchword_a105_f6=174563328B
public.searchword_a193_f6=95649792B public.searchword_a556_f6=90832896B
a10_f7_index_new=0B a10_index_station=0B a10_mf_index_new=0B a10_pkey=0B
a10_snj_index_new=0B searchword_a105_f6_index2=0B searchword_a105_f6_pkey=0B
searchword_a193_f6_index2=0B searchword_a193_f6_pkey=0B
searchword_a556_f6_pkey=0B

I wonder why --perflimit hides searchword_a556_f6:

$ ~/check_postgres.pl --action=bloat --db=edarchive --perflimit=3
--warning='200 MB' -u $USER
POSTGRES_BLOAT WARNING: DB "edarchive" (db edarchive) table public.a10
rows:23984772 pages:3482352 shouldbe:3334154 (1.0X) wasted size:1214038016 (1
GB) | public.a10=1214038016B a10_f7_index_new=0B a10_mf_index_new=0B

With --exclude I find more hidden tables:

$ ~/check_postgres.pl --action=bloat --db=edarchive --exclude=a10 --perflimit=5
--warning='200 MB' -u $USER
POSTGRES_BLOAT OK: DB "edarchive" (db edarchive) table
public.searchword_a105_f6 rows:30177624 pages:346277 shouldbe:324968 (1.1X)
wasted size:174563328 (166 MB) | public.searchword_a105_f6=174563328B
public.searchword_a193_f6=95649792B public.searchword_a556_f6=90832896B
public.a634=86278144B public.searchword_a106_f6=59932672B  

With --exclude=a10 and without --perflimit the output is unusable (maybe 10.000 tables).

I used --version 2.17.0 and 2.17.1 with the same repeatable results.