数据仓库-查看表和数据库的信息

查询表信息

• 使用系统表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)