postgres-table-hit-ratio.sql

gistfile1.txt

"
with table_stats as (
  select psut.relname,
  psut.n_live_tup,
  1.0 * psut.idx_scan / greatest(1, psut.seq_scan + psut.idx_scan) as index_use_ratio
  from pg_stat_user_tables psut
  order by psut.n_live_tup desc
),
table_io as (
  select psiut.relname,
  sum(psiut.heap_blks_read) as table_page_read,
  sum(psiut.heap_blks_hit) as table_page_hit,
  sum(psiut.heap_blks_hit) / greatest(1, sum(psiut.heap_blks_hit) + sum(psiut.heap_blks_read)) as table_hit_ratio
  from pg_statio_user_tables psiut
  group by psiut.relname
  order by table_page_read desc
),
index_io as (
  select psiui.relname,
  psiui.indexrelname,
  sum(psiui.idx_blks_read) as idx_page_read,
  sum(psiui.idx_blks_hit) as idx_page_hit,
  1.0 * sum(psiui.idx_blks_hit) / greatest(1.0, sum(psiui.idx_blks_hit) + sum(psiui.idx_blks_read)) as idx_hit_ratio
  from pg_statio_user_indexes psiui
  group by psiui.relname, psiui.indexrelname
  order by sum(psiui.idx_blks_read) desc
)
select ts.relname, ts.n_live_tup, ts.index_use_ratio,
  ti.table_page_read, ti.table_page_hit, ti.table_hit_ratio,
  ii.indexrelname, ii.idx_page_read, ii.idx_page_hit, ii.idx_hit_ratio
  from table_stats ts
  left outer join table_io ti
  on ti.relname = ts.relname
  left outer join index_io ii
  on ii.relname = ts.relname
  order by ti.table_page_read desc, ii.idx_page_read desc
;
"