PG数据库如何检查膨胀的表和索引

一 检查表膨胀

select

     to_char(now(),'yyyy-mm-dd hh24:mi:ss') "time"

    ,current_database() current_database 

    ,relname as "table_name(表名)"

    ,schemaname as "schema_name(模式名)"

    ,pg_size_pretty(pg_relation_size('"'||schemaname|| '"."'||relname||'"')) as "table_size(表大小)"

    ,n_dead_tup as "n_dead_tup(无效记录数)"

    ,n_live_tup as "n_live_tup(有效记录数)"

    ,to_char(round(n_dead_tup*1.0/(n_live_tup+n_dead_tup)*100,2),'fm990.00') as "dead_rate(无效记录比例%)"

from

    pg_stat_all_tables

where n_live_tup+n_dead_tup <> 0 ;

正常不存在表膨胀，因为有自动清理垃圾的进程。

异常处理，对膨胀表做vacuum analyze操作。

二 检查索引膨胀

select  to_char(now(),'yyyy-mm-dd hh24:mi:ss') "time",  current_database() AS db, schemaname, tablename,bs, reltuples::bigint AS tups, relpages::bigint AS pages, otta,  ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR sml.relpages=otta THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,  CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,  CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,  CASE WHEN relpages < otta THEN $$0 bytes$$::text ELSE (bs*(relpages-otta))::bigint || $$ bytes$$ END AS wastedsize,  iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta,  ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,  CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,  CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,  CASE WHEN ipages < iotta THEN $$0 bytes$$ ELSE (bs*(ipages-iotta))::bigint || $$ bytes$$ END AS wastedisize,  CASE WHEN relpages < otta THEN    CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta::bigint) END    ELSE CASE WHEN ipages < iotta THEN bs*(relpages-otta::bigint)      ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint) END  END AS totalwastedbytesFROM (  SELECT    nn.nspname AS schemaname,    cc.relname AS tablename,    COALESCE(cc.reltuples,0) AS reltuples,    COALESCE(cc.relpages,0) AS relpages,    COALESCE(bs,0) AS bs,    COALESCE(CEIL((cc.reltuples*((datahdr+ma-      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,    COALESCE(c2.relname,$$?$$) AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,    COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols  FROM     pg_class cc  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> $$information_schema$$  LEFT JOIN  (