Table and index stats

1. table and index hit
with stats as (
  with table_stats as (
    select psut.relname,
      psut.n_live_tup,
      1.0 * psut.idx_scan / greatest(1, psut.seq_scan + psut.idx_scan) as index_use_ratio
    from pg_stat_user_tables psut
    where schemaname = 'txcore'
    order by psut.n_live_tup desc
  ),
      table_io as (
      select psiut.relname,
        sum(psiut.heap_blks_read) as table_page_read,
        sum(psiut.heap_blks_hit)  as table_page_hit,
        sum(psiut.heap_blks_hit) / greatest(1, sum(psiut.heap_blks_hit) + sum(psiut.heap_blks_read)) as table_hit_ratio
      from pg_statio_user_tables psiut
      where schemaname = 'txcore'
      group by psiut.relname
      order by table_page_read desc
    ),
      index_io as (
      select psiui.relname,
        psiui.indexrelname,
        sum(psiui.idx_blks_read) as idx_page_read,
        sum(psiui.idx_blks_hit) as idx_page_hit,
        1.0 * sum(psiui.idx_blks_hit) / greatest(1.0, sum(psiui.idx_blks_hit) + sum(psiui.idx_blks_read)) as idx_hit_ratio
      from pg_statio_user_indexes psiui
      where schemaname = 'txcore'
      group by psiui.relname, psiui.indexrelname
      order by sum(psiui.idx_blks_read) desc
    )
  select ts.relname, ts.n_live_tup, ts.index_use_ratio,
    ti.table_page_read, ti.table_page_hit, ti.table_hit_ratio,
    ii.indexrelname, ii.idx_page_read, ii.idx_page_hit, ii.idx_hit_ratio
  from table_stats ts
    left outer join table_io ti
      on ti.relname = ts.relname
    left outer join index_io ii
      on ii.relname = ts.relname
  order by ti.table_page_read desc, ii.idx_page_read desc)
select * from stats
where relname = 'transfers'
order by idx_hit_ratio asc;

2. table and index size
WITH usage AS (
SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a
) a)
SELECT * FROM usage
WHERE table_schema = 'txcore'
AND table_name IN ('jobs', 'transfers');

3. index size (for table)
SELECT c2.relname, (c2.relpages * 8) / 1024 AS size
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'transfers' AND
      c.oid = i.indrelid AND
      c2.oid = i.indexrelid
ORDER BY c2.relname;