PostgreSQL bloat report

bloat.sql

with v as (select version() as ver),
constants(relkind, block_size, header_size, max_align, fill_factor, nonleaf_fill_factor, page_overhead, row_overhead) as (
    values (
        'r',
        current_setting('block_size'),
        CASE WHEN SUBSTRING((select ver from v),12,3) IN ('8.0','8.1','8.2') then 28 else 24 end,
        CASE WHEN (select ver from v) ~ 'mingw32|64' THEN 8 ELSE 4 end,
        1.0,
        null,
        32,
        4
    ), (
        'i',
        current_setting('block_size'),
        12,
        CASE WHEN (select ver from v) ~ 'mingw32|64' THEN 8 ELSE 4 end,
        0.9,
        0.7,
        32,
        0
    )
)
select
    relid AS "Id",
    relid::regclass::text AS "Name",
    relkind AS "Kind",
    reltuples::BIGINT AS "Rows",
    relpages AS "Current Pages",
    relpages::BIGINT * 8192 as "Current Size",
    expected_pages::BIGINT AS "Expected Pages",
    expected_pages::BIGINT * 8192 AS "Expected Size",
    ((relpages - expected_pages) * 8192)::bigint AS "Wasted Space",
    round(relpages/(expected_pages::numeric + 1), 1) AS "Bloat Factor"
from (
    select relid, relkind, relpages, reltuples, tuples_per_page, tuples_per_nonleaf_page, datawidth,
        case when relkind = 'r' then
            ceil(reltuples / tuples_per_page)
        else
            ceil(reltuples / tuples_per_page) + ceil(reltuples / tuples_per_page / tuples_per_nonleaf_page / (1 - 1.0/tuples_per_nonleaf_page)) + ceil(log(reltuples + 1)/log(tuples_per_nonleaf_page)) + 1
        end as expected_pages
    from (
        select
            relid, relkind, relpages, reltuples, datawidth,
            floor((8192 - page_overhead) * fill_factor / (row_overhead + datawidth + header_size)) /* - (relkind = 'i')::integer */ as tuples_per_page,
            floor((8192 - page_overhead) * nonleaf_fill_factor / (row_overhead + datawidth + header_size)) /* - (relkind = 'i')::integer */ as tuples_per_nonleaf_page
        from constants join (
            select
                relid, relkind, relpages, reltuples,
                (ceil(datawidth) + max_align - 1)::integer / max_align * max_align as datawidth
            from constants join pg_class using (relkind) join (
                    select starelid as relid,
                        SUM((1 - stanullfrac) * stawidth) AS datawidth,
                        MAX(stanullfrac) AS maxfracsum,
                        1 + SUM((stanullfrac != 0)::INTEGER)/8 as nullhdr
                    from
                    (
                        select starelid, stanullfrac, stawidth
                        from pg_statistic
                        where not stainherit
                    union all
                        select indexrelid, stanullfrac, stawidth
                        from pg_index join pg_statistic on indrelid = starelid
                        where not stainherit and staattnum = any (indkey)
                    ) as att_stats
                    group by 1
                ) as rel_stats on relid = pg_class.oid
            ) as rel_info using (relkind)
        ) as s
    ) as ss
where relkind IN ('r','i')
and relpages/(expected_pages::numeric + 1) > 1.1
order by relpages - expected_pages desc