1 #!/usr/bin/env python3

     2 """

     3 analyzeall

     4 

     5 Analyze/vacuum all tables in selected schemas.

     6 See also "VACUUM ANALYZE VERBOSE;" query.

     7 Unlike that, this program skips pg_catalog etc.

     8 

     9 """

    10 

    11 from pgtoolkit import pgbrowser, toolbase

    12 

    13 

    14 class AnalyzeAllTool(toolbase.SimpleTool):

    15     def __init__(self):

    16         toolbase.SimpleTool.__init__(self, name='analyzeall', desc='Analyze all tables.')

    17         self.parser.add_argument('-s', dest='schema', nargs='*', help='Schema filter')

    18         self.parser.add_argument('--vacuum', action='store_true', help='Call VACUUM ANALYZE')

    19         self.parser.add_argument('--vacuum-full', action='store_true', help='Call VACUUM FULL ANALYZE')

    20         self.parser.add_argument('--reindex', action='store_true', help='Call REINDEX TABLE')

    21         self.target_isolation_level = 'autocommit'

    22         self.init()

    23 

    24     def main(self):

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

    26 

    27         query_patterns = ['ANALYZE %s.%s;']

    28         if self.args.vacuum:

    29             query_patterns = ['VACUUM ANALYZE %s.%s;']

    30         if self.args.vacuum_full:

    31             query_patterns = ['VACUUM FULL ANALYZE %s.%s;']

    32         if self.args.reindex:

    33             query_patterns += ['REINDEX TABLE %s.%s;']

    34 

    35         schema_list = self.args.schema

    36         if not schema_list:

    37             schema_list = [schema['name'] for schema in browser.list_schemas() if not schema['system']]

    38 

    39         for schema in schema_list:

    40             tables = browser.list_tables(schema=schema)

    41             with self.pgm.cursor('target') as curs:

    42                 for table in tables:

    43                     for query_pattern in query_patterns:

    44                         query = query_pattern % (schema, table['name'])

    45                         self.log.info(query)

    46                         curs.execute(query, [])

    47 

    48 

    49 tool = AnalyzeAllTool()

    50 tool.main()

    51