Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- reporting current activity
- SELECT pid, age(query_start, clock_timestamp()), usename, query
- FROM pg_stat_activity
- WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
- and state = 'active'
- ORDER BY query_start desc;
- -- lock monitoring
- SELECT blocked_locks.pid AS blocked_pid,
- blocked_activity.usename AS blocked_user,
- blocking_locks.pid AS blocking_pid,
- blocking_activity.usename AS blocking_user,
- blocked_activity.query AS blocked_statement,
- blocking_activity.query AS current_statement_in_blocking_process
- FROM pg_catalog.pg_locks blocked_locks
- JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
- JOIN pg_catalog.pg_locks blocking_locks
- ON blocking_locks.locktype = blocked_locks.locktype
- AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
- AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
- AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
- AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
- AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
- AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
- AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
- AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
- AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
- AND blocking_locks.pid != blocked_locks.pid
- JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
- WHERE NOT blocked_locks.GRANTED;
- -- index usage
- SELECT
- t.tablename,
- indexname,
- c.reltuples AS num_rows,
- pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
- pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
- CASE WHEN indisunique THEN 'Y'
- ELSE 'N'
- END AS UNIQUE,
- idx_scan AS number_of_scans,
- idx_tup_read AS tuples_read,
- idx_tup_fetch AS tuples_fetched
- FROM pg_tables t
- LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
- LEFT OUTER JOIN
- ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
- JOIN pg_class c ON c.oid = x.indrelid
- JOIN pg_class ipg ON ipg.oid = x.indexrelid
- JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
- AS foo
- ON t.tablename = foo.ctablename
- WHERE
- t.schemaname='public'
- and tablename = ?
- ORDER BY 1,2;
- -- index stats
- SELECT schemaname, relname, seq_scan-idx_scan AS too_much_seq, case when seq_scan-idx_scan>0 THEN 'Missing Index?' ELSE 'OK' END, pg_relation_size(format('%I.%I', schemaname, relname)::regclass) AS rel_size, seq_scan, idx_scan
- FROM pg_stat_user_tables
- WHERE pg_relation_size(format('%I.%I', schemaname, relname)::regclass)>80000 ORDER BY too_much_seq DESC;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement