pgstats.sql

CREATE OR REPLACE VIEW table_stats AS (
SELECT t.relname AS table,
       n_live_tup AS approx_rows,
       heap_blks_read + heap_blks_hit AS reads,
       round(100 * heap_blks_hit::numeric / (heap_blks_hit + heap_blks_read + 0.0001), 3) AS cache_hit_rate,
       round(100 *  idx_blks_hit::numeric / ( idx_blks_hit +  idx_blks_read + 0.0001), 3) AS idx_cache_hit_rate,
       round(100 *      idx_scan::numeric / (     seq_scan +       idx_scan + 0.0001), 3) AS idx_usage_pct
  FROM pg_statio_user_tables AS t
  FULL OUTER
  JOIN pg_stat_user_tables
    ON pg_stat_user_tables.relname = t.relname
 ORDER BY approx_rows DESC
);

CREATE OR REPLACE VIEW cache_hit_rate AS (
SELECT relname AS table,
       heap_blks_read + heap_blks_hit AS reads,
       round(100 * heap_blks_hit::numeric / (heap_blks_hit + heap_blks_read + 0.0001), 3) AS     hit_pct,
       round(100 *  idx_blks_hit::numeric / ( idx_blks_hit +  idx_blks_read + 0.0001), 3) AS idx_hit_pct
  FROM pg_statio_user_tables 
 WHERE heap_blks_hit + heap_blks_read + idx_blks_hit + idx_blks_read > 0 
 ORDER BY reads DESC
);

CREATE OR REPLACE VIEW index_usage AS (
SELECT relname AS table,
       n_live_tup AS approx_rows,
       round(100 * idx_scan::numeric / (seq_scan + idx_scan + 0.0001), 3) AS idx_usage_pct
  FROM pg_stat_user_tables
 ORDER BY n_live_tup DESC
);

SELECT * FROM index_usage NATURAL JOIN cache_hit_rate;

select relname, round(100 * (n_tup_hot_upd::numeric / n_tup_upd), 3) from pg_stat_user_tables where n_tup_upd > 0
union all
select relname, 100::numeric from pg_stat_user_tables where n_tup_upd = 0;