Monday, February 28, 2011

Query to find top 20 most used tables in Postgresql

0 comments

Following query gives the top 20 most used tables in the database based on the collected statistics.

select c.relname as name, c.reltuples::numeric as No_of_records, c.relpages as  No_of_pages,
   pg_size_pretty(pg_relation_size(c.relname)) as size,
   t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch, t.n_tup_ins, t.n_tup_upd, t.n_tup_del,
   COALESCE(t.idx_tup_fetch,0) + COALESCE(t.seq_tup_read,0) as total_read
from pg_stat_user_tables as t inner join pg_class as c
on (t.relid = c.oid)
where c.relkind ='r'
order by total_read desc
limit 20

Leave a Reply

Labels