Greenplum查看表/库大小、进程、表膨胀处理（sql语句）

查询表相关进程

select a.pid
from pg_locks a,
pg_class b,
pg_stat_activity c
where a.relation = b.oid
and a.pid = c.pid
and b.relname like '%fact_seres_lead%';

查看数据库进程

select * from pg_stat_activity ORDER BY query_start;

查看数据库锁进程

select * from pg_stat_activity where waiting= 't';

杀进程

select pg_cancel_backend(线程id)   kill掉指定的查询语句，这个函数只能 kill  Select 查询，而updae,delete DML不生效

select  pg_terminate_backend(pid int)   可以kill 各种DML(SELECT,UPDATE,DELETE,DROP)操作，

查询表分布键

  SELECT att.nspname,att.relname,string_agg (a.attname, ',') attby 
  FROM 
  (
   SELECT c.oid,n.nspname,c.relname,regexp_split_to_table (array_to_string (d.distkey, ' '),' ')::int as attnu
   FROM gp_distribution_policy d 
   LEFT JOIN pg_class c ON c.oid = d.localoid 
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace  
   WHERE c.oid = '表名'::regclass
  ) att
  LEFT JOIN pg_attribute a ON a.attrelid = att.oid
  WHERE att.attnu = a.attnum
  GROUP BY 1,2;

表膨胀处理

vacuum full  ANALYZE 表名

查询数据条数

SELECT count(1)  from 表名

查询表和索引大小

select pg_size_pretty(pg_total_relation_size('表名'));

查看数据库物理大小

select pg_size_pretty(pg_database_size('库'));

查看所有数据库大小

select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;

查询表分布是否均匀

select gp_segment_id,count(*) from 表名 group by gp_segment_id;

重分布（也可起到处理表膨胀的作用）

ALTER TABLE 表名 SET WITH (REORGANIZE=true) DISTRIBUTED BY (分布键)

删除外部表

drop external table  ext_gpload_reusable_b8ff23e1_3e39_11ed_b47b_005056937fb4