1 #!/usr/bin/env python3 |
1 #!/usr/bin/env python3 |
2 |
2 |
3 from pgtoolkit import pgbrowser, toolbase |
3 from pgtoolkit import pgbrowser, toolbase |
4 from pycolib import prettysize |
4 from pycolib.prettysize import prettysize_short |
|
5 from pycolib.ansicolor import highlight |
5 |
6 |
6 |
7 |
7 class BigTablesTool(toolbase.SimpleTool): |
8 class BigTablesTool(toolbase.SimpleTool): |
8 def __init__(self): |
9 def __init__(self): |
9 toolbase.SimpleTool.__init__(self, name='bigtables', desc='List largest tables.') |
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.') |
10 self.init() |
13 self.init() |
11 |
14 |
12 def main(self): |
15 def main(self): |
13 browser = pgbrowser.PgBrowser(self.pgm.get_conn('target')) |
16 browser = pgbrowser.PgBrowser(self.pgm.get_conn('target')) |
14 |
17 |
15 # scan all tables from all shemas, remember names and sizes |
18 # scan all tables from all shemas, remember names and sizes |
16 all_tables = [] |
19 all_tables = [] |
|
20 all_indexes = [] |
17 schemas = browser.list_schemas() |
21 schemas = browser.list_schemas() |
18 for schema in schemas: |
22 for schema in schemas: |
19 tables = browser.list_tables(schema['name']) |
23 tables = browser.list_tables(schema['name']) |
20 for table in tables: |
24 for table in tables: |
21 table_name = '%s.%s' % (schema['name'], table['name']) |
25 table_name = '%s.%s' % (schema['name'], table['name']) |
22 all_tables.append({'name': table_name, 'size': table['size']}) |
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}) |
23 |
30 |
24 # print names and sizes of 20 largest tables |
31 # print names and sizes of 20 largest tables |
25 for table in sorted(all_tables, reverse=True, key=lambda x: x['size'])[:20]: |
32 for table in sorted(all_tables, reverse=True, key=lambda x: x['size'])[:self.args.limit]: |
26 print(prettysize.prettysize_short(table['size']).rjust(8), table['name'], sep=' ') |
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() |
27 |
44 |
28 |
45 |
29 tool = BigTablesTool() |
46 if __name__ == '__main__': |
30 tool.main() |
47 tool = BigTablesTool() |
|
48 tool.main() |
31 |
49 |