Re: Re: A 154 GB table swelled to 527 GB on the Slony slave. How to compact it? - Mailing list pgsql-general

On Wed, Mar 7, 2012 at 8:12 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
>
>> "check_postgres --action=bloat" returns OK [after VACUUM FULL].  So it's not bloat.
>> What else could it be?
>
> I would have to double check but I think
> check_postgres --action=bloat only checks for dead space, not usable space,
> so you could actually still have bloat, just bloat that is usable.

This is how check_postgres checks.  How would I check for usable
bloat, to confirm
that that's what I am running into?  What is usable bloat, anyway?
(Is there some write-up
on it?)


SELECT
          current_database() AS db, schemaname, tablename,
reltuples::bigint AS tups, relpages::bigint AS pages, otta,
          ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR
sml.relpages=otta THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS
tbloat,
          CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint -
otta END AS wastedpages,
          CASE WHEN relpages < otta THEN 0 ELSE
bs*(sml.relpages-otta)::bigint END AS wastedbytes,
          CASE WHEN relpages < otta THEN '0 bytes'::text ELSE
(bs*(relpages-otta))::bigint || ' bytes' END AS wastedsize,
          iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta,
          ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0
ELSE ipages/iotta::numeric END,1) AS ibloat,
          CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta
END AS wastedipages,
          CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END
AS wastedibytes,
          CASE WHEN ipages < iotta THEN '0 bytes' ELSE
(bs*(ipages-iotta))::bigint || ' bytes' END AS wastedisize,
          CASE WHEN relpages < otta THEN
            CASE WHEN ipages < iotta THEN 0 ELSE ipages-iotta::bigint END
            ELSE CASE WHEN ipages < iotta THEN relpages-otta::bigint
              ELSE relpages-otta::bigint + ipages-iotta::bigint END
          END AS totalwastedbytes
        FROM (
          SELECT
            schemaname, tablename, cc.reltuples, cc.relpages, bs,
            CEIL((cc.reltuples*((datahdr+ma-
              (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma
END))+nullhdr2+4))/(bs-20::float)) AS otta,
            COALESCE(c2.relname,'?') AS iname,
COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS
ipages,
            COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0)
AS iotta -- very rough approximation, assumes all cols
          FROM (
            SELECT
              ma,bs,schemaname,tablename,
              (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE
hdr%ma END)))::numeric AS datahdr,
              (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma
ELSE nullhdr%ma END))) AS nullhdr2
            FROM (
              SELECT
                schemaname, tablename, hdr, ma, bs,
                SUM((1-null_frac)*avg_width) AS datawidth,
                MAX(null_frac) AS maxfracsum,
                hdr+(
                  SELECT 1+count(*)/8
                  FROM pg_stats s2
                  WHERE null_frac<>0 AND s2.schemaname = s.schemaname
AND s2.tablename = s.tablename
                ) AS nullhdr
              FROM pg_stats s, (
                SELECT
                  (SELECT current_setting('block_size')::numeric) AS bs,
                    CASE WHEN SUBSTRING(SPLIT_PART(v, ' ', 2) FROM
'#"[0-9]+.[0-9]+#"%' for '#')
                      IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
                  CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8 ELSE
4 END AS ma
                FROM (SELECT version() AS v) AS foo
              ) AS constants
              GROUP BY 1,2,3,4,5
            ) AS foo
          ) AS rs
          JOIN pg_class cc ON cc.relname = rs.tablename
          JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND
nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
          LEFT JOIN pg_index i ON indrelid = cc.oid
          LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
        ) AS sml
         WHERE sml.relpages - otta > 0 OR ipages - iotta > 10 ORDER BY
totalwastedbytes DESC LIMIT 10

Yours,
Aleksey