paladin.sql

SELECT tablename AS TABLE_NAME,
       ROUND(CASE
                 WHEN otta=0 THEN 0.0
                 ELSE sml.relpages/otta::numeric
             END,1) AS table_bloat,
       CASE
           WHEN relpages < otta THEN '0'
           ELSE pg_size_pretty((bs*(sml.relpages-otta)::bigint)::bigint)
       END AS table_waste,
       iname AS index_name,
       ROUND(CASE
                 WHEN iotta=0
                      OR ipages=0 THEN 0.0
                 ELSE ipages/iotta::numeric
             END,1) AS index_bloat,
       CASE
           WHEN ipages < iotta THEN '0'
           ELSE pg_size_pretty((bs*(ipages-iotta))::bigint)
       END AS index_waste
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
   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(v,12,3) IN ('8.0',
                                                  '8.1',
                                                  '8.2') THEN 27
                       ELSE 23
                   END AS hdr,
                   CASE
                       WHEN v ~ 'mingw32' 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
ORDER BY CASE
             WHEN relpages < otta THEN 0
             ELSE bs*(sml.relpages-otta)::bigint
         END DESC;