postgresql VIEW to explore table/index bloat and size of data/index and use of index (# of index scans)

db_utils.sql

-- SELECT * FROM db_size;  -- list tables/index with the disk space occupied
-- SELECT * FROM db_bloat; -- compute lost space in data/index
-- SELECT * FROM db_stats; -- show size of data/index + number of reads (data) or index scans
-- SELECT * FROM db_index_list ; -- show original CREATE INDEX statement

CREATE OR REPLACE VIEW db_bloat AS
SELECT current_database() AS current_database,
    sml.schemaname,
    sml.tablename,
    round(
        CASE
            WHEN sml.otta = 0::double precision THEN 0.0::double precision
            ELSE sml.relpages::double precision / sml.otta
        END::numeric, 1) AS tbloat,
        CASE
            WHEN sml.relpages::double precision < sml.otta THEN 0::numeric
            ELSE sml.bs * (sml.relpages::double precision - sml.otta)::bigint::numeric
        END AS wastedbytes,
    sml.iname,
    round(
        CASE
            WHEN sml.iotta = 0::double precision OR sml.ipages = 0 THEN 0.0::double precision
            ELSE sml.ipages::double precision / sml.iotta
        END::numeric, 1) AS ibloat,
        CASE
            WHEN sml.ipages::double precision < sml.iotta THEN 0::double precision
            ELSE sml.bs::double precision * (sml.ipages::double precision - sml.iotta)
        END AS wastedibytes
   FROM ( SELECT rs.schemaname,
            rs.tablename,
            cc.reltuples,
            cc.relpages,
            rs.bs,
            ceil(cc.reltuples * ((rs.datahdr + rs.ma::numeric -
                CASE
                    WHEN (rs.datahdr % rs.ma::numeric) = 0::numeric THEN rs.ma::numeric
                    ELSE rs.datahdr % rs.ma::numeric
                END)::double precision + rs.nullhdr2 + 4::double precision) / (rs.bs::double precision - 20::double precision)) AS otta,
            COALESCE(c2.relname, '?'::name) AS iname,
            COALESCE(c2.reltuples, 0::real) AS ituples,
            COALESCE(c2.relpages, 0) AS ipages,
            COALESCE(ceil(c2.reltuples * (rs.datahdr - 12::numeric)::double precision / (rs.bs::double precision - 20::double precision)), 0::double precision) AS iotta
           FROM ( SELECT foo.ma,
                    foo.bs,
                    foo.schemaname,
                    foo.tablename,
                    (foo.datawidth + (foo.hdr + foo.ma -
                        CASE
                            WHEN (foo.hdr % foo.ma) = 0 THEN foo.ma
                            ELSE foo.hdr % foo.ma
                        END)::double precision)::numeric AS datahdr,
                    foo.maxfracsum * (foo.nullhdr + foo.ma -
                        CASE
                            WHEN (foo.nullhdr % foo.ma::bigint) = 0 THEN foo.ma::bigint
                            ELSE foo.nullhdr % foo.ma::bigint
                        END)::double precision AS nullhdr2
                   FROM ( SELECT s.schemaname,
                            s.tablename,
                            constants.hdr,
                            constants.ma,
                            constants.bs,
                            sum((1::double precision - s.null_frac) * s.avg_width::double precision) AS datawidth,
                            max(s.null_frac) AS maxfracsum,
                            constants.hdr + (( SELECT 1 + count(*) / 8
                                   FROM pg_stats s2
                                  WHERE s2.null_frac <> 0::double precision AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename)) AS nullhdr
                           FROM pg_stats s,
                            ( SELECT ( SELECT current_setting('block_size'::text)::numeric AS current_setting) AS bs,
CASE
 WHEN "substring"(foo_1.v, 12, 3) = ANY (ARRAY['8.0'::text, '8.1'::text, '8.2'::text]) THEN 27
 ELSE 23
END AS hdr,
CASE
 WHEN foo_1.v ~ 'mingw32'::text THEN 8
 ELSE 4
END AS ma
                                   FROM ( SELECT version() AS v) foo_1) constants
                          GROUP BY s.schemaname, s.tablename, constants.hdr, constants.ma, constants.bs) foo) 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'::name
             LEFT JOIN pg_index i ON i.indrelid = cc.oid
             LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid) sml
  ORDER BY
        CASE
            WHEN sml.relpages::double precision < sml.otta THEN 0::numeric
            ELSE sml.bs * (sml.relpages::double precision - sml.otta)::bigint::numeric
        END DESC;


CREATE OR REPLACE VIEW db_size AS
 SELECT (n.nspname::text || '.'::text) || c.relname::text AS relation,
        CASE
            WHEN c.reltype = 0::oid THEN pg_size_pretty(pg_total_relation_size(c.oid::regclass)) || ' (index)'::text
            ELSE ((pg_size_pretty(pg_total_relation_size(c.oid::regclass)) || ' ('::text) || pg_size_pretty(pg_relation_size(c.oid::regclass))) || ' data)'::text
        END AS "size (data)",
    COALESCE(t.tablespace, i.tablespace, ''::name) AS tablespace
   FROM pg_class c
     LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_tables t ON t.tablename = c.relname
     LEFT JOIN pg_indexes i ON i.indexname = c.relname
     LEFT JOIN pg_tablespace ts ON ts.spcname = t.tablespace
     LEFT JOIN pg_tablespace xs ON xs.spcname = i.tablespace
  WHERE n.nspname <> ALL (ARRAY['pg_catalog'::name, 'pg_toast'::name, 'information_schema'::name])
  ORDER BY pg_total_relation_size(c.oid::regclass) DESC;


CREATE OR REPLACE VIEW db_stats AS SELECT
--    t.schemaname,
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN x.is_unique = 1  THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
       (SELECT indrelid,
           max(CAST(indisunique AS integer)) AS is_unique
       FROM pg_index
       GROUP BY indrelid) x
       ON c.oid = x.indrelid
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch,indexrelname FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname IN ('public')
ORDER BY pg_relation_size(quote_ident(t.tablename)::text) DESC,
         pg_relation_size(quote_ident(indexrelname)::text) DESC;


CREATE OR REPLACE VIEW db_index_list AS SELECT
    c2.relname,
    pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) AS create_index
FROM pg_tables t
LEFT OUTER JOIN pg_class c  ON t.tablename=c.relname
LEFT OUTER JOIN pg_index i  ON c.oid = i.indrelid
LEFT OUTER JOIN pg_class c2 ON i.indexrelid = c2.oid
WHERE t.schemaname IN ('public')
ORDER BY c2.relname;