Greenplum关于表膨胀，数据倾斜

Greenplum关于表膨胀，数据倾斜

检查表膨胀

mydb=# select *  from gp_toolkit.gp_bloat_diag limit 3;
 bdirelid | bdinspname |    bdirelname    | bdirelpages | bdiexppages |                bdidiag                
----------+------------+------------------+-------------+-------------+---------------------------------------
     6040 | pg_catalog | pg_exttable      |        7807 |          32 | significant amount of bloat suspected
     5094 | pg_catalog | gp_relation_node |       26317 |         211 | significant amount of bloat suspected

#bdidiag：bloat诊断结果 (比率1到3表示：no bloat；比率从4到10表示：moderate bloat；比率从ratio大于10表示：significantamount of bloat suspected)
bdirelpages:磁盘上的实际页数。
bdiexppages:期望的页数
或者直接定义到表

select  * from gp_toolkit.gp_bloat_diag where  bdirelname ='twb_list_cm_lxrkzxx';     

查看重度膨胀的表的数量

select count(*) from gp_toolkit.gp_bloat_diag where  bdidiag='significant amount of bloat suspected';

查看重度膨胀的表有哪些

select bdinspname||'.'||bdirelname ,bdirelpages,bdiexppages from gp_toolkit.gp_bloat_diag where  bdidiag='significant amount of bloat suspected' order by bdirelpages desc limit 30 ;

包含膨胀倍数（按膨胀倍数排序）

`select bdirelid ,bdinspname ,bdirelname ,bdirelpages*32/1024/1024 realsize_G,bdiexppages*32/1024/1024 expectsize_G, bdirelpages/bdiexppages as expansion from gp_toolkit.gp_bloat_diag where  bdidiag='significant amount of bloat suspected' order by expansion desc limit 30;` 

按表实际大小排序

select bdirelid ,bdinspname||'.'||bdirelname ,bdirelpages*32/1024/1024 realsize_G,bdiexppages*32/1024/1024 expectsize_G, bdirelpages/bdiexppages as expansion from gp_toolkit.gp_bloat_diag where  bdidiag='significant amount of bloat suspected' order by bdirelpages desc limit 30;

中度膨胀的表

select bdirelid ,bdinspname ,bdirelname ,bdirelpages*32/1024/1024 realsize_G,bdiexppages*32/1024/1024 expectsize_G, bdirelpages/bdiexppages as expansion from gp_toolkit.gp_bloat_diag where  bdidiag='moderate amount of bloat suspected' and 
bdinspname not like 'pg_catalog' order by bdirelpages limit 50;

除去系统表，临时表，外部表，错误表膨胀的表

select bdirelid ,bdinspname ||'.'||bdirelname ,bdirelpages*32/1024/1024 realsize_G,bdiexppages*32/1024/1024 expectsize_G, bdirelpages/bdiexppages as expansion from gp_toolkit.gp_bloat_diag where bdinspname not like '%pg_catalog%'  and bdirelname not like '%_tmp%'  and bdirelname not like 'tmp%'  and  bdirelname not like 'temp%'  and bdirelname not like 'err_%'  and bdirelname not like 'ext_%' and bdirelname not