The script prints top of bloated indexes with stat.

print_bloat.py

#!/usr/bin/python
# print_bloat.py - Print top of bloated indexes with stat
# Author: Andreyk Klychkov aaklychkov@mail.ru
# Licence: Copyleft free software
# Syntax: ./print_bloat.py <dbname>
# Date: 23-03-2018
#
# Requirements: python2+, psql and postgres (installed locally),
# run it as 'postgres' user
#
# It has output like below:
# -------------------------
#  tblname      | full_size | table_size | bloat_size | bloat_ratio 
# --------------+-----------+------------+------------+------------
#  test_table1  | 733 GB    | 729 GB     | 329 GB     |       46.02
#  test_table2  | 252 GB    | 252 GB     | 151 GB     |       60.77
#  test_table3  | 358 GB    | 236 GB     | 71 GB      |       29.87
#  ...          | ...       | ...        | ...        |       ...
#  test_table40 | 100 GB    | 100 GB     | 50 GB      |       50.00
# (40 rows)

from __future__ import print_function
import subprocess
import sys


# Output line numbers:
LIMIT_LINES = 40


def exec_shell_cmd(cmd):
    '''Execute a shell command'''
    ret = subprocess.Popen(cmd, shell=True,
                           stdout=subprocess.PIPE,
                           stderr=subprocess.STDOUT)
    out = ret.stdout.readlines()
    ret.communicate()
    rcode = ret.poll()
    return out, rcode


def get_tbl_bloat_stat(dbname):
    '''Get top of bloated indexes'''
    TABLE_BLOAT_SQL = '''(SELECT tblname,
  pg_size_pretty(pg_total_relation_size(tblid)) AS full_size,
  pg_size_pretty(pg_table_size(tblid)) AS table_size,
  pg_size_pretty(((tblpages-est_tblpages_ff)*bs)::numeric) AS bloat_size,
  CASE WHEN tblpages - est_tblpages_ff > 0
    THEN (100 * (tblpages - est_tblpages_ff)/tblpages::float)::numeric(5,2)
    ELSE 0
  END AS bloat_ratio
FROM (
  SELECT ceil( reltuples / ( (bs-page_hdr)/tpl_size ) ) + ceil( toasttuples / 4 ) AS est_tblpages,
    ceil( reltuples / ( (bs-page_hdr)*fillfactor/(tpl_size*100) ) ) + ceil( toasttuples / 4 ) AS est_tblpages_ff,
    tblpages, fillfactor, bs, tblid, schemaname, tblname, heappages, toastpages, is_na
  FROM (
    SELECT
      ( 4 + tpl_hdr_size + tpl_data_size + (2*ma)
        - CASE WHEN tpl_hdr_size%ma = 0 THEN ma ELSE tpl_hdr_size%ma END
        - CASE WHEN ceil(tpl_data_size)::int%ma = 0 THEN ma ELSE ceil(tpl_data_size)::int%ma END
      ) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages,
      toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname, fillfactor, is_na
    FROM (
      SELECT
        tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples,
        tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages,
        coalesce(toast.reltuples, 0) AS toasttuples,
        coalesce(substring(
          array_to_string(tbl.reloptions, ' ')
          FROM '%fillfactor=#"__#"%' FOR '#')::smallint, 100) AS fillfactor,
        current_setting('block_size')::numeric AS bs,
        CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma,
        24 AS page_hdr,
        23 + CASE WHEN MAX(coalesce(null_frac,0)) > 0 THEN ( 7 + count(*) ) / 8 ELSE 0::int END
          + CASE WHEN tbl.relhasoids THEN 4 ELSE 0 END AS tpl_hdr_size,
        sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024) ) AS tpl_data_size,
        bool_or(att.atttypid = 'pg_catalog.name'::regtype) AS is_na
      FROM pg_attribute AS att
        JOIN pg_class AS tbl ON att.attrelid = tbl.oid
        JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
        JOIN pg_stats AS s ON s.schemaname=ns.nspname
          AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname
        LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid
      WHERE att.attnum > 0 AND NOT att.attisdropped
        AND tbl.relkind = 'r'
      GROUP BY 1,2,3,4,5,6,7,8,9,10, tbl.relhasoids
      ORDER BY 2,3
    ) AS s
  ) AS s2
) AS s3
WHERE schemaname = 'public'
ORDER BY (tblpages-est_tblpages_ff)*bs DESC)

UNION ALL

(SELECT
  c.relname AS tblname,
  pg_size_pretty(pg_total_relation_size(c.oid)) AS full_size,
  pg_size_pretty(pg_table_size(c.oid)) AS table_size,
  '' AS bloat_size,
  '0' AS bloat_ratio
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
      AND pg_catalog.pg_table_is_visible(c.oid)
      AND pg_table_size(c.oid) <= 8192
      ORDER BY pg_total_relation_size(c.oid) DESC) limit '''

    get_tbl_bloat_stat_cmd = 'psql %s -c "%s %s;"' % (
        dbname, TABLE_BLOAT_SQL, LIMIT_LINES)

    stat = exec_shell_cmd(get_tbl_bloat_stat_cmd)

    print("")
    for s in stat[0]:
        print(s.rstrip('\n'))


if __name__ == '__main__':

    if len(sys.argv) != 2:
        print('syntax: ./print_bloat.py <dbname>')
        sys.exit(1)

    dbname = sys.argv[1]

    # Get and show table bloat stat:
    get_tbl_bloat_stat(dbname)

    sys.exit(0)