1、数据倾斜查询
select schemaname,tablename,sum(dnsize)/1024^3 dnsize_gb,(max(dnsize) - avg(dnsize))*100/nullif(max(dnsize),0) skewness_factor
from (
select schemaname
,tablename
,(regexp_split_to_array(tbl_dis,'[\,\(\)]+'))[4]::bigint as vprocname
,(regexp_split_to_array(tbl_dis,'[\,\(\)]+'))[5]::bigint as dnsize
from (
select nspname as schemaname
,relname as tablename
,table_distribution(nspname,relname)::text as tbl_dis
from pg_class a
inner join pg_namespace b
on a.relnamespace = b.oid
and a.relkind = 'r'
and b.oid not in (100)
)
)
where schemaname= 'yace' group by 1,2 order by 3 desc;
查询结果如下:
建议倾斜度超过10%就需要调整分布列。
2、造数函数
insert into lee select generate_series(1,10000);
造数10000条。
3、查询统计信息
select count(*) from pg_stats where tablename = 't_sys_org'
4、统计信息收集
ANALYZE lee;