The script shows postgresql indexes of a passed table with their size and some table statistics.

pg_tbl_idx_stat.py

#!/usr/bin/python3
# pg_tbl_idx_stat.py - The script shows postgresql indexes
# of a passed table with their size and some table statistics
#
# Author: Andrey Klychkov aaklychkov@mail.ru
# Date: 23-03-2018
# Licence: Copyleft free software
# Requirements: python3, psql and postgres (installed locally),
# run as 'postgres' user
#
# Syntax: ./pg_tbl_idx_stat.py DBNAME TBLNAME
#
# It has the output like below:
# -----------------------------
# Table: test_table
# Relation size: 54.4 GB
# Total relation size: 142.41 GB
# Total indexes size: 78.14 GB
# Idx percentage: 54%
# -----------------------------------------------------------
#     SIZE | IDX_NAME                         | COLUMN
# -----------------------------------------------------------
#  3.45 GB | test_table_fileuuid_idx          | fileuuid
#  2.32 GB | test_table_modifydate            | modifydate
#  16.0 KB | test_table_creationdate          | creationdate
#  16.0 KB | test_table_deleted               | deleted
#  ...

import math
import re
import subprocess
import sys


if len(sys.argv) != 3:
    print('use: ./pg_tbl_idx_stat.py <dbname> <tblname>')
    sys.exit(1)


DB = sys.argv[1]
TBL = sys.argv[2]


def convert_size(size_bytes):
    if size_bytes == 0:
        return "0B"
    size_name = ("B", "KB", "MB", "GB", "TB", "PB", "EB", "ZB", "YB")
    i = int(math.floor(math.log(size_bytes, 1024)))
    p = math.pow(1024, i)
    s = round(size_bytes / p, 2)
    return "%s %s" % (s, size_name[i])


def exec_command(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


if __name__ == '__main__':
    GET_IDX_CMD = 'psql %s -t -c "SELECT indexname, indexdef '\
                  'FROM pg_indexes WHERE tablename = \'%s\';"' % (DB, TBL)

    out = exec_command(GET_IDX_CMD)[0]

    constraint = re.compile(' UNIQUE ')

    idx_list = []

    total_idx_size = 0

    for i in out:
        t = i.decode("utf-8").strip(' ').rstrip('\n').split('|')

        if t[0]:
            if constraint.search(t[1]):
                continue

            idx_name = t[0].rstrip(' ')
            idx_def = t[1].strip(' ').split(' ')
            column = idx_def[7].strip('(').strip(')')

            GET_REL_SIZE = 'psql %s -t -c "SELECT '\
                           'pg_relation_size(\'%s\');"' % (DB, idx_name)
            idx_size = exec_command(GET_REL_SIZE)[0][0].decode("utf-8")
            idx_size = idx_size.rstrip('\n').lstrip(' ')
            idx_pr_size = convert_size(int(idx_size))

            total_idx_size += int(idx_size)

            idx_list.append([idx_size, idx_pr_size, idx_name, column])

    # Sort the index list by index size:
    sorted(idx_list, key=lambda x: x[0])

    # Get table size:
    GET_REL_SIZE = 'psql %s -t -c \"SELECT '\
                   'pg_relation_size(\'%s\');"' % (DB, TBL)
    rel_size = exec_command(GET_REL_SIZE)[0][0].decode("utf-8")
    rel_size = rel_size.rstrip('\n').lstrip(' ')

    GET_TOT_REL_SIZE = 'psql %s -t -c \"SELECT '\
                       'pg_total_relation_size(\'%s\');"' % (DB, TBL)
    total_rel_size = exec_command(GET_TOT_REL_SIZE)[0][0].decode("utf-8")
    total_rel_size = total_rel_size.rstrip('\n').lstrip(' ')

    # Compute what part of total relation size consists of indexes::
    idx_percentage = total_idx_size * 100 // int(total_rel_size)

    # For size in a pretty form:
    pretty_total_idx_size = convert_size(total_idx_size)
    pretty_rel_size = convert_size(int(rel_size))
    pretty_total_rel_size = convert_size(int(total_rel_size))

    # Printing block:
    row_sep = '-' * 100
    print('')
    print('Table: %s\nRelation size: %s' % (TBL, pretty_rel_size))
    print('Total relation size: %s' % pretty_total_rel_size)
    print('Total indexes size: %s' % pretty_total_idx_size)
    print('Idx percentage: %s%%' % idx_percentage)
    print(row_sep)

    print('{:{}{}} | {:{}{}} | {}'.format(
        'SIZE', '>', '12', 'IDX_NAME', '<', '64', 'COLUMN'))
    print(row_sep)

    for i in idx_list:
        print('{:{}{}} | {:{}{}} | {}'.format(
            i[1], '>', '12', i[2], '<', '64', i[3]))