Postgres SQL めもめも

テーブル件数・容量

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;

参照

Let's Postgres 稼動統計情報を活用しよう(2)