数据仓库服务 GAUSSDB(DWS)-PGXC_GET_TABLE_SKEWNESS:应用示例

时间：2024-09-24 17:39:44

数据仓库服务 GAUSSDB(DWS) 系统视图

应用示例

查询当前数据库中（库中表个数少于1W的场景）所有表的数据倾斜情况：

  
       SELECT * FROM pgxc_get_table_skewness ORDER BY totalsize DESC;
 schemaname |        tablename        | totalsize | avgsize | maxratio | minratio | skewsize | skewratio | skewstddev
------------+-------------------------+-----------+---------+----------+----------+----------+-----------+------------
 dbadmin    | reason                  |    147456 |   49152 |     .333 |     .333 |        0 |     0.000 |          0
 tpcds      | reason_t2               |     73728 |   24576 |     .556 |    0.000 |    40960 |      .556 |      21674
 dbadmin    | reason_bk               |     65536 |   21845 |     .500 |    0.000 |    32768 |      .500 |      18919
 tsearch    | pgweb                   |     49152 |   16384 |     .333 |     .333 |        0 |     0.000 |          0
 dbadmin    | student                 |     40960 |   13653 |     .400 |     .200 |     8192 |      .200 |       4730
 tsearch    | ts_zhparser             |     40960 |   13653 |     .400 |     .200 |     8192 |      .200 |       4730
 dbms_om    | gs_wlm_session_info     |     24576 |    8192 |     .333 |     .333 |        0 |     0.000 |          0
 dbms_om    | gs_wlm_ec_operator_info |     24576 |    8192 |     .333 |     .333 |        0 |     0.000 |          0
 dbms_om    | gs_wlm_operator_info    |     24576 |    8192 |     .333 |     .333 |        0 |     0.000 |          0
(9 rows)

若数据库中表个数非常多（至少大于1W的场景），因PGXC_GET_TABLE_SKEWNESS涉及全库查并计算非常全面的倾斜字段，所以可能会花费比较长的时间（小时级），建议参考PGXC_GET_TABLE_SKEWNESS视图定义，直接使用table_distribution()函数自定义输出，减少输出列进行计算优化，例如：

   
        SELECT schemaname,tablename,max(dnsize) AS maxsize, min(dnsize) AS minsize 
FROM pg_catalog.pg_class c 
INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 
INNER JOIN pg_catalog.table_distribution() s ON s.schemaname = n.nspname AND s.tablename = c.relname 
INNER JOIN pg_catalog.pgxc_class x ON c.oid = x.pcrelid AND x.pclocatortype = 'H' 
GROUP BY schemaname,tablename;