【openGauss】openGauss查表大小、存储占比、行数、死元祖数量、膨胀率

---

一、openGauss查表大小、存储占比、行数、死元祖数量、膨胀率

/* openGauss查表膨胀率 */
WITH t1 AS(
  SELECT schemaname,
         tablename,
         (11 + ceil(count(*)*(1+avg(null_frac)) / 8))::bigint nullheader,
         max(null_frac) nullfrac,
         ceil(sum(case when a.attbyval
                   then avg_width
                   else (1 - null_frac) * avg_width
                  end))::bigint datawidth
  FROM pg_stats s,pg_class c,pg_namespace n,pg_attribute a
  WHERE s.schemaname = n.nspname AND
        s.tablename = c.relname AND
        s.attname = a.attname AND
        c.relnamespace = n.oid AND
        a.attrelid = c.oid
  GROUP BY schemaname,tablename 
), t2 AS(
SELECT schemaname,
     tablename,
     4 + datawidth + (1 - nullfrac) * 11 + nullfrac * nullheader avgtuplelen
FROM t1
) 
SELECT
 n.nspname "tableSchema", 
 c.relname "tableName",   
 pg_total_relation_size(c.oid) as "tableSize", 
 round(pg_total_relation_size(c.oid) / (select sum(pg_database_size(datname)) as dbsize from pg_database), 4) as tblpercent,
 c.reltuples "tableRows", 
 stat.n_dead_tup "deadTup",
 case when relpages >0 then  round((1- (ceil(reltuples * avgtuplelen / 8076.0)) / relpages)::numeric,2) else 0 end as  bloat_pct
FROM
 pg_class c
 inner join  pg_namespace n on c.relnamespace = n.oid
 left join pg_stat_user_tables stat on c.oid = stat.relid
 left join t2 on n.nspname=t2.schemaname and c.relname=t2.tablename
where 
 n.nspname NOT IN ('pg_catalog', 'information_schema') and n.nspname !~ '^pg_toast'
 and c.relkind in  ('r', 'p')
 order by "tableSize" desc
 limit 100
;