Tuesday, March 27, 2012

QUERY TO RETUN MOSTLY USED / ACCESSED TABLE IN POSTGRESQL


Below query will return most accessed table in PostgreSQL database,

SELECT c.relname as table_name, c.reltuples::numeric as no_of_records, c.relpages as  no_of_pages,
       t.seq_scan as tbl_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  5


it return top 5 mostly used table, if you want this query to return more-then 5 table change the limit 5 to desired No.of table




No comments: