reindex_bloated.sh

#!/bin/bash

cd /home/postgres || exit

DATABASE=${1:-"postgres"}
cat <<EOF > /tmp/idx_targets.sql
with step1 as (
  select
    i.nspname as schema_name,
    i.tblname as table_name,
    i.idxname as index_name,
    i.reltuples,
    i.relpages,
    i.relam,
    a.attrelid AS table_oid,
    current_setting('block_size')::numeric AS bs,
    fillfactor,
    case when version() ~ 'mingw32|64-bit|x86_64|ppc64|ia64|amd64' then 8 else 4 end as maxalign,
    24 AS pagehdr,
    16 AS pageopqdata,
    case
      when max(coalesce(s.null_frac,0)) = 0 then 2 
      else 2 + (( 32 + 8 - 1 ) / 8) 
    end as index_tuple_hdr_bm,
    sum((1 - coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024)) as nulldatawidth,
    max(case when a.atttypid = 'pg_catalog.name'::regtype then 1 else 0 end) > 0 as is_na
  from pg_attribute as a
  join (
    select
      nspname, tbl.relname AS tblname, idx.relname AS idxname, idx.reltuples, idx.relpages, idx.relam,
      indrelid, indexrelid, indkey::smallint[] AS attnum,
      coalesce(substring(array_to_string(idx.reloptions, ' ') from 'fillfactor=([0-9]+)')::smallint, 90) as fillfactor
    from pg_index
    join pg_class idx on idx.oid = pg_index.indexrelid
    join pg_class tbl on tbl.oid = pg_index.indrelid
    join pg_namespace on pg_namespace.oid = idx.relnamespace
    where pg_index.indisvalid AND tbl.relkind = 'r' AND idx.relpages > 0
  ) as i on a.attrelid = i.indexrelid
  join pg_stats as s on
    s.schemaname = i.nspname
    and (
      (s.tablename = i.tblname and s.attname = pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, true)) -- stats from tbl
      OR (s.tablename = i.idxname AND s.attname = a.attname) -- stats from functionnal cols
    )
  join pg_type as t on a.atttypid = t.oid
  where a.attnum > 0
  group by 1, 2, 3, 4, 5, 6, 7, 8, 9
), step2 as (
  select
    *,
    (
      index_tuple_hdr_bm + maxalign
      -- Add padding to the index tuple header to align on MAXALIGN
      - case when index_tuple_hdr_bm % maxalign = 0 THEN maxalign else index_tuple_hdr_bm % maxalign end
      + nulldatawidth + maxalign
      -- Add padding to the data to align on MAXALIGN
      - case
          when nulldatawidth = 0 then 0
          when nulldatawidth::integer % maxalign = 0 then maxalign
          else nulldatawidth::integer % maxalign
        end
    )::numeric as nulldatahdrwidth
    -- , index_tuple_hdr_bm, nulldatawidth -- (DEBUG INFO)
  from step1
), step3 as (
  select
    *,
    coalesce(1 + ceil(reltuples / floor((bs - pageopqdata - pagehdr) / (4 + nulldatahdrwidth)::float)), 0) as est_pages,
    coalesce(1 + ceil(reltuples / floor((bs - pageopqdata - pagehdr) * fillfactor / (100 * (4 + nulldatahdrwidth)::float))), 0) as est_pages_ff
  from step2
  join pg_am am on step2.relam = am.oid
  where am.amname = 'btree'
), step4 as (
  SELECT
    *,
    bs*(relpages)::bigint AS real_size,
    bs*(relpages-est_pages)::bigint AS extra_size,
    100 * (relpages-est_pages)::float / relpages AS extra_ratio,
    bs*(relpages-est_pages_ff) AS bloat_size,
    100 * (relpages-est_pages_ff)::float / relpages AS bloat_ratio
  from step3
  WHERE NOT is_na
)
select
   schema_name || '.' || index_name
   --- , pg_size_pretty(real_size::numeric) as "Size",
   --- case
   ---  when bloat_size::numeric >= 0
   ---    then '~' || pg_size_pretty(bloat_size::numeric)::text || ' (' || round(bloat_ratio::numeric, 2)::text || '%)'
   ---  else null
   --- end as "Bloat"
from step4
where 
    (
        round(bloat_ratio::numeric, 2) > 10 
        or
        bloat_size::numeric > 1000000000
    ) and real_size::numeric > 1000000000
order by real_size desc nulls last, bloat_size desc
;
EOF

chmod 666 /tmp/idx_targets.sql

INDEXES=$(sudo -u postgres psql -d ${DATABASE} -f /tmp/idx_targets.sql -t)
for idx in ${INDEXES}
do
    echo "REINDEXING ${idx}"
    sudo -u postgres psql -d ${DATABASE} -c "REINDEX INDEX CONCURRENTLY ${idx}"
    echo "REINDEXING ${idx} is done"
done