# 统计表膨胀率


# 统计表膨胀率

```
WITH bloat AS (
	WITH t1 AS (
		SELECT
			schemaname,
			tablename,
			( 23 + CEIL ( COUNT ( * ) >> 3 ) ) :: BIGINT nullheader,
			MAX ( null_frac ) nullfrac,
			CEIL ( SUM ( ( 1 - null_frac ) * avg_width ) ) :: BIGINT datawidth 
		FROM
			pg_stats 
			--where schemaname='form'
		GROUP BY
			schemaname,
			tablename 
		),
		t2 AS (
		SELECT
			schemaname,
			tablename,
			( datawidth + 8 - ( CASE WHEN datawidth % 8 = 0 THEN 8 ELSE datawidth % 8 END ) ) -- avg data len
			+ ( 1 - nullfrac ) * 24 + nullfrac * ( nullheader + 8 - ( CASE WHEN nullheader % 8 = 0 THEN 8 ELSE nullheader % 8 END ) ) avgtuplelen 
FROM
	t1 
	) SELECT C
	.oid,
	avgtuplelen
FROM
	t2 T,
	pg_class C,
	pg_namespace n 
WHERE
	T.schemaname = n.nspname 
	AND C.relname = T.tablename 
	AND C.relnamespace = n.oid 
	AND relpages > 100 
and c.relname in 
	(	select relname from (
select relname , reltuples::numeric as 记录数
from pg_class
where relkind = 'r'
)t order by 记录数 desc limit 50)
	),
	parti AS (
	SELECT
		parti.inhparent,
		parti.inhrelid,
		stat.n_dead_tup,
		c.relpages,
		c.reltuples
	FROM
		(
		SELECT
			inhparent,
			inhrelid
		FROM
			pg_inherits inh UNION ALL
			( SELECT b.inhparent AS grandpa, A.inhrelid me FROM pg_inherits A INNER JOIN pg_inherits b ON A.inhparent = b.inhrelid ) 
		) AS parti
		inner join pg_class c on parti.inhrelid = c.oid
		LEFT JOIN pg_stat_user_tables stat ON parti.inhrelid = stat.relid 
	 where  c.relname in 
	(	select relname from (
select relname , reltuples::numeric as 记录数
from pg_class
where relkind = 'r'
)t order by 记录数 desc limit 50)
	) SELECT
	 n.nspname "tableSchema",
	C.relname "tableName",
	'vacuum full '||C.relname||';' "sql",
	round(SUM ( pg_total_relation_size ( COALESCE ( parti1.inhrelid, C.oid ) )+0.0 )/1024/1024/1024,2) AS "表大小 GB", --表及其子分区、索引大小，包含索引
	round(
		SUM ( pg_total_relation_size ( COALESCE ( parti1.inhrelid, C.oid ) ) ) / ( SELECT SUM ( pg_database_size ( datname ) ) AS dbsize FROM pg_database ),
		4 
	) AS "占数据文件比例%",-- 表占数据文件比例	 
	to_char(COALESCE ( MAX ( c.reltuples ), 0 ) + COALESCE ( SUM ( COALESCE ( parti1.reltuples, 0 ) ), 0 ),'9999999999') AS "元组(记录行数)",
CASE
		WHEN (coalesce(max( bloat.avgtuplelen), 0) = 0  and coalesce(avg( parti1.avgtuplelen), 0) = 0) or  MAX ( COALESCE (c.relpages , 0 ) ) + SUM ( COALESCE ( parti1.relpages, 0 ))  = 0 THEN
		0 ELSE  
		round((1 - CEIL ( (MAX ( COALESCE ( c.reltuples, 0 ))  + SUM ( COALESCE ( parti1.reltuples, 0 ))) * 
		(case when max( parti1.avgtuplelen) is not null then AVG( parti1.avgtuplelen)  else max ( COALESCE ( bloat.avgtuplelen, 0 ) ) end  ) / 8168) / 
		(  MAX ( COALESCE (c.relpages , 0 ) ) + SUM ( COALESCE ( parti1.relpages, 0 )) ))::NUMERIC, 2)*100
	END AS "表膨胀率%",
	COALESCE ( MAX ( stat.n_dead_tup ), 0 ) + COALESCE ( SUM ( COALESCE ( parti1.n_dead_tup, 0 ) ), 0 ) AS "死元组(行)" --表内死数据行
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 (select p.*, b.* from parti p left join bloat as b  ON p.inhrelid = b.oid) as parti1 on c.oid = parti1.inhparent
	LEFT JOIN bloat ON C.oid = bloat.oid 
WHERE
	n.nspname NOT IN ( 'pg_catalog', 'information_schema' ) 
	AND n.nspname !~ '^pg_toast' 
	AND C.relkind IN ( 'r', 'p' ) 
	AND C.oid NOT IN ( SELECT inhrelid FROM pg_inherits ) 
	and c.relname in 
	(	select relname from (
select relname , reltuples::numeric as 记录数,
'truncate table '||relname||' ;'
from pg_class
where relkind = 'r'
)t order by 记录数 desc limit 50)
GROUP BY
	C.relname,
	n.nspname 
ORDER BY
--	"表大小 GB" DESC
"表膨胀率%" DESC
 limit 100
	

```