1 #!/usr/bin/env python3

     2 

     3 from pgtoolkit import pgbrowser, toolbase

     4 from pycolib.prettysize import prettysize_short

     5 from pycolib.ansicolor import highlight

     6 

     7 

     8 class BigTablesTool(toolbase.SimpleTool):

     9     def __init__(self):

    10         toolbase.SimpleTool.__init__(self, name='bigtables', desc='List largest tables.')

    11         self.parser.add_argument('-n', '--limit', metavar='NUM', dest='limit', type=int, default=5, help='Show NUM biggest tables.')

    12         self.parser.add_argument('-v', '--details', dest='details', action='store_true', help='Show sizes of data and individual indexes.')

    13         self.init()

    14 

    15     def main(self):

    16         browser = pgbrowser.PgBrowser(self.pgm.get_conn('target'))

    17 

    18         # scan all tables from all shemas, remember names and sizes

    19         all_tables = []

    20         all_indexes = []

    21         schemas = browser.list_schemas()

    22         for schema in schemas:

    23             tables = browser.list_tables(schema['name'])

    24             for table in tables:

    25                 table_name = '%s.%s' % (schema['name'], table['name'])

    26                 indexes = browser.list_indexes(table['name'], schema['name'])

    27                 for index in indexes:

    28                     all_indexes.append({'name': index['name'], 'table': table_name, 'size': index['size']})

    29                 all_tables.append({'name': table_name, 'size': table['size'], 'indexes': indexes})

    30 

    31         # print names and sizes of 20 largest tables

    32         for table in sorted(all_tables, reverse=True, key=lambda x: x['size'])[:self.args.limit]:

    33             size_of_indexes = sum(index['size'] for index in table['indexes'])

    34             print(highlight(1) + prettysize_short(table['size'] + size_of_indexes, trailing_zeros=True).rjust(8) + highlight(0),

    35                   '(total)'.ljust(8),

    36                   highlight(1) + table['name'] + highlight(0), sep='  ')

    37             if self.args.details:

    38                 print(prettysize_short(table['size'], trailing_zeros=True).rjust(8),

    39                       '(data)'.ljust(8), sep='  ')

    40                 for index in sorted(table['indexes'], reverse=True, key=lambda x: x['size']):

    41                     print(prettysize_short(index['size'], trailing_zeros=True).rjust(8),

    42                           '(index)'.ljust(8), index['name'], sep='  ')

    43             print()

    44 

    45 

    46 if __name__ == '__main__':

    47     tool = BigTablesTool()

    48     tool.main()

    49