Skip to content

Instantly share code, notes, and snippets.

@snewcomer
Created November 25, 2022 12:48
Show Gist options
  • Save snewcomer/900d06725096b837d2403366919f7287 to your computer and use it in GitHub Desktop.
Save snewcomer/900d06725096b837d2403366919f7287 to your computer and use it in GitHub Desktop.
postgres-table-hit-ratio.sql
"
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
;
"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment