create view vw_index_bloat(current_database, schemaname, tblname, idxname, real_size, extra_size, extra_pct, fillfactor, bloat_size, bloat_pct, is_na) as
	SELECT current_database() AS current_database,
    relation_stats.nspname AS schemaname,
    relation_stats.tblname,
    relation_stats.idxname,
    pg_size_pretty(relation_stats.bs * relation_stats.relpages::bigint::numeric) AS real_size,
    pg_size_pretty(relation_stats.bs * (relation_stats.relpages::double precision - relation_stats.est_pages)::bigint::numeric) AS extra_size,
    100::double precision * (relation_stats.relpages::double precision - relation_stats.est_pages) / relation_stats.relpages::double precision AS extra_pct,
    relation_stats.fillfactor,
    pg_size_pretty(
        CASE
            WHEN relation_stats.relpages::double precision > relation_stats.est_pages_ff THEN relation_stats.bs::double precision * (relation_stats.relpages::double precision - relation_stats.est_pages_ff)
            ELSE 0::double precision
        END::bigint) AS bloat_size,
    100::double precision * (relation_stats.relpages::double precision - relation_stats.est_pages_ff) / relation_stats.relpages::double precision AS bloat_pct,
    relation_stats.is_na
   FROM ( SELECT COALESCE(1::double precision + ceil(rows_hdr_pdg_stats.reltuples / floor((rows_hdr_pdg_stats.bs - rows_hdr_pdg_stats.pageopqdata::numeric - rows_hdr_pdg_stats.pagehdr::numeric)::double precision / (4::numeric + rows_hdr_pdg_stats.nulldatahdrwidth)::double precision)), 0::double precision) AS est_pages,
            COALESCE(1::double precision + ceil(rows_hdr_pdg_stats.reltuples / floor(((rows_hdr_pdg_stats.bs - rows_hdr_pdg_stats.pageopqdata::numeric - rows_hdr_pdg_stats.pagehdr::numeric) * rows_hdr_pdg_stats.fillfactor::numeric)::double precision / (100::double precision * (4::numeric + rows_hdr_pdg_stats.nulldatahdrwidth)::double precision))), 0::double precision) AS est_pages_ff,
            rows_hdr_pdg_stats.bs,
            rows_hdr_pdg_stats.nspname,
            rows_hdr_pdg_stats.tblname,
            rows_hdr_pdg_stats.idxname,
            rows_hdr_pdg_stats.relpages,
            rows_hdr_pdg_stats.fillfactor,
            rows_hdr_pdg_stats.is_na
           FROM ( SELECT rows_data_stats.maxalign,
                    rows_data_stats.bs,
                    rows_data_stats.nspname,
                    rows_data_stats.tblname,
                    rows_data_stats.idxname,
                    rows_data_stats.reltuples,
                    rows_data_stats.relpages,
                    rows_data_stats.idxoid,
                    rows_data_stats.fillfactor,
                    ((rows_data_stats.index_tuple_hdr_bm + rows_data_stats.maxalign -
                        CASE
                            WHEN (rows_data_stats.index_tuple_hdr_bm % rows_data_stats.maxalign) = 0 THEN rows_data_stats.maxalign
                            ELSE rows_data_stats.index_tuple_hdr_bm % rows_data_stats.maxalign
                        END)::double precision + rows_data_stats.nulldatawidth + rows_data_stats.maxalign::double precision -
                        CASE
                            WHEN rows_data_stats.nulldatawidth = 0::double precision THEN 0
                            WHEN (rows_data_stats.nulldatawidth::integer % rows_data_stats.maxalign) = 0 THEN rows_data_stats.maxalign
                            ELSE rows_data_stats.nulldatawidth::integer % rows_data_stats.maxalign
                        END::double precision)::numeric AS nulldatahdrwidth,
                    rows_data_stats.pagehdr,
                    rows_data_stats.pageopqdata,
                    rows_data_stats.is_na
                   FROM ( SELECT n.nspname,
                            i.tblname,
                            i.idxname,
                            i.reltuples,
                            i.relpages,
                            i.idxoid,
                            i.fillfactor,
                            current_setting('block_size'::text)::numeric AS bs,
                                CASE
                                    WHEN version() ~ 'mingw32'::text OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64'::text THEN 8
                                    ELSE 4
                                END AS maxalign,
                            24 AS pagehdr,
                            16 AS pageopqdata,
                                CASE
                                    WHEN max(COALESCE(s.null_frac, 0::real)) = 0::double precision THEN 8
                                    ELSE 8 + (32 + 8 - 1) / 8
                                END AS index_tuple_hdr_bm,
                            sum((1::double precision - COALESCE(s.null_frac, 0::real)) * COALESCE(s.avg_width, 1024)::double precision) AS nulldatawidth,
                            max(
                                CASE
                                    WHEN i.atttypid = 'name'::regtype::oid THEN 1
                                    ELSE 0
                                END) > 0 AS is_na
                           FROM ( SELECT ct.relname AS tblname,
                                    ct.relnamespace,
                                    ic.idxname,
                                    ic.attpos,
                                    ic.indkey,
                                    ic.indkey[ic.attpos] AS indkey,
                                    ic.reltuples,
                                    ic.relpages,
                                    ic.tbloid,
                                    ic.idxoid,
                                    ic.fillfactor,
                                    COALESCE(a1.attnum, a2.attnum) AS attnum,
                                    COALESCE(a1.attname, a2.attname) AS attname,
                                    COALESCE(a1.atttypid, a2.atttypid) AS atttypid,
CASE
 WHEN a1.attnum IS NULL THEN ic.idxname
 ELSE ct.relname
END AS attrelname
                                   FROM ( SELECT idx_data.idxname,
    idx_data.reltuples,
    idx_data.relpages,
    idx_data.tbloid,
    idx_data.idxoid,
    idx_data.fillfactor,
    idx_data.indkey,
    generate_series(1, idx_data.indnatts::integer) AS attpos
   FROM ( SELECT ci.relname AS idxname,
      ci.reltuples,
      ci.relpages,
      i_1.indrelid AS tbloid,
      i_1.indexrelid AS idxoid,
      COALESCE("substring"(array_to_string(ci.reloptions, ' '::text), 'fillfactor=([0-9]+)'::text)::smallint::integer, 90) AS fillfactor,
      i_1.indnatts,
      string_to_array(textin(int2vectorout(i_1.indkey)), ' '::text)::integer[] AS indkey
     FROM pg_index i_1
       JOIN pg_class ci ON ci.oid = i_1.indexrelid
    WHERE ci.relam = (( SELECT pg_am.oid
       FROM pg_am
      WHERE pg_am.amname = 'btree'::name)) AND ci.relpages > 0) idx_data) ic
                                     JOIN pg_class ct ON ct.oid = ic.tbloid
                                     LEFT JOIN pg_attribute a1 ON ic.indkey[ic.attpos] <> 0 AND a1.attrelid = ic.tbloid AND a1.attnum = ic.indkey[ic.attpos]
                                     LEFT JOIN pg_attribute a2 ON ic.indkey[ic.attpos] = 0 AND a2.attrelid = ic.idxoid AND a2.attnum = ic.attpos) i(tblname, relnamespace, idxname, attpos, indkey, indkey_1, reltuples, relpages, tbloid, idxoid, fillfactor, attnum, attname, atttypid, attrelname)
                             JOIN pg_namespace n ON n.oid = i.relnamespace
                             JOIN pg_stats s ON s.schemaname = n.nspname AND s.tablename = i.attrelname AND s.attname = i.attname
                          GROUP BY n.nspname, i.tblname, i.idxname, i.reltuples, i.relpages, i.idxoid, i.fillfactor, (current_setting('block_size'::text)::numeric), (
                                CASE
                                    WHEN version() ~ 'mingw32'::text OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64'::text THEN 8
                                    ELSE 4
                                END), 24::integer, 16::integer) rows_data_stats) rows_hdr_pdg_stats) relation_stats
  WHERE (relation_stats.bs::double precision * (relation_stats.relpages::double precision - relation_stats.est_pages)) > 0::double precision OR (relation_stats.bs::double precision * (relation_stats.relpages::double precision - relation_stats.est_pages_ff)) > 0::double precision
  ORDER BY (relation_stats.bs::double precision * (relation_stats.relpages::double precision - relation_stats.est_pages_ff)) DESC;

