PG/Gauss 查看表大小

TOAST 是“ The Oversized-Attribute Storage Technique ”的缩写，主要用于存储一个大字段的值。要理解 TOAST ，我们要先理解页（ BLOCK ）的概念。在 PG 中，页是数据在文件存储中的基本单位，其大小是固定的且只能在编译期指定，之后无法修改，默认的大小为8 KB 。同时，PG 不允许一行数据跨页存储，那么对于超长的行数据，PG 就会启动 TOAST ，具体就是采用压缩和切片的方式。如果启用了切片，实际数据存储在另一张系统表的多个行中，这张表就叫 TOAST 表，这种存储方式叫行外存储。参考链接1, 参考链接2

查询SQL：

select
	nspname,
	relname,
	pg_size_pretty(pg_total_relation_size(c.oid)) as "表总size=b+c",
	pg_size_pretty(pg_table_size(c.oid)) " b:数据 =b1+b2",
	pg_size_pretty(pg_indexes_size(c.oid)) "c:索引",
	pg_size_pretty(pg_relation_size(c.oid)+pg_relation_size(c.oid,'fsm')+pg_relation_size(c.oid,'vm'))  as "b1:数据1=main+fsm+vm", -- main:数据，主要部分，fsm 自由空间映射  vm:可见性映射
	pg_size_pretty(pg_total_relation_size(c.reltoastrelid)) "b2:数据2:toast",
	case c.relkind
		when 'r' then '表'
		when 'v' then '视图'
		when 'm' then '物化视图'
		when 'i' then '索引'
		when 'S' then '序列'
		when 's' then 'special'
		when 'f' then '外表'
		when 'p' then '分区表' -- gauss没有
		when 'I' then '分区表GLOBAL索引'
		when 't' then 'TOAST表'
		when 'c' then '复核类型'
	end as "Type",
	case c.parttype
		when 'p' then '分区'
		when 'n' then '非分区'
		when 'v' then 'HDFS的Value分区表'
	end "分区",
	case 
		when c.relkind = 'r' or c.relkind = 'p' then pg_get_tabledef(c.oid)
		when c.relkind = 'i' or c.relkind = 'I' then pg_get_indexdef(c.oid)
	end def,
	c.oid,
	pg_size_pretty(pg_relation_size(c.oid)+pg_relation_size(c.oid,'fsm')+pg_relation_size(c.oid,'vm'))||' = '||pg_size_pretty(pg_relation_size(c.oid)) ||' + ' ||pg_size_pretty(pg_relation_size(c.oid,'fsm')) ||' + ' || pg_size_pretty(pg_relation_size(c.oid,'vm'))  as "b1:数据1=main+fsm+vm" -- fsm 自由空间映射  vm:可见性映射
from
	pg_class c,
	pg_namespace n
where
	n.oid = c.relnamespace
	and c.relname in ('xxx_table',
	'xxx_index',
	'pg_toast_xxx')
order by
	nspname,
	relname;

改写SQL

SELECT nspname,
       relname,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS "表总size=b+c",
       pg_size_pretty(pg_table_size(c.oid)) " b:数据 =b1+b2",
       pg_size_pretty(pg_indexes_size(c.oid)) "c:索引",
       pg_size_pretty(pg_relation_size(c.oid) + pg_relation_size(c.oid, 'fsm') + pg_relation_size(c.oid, 'vm')) AS "b1:数据1=main+fsm+vm", -- main:数据，主要部分，fsm 自由空间映射  vm:可见性映射
       pg_size_pretty(pg_total_relation_size(c.reltoastrelid)) "b2:数据2:toast",
       kindname AS "Type",
       CASE c.parttype
         WHEN 'p' THEN
          '分区'
         WHEN 'n' THEN
          '非分区'
         WHEN 'v' THEN
          'HDFS的Value分区表'
       END "分区",
       CASE
         WHEN c.relkind = 'r' OR c.relkind = 'p' THEN
          pg_get_tabledef(c.oid)
         WHEN c.relkind = 'i' OR c.relkind = 'I' THEN
          pg_get_indexdef(c.oid)
       END def,
       c.oid,
       pg_size_pretty(pg_relation_size(c.oid) + pg_relation_size(c.oid, 'fsm') +  pg_relation_size(c.oid, 'vm')) || ' = ' ||  pg_size_pretty(pg_relation_size(c.oid)) || ' + ' ||   pg_size_pretty(pg_relation_size(c.oid, 'fsm')) || ' + ' ||   pg_size_pretty(pg_relation_size(c.oid, 'vm')) AS "b1:数据1=main+fsm+vm" -- fsm 自由空间映射  vm:可见性映射
  FROM pg_class c
  JOIN pg_namespace n
    ON c.relnamespace = n.oid
  LEFT JOIN(VALUES('r', '表'),('v', '视图'),('m', '物化视图'),('i', '索引'),('S', '序列'),('s', 'special'),('f', '外表'),('p', '分区表'),('I', '分区表GLOBAL索引'),('t', 'TOAST表'),('c', '复核类型')) AS kind(kind, kindname)
    ON kind = relkind
 WHERE c.relname IN ('xxx_table', 'xxx_index', 'pg_toast_xxx')
 ORDER BY nspname, relname;