# 表大小查询最佳实践 本文主要介绍DWS表大小查询的多种方法以及一些特殊表大小查询的最佳实践。 #### 表大小查询 普通表大小按查询的范围不同，主要分为以下几种场景。 表1表大小查询场景 | 场景 | 查询范围 | 使用函数 | |:---|:---|:---| | 查询指定表的**全部磁盘空间** ，**包括索引，数据文件以及列存辅助表等**。单位：字节（bytes）。 | 表（数据文件 + cudesc表 + delta表) + 表索引 + toast | [pg_total_relation_size](https://support.huaweicloud.com/sqlreference-dws/dws_06_0302.html) | | 查询指定表或者索引使用的磁盘空间，**不计表索引和** **toast**。单位：字节（bytes）。 | 表（数据文件 + cudesc表 + delta表） | [pg_relation_size](https://support.huaweicloud.com/sqlreference-dws/dws_06_0302.html) | | 查询指定的表使用的磁盘空间，**不计索引（但是包含toast**，自由空间映射和可见性映射）。单位：字节（bytes）。 | 表（数据文件 + cudesc表 + delta表）+ toast | [pg_table_size](https://support.huaweicloud.com/sqlreference-dws/dws_06_0302.html) | | 查询指定表的**索引使用**的总磁盘空间。单位：字节（bytes）。 | 索引 | [pg_indexes_size](https://support.huaweicloud.com/sqlreference-dws/dws_06_0302.html) | 1. 准备测试表。 ``` DROP TABLE IF EXISTS user_info; CREATE TABLE IF NOT EXISTS user_info ( id BIGSERIAL, user_name VARCHAR(50) NOT NULL, gender CHAR(1), age INT, phone VARCHAR(20) NOT NULL, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) DISTRIBUTE BY HASH(phone); CREATE UNIQUE INDEX IF NOT EXISTS idx_user_info_phone ON user_info(phone); INSERT INTO user_info (user_name, gender, age, phone) SELECT '用户_' || gs, CASE WHEN random() > 0.5 THEN 'M' ELSE 'F' END, floor(18 + random() * 40)::INT, '138' || lpad(gs::TEXT, 8, '0') FROM generate_series(1, 10000) AS gs; ``` 2. 查询表的**全部磁盘空间** ，**包括索引，数据文件以及列存辅助表等** （以下语句等价）。 ``` SELECT * FROM pg_total_relation_size('user_info'); ---方式一，根据表名查询 SELECT * FROM pg_total_relation_size('user_info'::regclass); ---方式二，系统根据表名自动转oid查询 SELECT oid FROM PG_CLASS WHERE relname = 'user_info'; ---方式三，先查询oid，再根据oid查询 SELECT * FROM pg_total_relation_size(oid); ``` ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002583199704.png "点击放大") 3. 以上结果返回**单位是** **字节（bytes）** ，如果需要换成MB或KB，可以用[pg_size_pretty](https://support.huaweicloud.com/sqlreference-dws/dws_06_0302.html) 函数转换包装一下。 ``` SELECT pg_size_pretty(pg_total_relation_size('user_info')); ``` ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002583365830.png) 4. 查询**表使用的磁盘空间，** **不计表索引和** **toast** 。 ``` SELECT * FROM pg_relation_size('user_info'); SELECT * FROM pg_relation_size('user_info'::regclass); SELECT * FROM pg_relation_size(oid); ``` ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002613601149.png "点击放大") 5. 查询表使用的磁盘空间，**不计索引（但是包含toast** ，自由空间映射和可见性映射）。 ``` SELECT * FROM pg_table_size('user_info'); SELECT * FROM pg_table_size('user_info'::regclass); SELECT * FROM pg_table_size(oid); ``` ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002613601769.png "点击放大") 6. 指定表的**索引使用的总磁盘空间。** ``` SELECT * FROM pg_indexes_size('user_info'); SELECT * FROM pg_indexes_size('user_info'::regclass); SELECT * FROM pg_indexes_size(oid); SELECT pg_size_pretty(pg_indexes_size('user_info')); ``` ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002583375178.png "点击放大") #### 分区和分区索引大小查询 分区表的表大小可以通过以上的[表大小查询]方法查询，以下只提供**分区** 或**分区索引**大小的查询方法。 表2分区和分区索引查询 | 场景 | 查询范围 | 使用函数 | |:---|:---|:---| | 查询**分区**使用的磁盘空间，不计索引。单位：字节（bytes）。 | 分区（数据文件 + cudesc表 + delta表) + 分区toast | [pg_partition_size](https://support.huaweicloud.com/sqlreference-dws/dws_06_0302.html) | | 查询**分区的索引**使用的磁盘空间。单位：字节（bytes）。 | 分区索引 | [pg_partition_indexes_size](https://support.huaweicloud.com/sqlreference-dws/dws_06_0302.html) | 1. 准备测试分区表和数据： ``` DROP TABLE IF EXISTS customer_address; CREATE TABLE customer_address ( ca_address_sk INTEGER NOT NULL , ca_address_id CHARACTER(16) NOT NULL , ca_street_number CHARACTER(10) , ca_street_name CHARACTER varying(60) , ca_street_type CHARACTER(15) , ca_suite_number CHARACTER(10) ) DISTRIBUTE BY HASH (ca_address_sk) PARTITION BY RANGE(ca_address_sk) ( PARTITION P1 VALUES LESS THAN(2450815), PARTITION P2 VALUES LESS THAN(2451179), PARTITION P3 VALUES LESS THAN(2451544), PARTITION P4 VALUES LESS THAN(MAXVALUE) ); CREATE INDEX customer_address_idx ON customer_address (ca_address_sk) LOCAL ( PARTITION ca_address_sk_idx1, PARTITION ca_address_sk_idx2, PARTITION ca_address_sk_idx3, PARTITION ca_address_sk_idx4 ); INSERT INTO customer_address ( ca_address_sk, ca_address_id, ca_street_number, ca_street_name, ca_street_type, ca_suite_number ) SELECT 2450000 + gs, lpad(('ADDR' || gs)::TEXT, 16, '0'), lpad((gs % 1000)::TEXT, 10, ' '), 'Street_' || gs, CASE WHEN random() > 0.5 THEN 'St' ELSE 'Ave' END, lpad(gs::TEXT, 10, '0') FROM generate_series(1, 10000) AS gs; ``` 2. **查询分区** 使用的磁盘空间，不计索引。以下两个方式等价，其中方式二，先通过[PGXC_GET_STAT_ALL_PARTITIONS](https://support.huaweicloud.com/devg-dws/dws_04_0804.html)视图查询对应的表和分区的oid，再通过oid查询。 ``` SELECT * FROM pg_partition_size('customer_address','p1'); ---方式一，通过表名、分区名查询。 SELECT relid,partid,partname FROM PGXC_GET_STAT_ALL_PARTITIONS WHERE relname = 'customer_address'; ---方式二，先查询表和分区的oid，再根据oid查询。 SELECT * FROM pg_partition_size(表oid,分区oid); ``` ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002613799261.png "点击放大") 3. 查询**分区索引** 使用的磁盘空间。 ``` SELECT * FROM pg_partition_indexes_size('customer_address','p1'); SELECT relid,partid,partname FROM PGXC_GET_STAT_ALL_PARTITIONS WHERE relname = 'customer_address'; SELECT * FROM pg_partition_indexes_size(表oid,分区oid); ``` ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002613933015.png "点击放大") #### 冷热表大小查询 冷热表大小，也可以通过普通表大小查询函数来查询，参见[表大小查询]，但是查询结果会包含冷数据大小，如果要分别计算冷、热数据的大小，可通过[pg_lifecycle_table_data_distribute](https://support.huaweicloud.com/sqlreference-dws/dws_06_0362.html)查询，如查询所有冷热表的大小则通过[pg_lifecycle_node_data_distribute](https://support.huaweicloud.com/sqlreference-dws/dws_06_0362.html)查询。 1. 准备测试数据： ``` DROP TABLE IF EXISTS lifecycle_table; CREATE TABLE lifecycle_table(i int, val text) WITH (ORIENTATION = COLUMN, storage_policy = 'LMT:100') PARTITION BY RANGE (i) ( PARTITION P1 VALUES LESS THAN(5), PARTITION P2 VALUES LESS THAN(10), PARTITION P3 VALUES LESS THAN(15), PARTITION P8 VALUES LESS THAN(MAXVALUE) ) ENABLE ROW MOVEMENT; INSERT INTO lifecycle_table(i, val) SELECT (generate_series % 100), -- 0~99 循环，覆盖所有分区 'lifecycle_val_' || generate_series FROM generate_series(1, 10000); DROP TABLE IF EXISTS cold_hot_table; CREATE TABLE cold_hot_table ( W_WAREHOUSE_ID CHAR(16) NOT NULL, W_WAREHOUSE_NAME VARCHAR(20) , W_STREET_NUMBER CHAR(10) , W_STREET_NAME VARCHAR(60) , W_STREET_ID CHAR(15) , W_SUITE_NUMBER CHAR(10) ) WITH (ORIENTATION = COLUMN, storage_policy = 'LMT:30') DISTRIBUTE BY HASH (W_WAREHOUSE_ID) PARTITION BY RANGE(W_STREET_ID) ( PARTITION P1 VALUES LESS THAN(100000), PARTITION P2 VALUES LESS THAN(200000), PARTITION P3 VALUES LESS THAN(300000), PARTITION P4 VALUES LESS THAN(MAXVALUE) )ENABLE ROW MOVEMENT; INSERT INTO cold_hot_table ( W_WAREHOUSE_ID, W_WAREHOUSE_NAME, W_STREET_NUMBER, W_STREET_NAME, W_STREET_ID, W_SUITE_NUMBER ) SELECT -- 16 位固定长度仓库ID lpad((generate_series % 1000000)::text, 16, '0'), -- 仓库名称 'WAREHOUSE_' || generate_series, -- 10 位街道门牌号 lpad((random() * 9999999999)::bigint::text, 10, '0'), -- 街道名称 'STREET_' || (random() * 10000)::int, -- 分区字段：0 ~ 40万 均匀分布，自动落入 P1/P2/P3/P4 (generate_series % 400000)::text, -- 10 位房间号 lpad((random() * 9999999999)::bigint::text, 10, '0') FROM generate_series(1, 5000); ``` 2. 查看某张表的冷数据，热数据的大小。 ``` SELECT * FROM pg_lifecycle_table_data_distribute ('lifecycle_table'); ``` ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002583715434.png "点击放大") 3. 查看所有冷热表的冷数据、热数据的大小。 ``` SELECT * FROM pg_lifecycle_node_data_distribute() ORDER BY tablename; ``` ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002614242847.png "点击放大") #### 数据库大小查询 通过[pg_database_size](https://support.huaweicloud.com/sqlreference-dws/dws_06_0302.html)可以查询指定名称的数据库使用的磁盘空间。 支持通过指定名称或oid查询。 ![](https://support.huaweicloud.com/bestpractice-dws/public_sys-resources/note_3.0-zh-cn.png) - DWS 9.1.1.200及以上版本，设置GUC参数**fast_obs_dbsize_method**为2，可以快速计算存算分离下数据库大小，相比之前版本，性能提升10倍以上。 - DWS 9.1.1.200以下版本，当集群中只有一个数据库和tablespace时，统计OBS空间会直接使用OBS桶的大小（具体介绍见[表大小相关GUC介绍]中fast_obs_dbsize_method的介绍），该方法的好处是性能更好，但会统计到备份集的大小。 1. 通过**指定名称** **查询数据库大小** 。 ``` SELECT pg_database_size('gaussdb'); ``` ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002583736952.png "点击放大") 2. 通过**指定oid查询数据库大小** 。首先通过[PG_STAT_DATABASE](https://support.huaweicloud.com/devg-dws/dws_04_0761.html)查询数据库的oid。 ``` SELECT datid FROM PG_STAT_DATABASE WHERE datname = 'gaussdb'; SELECT pg_database_size(2147483821); ``` ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002614098165.png "点击放大") #### 其他大小（CU大小）查询 针对V3表，可以通过[pg_obs_file_size](https://support.huaweicloud.com/sqlreference-dws/dws_06_0302.html)函数查询表在OBS上的CU大小。 针对Hstore_opt表，可以通过[pgxc_get_cstore_dirty_ratio](https://support.huaweicloud.com/devg-dws/dws_04_1027.html)函数查询目标表在各个DN上的cu、delta以及cudesc的脏页率以及大小。 1. 创建存算分离V3表和导入数据。 ``` DROP TABLE IF EXISTS test_t5; CREATE TABLE test_t5 ( id integer not null, data integer, age integer ) WITH (ORIENTATION =COLUMN, COLVERSION =3.0) DISTRIBUTE BY ROUNDROBIN; INSERT INTO test_t5 (id, data, age) SELECT id, (random() * 10000)::integer, -- 0~10000 随机整数 (20 + random() * 60)::integer -- 年龄 20~80 随机 FROM generate_series(1, 10000) AS t(id); UPDATE test_t5 SET age = 25 WHERE id = 10; ``` 2. 查询该V3表在OBS上存储的CU文件大小。 ``` SELECT * FROM pg_obs_file_size('test_t5'); ``` ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002585930660.png "点击放大") 3. 如果V3为分区表，还可以查询指定分区在OBS上的CU文件大小。 ``` SELECT * FROM pg_obs_file_size('表名','分区名'); ``` 4. 创建Hstore_opt表。 ``` DROP TABLE IF EXISTS hstore_opt_table_demo; CREATE TABLE hstore_opt_table_demo( t_code character varying(20), t_gisid character varying(800), t_datatime timestamp(6) without time zone, t_gmid character varying(64) ) WITH (orientation=column, enable_hstore_opt=on) -- 创建表时，默认使用该配置。 DISTRIBUTE BY hash (t_gmid) --分布键，选择主键或者关联字段 PARTITION BY range(t_datatime) -- 分区键 ( partition p2024_1 start('2024-01-01') end ('2024-06-01') every (interval '1 month'), partition p2024_7 start('2024-06-01') end ('2024-12-31') every (interval '1 month') ); INSERT INTO hstore_opt_table_demo (t_code, t_gisid, t_datatime, t_gmid) VALUES ('T001','GIS001','2024-01-01 10:00:00','M001'), ('T002','GIS002','2024-01-02 10:00:00','M002'), ('T003','GIS003','2024-01-03 10:00:00','M003'), ('T004','GIS004','2024-01-04 10:00:00','M004'), ('T005','GIS005','2024-01-05 10:00:00','M005'), ('T006','GIS006','2024-01-06 10:00:00','M006'), ('T007','GIS007','2024-01-07 10:00:00','M007'), ('T008','GIS008','2024-01-08 10:00:00','M008'), ('T009','GIS009','2024-01-09 10:00:00','M009'), ('T010','GIS010','2024-01-10 10:00:00','M010'); ``` 5. 查询该表在各个DN上的cu、delta以及cudesc的脏页率以及大小。 ``` SELECT * FROM pgxc_get_cstore_dirty_ratio('hstore_opt_table_demo'); ``` ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002585788238.png "点击放大") 6. 也可以查指定分区在各个DN上的cu、delta以及cudesc的脏页率以及大小。 ``` SELECT * FROM pgxc_get_cstore_dirty_ratio('hstore_opt_table_demo','p2024_1_4'); ``` ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002585793002.png "点击放大") #### 表大小相关GUC介绍 表3表大小相关GUC介绍 | 参数名称 | 描述 | 取值范围 | 默认值 | |:---|:---|:---|:---| | **[fast_obs_tablesize_method](https://support.huaweicloud.com/devg-dws/dws_04_0922.html)** | 设置快速计算列存V3和V3 hstore_opt表大小的方式。该参数仅9.1.0.100及以上集群版本支持。 | **参数类型**：USERSET **取值范围**：枚举型 - **0**：通过遍历OBS文件的方式计算V3表大小。 - **1**：以wlm后台统计的方式，通过pg_relfilenode_size计算表大小。 - **2**：9.1.0.100版本通过cudesc的每个文件的最大偏移和近似估算表大小；9.1.1.200及以上版本通过扫描pg_db_file系统表获取大小，若enable_pg_db_file 为off，则通过估算cudesc表来获取表大小 。 | 2 | | **[fast_obs_dbsize_method](https://support.huaweicloud.com/devg-dws/dws_04_0922.html)** | 设置快速计算OBS上数据库大小的方式。该参数仅9.1.0.100及以上集群版本支持。 | **参数类型**：USERSET **取值范围**：枚举型 - **0**：直接根据OBS桶来估算数据库大小。 - **1**：遍历OBS目录计算数据库大小。 - **2**：遍历pg_db_file系统表计算数据库大小。（9.1.1.200及以上集群有该选项） | - 9.1.0.100版本为0。 - 9.1.0.200及以上版本为2。 | #### 查询V3表大小最佳配置实践 1. 表大小查询函数，在查询V3表大小时，所查询的结果都是**包含存放在OBS上的CU文件大小**。 2. 仅查询V3表OBS文件大小可以使用pg_obs_file_size函数。 3. 9.1.0.100版本查询V3表默认使用**fast_obs_tablesize_method为2**的方法，该方法性能最好，但由于是对cudesc表的估算，精准度不如fast_obs_tablesize_method为0时的方法（精确度最高，但性能最差）。 4. 9.1.1.200版本查询V3表大小的精确度和性能达到V2表的能力，但要确保obs list优化特性打开(新装默认打开，升级默认关闭，打开方式：GUC设置**enable_pg_db_file** = on，特性打开后需要等待autovacuum重建完pg_db_file系统表才可以使用)。 5. 9.1.1.100版本中，计算包含V3表的数据库大小，当集群中只有一个数据库时可通过获取OBS桶信息来计算，性能较好，若有多个数据库时需要遍历OBS上的文件，性能较差；在9.1.1.200及以上版本对数据库大小计算进行优化，相比9.1.1.100性能提升10倍，但需要确保obs list优化特性打开。 #### 查询冷热表大小最佳配置实践 1. 在查询冷热表大小时，如使用普通表大小查询函数（参见[表大小查询]），所查询的结果都是包含冷数据的大小。 2. 若要分别查询冷热表的热数据和冷数据大小时，可以使用pg_lifecycle_table_data_distribute函数，参见[冷热表大小查询]。