9个GaussDB常用的对象语句

摘要：本文介绍了9个GaussDB常用的对象语句，希望对大家有帮助。

本文分享自华为云社区《GaussDB对象相关语句》，作者：酷哥。
1. 常用函数

1. pg_database_size() -- 数据库使用的磁盘空间。
2. pg_table_size() -- 表使用的磁盘空间。
3. pg_total_relation_size() -- 表和索引共使用的磁盘空间。
4. pg_indexes_size() -- 索引使用的磁盘空间。

复制代码

2. 常用系统表

1. pg_class -- 存储数据库对象信息及其之间的关系。
2. pg_index -- 存储索引的一部分信息，其他的信息大多数在PG_CLASS中。    pg_namespace -- 存储schema相关的信息。
3. pg_database -- 存储数据库相关的信息。

复制代码

3. 常用视图

1. pg_stat_user_tables -- 显示所有用户自定义普通表和toast表的状态信息。
2. pg_stat_user_indexes -- 显示数据库中用户自定义普通表和toast表的索引状态信息。

复制代码

4. 常用语句

4.1查询库大小

1. select datname, pg_database_size(datname), pg_database_size(datname)/1024/1024/1024 as "dataSize_GB" FROM pg_database where datname not in ('template1', 'template0');

复制代码

4.2查看schema的所有者

1. -- pg_user这个视图只有sysadmin用户有权限查，普通用户无法查询。
2. SELECT s.nspname schema_name,u.usename schema_owner FROM pg_namespace s, pg_user u WHERE nspname = '$schema_name' AND s.nspowner = u.usesysid;

复制代码

4.3获取表结构

1. set current_schema to $schema;
2. select pg_get_tabledef('$table_name');
3. 或
4. select pg_get_tabledef('$schema_name.$table_name');

复制代码

示例：

1. select pg_get_tabledef('testschema.t1');
2. pg_get_tabledef
3. ----------------------------------------
4. SET search_path = testschema; +
5. CREATE TABLE t1 ( +
6. id integer, +
7. name character varying(15) +
8. ) +
9. WITH (orientation=row, compression=no)+
10. DISTRIBUTE BY HASH(id) +
11. TO GROUP group_version1;
12. (1 row)

复制代码

4.4查询表大小

1. -- 查询单个确定表的大小。
2. select pg_table_size('$schema_name.$table_name'); -- 不包含索引，单位B
3. select pg_total_relation_size('$schema_name.$table_name'); -- 包含索引，单位B
4. -- 查询连接的数据中，所有用户表的大小
5. SELECT schemaname,relname, pg_total_relation_size(concat(schemaname,'.',relname))/1024/1024/1024 table_size_GB FROM PG_STAT_USER_TABLES ORDER BY 3 DESC; -- 包含索引
6. SELECT schemaname,relname, pg_table_size(concat(schemaname,'.',relname))/1024/1024/1024 table_size_GB FROM PG_STAT_USER_TABLES ORDER BY 3 DESC; -- 不包含索引"
7. SELECT table_name,pg_size_pretty(table_size) AS table_size,pg_size_pretty(indexes_size) AS indexes_size,pg_size_pretty(total_size) AS total_size FROM (SELECT table_name,pg_table_size(table_name) AS table_size,pg_indexes_size(table_name) AS indexes_size,pg_total_relation_size(table_name) AS total_size FROM (SELECT concat(table_schema,concat('.',table_name)) AS table_name FROM information_schema.tables where table_schema ilike '$schema_name') AS all_tables ORDER BY total_size DESC) AS pretty_sizes;

复制代码

示例

1. -- 查询单个确定表的大小。
2. select pg_table_size('testschema.t1');
3. pg_table_size
4. ---------------
5. 17801216
6. (1 row)
7. -- 查询连接的数据中，所有用户表的大小
8. SELECT schemaname,relname, pg_total_relation_size(concat(schemaname,'.',relname))/1024/1024/1024 table_size_GB FROM PG_STAT_USER_TABLES ORDER BY 3 DESC; -- 包含索引
9. schemaname | relname | table_size_gb
10. ------------+--------------+--------------------
11. testschema | t_ran2 | 2.288818359375e-05
12. testschema | t_ran1 | 1.52587890625e-05
13. testschema | t_ran3 | 1.52587890625e-05
14. -- 需要指定schema
15. SELECT table_name,pg_size_pretty(table_size) AS table_size,pg_size_pretty(indexes_size) AS indexes_size,pg_size_pretty(total_size) AS total_size FROM (SELECT table_name,pg_table_size(table_name) AS table_size,pg_indexes_size(table_name) AS indexes_size,pg_total_relation_size(table_name) AS total_size FROM (SELECT concat(table_schema,concat('.',table_name)) AS table_name FROM information_schema.tables where table_schema ilike 'testschema') AS all_tables ORDER BY total_size DESC) AS pretty_sizes;
16. table_name | table_size | indexes_size | total_size
17. -------------------------+------------+--------------+------------
18. testschema.t_ran2 | 24 kB | 0 bytes | 24 kB
19. testschema.t_ran3 | 16 kB | 0 bytes | 16 kB
20. testschema.t_ran1 | 16 kB | 0 bytes | 16 kB

复制代码

4.5查看表的统计信息

1. -- 查看指定schema和table的统计信息。
2. select * from pg_stat_user_tables where schemaname = '$schema_name' and relname = '$table_name';
3. -- 查询全库的表的活跃元组数、死元组数及死元组占比。
4. select schemaname, relname, n_live_tup, n_dead_tup, (n_dead_tup/(n_live_tup+1)) as dead_rating from pg_stat_user_tables order by rating desc,n_dead_tup desc limit 30;
5. -- 查看表大小及活跃元组、死元组、死元组比例。
6. select schemaname, relname, pg_size_pretty(table_size) as table_size, pg_size_pretty(indexes_size) as indexes_size, pg_size_pretty(total_size) as total_size, round((total_size / pg_database_size(current_database())) * 100,2) as "percent(%)", n_live_tup,n_dead_tup,(n_dead_tup/(n_live_tup+1)) as dead_tuple_rating from (select schemaname, relname, pg_table_size(concat(schemaname,'.',relname)) as table_size, pg_indexes_size(concat(schemaname,'.',relname)) as indexes_size, pg_total_relation_size(concat(schemaname,'.',relname)) as total_size,n_live_tup,n_dead_tup from pg_stat_user_tables) order by "percent(%)" desc;

复制代码

示例：

1. select schemaname, relname, pg_size_pretty(table_size) as table_size, pg_size_pretty(indexes_size) as indexes_size, pg_size_pretty(total_size) as total_size, round((total_size / pg_database_size(current_database())) * 100,2) as "percent(%)", n_live_tup,n_dead_tup,(n_dead_tup/(n_live_tup+1)) as dead_tuple_rating from (select schemaname, relname, pg_table_size(concat(schemaname,'.',relname)) as table_size, pg_indexes_size(concat(schemaname,'.',relname)) as indexes_size, pg_total_relation_size(concat(schemaname,'.',relname)) as total_size,n_live_tup,n_dead_tup from pg_stat_user_tables) order by "percent(%)" desc;
2. schemaname | relname | table_size | indexes_size | total_size | percent(%) | n_live_tup | n_dead_tup | dead_tuple_rating
3. ------------+--------------+------------+--------------+------------+------------+------------+------------+-------------------
4. testschema | t_ran2 | 24 kB | 0 bytes | 24 kB | .01 | 4 | 0 | 0
5. testschema | t_ran1 | 16 kB | 0 bytes | 16 kB | .01 | 3 | 0 | 0
6. testschema | t_ran3 | 16 kB | 0 bytes | 16 kB | .01 | 6 | 0 | 0

复制代码

4.6查询数据是否倾斜

1. SELECT a.count,b.node_name FROM (SELECT count(*) AS count,xc_node_id FROM table_name GROUP BY xc_node_id) a, pgxc_node b WHERE a.xc_node_id=b.node_id ORDER BY a.count desc;

复制代码

4.7查询给定分布键归属的DN

1. select * from pgxc_node where node_id = (select xc_node_id from $table where $col = $value limit 1);

复制代码

示例

1. select * from pgxc_node where node_id = (select xc_node_id from t1 where id = 1);
2. node_name | node_type | node_port | node_host | node_port1 | node_host1 | hostis_primary | nodeis_primary | nodeis_preferred | node_id | sctp_port | control_port | sctp_port1 | control_po
3. rt1 | nodeis_central | nodeis_active
4. -------------------+-----------+-----------+--------------+------------+--------------+----------------+----------------+-------
5. dn_xxx | D | 45700 | 10.30.41.163 | 45700 | 10.30.41.163 | t | f | f | -564789568 | 45702 | 45703 | 0 |
6. 0 | f | t

复制代码

4.8查询表的主键

1. select pg_constraint.conname as pk_name
2. from pg_constraint
3. inner join pg_class on pg_constraint.conrelid = pg_class.oid
4. where pg_class.relname = '$table_name'
5. and pg_constraint.contype = 'p';

复制代码

4.9查询事务信息

1. select xmin, xmax, * from $table_name;

复制代码

 
点击关注，第一时间了解华为云新鲜技术~

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！