文档首页> 数据仓库服务 GaussDB(DWS)> 最佳实践> 数据库管理> 查看表和数据库的信息
更新时间：2024-03-06 GMT+08:00
查看PDF

查看表和数据库的信息

查询表信息

  • 使用系统表pg_tables查询数据库所有表的信息。 
    1
    		 
    SELECT * 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。 
    1
    		 
    SELECT 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
    		 
    SELECT /*+ nestloop(a c)*/ c.column_name, c.data_type, c.ordinal_position, pgd.description, pp.partkey, c.is_nullable, c.column_default, c.character_maximum_length, c.numeric_precision,  c.numeric_scale, c.datetime_precision, c.interval_type, c.udt_name from information_schema.columns as c left join pg_namespace sp on sp.nspname = c.table_schema left join pg_class cla on cla.relname = c.table_name and cla.relnamespace = sp.oid left join pg_catalog.pg_partition pp on (pp.parentid = cla.oid and pp.parttype = 'r') left join pg_catalog.pg_description pgd on (pgd.objoid=cla.oid and pgd.objsubid = c.ordinal_position)where c.table_name in ('tablename') and c.table_schema = 'public';

    例如，快速查询表customer_t1的列信息：

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
    		 
    SELECT /*+ nestloop(a c)*/ c.column_name, c.data_type, c.ordinal_position, pgd.description, pp.partkey, c.is_nullable, c.column_default, c.character_maximum_length, c.numeric_precision, c.numeric_scale, c.datetime_precision, c.interval_type, c.udt_name from information_schema.columns as c left join pg_namespace sp on sp.nspname = c.table_schema left join pg_class cla on cla.relname = c.table_name and cla.relnamespace = sp.oid left join pg_catalog.pg_partition pp on (pp.parentid = cla.oid and pp.parttype = 'r') left join pg_catalog.pg_description pgd on (pgd.objoid=cla.oid and pgd.objsubid = c.ordinal_position) where c.table_name in ('customer_t1') and c.table_schema = 'public';
  column_name  | data_type | ordinal_position | description | partkey | is_nullable | column_default | character_maximum_length | numeric_precision | numeric_scale | datetime_precision | interval_type | udt_name
---------------+-----------+------------------+-------------+---------+-------------+----------------+--------------------------+-------------------+-------------
--+--------------------+---------------+----------
 c_last_name   | character |                4 |             |         | YES         |                |                        8 |                   |
  |                    |               | bpchar
 c_first_name  | character |                3 |             |         | YES         |                |                        6 |                   |
  |                    |               | bpchar
 c_customer_id | character |                2 |             |         | YES         |                |                        5 |                   |
  |                    |               | bpchar
 c_customer_sk | integer   |                1 |             |         | YES         |                |                          |                32 |
0 |                    |               | int4
(4 rows)
  • 通过查询审计日志获取表定义。

    使用函数pgxc_query_audit可以查询所有CN节点的审计日志，其语法为：

    1
    		 
    pgxc_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('2022-08-26 8:00:00','2022-08-26 22:55:00') where command_text like '%student%';

查询表大小

  • 查询表的总大小（包含表的索引和数据）。 
    1
    		 
    SELECT pg_size_pretty(pg_total_relation_size('<schemaname>.<tablename>'));

    示例：

    先在customer_t1创建索引：

    1
    		 
    CREATE 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)
  • 查询表的数据大小（不包括索引）。 
    1
    		 
    SELECT 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表占用空间最大，且数据有一定的倾斜。

  1. 视图pgxc_wlm_table_distribution_skewness需要打开GUC参数use_workload_manager和enable_perm_space才能进行查询，在低版本查询全库时建议使用table_distribution()函数，如果仅查询某一张表的大小，推荐使用table_distribution(schemaname text, tablename text)函数。
  2. 8.2.1及以上集群版本中，GaussDB(DWS)已支持pgxc_wlm_table_distribution_skewness视图，可直接查询。
  3. 在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下的表大小及表对应索引的大小

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;
父主题： 数据库管理