文章目录环境文档用途详细信息环境系统平台Linux x86-64 Red Hat Enterprise Linux 7版本4.5,9.0文档用途本文档介绍如何通过一段 SQL 脚本查询当前连接数据库current_database()下表和索引的 TOP 膨胀信息用于日常巡检脚本适用版本HGDB 4.5 系列HGDB 9.0 系列详细信息SQL脚本SELECTcurrent_database()ASdb,schemaname,tablename,reltuples::bigintAStups,relpages::bigintASpages,otta,ROUND(CASEWHENotta0ORsml.relpages0THEN0.0WHENsml.relpagesottaTHEN1.0ELSEsml.relpages/otta::numericEND,1)AStbloat,CASEWHENrelpagesottaTHEN0ELSErelpages::bigint-ottaENDASwastedpages,CASEWHENrelpagesottaTHEN0ELSEbs*(sml.relpages-otta)::bigintENDASwastedbytes,CASEWHENrelpagesottaTHEN$$0bytes$$::textELSE(bs*(relpages-otta))::bigint||$$ bytes$$ENDASwastedsize,iname,ituples::bigintASitups,ipages::bigintASipages,iotta,ROUND(CASEWHENiotta0ORipages0THEN0.0WHENipagesiottaTHEN1.0ELSEipages/iotta::numericEND,1)ASibloat,CASEWHENipagesiottaTHEN0ELSEipages::bigint-iottaENDASwastedipages,CASEWHENipagesiottaTHEN0ELSEbs*(ipages-iotta)ENDASwastedibytes,CASEWHENipagesiottaTHEN$$0bytes$$ELSE(bs*(ipages-iotta))::bigint||$$ bytes$$ENDASwastedisize,CASEWHENrelpagesottaTHENCASEWHENipagesiottaTHEN0ELSEbs*(ipages-iotta::bigint)ENDELSECASEWHENipagesiottaTHENbs*(relpages-otta::bigint)ELSEbs*(relpages-otta::bigintipages-iotta::bigint)ENDENDAStotalwastedbytesFROM(SELECTnn.nspnameASschemaname,cc.relnameAStablename,COALESCE(cc.reltuples,0)ASreltuples,COALESCE(cc.relpages,0)ASrelpages,COALESCE(bs,0)ASbs,COALESCE(CEIL((cc.reltuples*((datahdrma-(CASEWHENdatahdr%ma0THENmaELSEdatahdr%maEND))nullhdr24))/(bs-20::float)),0)ASotta,COALESCE(c2.relname,$$?$$)ASiname,COALESCE(c2.reltuples,0)ASituples,COALESCE(c2.relpages,0)ASipages,COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0)ASiottaFROMpg_class ccJOINpg_namespace nnONcc.relnamespacenn.oidANDnn.nspname$$information_schema$$LEFTJOIN(SELECTma,bs,foo.nspname,foo.relname,(datawidth(hdrma-(CASEWHENhdr%ma0THENmaELSEhdr%maEND)))::numericASdatahdr,(maxfracsum*(nullhdrma-(CASEWHENnullhdr%ma0THENmaELSEnullhdr%maEND)))ASnullhdr2FROM(SELECTns.nspname,tbl.relname,hdr,ma,bs,SUM((1-coalesce(null_frac,0))*coalesce(avg_width,2048))ASdatawidth,MAX(coalesce(null_frac,0))ASmaxfracsum,hdr(SELECT1count(*)/8FROMpg_stats s2WHEREnull_frac0ANDs2.schemanamens.nspnameANDs2.tablenametbl.relname)ASnullhdrFROMpg_attribute attJOINpg_class tblONatt.attrelidtbl.oidJOINpg_namespace nsONns.oidtbl.relnamespaceLEFTJOINpg_stats sONs.schemanamens.nspnameANDs.tablenametbl.relnameANDs.inheritedfalseANDs.attnameatt.attname,(SELECT(SELECTcurrent_setting($$block_size$$)::numeric)ASbs,CASEWHENSUBSTRING(SPLIT_PART(v,$$ $$,2)FROM$$#[0-9].[0-9]#%$$ FOR $$#$$)IN($$8.0$$,$$8.1$$,$$8.2$$)THEN27ELSE23ENDAShdr,CASEWHENv~$$mingw32$$ORv~$$64-bit$$THEN8ELSE4ENDASmaFROM(SELECTversion()ASv)ASfoo)ASconstantsWHEREatt.attnum0ANDtbl.relkind$$r$$GROUPBY1,2,3,4,5)ASfoo)ASrsONcc.relnamers.relnameANDnn.nspnamers.nspnameLEFTJOINpg_index iONindrelidcc.oidLEFTJOINpg_class c2ONc2.oidi.indexrelid)ASsmlORDERBYwastedbytesDESCLIMIT5;测试结果-[RECORD1]--------------------------------------db|highgo schemaname|pg_catalog tablename|pg_depend tups|11840pages|100otta|82tbloat|1.2wastedpages|18wastedbytes|147456wastedsize|147456bytes iname|pg_depend_depender_index itups|11840ipages|57iotta|54ibloat|1.1wastedipages|3wastedibytes|24576wastedisize|24576bytes totalwastedbytes|172032结果说明该脚本查询的是当前连接数据库下对象的膨胀信息由 current_database() 决定。该脚本默认按「表浪费空间字节数wastedbytes」降序展示 TOP 5如需调整统计数量修改末尾 LIMIT 值即可。若希望按膨胀率排名可将 ORDER BY wastedbytes DESC 改为 ORDER BY tbloat DESC 或 ORDER BY totalwastedbytes DESC。itups索引的有效元组数索引条目数约等于有效行数已排除死元组对应的索引项。pages表在 pg_class 中记录的页面估算值relpages由 VACUUM / ANALYZE 更新反映表当前占用的页面数即大致物理磁盘空间单个 page 大小由 block_size 参数决定默认 8 KB。otta理论上表最紧凑时占用的页面数基于平均行宽与填充率的估算近似等于去除死元组后的理想页数。tbloat表膨胀率 pages / otta1.0 即没有膨胀、没有碎片1.2 即为膨胀了约 20%。wastedpages浪费的页面数 pages - otta。wastedbytes浪费的字节数 (pages - otta) * block_size以上例 pg_depend 计算为 (100 - 82) * 8192 147456 字节 ≈ 144 KB。iname该表对应索引的膨胀信息iotta / ibloat / wastedipages 等为其索引侧等效指标。totalwastedbytes表与索引一共浪费的磁盘空间wastedbytes wastedibytes。