更新时间：2024-08-23 GMT+08:00
查看表和数据库的信息
查询表信息
- 使用系统表pg_tables查询数据库所有表的信息。
1SELECT * FROM pg_tables;
- 使用gsql的\d+命令查询表结构。
示例：先创建表customer_t1并插入数据。
1 2 3 4 5 6 7 8 9
CREATE TABLE customer_t1 ( c_customer_sk integer, c_customer_id char(5), c_first_name char(6), c_last_name char(8) ) with (orientation = column,compression=middle) distribute by hash (c_last_name);
1 2 3 4
INSERT INTO customer_t1 (c_customer_sk, c_customer_id, c_first_name) VALUES (6885, 'map', 'Peter'), (4321, 'river', 'Lily'), (9527, 'world', 'James');
查询表结构。（若建表时不指定schema，则表的默认schema为public）
1 2 3 4 5 6 7 8 9 10 11 12
\d+ customer_t1; Table "public.customer_t1" Column | Type | Modifiers | Storage | Stats target | Description ---------------+--------------+-----------+----------+--------------+------------- c_customer_sk | integer | | plain | | c_customer_id | character(5) | | extended | | c_first_name | character(6) | | extended | | c_last_name | character(8) | | extended | | Has OIDs: no Distribute By: HASH(c_last_name) Location Nodes: ALL DATANODES Options: orientation=column, compression=middle, colversion=2.0, enable_delta=false
此处的Options在不同版本会有差异，对实际业务没有影响，仅作参考，实际以用户当前版本查询为准。
- 使用函数pg_get_tabledef查询表定义。
1 2 3 4 5 6 7 8 9 10 11 12 13 14
SELECT * FROM PG_GET_TABLEDEF('customer_t1'); pg_get_tabledef ----------------------------------------------------------------------------------- SET search_path = tpchobs; + CREATE TABLE customer_t1 ( + c_customer_sk integer, + c_customer_id character(5), + c_first_name character(6), + c_last_name character(8) + ) + WITH (orientation=column, compression=middle, colversion=2.0, enable_delta=false)+ DISTRIBUTE BY HASH(c_last_name) + TO GROUP group_version1; (1 row)
- 执行如下命令查询表customer_t1的所有数据。
1 2 3 4 5 6 7
SELECT * FROM customer_t1; c_customer_sk | c_customer_id | c_first_name | c_last_name ---------------+---------------+--------------+------------- 6885 | map | Peter | 4321 | river | Lily | 9527 | world | James | (3 rows)
- 使用SELECT查询表customer_t1中某一字段的所有数据。
1 2 3 4 5 6 7
SELECT c_customer_sk FROM customer_t1; c_customer_sk --------------- 6885 4321 9527 (3 rows)
- 查询表是否做过表分析，执行如下命令会返回每个表最近一次做analyze的时间，没有返回时间的则表示没有做过analyze。
1SELECT pg_stat_get_last_analyze_time(oid),relname FROM pg_class where relkind='r';
查询public下的表做表分析的时间：
1 2 3 4 5 6 7 8 9 10 11 12 13
SELECT pg_stat_get_last_analyze_time(c.oid),c.relname FROM pg_class c LEFT JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relkind='r' AND n.nspname='public'; pg_stat_get_last_analyze_time | relname -------------------------------+---------------------- 2022-05-17 07:48:26.923782+00 | warehouse_t19 2022-05-17 07:48:26.964512+00 | emp 2022-05-17 07:48:27.016709+00 | test_trigger_src_tbl 2022-05-17 07:48:27.045385+00 | customer 2022-05-17 07:48:27.062486+00 | warehouse_t1 2022-05-17 07:48:27.114884+00 | customer_t1 2022-05-17 07:48:27.172256+00 | product_info_input 2022-05-17 07:48:27.197014+00 | tt1 2022-05-17 07:48:27.212906+00 | timezone_test (9 rows)
- 快速查到一张表的列信息，information_schema下的视图在数据库中对象较多时返回结果很慢，可以通过以下sql快速查询到一张或几张表的列信息：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35
SELECT /*+ set (enable_hashjoin off) */T.table_schema AS tableschema, T.TABLE_NAME AS tablename, T.dtd_identifier AS srcAttrId, COLUMN_NAME AS fieldName, 'N' AS isPrimaryKey, nvl ( nvl ( T.character_maximum_length, T.numeric_precision ), 0 ) AS fieldLength, T.udt_name AS fieldType from ( SELECT /*+ indexscan(co) indexscan(nco) indexscan(a) indexscan(t) leading((nc c a)) leading((co nco)) indexscan(bt) indexscan(nt) */ nc.nspname AS table_schema, c.relname AS table_name, a.attname AS column_name, information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS character_maximum_length, information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS numeric_precision, COALESCE(bt.typname, t.typname)::information_schema.sql_identifier AS udt_name, a.attnum AS dtd_identifier FROM pg_attribute a LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum JOIN (pg_class c JOIN pg_namespace nc ON c.relnamespace = nc.oid) ON a.attrelid = c.oid JOIN (pg_type t JOIN pg_namespace nt ON t.typnamespace = nt.oid) ON a.atttypid = t.oid LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid) ON t.typtype = 'd'::"char" AND t.typbasetype = bt.oid LEFT JOIN (pg_collation co JOIN pg_namespace nco ON co.collnamespace = nco.oid) ON a.attcollation = co.oid AND (nco.nspname <> 'pg_catalog'::name OR co.collname <> 'default'::name) WHERE NOT pg_is_other_temp_schema(nc.oid) AND a.attnum > 0 AND NOT a.attisdropped AND (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char"])) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)) ) t WHERE 1 = 1 AND UPPER ( T.TABLE_NAME ) <> 'DIS_USER_DATARIGHT_IF_SPLIT_T' AND UPPER ( T.TABLE_NAME ) NOT LIKE'DIS_TMP_%' AND UPPER ( T.COLUMN_NAME ) <> '_DISAPP_AUTO_ID_' AND ( ( T.TABLE_NAME ), ( T.table_schema ) ) IN ( ( lower ( 'table_name' )::name, lower ( 'schema_name' )::name ) );
例如，快速查询表promotion的列信息：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35
SELECT /*+ set (enable_hashjoin off) */T.table_schema AS tableschema, T.TABLE_NAME AS tablename, T.dtd_identifier AS srcAttrId, COLUMN_NAME AS fieldName, 'N' AS isPrimaryKey, nvl ( nvl ( T.character_maximum_length, T.numeric_precision ), 0 ) AS fieldLength, T.udt_name AS fieldType from ( SELECT /*+ indexscan(co) indexscan(nco) indexscan(a) indexscan(t) leading((nc c a)) leading((co nco)) indexscan(bt) indexscan(nt) */ nc.nspname AS table_schema, c.relname AS table_name, a.attname AS column_name, information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS character_maximum_length, information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS numeric_precision, COALESCE(bt.typname, t.typname)::information_schema.sql_identifier AS udt_name, a.attnum AS dtd_identifier FROM pg_attribute a LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum JOIN (pg_class c JOIN pg_namespace nc ON c.relnamespace = nc.oid) ON a.attrelid = c.oid JOIN (pg_type t JOIN pg_namespace nt ON t.typnamespace = nt.oid) ON a.atttypid = t.oid LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid) ON t.typtype = 'd'::"char" AND t.typbasetype = bt.oid LEFT JOIN (pg_collation co JOIN pg_namespace nco ON co.collnamespace = nco.oid) ON a.attcollation = co.oid AND (nco.nspname <> 'pg_catalog'::name OR co.collname <> 'default'::name) WHERE NOT pg_is_other_temp_schema(nc.oid) AND a.attnum > 0 AND NOT a.attisdropped AND (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char"])) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)) ) t WHERE 1 = 1 AND UPPER ( T.TABLE_NAME ) <> 'DIS_USER_DATARIGHT_IF_SPLIT_T' AND UPPER ( T.TABLE_NAME ) NOT LIKE'DIS_TMP_%' AND UPPER ( T.COLUMN_NAME ) <> '_DISAPP_AUTO_ID_' AND ( ( T.TABLE_NAME ), ( T.table_schema ) ) IN ( ( lower ( 'promotion' )::name, lower ( 'public' )::name ) );
- 通过查询审计日志获取表定义。
使用函数pgxc_query_audit可以查询所有CN节点的审计日志，其语法为：
1pgxc_query_audit(timestamptz startime,timestamptz endtime)
查询审计多个对象名的记录：
1 2
SET audit_object_name_format TO 'all'; SELECT object_name,result,operation_type,command_text FROM pgxc_query_audit('2024-05-26 8:00:00','2024-05-26 22:55:00') where command_text like '%student%';
查询表大小
- 查询表的总大小（包含表的索引和数据）。
1SELECT pg_size_pretty(pg_total_relation_size('<schemaname>.<tablename>'));
示例：
先在customer_t1创建索引：
1CREATE INDEX index1 ON customer_t1 USING btree(c_customer_sk);
然后查询public模式下，customer_t1表的大小。
1 2 3 4 5
SELECT pg_size_pretty(pg_total_relation_size('public.customer_t1')); pg_size_pretty ---------------- 264 kB (1 row)
- 查询表的数据大小（不包括索引）。
1SELECT pg_size_pretty(pg_relation_size('<schemaname>.<tablename>'));
示例：查询public模式下，customer_t1表的大小。1 2 3 4 5
SELECT pg_size_pretty(pg_relation_size('public.customer_t1')); pg_size_pretty ---------------- 208 kB (1 row)
- 查询系统中所有表占用空间大小排行。
1 2 3
SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size FROM information_schema.tables ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit xx;
示例1：查询系统中所有表占用空间大小排行前15。1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size FROM information_schema.tables ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit 15; table_full_name | size ---------------------------+--------- pg_catalog.pg_attribute | 2048 KB pg_catalog.pg_rewrite | 1888 KB pg_catalog.pg_depend | 1464 KB pg_catalog.pg_proc | 1464 KB pg_catalog.pg_class | 512 KB pg_catalog.pg_description | 504 KB pg_catalog.pg_collation | 360 KB pg_catalog.pg_statistic | 352 KB pg_catalog.pg_type | 344 KB pg_catalog.pg_operator | 224 KB pg_catalog.pg_amop | 208 KB public.tt1 | 160 KB pg_catalog.pg_amproc | 120 KB pg_catalog.pg_index | 120 KB pg_catalog.pg_constraint | 112 KB (15 rows)
示例2：查询public模式下所有表占用空间排行前20。1 2 3 4 5 6 7 8 9 10 11 12 13 14
SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size FROM information_schema.tables where table_schema='public' ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit 20; table_full_name | size -----------------------------+--------- public.tt1 | 160 KB public.product_info_input | 112 KB public.customer_t1 | 96 KB public.warehouse_t19 | 48 KB public.emp | 32 KB public.customer | 0 bytes public.test_trigger_src_tbl | 0 bytes public.warehouse_t1 | 0 bytes (8 rows)
快速查询全库中所有表占用空间大小
8.1.3及以上集群版本在大集群大数据量（表数量大于1000）场景下，如果进行全库表查询，建议优先使用pgxc_wlm_table_distribution_skewness视图，该视图可以查到全库内的各表空间使用情况以及数据倾斜分布情况。其中，total_size和avg_size的单位为字节（bytes）。
1 2 3 4 5 6 7 |
SELECT *, pg_size_pretty(total_size) as tableSize FROM pgxc_wlm_table_distribution_skewness ORDER BY total_size desc; schema_name | table_name | total_size | avg_size | max_percent | min_percent | skew_percent | tablesize --------------------+---------------------------------------------------+------------+-----------+-------------+-------------+--------------+----------- public | history_tbs_test_row_1 | 804347904 | 134057984 | 18.02 | 15.63 | 7.53 | 767 MB public | history_tbs_test_row_3 | 402096128 | 67016021 | 18.30 | 15.60 | 8.90 | 383 MB public | history_tbs_test_row_2 | 401743872 | 66957312 | 18.01 | 15.01 | 7.47 | 383 MB public | i_history_tbs_test_1 | 325263360 | 54210560 | 17.90 | 15.50 | 6.90 | 310 MB |
查询结果显示history_tbs_test_row_1表占用空间最大，且数据有一定的倾斜。
- 视图pgxc_wlm_table_distribution_skewness需要打开GUC参数use_workload_manager和enable_perm_space才能进行查询，在低版本查询全库时建议使用table_distribution()函数，如果仅查询某一张表的大小，推荐使用table_distribution(schemaname text, tablename text)函数。
- 8.2.1及以上集群版本中，GaussDB(DWS)已支持pgxc_wlm_table_distribution_skewness视图，可直接查询。
- 在8.1.3集群版本中，可使用如下定义创建视图后再进行查询：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
CREATE OR REPLACE VIEW pgxc_wlm_table_distribution_skewness AS WITH skew AS ( SELECT schemaname, tablename, pg_catalog.sum(dnsize) AS totalsize, pg_catalog.avg(dnsize) AS avgsize, pg_catalog.max(dnsize) AS maxsize, pg_catalog.min(dnsize) AS minsize, (maxsize - avgsize) * 100 AS skewsize FROM pg_catalog.gs_table_distribution() GROUP BY schemaname, tablename ) SELECT schemaname AS schema_name, tablename AS table_name, totalsize AS total_size, avgsize::numeric(1000) AS avg_size, ( CASE WHEN totalsize = 0 THEN 0.00 ELSE (maxsize * 100 / totalsize)::numeric(5, 2) END ) AS max_percent, ( CASE WHEN totalsize = 0 THEN 0.00 ELSE (minsize * 100 / totalsize)::numeric(5, 2) END ) AS min_percent, ( CASE WHEN totalsize = 0 THEN 0.00 ELSE (skewsize / maxsize)::numeric(5, 2) END ) AS skew_percent FROM skew; |
查询数据库
- 使用gsql的\l元命令查看数据库系统的数据库列表。
1 2 3 4 5 6 7 8 9 10
\l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+-------+-----------+---------+-------+------------------- gaussdb | Ruby | SQL_ASCII | C | C | template0 | Ruby | SQL_ASCII | C | C | =c/Ruby + | | | | | Ruby=CTc/Ruby template1 | Ruby | SQL_ASCII | C | C | =c/Ruby + | | | | | Ruby=CTc/Ruby (3 rows)
- 如果用户在数据库安装的时候没有指定LC_COLLATE、LC_CTYPE参数，则LC_COLLATE、LC_CTYPE参数的默认值为C。
- 如果用户在创建数据库时没有指定LC_COLLATE、LC_CTYPE参数，则默认使用模板数据库的排序顺序及字符分类。
详细内容可参见CREATE DATABASE参数说明。
- 通过系统表pg_database查询数据库列表。
1 2 3 4 5 6 7
SELECT datname FROM pg_database; datname ----------- template1 template0 gaussdb (3 rows)
查询数据库大小
查询数据库的大小。
1
|
select datname,pg_size_pretty(pg_database_size(datname)) from pg_database; |
示例：
1 2 3 4 5 6 7 |
select datname,pg_size_pretty(pg_database_size(datname)) from pg_database; datname | pg_size_pretty -----------+---------------- template1 | 61 MB template0 | 61 MB postgres | 320 MB (3 rows) |
查询指定SCHEMA下的表大小及表对应索引的大小
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SELECT t.tablename, indexname, c.reltuples AS num_rows, pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size, pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size, CASE WHEN indisunique THEN 'Y' ELSE 'N' END AS UNIQUE, idx_scan AS number_of_scans, idx_tup_read AS tuples_read, idx_tup_fetch AS tuples_fetched FROM pg_tables t LEFT OUTER JOIN pg_class c ON t.tablename=c.relname LEFT OUTER JOIN ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x JOIN pg_class c ON c.oid = x.indrelid JOIN pg_class ipg ON ipg.oid = x.indexrelid JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid ) AS foo ON t.tablename = foo.ctablename WHERE t.schemaname='public' ORDER BY 1,2; |
父主题： 数据库管理
