public
Created

See overall stats on your PostgreSQL database

  • Download Gist
gistfile1.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
select
psut.schemaname,
pc.relname,
pg_size_pretty(pg_relation_size(pc.relname::varchar)) relsize_pret,
pg_size_pretty(pg_total_relation_size(pc.relname::varchar)) total_relsize_pret,
pg_relation_size(pc.relname::varchar) relsize,
pg_total_relation_size(pc.relname::varchar) total_relsize,
pc.reltuples::integer,
pc.relpages,
coalesce(round((8000 / (nullif(pc.reltuples, 0) / nullif(pc.relpages, 0)))), 0) avg_tuplesize,
psut.seq_scan,
psut.idx_scan,
coalesce(100 * psut.idx_scan / nullif((psut.idx_scan + psut.seq_scan), 0), 0) per_idx_scan,
coalesce(100 * psiout.heap_blks_hit / nullif((psiout.heap_blks_hit + psiout.heap_blks_read), 0), 0) per_rel_hit,
coalesce(100 * psiout.idx_blks_hit / nullif((psiout.idx_blks_hit + psiout.idx_blks_read), 0), 0) per_idx_hit,
psut.n_tup_ins,
psut.n_tup_upd,
psut.n_tup_hot_upd,
coalesce(100 * psut.n_tup_hot_upd / nullif(psut.n_tup_upd, 0), 0) per_hot_upd,
psut.n_tup_del,
psut.n_live_tup,
psut.n_dead_tup,
coalesce(100 * psut.n_dead_tup / nullif(psut.n_live_tup, 0), 0) per_deadfill,
psut.last_autovacuum,
psut.last_autoanalyze
from pg_stat_user_tables psut
inner join pg_statio_user_tables psiout on psiout.relname = psut.relname
inner join pg_class pc on pc.relname = psut.relname
order by pc.relname asc;

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.