计算
弹性云服务器 ECS
Flexus云服务
裸金属服务器 BMS
弹性伸缩 AS
镜像服务 IMS
专属主机 DeH
函数工作流 FunctionGraph
云手机服务器 CPH
Huawei Cloud EulerOS
网络
虚拟私有云 VPC
弹性公网IP EIP
虚拟专用网络 VPN
弹性负载均衡 ELB
NAT网关 NAT
云专线 DC
VPC终端节点 VPCEP
云连接 CC
企业路由器 ER
企业交换机 ESW
全球加速 GA
安全与合规
安全技术与应用
Web应用防火墙 WAF
企业主机安全 HSS
云防火墙 CFW
安全云脑 SecMaster
DDoS防护 AAD
数据加密服务 DEW
数据库安全服务 DBSS
云堡垒机 CBH
数据安全中心 DSC
云证书管理服务 CCM
边缘安全 EdgeSec
威胁检测服务 MTD
CDN与智能边缘
内容分发网络 CDN
CloudPond云服务
智能边缘云 IEC
迁移
主机迁移服务 SMS
对象存储迁移服务 OMS
云数据迁移 CDM
迁移中心 MGC
大数据
MapReduce服务 MRS
数据湖探索 DLI
表格存储服务 CloudTable
云搜索服务 CSS
数据接入服务 DIS
数据仓库服务 GaussDB(DWS)
数据治理中心 DataArts Studio
数据可视化 DLV
数据湖工厂 DLF
湖仓构建 LakeFormation
企业应用
云桌面 Workspace
应用与数据集成平台 ROMA Connect
云解析服务 DNS
专属云
专属计算集群 DCC
IoT物联网
IoT物联网
设备接入 IoTDA
智能边缘平台 IEF
用户服务
账号中心
费用中心
成本中心
资源中心
企业管理
工单管理
国际站常见问题
ICP备案
我的凭证
支持计划
客户运营能力
合作伙伴支持计划
专业服务
区块链
区块链服务 BCS
Web3节点引擎服务 NES
解决方案
SAP
高性能计算 HPC
视频
视频直播 Live
视频点播 VOD
媒体处理 MPC
实时音视频 SparkRTC
数字内容生产线 MetaStudio
存储
对象存储服务 OBS
云硬盘 EVS
云备份 CBR
存储容灾服务 SDRS
高性能弹性文件服务 SFS Turbo
弹性文件服务 SFS
云硬盘备份 VBS
云服务器备份 CSBS
数据快递服务 DES
专属分布式存储服务 DSS
容器
云容器引擎 CCE
容器镜像服务 SWR
应用服务网格 ASM
华为云UCS
云容器实例 CCI
管理与监管
云监控服务 CES
统一身份认证服务 IAM
资源编排服务 RFS
云审计服务 CTS
标签管理服务 TMS
云日志服务 LTS
配置审计 Config
资源访问管理 RAM
消息通知服务 SMN
应用运维管理 AOM
应用性能管理 APM
组织 Organizations
优化顾问 OA
IAM 身份中心
云运维中心 COC
资源治理中心 RGC
应用身份管理服务 OneAccess
数据库
云数据库 RDS
文档数据库服务 DDS
数据管理服务 DAS
数据复制服务 DRS
云数据库 GeminiDB
云数据库 GaussDB
分布式数据库中间件 DDM
数据库和应用迁移 UGO
云数据库 TaurusDB
人工智能
人脸识别服务 FRS
图引擎服务 GES
图像识别 Image
内容审核 Moderation
文字识别 OCR
AI开发平台ModelArts
图像搜索 ImageSearch
对话机器人服务 CBS
华为HiLens
视频智能分析服务 VIAS
语音交互服务 SIS
应用中间件
分布式缓存服务 DCS
API网关 APIG
微服务引擎 CSE
分布式消息服务Kafka版
分布式消息服务RabbitMQ版
分布式消息服务RocketMQ版
多活高可用服务 MAS
事件网格 EG
企业协同
华为云会议 Meeting
云通信
消息&短信 MSGSMS
云生态
合作伙伴中心
云商店
开发者工具
SDK开发指南
API签名指南
Terraform
华为云命令行工具服务 KooCLI
其他
产品价格详情
系统权限
管理控制台
客户关联华为云合作伙伴须知
消息中心
公共问题
开发与运维
应用管理与运维平台 ServiceStage
软件开发生产线 CodeArts
需求管理 CodeArts Req
部署 CodeArts Deploy
性能测试 CodeArts PerfTest
编译构建 CodeArts Build
流水线 CodeArts Pipeline
制品仓库 CodeArts Artifact
测试计划 CodeArts TestPlan
代码检查 CodeArts Check
代码托管 CodeArts Repo
云应用引擎 CAE
开天aPaaS
云消息服务 KooMessage
云手机服务 KooPhone
云空间服务 KooDrive

查看表和数据库的信息

更新时间:2024-08-23 GMT+08:00

查询表信息

  • 使用系统表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
     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节点的审计日志,其语法为:

    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('2024-05-26 8:00:00','2024-05-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下的表大小及表对应索引的大小

 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;

我们使用cookie来确保您的高速浏览体验。继续浏览本站,即表示您同意我们使用cookie。 详情

文档反馈

文档反馈

意见反馈

0/500

标记内容

同时提交标记内容