テーブル件数・容量
select relname, to_char(reltuples, '999999999') as rows, to_char(pg_relation_size(relname::regclass), '999999999999') as bytes from pg_class where relkind='r' and relnamespace = (select oid from pg_namespace where nspname='public') order by relname;
インデックス件数・容量
select relname, to_char(reltuples, '999999999') as rows, to_char(pg_relation_size(relname::regclass), '999999999999') as bytes from pg_class where relkind='i' and relnamespace = (select oid from pg_namespace where nspname='public') order by relname;
キャッシュヒット率（データベース）
select datname,round(blks_hit*100/(blks_hit+blks_read), 2) as cache_hit_ratio from pg_stat_database where blks_read > 0;
キャッシュヒット率（テーブル）
select relname,round(heap_blks_hit*100/(heap_blks_hit+heap_blks_read), 2) as cache_hit_ratio from pg_statio_user_tables where heap_blks_read > 0;
インデックスキャッシュヒット率
select relname, indexrelname, round(idx_blks_hit*100/(idx_blks_hit+idx_blks_read), 2) as cache_hit_ratio from pg_statio_user_indexes where idx_blks_read > 0;
表スキャンあたりの読み取り行数の確認
select relname, seq_scan, seq_tup_read, seq_tup_read/seq_scan as tup_per_read from pg_stat_user_tables where seq_scan > 0;
ガベージの量の確認
select relname, n_live_tup, n_dead_tup, round(n_dead_tup*100/(n_dead_tup+n_live_tup), 2) as dead_ratio, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where n_live_tup > 0;
HOT更新の比率の確認
select relname, n_tup_upd, n_tup_hot_upd, round(n_tup_hot_upd*100/n_tup_upd, 2) as hot_upd_ratio from pg_stat_user_tables where n_tup_upd > 0;
トングトランザクションの処理と経過時間の確認
select procpid, waiting, (current_timestamp - xact_start)::interval(3) as duration, current_query from pg_stat_activity where procpid <> pg_backend_pid();
ロック待ちとなっている処理内容と対象のテーブルを確認
select l.locktype, c.relname, l.pid, l.mode, substring(a.current_query, 1, 6) as query, (current_timestamp - xact_start)::interval(3) as duration from pg_locks l left outer join pg_stat_activity a on l.pid = a. procpid left outer join pg_class c on l.relation = c.oid where not l.granted order by l.pid;
