摘要：本文介紹了9個GaussDB常用的物件陳述句，希望對大家有幫助，
本文分享自華為云社區《GaussDB物件相關陳述句》，作者：酷哥，
1. 常用函式
pg_database_size() -- 資料庫使用的磁盤空間， pg_table_size() -- 表使用的磁盤空間， pg_total_relation_size() -- 表和索引共使用的磁盤空間， pg_indexes_size() -- 索引使用的磁盤空間，
2. 常用系統表
pg_class -- 存盤資料庫物件資訊及其之間的關系， pg_index -- 存盤索引的一部分資訊，其他的資訊大多數在PG_CLASS中， pg_namespace -- 存盤schema相關的資訊， pg_database -- 存盤資料庫相關的資訊，
3. 常用視圖
pg_stat_user_tables -- 顯示所有用戶自定義普通表和toast表的狀態資訊，
pg_stat_user_indexes -- 顯示資料庫中用戶自定義普通表和toast表的索引狀態資訊，
4. 常用陳述句
4.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的所有者
-- pg_user這個視圖只有sysadmin用戶有權限查，普通用戶無法查詢， 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獲取表結構
set current_schema to $schema; select pg_get_tabledef('$table_name'); 或 select pg_get_tabledef('$schema_name.$table_name');
示例：
select pg_get_tabledef('testschema.t1'); pg_get_tabledef ---------------------------------------- SET search_path = testschema; + CREATE TABLE t1 ( + id integer, + name character varying(15) + ) + WITH (orientation=row, compression=no)+ DISTRIBUTE BY HASH(id) + TO GROUP group_version1; (1 row)
4.4查詢表大小
-- 查詢單個確定表的大小， select pg_table_size('$schema_name.$table_name'); -- 不包含索引，單位B select pg_total_relation_size('$schema_name.$table_name'); -- 包含索引，單位B -- 查詢連接的資料中，所有用戶表的大小 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; -- 包含索引 SELECT schemaname,relname, pg_table_size(concat(schemaname,'.',relname))/1024/1024/1024 table_size_GB FROM PG_STAT_USER_TABLES ORDER BY 3 DESC; -- 不包含索引" 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;
示例
-- 查詢單個確定表的大小， select pg_table_size('testschema.t1'); pg_table_size --------------- 17801216 (1 row) -- 查詢連接的資料中，所有用戶表的大小 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; -- 包含索引 schemaname | relname | table_size_gb ------------+--------------+-------------------- testschema | t_ran2 | 2.288818359375e-05 testschema | t_ran1 | 1.52587890625e-05 testschema | t_ran3 | 1.52587890625e-05 -- 需要指定schema 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; table_name | table_size | indexes_size | total_size -------------------------+------------+--------------+------------ testschema.t_ran2 | 24 kB | 0 bytes | 24 kB testschema.t_ran3 | 16 kB | 0 bytes | 16 kB testschema.t_ran1 | 16 kB | 0 bytes | 16 kB
4.5查看表的統計資訊
-- 查看指定schema和table的統計資訊， select * from pg_stat_user_tables where schemaname = '$schema_name' and relname = '$table_name'; -- 查詢全庫的表的活躍元組數、死元組數及死元組占比， 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; -- 查看表大小及活躍元組、死元組、死元組比例， 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;
示例：
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; schemaname | relname | table_size | indexes_size | total_size | percent(%) | n_live_tup | n_dead_tup | dead_tuple_rating ------------+--------------+------------+--------------+------------+------------+------------+------------+------------------- testschema | t_ran2 | 24 kB | 0 bytes | 24 kB | .01 | 4 | 0 | 0 testschema | t_ran1 | 16 kB | 0 bytes | 16 kB | .01 | 3 | 0 | 0 testschema | t_ran3 | 16 kB | 0 bytes | 16 kB | .01 | 6 | 0 | 0
4.6查詢資料是否傾斜
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
select * from pgxc_node where node_id = (select xc_node_id from $table where $col = $value limit 1);
示例
select * from pgxc_node where node_id = (select xc_node_id from t1 where id = 1); 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 rt1 | nodeis_central | nodeis_active -------------------+-----------+-----------+--------------+------------+--------------+----------------+----------------+------- dn_xxx | D | 45700 | 10.30.41.163 | 45700 | 10.30.41.163 | t | f | f | -564789568 | 45702 | 45703 | 0 | 0 | f | t
4.8查詢表的主鍵
select pg_constraint.conname as pk_name from pg_constraint inner join pg_class on pg_constraint.conrelid = pg_class.oid where pg_class.relname = '$table_name' and pg_constraint.contype = 'p';
4.9查詢事務資訊
select xmin, xmax, * from $table_name;
點擊關注，第一時間了解華為云新鮮技術~
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/528865.html
標籤：其它