analyzeall.py
author Radek Brich <radek.brich@devl.cz>
Tue, 07 Feb 2012 11:32:07 +0100
changeset 28 27fc0504663d
parent 27 5fb4883604d6
child 30 a8b7cd92f39f
permissions -rwxr-xr-x
analyzeall: analyze tables from all schemas by default
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     1
#!/usr/bin/env python3.2
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     2
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     3
from pgtoolkit import pgbrowser, toolbase
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     4
from pgtoolkit.highlight import highlight
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     5
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     6
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     7
class AnalyzeAllTool(toolbase.SimpleTool):
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     8
    def __init__(self):
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     9
        toolbase.SimpleTool.__init__(self, name='analyzeall', desc='Analyze all tables.')
28
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    10
        self.parser.add_argument('-s', dest='schema', nargs='*', help='Schema filter')
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    11
        self.parser.add_argument('--vacuum', action='store_true', help='Call VACUUM ANALYZE')
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    12
        self.parser.add_argument('--full', action='store_true', help='Call VACUUM FULL ANALYZE')
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    13
        self.init()
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    14
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    15
    def main(self):
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    16
        browser = pgbrowser.PgBrowser(self.pgm.get_conn('target'))
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    17
        
28
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    18
        query_pattern = 'ANALYZE %s.%s;'
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    19
        if self.args.vacuum:
28
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    20
            query_pattern = 'VACUUM ANALYZE %s.%s;'
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    21
        if self.args.full:
28
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    22
            query_pattern = 'VACUUM FULL ANALYZE %s.%s;'
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    23
28
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    24
        schema_list = self.args.schema
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    25
        if not schema_list:
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    26
            schema_list = [schema['name'] for schema in browser.list_schemas() if not schema['system']]
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    27
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    28
        for schema in schema_list:
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    29
            tables = browser.list_tables(schema=schema)
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    30
            with self.pgm.cursor('target') as curs:
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    31
                for table in tables:
28
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    32
                    query = query_pattern % (schema, table['name'])
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    33
                    print(query)
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    34
                    curs.execute(query, [])
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    35
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    36
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    37
tool = AnalyzeAllTool()
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    38
tool.main()
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    39