1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- Useful shortcuts
\set conninfo 'SELECT usename, application_name, client_addr, state FROM pg_stat_activity;'
\set activity 'SELECT datname, pid, usename, application_name, client_addr, state, query FROM pg_stat_activity ORDER BY query_start DESC;'
\set locks 'SELECT mode, locktype, database, relation, page, tuple, classid, granted, query FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid;'
\set waits 'SELECT pg_stat_activity.pid, pg_stat_activity.query, pg_stat_activity.waiting, now() - pg_stat_activity.query_start AS \"totaltime\", pg_stat_activity.backend_start FROM pg_stat_activity WHERE pg_stat_activity.query !~ \'%IDLE%\'::text AND pg_stat_activity.waiting = true;'

-- Database size information
\set dbsize 'SELECT datname, pg_size_pretty(pg_database_size(datname)) as size FROM pg_database ORDER BY pg_database_size(datname) DESC;'
\set tablesize 'SELECT schemaname,tablename,pg_size_pretty(size) as size, pg_size_pretty(total_size) as total_size FROM (SELECT schemaname,tablename,pg_relation_size(schemaname||''.''||tablename) as size, pg_total_relation_size(schemaname||''.''||tablename) as total_size FROM pg_tables) as TABLES ORDER BY total_size DESC;'

-- Index usage statistics
\set unused_indexes 'SELECT schemaname, tablename, attname, n_distinct, correlation FROM pg_stats WHERE schemaname = ''public'' ORDER BY n_distinct DESC;'
\set index_usage 'SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_tup_upd + n_tup_ins + n_tup_del as num_writes, indexrelname as index_name FROM pg_stat_user_tables JOIN pg_stat_user_indexes USING (relid) ORDER BY percent_of_times_index_used;'