gistfile1.txt

SELECT
  nspace.nspname AS schema,
  class.relname AS relation,
  pg_catalog.pg_total_relation_size(class.oid) AS total_size,
  pg_catalog.pg_relation_size(class.oid) AS heap_size,
  CASE class.reltoastrelid WHEN 0 THEN NULL ELSE pg_catalog.pg_total_relation_size(class.reltoastrelid) END AS toast_size,
  pg_catalog.pg_indexes_size(class.oid) AS idx_size,
  stat_t.seq_scan AS seqscan,
  stat_t.idx_scan AS idxScan,
  CASE (stat_t.n_live_tup+stat_t.n_dead_tup) WHEN 0 THEN 0 ELSE (stat_t.n_tup_upd/(stat_t.n_live_tup+stat_t.n_dead_tup)::float4 * 100)::int  END AS changed,
  CASE (stat_t.n_live_tup+stat_t.n_dead_tup) WHEN 0 THEN 0 ELSE (stat_t.n_dead_tup/(stat_t.n_live_tup+stat_t.n_dead_tup)::float4 * 100)::int END AS dead,
  stat_t.last_autovacuum as autovac_ts,
  stat_t.last_autoanalyze as autoanalyze_ts,
  CASE (stat_t_io.heap_blks_hit+stat_t_io.heap_blks_read) WHEN 0 THEN 0 ELSE (stat_t_io.heap_blks_hit/(stat_t_io.heap_blks_hit+stat_t_io.heap_blks_read)::float4 * 100)::int END AS heap_bufhit,
  CASE (stat_t_io.idx_blks_hit+stat_t_io.idx_blks_read) WHEN 0 THEN 0 ELSE (stat_t_io.idx_blks_hit/(stat_t_io.idx_blks_hit+stat_t_io.idx_blks_read)::float4 * 100)::int END AS idx_bufhit
FROM pg_class class
    LEFT JOIN pg_catalog.pg_namespace nspace ON class.relnamespace = nspace.oid
    LEFT JOIN pg_catalog.pg_stat_all_tables stat_t ON class.oid = stat_t.relid
    LEFT JOIN pg_catalog.pg_statio_all_tables stat_t_io ON class.oid = stat_t_io.relid 
    LEFT JOIN pg_catalog.pg_stat_all_indexes stat_i ON class.oid = stat_i.indexrelid
WHERE class.relkind IN ('r', 'p', 'm')
    AND class.relpersistence = 'p'
    AND nspace.nspname <> 'information_schema'
ORDER BY pg_catalog.pg_total_relation_size(class.oid) DESC;