存在子分区时，统计分区表大小
pg数据库中存在子分区的时候，如果要统计分区表大小就有点麻烦，因为存在多层继承关系，直接一个sql好像不太容易统计出来。干脆写一个函数试试。
主要思路：
1）在pg_class表中把分区表父表查询出来
2）loop父表oid,使用递归查询在pg_inherits表中把对应分区表及子分区表oid查询出来,并使用函数pg_table_size(oid)计算大小，把统计后结果插入临时表
3）按表大小展示统计结果
结果验证：
分区表继承关系
- test_p：
-- test_p1
-- test_p2
-- test_p3:
-- test_sp31
对比函数查询结果
分区表test_p(图中红色标记)不包含索引大小为3952=(3568+384)，
包含索引大小为6424=(3568+384)+(240+2224+8)，大小一致
普通表test4(图中蓝色标记)，大小一致
另附function：
CREATE OR REPLACE FUNCTION public.list_all_table_size()
RETURNS table(table_name name,schema_name name,table_type varchar,only_table_size text,total_table_size text)
LANGUAGE plpgsql
AS $function$
DECLARE
table_id oid;
table_name name;
schema_name name;
table_type varchar;
table_size bigint; --pg_table_size
table_total_size bigint; --pg_total_relation_size
sql varchar;
BEGIN
--create temp table
drop table if exists all_table_size_temp;
create temporary table all_table_size_temp(tab_id oid,tab_name name,sch_name name,tab_type varchar,tab_size bigint,total_tab_size bigint);
--insert simple table size to temp
insert into all_table_size_temp select a.oid,a.relname,b.nspname,'table' as type,pg_table_size(a.oid),pg_total_relation_size(a.oid) from pg_class a left join pg_namespace b on a.relnamespace=b.oid where a.relkind = 'r' and a.relispartition='f' and b.nspname not like 'pg_%' and b.nspname!='information_schema';
--loop get partition table parent table
for table_id,table_name,schema_name,table_type in select a.oid,a.relname,b.nspname,'partition_table' as type from pg_class a left join pg_namespace b on a.relnamespace=b.oid where relkind='p' and relispartition='f'
loop
--sum partition table size
sql := 'with RECURSIVE inherit_list (inhrelid,inhparent) as ( select inhrelid,inhparent from pg_inherits where inhparent='''||table_id||''' union all select c.inhrelid,c.inhparent from pg_inherits c,inherit_list d where d.inhrelid=c.inhparent ) select sum(pg_table_size(inhrelid)),sum(pg_total_relation_size(inhrelid)) from inherit_list';
execute sql into table_size,table_total_size;
--insert partition table size to temp
insert into all_table_size_temp values (table_id,table_name,schema_name,table_type,table_size,table_total_size);
end loop;
return query select temp.tab_name,temp.sch_name,temp.tab_type,pg_size_pretty(temp.tab_size),pg_size_pretty(temp.total_tab_size) from all_table_size_temp temp order by tab_size desc;
end;
$function$;
-------------------------------------2021-12-10更新---------------------------------------
今天发现pg12以后提供了函数pg_partition_tree可以列出所有分区表包括子分区，另外发现还可以通过 \dP+ 展示分区表大小，通过查看源码src/bin/psql/describe.c找到 \dP 实现方式，12版本前使用递归查询，12版本后使用函数pg_partition_tree。
所以，照葫芦画瓢对语句进行了改写，语句中用到了LATERAL子查询语法，解释如下图。在此场景中使用时的理解就是：把每一个分区表根表oid传入递归查询中，分别查询出对应分区表，并汇总分区大小；不使用LATERAL直接关联查询时，递归查询的结果中二级分区的父表是一级分区，并不是根表，无法按照根表分组求和统计大小。
--------pg12以前版本，用下面sql
select r.relname,
r.nspname,
r.type,
pg_size_pretty(r.only_table) as only_table_size,
pg_size_pretty(r.total_table) as total_table_size
from (select a.oid,
a.relname,
b.nspname,
'partition table' as type,
r.only_table,
r.total_table
from pg_class a
left join pg_namespace b
on a.relnamespace = b.oid
left join LATERAL(with RECURSIVE inherit_list(inhrelid, inhparent) as
(select inhrelid,inhparent from pg_inherits where inhparent = a.oid
union all
select c.inhrelid,c.inhparent from pg_inherits c, inherit_list d where d.inhrelid = c.inhparent)
select sum(pg_table_size(inhrelid)) as only_table, sum(pg_total_relation_size(inhrelid)) as total_table from inherit_list) r
on true
where relkind = 'p'
and relispartition = 'f'
union all
select a.oid,
a.relname,
b.nspname,
'table' as type,
pg_table_size(a.oid) as only_table,
pg_total_relation_size(a.oid) as total_table
from pg_class a
left join pg_namespace b
on a.relnamespace = b.oid
where a.relkind = 'r'
and a.relispartition = 'f'
and b.nspname not like 'pg_%'
and b.nspname != 'information_schema') r
order by r.total_table desc;
-------pg12以后版本，用下面sql
select r.relname,
r.nspname,
r.type,
pg_size_pretty(r.only_table) as only_table_size,
pg_size_pretty(r.total_table) as total_table_size
from (select a.oid,
a.relname,
c.nspname,
'partition table' as type,
sum(pg_table_size(b.relid)) as only_table,
sum(pg_total_relation_size(b.relid)) as total_table
from pg_class a left join pg_namespace c on a.relnamespace = c.oid left join LATERAL pg_partition_tree(a.oid) b on true
where relkind = 'p'
and relispartition = 'f'
group by a.oid, a.relname, c.nspname, type
union all
select a.oid,
a.relname,
b.nspname,
'table' as type,
pg_table_size(a.oid) as only_table,
pg_total_relation_size(a.oid) as total_table
from pg_class a
left join pg_namespace b
on a.relnamespace = b.oid
where a.relkind = 'r'
and a.relispartition = 'f'
and b.nspname not like 'pg_%'
and b.nspname != 'information_schema') r
order by r.total_table desc;
