pgextras package¶
Submodules¶
pgextras.sql_constants module¶
SQL statements are kept here as to not clutter up main file.
Module contents¶
- class pgextras.PgExtras(dsn=None)[source]¶
Bases: object
- bloat()[source]¶
Table and index bloat in your database ordered by most wasteful.
- Record(
- type=’index’, schemaname=’public’, object_name=’pgbench_accounts::pgbench_accounts_pkey’, bloat=Decimal(‘0.2’), waste=‘0 bytes’
)
Returns: list of Records
- blocking()[source]¶
Display queries holding locks other queries are waiting to be released.
- Record(
- pid=40821, source=’‘, running_for=datetime.timedelta(0, 0, 2857), waiting=False, query=’SELECT pg_sleep(10);’
)
Returns: list of Records
- cache_hit()[source]¶
Calculates your cache hit rate (effective databases are at 99% and up).
- Record(
- name=’index hit rate’, ratio=Decimal(‘0.99994503346970922117’)
)
Returns: list of Records
- calls(truncate=False)[source]¶
Show 10 most frequently called queries. Requires the pg_stat_statements Postgres module to be installed.
- Record(
- query=’BEGIN;’, exec_time=datetime.timedelta(0, 0, 288174), prop_exec_time=‘0.0%’, ncalls=‘845590’, sync_io_time=datetime.timedelta(0)
)
Parameters: truncate – trim the Record.query output if greater than 40 chars Returns: list of Records
- execute(statement)[source]¶
Execute the given sql statement.
Parameters: statement – sql statement to run Returns: list
- index_usage()[source]¶
Calculates your index hit rate (effective databases are at 99% and up).
- Record(
- relname=’pgbench_history’, percent_of_times_index_used=None, rows_in_table=249976
)
Returns: list of Records
- is_pg_at_least_nine_two()[source]¶
Some queries have different syntax depending what version of postgres we are querying against.
Returns: boolean
- locks()[source]¶
Display queries with active locks.
- Record(
- procpid=31776, relname=None, transactionid=None, granted=True, query_snippet=’select * from hello;’, age=datetime.timedelta(0, 0, 288174),
)
Returns: list of Records
- long_running_queries()[source]¶
Show all queries longer than five minutes by descending duration.
- Record(
- pid=19578, duration=datetime.timedelta(0, 19944, 993099), query=’SELECT * FROM students’
)
Returns: list of Records
- outliers(truncate=False)[source]¶
Show 10 queries that have longest execution time in aggregate. Requires the pg_stat_statments Postgres module to be installed.
- Record(
- qry=’UPDATE pgbench_tellers SET tbalance = tbalance + ?;’, exec_time=datetime.timedelta(0, 19944, 993099), prop_exec_time=‘67.1%’, ncalls=‘845589’, sync_io_time=datetime.timedelta(0)
)
Parameters: truncate – trim the Record.qry output if greater than 40 chars Returns: list of Records
- pg_stat_statement()[source]¶
Some queries require the pg_stat_statement module to be installed. http://www.postgresql.org/docs/current/static/pgstatstatements.html
Returns: boolean
- ps()[source]¶
View active queries with execution time.
- Record(
- pid=28023, source=’pgbench’, running_for=datetime.timedelta(0, 0, 288174), waiting=0, query=’UPDATE pgbench_accounts SET abalance = abalance + 423;’
)
Returns: list of Records
- seq_scans()[source]¶
Show the count of sequential scans by table descending by order.
- Record(
- name=’pgbench_branches’, count=237
)
Returns: list of Records
- table_indexes_size()[source]¶
Show the total size of all the indexes on each table, descending by size.
- Record(
- table=’pgbench_accounts’, index_size=‘2208 kB’
)
Returns: list of Records
- table_size()[source]¶
Show the size of the tables (excluding indexes), descending by size.
Returns: list
- total_index_size()[source]¶
Show the total size of all indexes.
- Record(
- size=‘2240 kB’
)
Returns: list of Records
- total_indexes_size()[source]¶
Show the total size of all the indexes on each table, descending by size.
- Record(
- table=’pgbench_accounts’, index_size=‘2208 kB’
)
Returns: list of Records
- total_table_size()[source]¶
Show the size of the tables (including indexes), descending by size.
- Record(
- name=’pgbench_accounts’, size=‘15 MB’
)
Returns: list of Records
- unused_indexes()[source]¶
Show unused and almost unused indexes, ordered by their size relative to the number of index scans. Exclude indexes of very small tables (less than 5 pages), where the planner will almost invariably select a sequential scan, but may not in the future as the table grows.
- Record(
- table=’public.grade_levels’, index=’index_placement_attempts_on_grade_level_id’, index_size=‘97 MB’, index_scans=0
)
Returns: list of Records
- vacuum_stats()[source]¶
Show dead rows and whether an automatic vacuum is expected to be triggered.
- Record(
- schema=’public’, table=’pgbench_tellers’, last_vacuum=‘2014-04-29 14:45’, last_autovacuum=‘2014-04-29 14:45’, rowcount=‘10’, dead_rowcount=‘0’, autovacuum_threshold=‘52’, expect_autovacuum=None
)
Returns: list of Records