deeejay

psql - DJ's Table Performance Query

Jan 28th, 2016
362
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT pg_stat_user_tables.relid
  2. , pg_stat_user_tables.schemaname
  3. , pg_stat_user_tables.relname AS tablename
  4. , COALESCE(pg_stat_user_tables.idx_tup_fetch,0) + COALESCE(pg_stat_user_tables.seq_tup_read,0) AS total_rows_read
  5. , pg_size_pretty(pg_relation_size(pg_stat_user_tables.relid)) AS table_size
  6. , pg_relation_size(pg_stat_user_tables.relid) AS table_size_amount
  7. , pg_size_pretty(pg_total_relation_size(pg_statio_user_tables.relid) - pg_relation_size(pg_statio_user_tables.relid)) AS external_size
  8. , pg_total_relation_size(pg_statio_user_tables.relid) - pg_relation_size(pg_statio_user_tables.relid) AS external_size_amount
  9. , pg_size_pretty(pg_total_relation_size(pg_statio_user_tables.relid)) AS total_table_size
  10. , pg_total_relation_size(pg_statio_user_tables.relid) AS total_table_size_amount
  11. , pg_stat_user_tables.n_live_tup
  12. , pg_stat_user_tables.n_dead_tup
  13. , pg_stat_user_tables.last_vacuum
  14. , pg_stat_user_tables.last_autovacuum
  15. , pg_stat_user_tables.last_analyze
  16. , pg_stat_user_tables.last_autoanalyze
  17.  FROM pg_catalog.pg_stat_user_tables
  18.  LEFT OUTER JOIN  pg_catalog.pg_statio_user_tables
  19.  ON pg_stat_user_tables.relid = pg_statio_user_tables.relid
  20.  ORDER BY total_rows_read DESC
  21.  -- LIMIT 10
  22.  ;
Advertisement
Add Comment
Please, Sign In to add comment