Skip to content

Instantly share code, notes, and snippets.

@whoahbot

whoahbot/gist:1691549

Created January 27, 2012 23:24
Show Gist options
  • Save whoahbot/1691549 to your computer and use it in GitHub Desktop.
Save whoahbot/1691549 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
select
s.relname,
s.seq_tup_read + s.idx_tup_fetch usage,
(s.idx_tup_fetch/s.seq_tup_read::float)::decimal(18,4) index_ratio,
s.seq_tup_read, s.idx_tup_fetch,
s.seq_scan, s.n_live_tup,
((io.heap_blks_hit + io.idx_blks_hit)/((io.heap_blks_read + io.idx_blks_read)::float))::decimal(18,4) cache_hit_ratio
FROM pg_stat_user_tables s
INNER JOIN pg_statio_user_tables io
ON s.relid = io.relid
WHERE s.seq_tup_read + s.idx_tup_fetch > 0
AND s.n_live_tup > 5000
ORDER BY
usage desc,
cache_hit_ratio desc,
index_ratio asc, s.seq_scan desc,
s.n_live_tup desc
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment