【gp数据库】查询GP中某张表的分布键信息

本文参考
https://blog.csdn.net/sinat_35630008/article/details/82192574?spm=1001.2101.3001.6650.20&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-20.no_search_link&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-20.no_search_link
可能与参考链接数据库版本不同，修改了几个字段：
我用的gp数据库版本为9.4.24

查询某张表分布键的语句：

SELECT
	aaa.oid,
	aaa.nspname AS "模式名",
	aaa.relname AS "表名",
	aaa.table_comment AS "中文表名",
	ccc.attname AS "分布键" 
FROM
	(
SELECT
	aa.oid,
	aa.relname,
	obj_description ( aa.oid ) AS table_comment,
	bb.localoid,
	bb.distkey,
	regexp_split_to_table( array_to_string( bb.distkey, ' ' ), ' ' ) att,
	dd.nspname 
FROM
	pg_class aa
	LEFT JOIN pg_catalog.gp_distribution_policy bb ON bb.localoid = aa.oid
	LEFT JOIN pg_namespace dd ON dd.oid = aa.relnamespace --模式
	LEFT JOIN pg_inherits hh ON aa.oid = hh.inhrelid --继承表
WHERE
	dd.nspname = 'u_dws_dev' -- 替换成需要的模式schema
	AND hh.inhrelid IS NULL 
	) aaa
	LEFT JOIN pg_attribute ccc ON ccc.attrelid = aaa.oid 
	AND CAST ( ccc.attnum AS TEXT ) = aaa.att 
WHERE
	ccc.attnum > 0 
AND aaa.relname = 'opn_label_factory_user_group' --需要查询的表名

--或者下面语句也行--
	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 = 'u_dws.opn_label_factory_user_group'::regclass
  ) att
  LEFT JOIN pg_attribute a ON a.attrelid = att.oid
  WHERE att.attnu = a.attnum
  GROUP BY 1,2;

查询结果如下：

GP数据库其他命令：

	查看GP表大小：select pg_size_pretty(pg_relation_size('u_dws_dev.opn_label_factory_user_group_list')) ;
	查看数据库的连接数：select count(*) from pg_stat_activity;
	展示最大连接数： show max connections;
	查看某表是否分布均匀；select gp_segment_id,count(*) from fact_tablegroup by gp_segment_id;
	清空表：truncate table u_dws_dev.opn_label_factory_index  
	自增ID从1开始：TRUNCATE u_dws_dev.opn_label_factory_index RESTART IDENTITY;  
	更改表中列的大小：alter table rtime_interface_sql  MODIFY COLUMN  config_sql varchar(12000)
	增加列：alter table u_dws.opn_label_factory_user_group add column group_desc varchar(500) ;
	关闭GP的扫描顺序，GP默认顺序扫描，开启索引扫描：set enable_seqscan=Off；
	创建索引：create index record_id_01 on test using btree(record_id);
	删除索引：drop inde if exists bitmap01;