--跟踪倾斜率

select schemaname,tablename

,(100-100*avg(dnsize)/nullif(max(dnsize),0))::numeric(5,2) "New_Skew(%)"

,(max(dnsize)-min(dnsize)) skewsize

,(((max(dnsize)-min(dnsize))/sum(dnsize))*100)::numeric(6,3) skewratio

,stddev(dnsize)::numeric(1000,0) skewstddev

from table_distribution('public','wzy_rows') a

group by 1,2 order by 3 desc;

Select schemaname,tablename,

(100-100*avg(dnsize)/nullif(max(dnsize),0))::numeric(5,2)   "New_Skew(%)"

,max(dnsize)*count(1)-sum(dnsize)   "WastedPerm(B)"

From   table_distribution('public','wzy_rows') a

group   by 1,2 order by 3 desc;

select * from

(

select   schemaname,tablename,(100-100*avg(dnsize)/nullif(max(dnsize),0))::numeric(5,2)   Skew

,max(dnsize)*count(1)-sum(dnsize)   WastedPerm

,Table_RowSkew(schemaname||'.'||tablename)   as Row_Skew

 From table_distribution() a   group by 1,2

having Skew>30 ) 

where Row_Skew>30 order by WastedPerm desc

--提取表名

/* （可选）

空间分布：

select * from table_distribution('db1','tb1');

记录分布:

select * from table_skewness('db1.tb1');

*/

--1.查看表字段信息

Select pg_get_tabledef('db1.tb1');

--或

select tbl.relname,col.attname,col.attnum

from pg_attribute col

inner JOIN pg_class tbl

ON col.attrelid = tbl.oid

where col.attnum>0

and tbl.oid='db1.tb1'::regclass

;

--2.重新选择分布列，查看记录倾斜情况

with tmp as (

select * from table_skewness('db1.tb1','colA,ColB')

)

select tmp.*,b.skew::text||'%' skew

from tmp,

(select (100-100*(avg(num::bigint)/max(num::bigint)))

  ::dec(5,2) skew

  from tmp ) b

;

--3.重建表

create table db1.tmp_tb1 DISTRIBUTE BY HASH (colA,colB)

as table db1.tb1  with data;

--4.验证空间倾斜情况

with tmp as (

select * from table_distribution('db1','tb1')

)

select tmp.*,b.skew::text||'%' skew

from tmp,

(select (100-100*(avg(dnsize)/max(dnsize)))

  ::dec(5,2) skew

  from tmp ) b

;

--5.对换表名，旧名事后或人工或程序化删除

alter table db1.tb1 rename to tmp_tb1_20200612;

alter table db1.tmp_tb1 rename to tb1;

CREATE OR REPLACE FUNCTION Table_RowSkew(_table_name   text )
 RETURNS numeric
 LANGUAGE plpgsql
 NOT FENCED NOT SHIPPABLE
AS $function$
DECLARE
  row_data record;
  row_name record;
  query_str text;
  query_str_nodes text;
  cnt bigint;
  total_rows bigint;
  total_cnt bigint := 0;
  max_row bigint := 0;
 
  BEGIN
        -- total records
        EXECUTE 'SELECT count(1) FROM ' ||   _table_name
                     INTO total_rows;
        IF total_rows = 0 THEN
            RETURN 0;
        END IF;
        
        --Get the node names
        query_str_nodes := 'SELECT node_name   FROM pgxc_node where node_type IN (''C'', ''D'') AND oid::text in ' ||
        '(SELECT   regexp_split_to_table(nodeoids::text, '' '') FROM pgxc_class WHERE   pcrelid=''' || _table_name || '''::regclass::oid)';
 
        FOR row_name IN   EXECUTE(query_str_nodes) LOOP
            query_str := 'EXECUTE DIRECT ON   (' || row_name.node_name || ') ''select count(1) as count1 from ' || $1 ||   '''';
            
            EXECUTE(query_str) INTO cnt;
            IF max_row < cnt THEN
                max_row := cnt;
            END IF;
            total_cnt := total_cnt + 1;
        END LOOP;
 
            RETURN 100 - 100 * (total_rows/total_cnt)/max_row;
         END; $function$