deeejay

psql - Find most heavily used tables and indexes

Dec 16th, 2015
373
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Which tables in your database are most heavily used? Top 10 tables in terms of numbers of rows read:
  2.  
  3. SELECT relname, COALESCE(idx_tup_fetch,0) + COALESCE(seq_tup_read,0) AS Total
  4.  FROM pg_stat_all_tables
  5.  WHERE COALESCE(idx_tup_fetch,0) + COALESCE(seq_tup_read,0) <> 0
  6.  AND relname NOT LIKE 'pg_%'
  7.  ORDER BY Total desc
  8.  -- LIMIT 10
  9.  ;
  10.  
  11. Variations to above:
  12. pg_stat_system_tables = system tables
  13. pg_stat_user_tables = user created tables
  14. pg_stat_all_indexes = indexes
  15. pg_stat_system_indexes = system indexes
  16. pg_stat_user_indexes = user created indexes
  17. pg_stat
Advertisement
Add Comment
Please, Sign In to add comment