Here are queries I put into a file for furfly that helped a lot. The first selects (i.e. "select 'Best Hit Ratios'") are just there to give headers for the various outputs.
You can put them in a file then say psql dbname -f filename.sql
select 'Best hit ratios';
select relname,
heap_blks_read + heap_blks_hit as heap_blk_requests,
case when heap_blks_hit > 0
then
round(100.0-100.0*heap_blks_read/(heap_blks_read+heap_blks_hit),2)
else round(0.0,2)
end as heap_blks_hit_percent,
idx_blks_read + idx_blks_hit as idx_blk_requests,
case when idx_blks_hit > 0
then round(100.0-100.0*idx_blks_read/(idx_blks_read +
idx_blks_hit),2)
else round(0.0,2)
end as idx_blks_hit_percent
from pg_statio_user_tables
where heap_blks_read > 0
order by heap_blks_hit_percent desc
limit 10;
select 'Worst hit ratios';
select relname,
heap_blks_read + heap_blks_hit as heap_blk_requests,
case when heap_blks_hit > 0
then
round(100.0-100.0*heap_blks_read/(heap_blks_read+heap_blks_hit),2)
else round(0.0,2)
end as heap_blks_hit_percent,
idx_blks_read + idx_blks_hit as idx_blk_requests,
case when idx_blks_hit > 0
then round(100.0-100.0*idx_blks_read/(idx_blks_read +
idx_blks_hit),2)
else round(0.0,2)
end as idx_blks_hit_percent
from pg_statio_user_tables
where heap_blks_read > 0
order by heap_blks_hit_percent
limit 10;
select 'Most active tables';
select relname,
heap_blks_read + heap_blks_hit as heap_blk_requests,
case when heap_blks_hit > 0
then
round(100.0-100.0*heap_blks_read/(heap_blks_read+heap_blks_hit),2)
else round(0.0,2)
end as heap_blks_hit_percent,
idx_blks_read + idx_blks_hit as idx_blk_requests,
case when idx_blks_hit > 0
then round(100.0-100.0*idx_blks_read/(idx_blks_read +
idx_blks_hit),2)
else round(0.0,2)
end as idx_blks_hit_percent
from pg_statio_user_tables
where heap_blks_read > 0
order by heap_blk_requests desc
limit 10;
