Monday, February 28, 2011

Query to find top 20 most used tables in Postgresql


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