数据仓库-查看表和数据库的信息
查询表信息
- 使用系统表pg_tables查询数据库所有表的信息。
SELECT * FROM pg_tables;
- 使用gsql的\d+命令查询表结构。
示例：先创建表customer_t1并插入数据。
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);
INSERT INTO customer_t1 (c_customer_sk, c_customer_id, c_first_name) VALUES (6885, 'map', 'Peter'), (4321, 'river', 'Lily'), (9527, 'world', 'James');
查询表结构。（若建表时不指定schema，则表的默认schemaname是public）
\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
- 使用函数pg_get_tabledef查询表定义。
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的所有数据。
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中某一字段的所有数据。
SELECT c_customer_sk FROM customer_t1; c_customer_sk
--------------- 6885 4321 9527
(3 rows)
- 查询表是否做过表分析，执行如下命令会返回每个表最近一次做analyze的时间，没有返回的则表示没有做过analyze。
select pg_stat_get_last_analyze_time(oid),relname from pg_class where relkind='r';
查询public下的表做表分析的时间：
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)
查询表大小
- 查询表的总大小（包含表的索引和数据）。
select pg_size_pretty(pg_total_relation_size('<schemaname>.<tablename>'));
示例：
先在customer_t1创建索引：
CREATE INDEX index1 ON customer_t1 USING btree(c_customer_sk);
然后查询public模式下，customer_t1表的大小。
select pg_size_pretty(pg_total_relation_size('public.customer_t1')); pg_size_pretty
---------------- 264 kB
(1 row)
- 查询表的数据大小（不包括索引）
select pg_size_pretty(pg_relation_size('<schemaname>.<tablename>'));
示例：查询public模式下，customer_t1表的大小。
select pg_size_pretty(pg_relation_size('public.customer_t1')); pg_size_pretty
---------------- 208 kB
(1 row)
- 查询系统中所有表占用空间大小排行
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。
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模式下所有表占用空间排行。
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)
查询数据库
- 使用gsql的\l元命令查看数据库系统的数据库列表。
\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)
说明
l 如果用户在数据库安装的时候没有指定LC_COLLATE、LC_CTYPE参数，则LC_COLLATE、LC_CTYPE参数的默认值为C。
l 如果用户在创建数据库时没有指定LC_COLLATE、LC_CTYPE参数，则默认使用模板数据库的排序顺序及字符分类。
- 通过系统表pg_database查询数据库列表。
SELECT datname FROM pg_database; datname
----------- template1 template0 gaussdb
(3 rows)
查询数据库大小
查询数据库的大小。
select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;
示例：
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)