分区表统计信息函数

• gs_stat_get_partition_stats(oid)

  描述：获取特定分区的统计信息。

  返回值类型：record

• gs_stat_get_xact_partition_stats(oid)

  描述：获取特定分区的事务中统计信息。

  返回值类型：record

• gs_stat_get_all_partitions_stats()

  描述：获取所有分区的统计信息。

  返回值类型：setof record

• gs_stat_get_xact_all_partitions_stats()

  描述：获取所有分区的事务中统计信息。

  返回值类型：setof record

• gs_statio_get_all_partitions_stats()

  描述：获取所有分区的I/O统计信息。

  返回值类型：setof record

  上述五个函数示例：

  

  运行时统计信息上报是异步的，且基于UDP协议，后台线程处理可能存在延迟和丢包，此处示例预期仅供参考。

  事务外统计信息查询

  gaussdb=# CREATE TABLE part_tab1gaussdb-# (gaussdb(#     a int, b intgaussdb(# )gaussdb-# PARTITION BY RANGE(b)gaussdb-# (gaussdb(#     PARTITION P1 VALUES LESS THAN(10),gaussdb(#     PARTITION P2 VALUES LESS THAN(20),gaussdb(#     PARTITION P3 VALUES LESS THAN(MAXVALUE)gaussdb(# );CREATE TABLEgaussdb=# CREATE TABLE subpart_tab1gaussdb-# (gaussdb(#     month_code VARCHAR2 ( 30 ) NOT NULL ,gaussdb(#     dept_code  VARCHAR2 ( 30 ) NOT NULL ,gaussdb(#     user_no    VARCHAR2 ( 30 ) NOT NULL ,gaussdb(#     sales_amt  intgaussdb(# )gaussdb-# PARTITION BY RANGE (month_code) SUBPARTITION BY RANGE (dept_code)gaussdb-# (gaussdb(#   PARTITION p_201901 VALUES LESS THAN( '201903' )gaussdb(#   (gaussdb(#     SUBPARTITION p_201901_a VALUES LESS THAN( '2' ),gaussdb(#     SUBPARTITION p_201901_b VALUES LESS THAN( '3' )gaussdb(#   ),gaussdb(#   PARTITION p_201902 VALUES LESS THAN( '201904' )gaussdb(#   (gaussdb(#     SUBPARTITION p_201902_a VALUES LESS THAN( '2' ),gaussdb(#     SUBPARTITION p_201902_b VALUES LESS THAN( '3' )gaussdb(#   )gaussdb(# );CREATE TABLEgaussdb=# CREATE INDEX index_part_tab1 ON part_tab1(b) LOCALgaussdb-# (gaussdb(# PARTITION b_index1,gaussdb(# PARTITION b_index2,gaussdb(# PARTITION b_index3gaussdb(# );CREATE INDEXgaussdb=# CREATE INDEX idx_user_no ON subpart_tab1(user_no) LOCAL;CREATE INDEXgaussdb=# INSERT INTO part_tab1 VALUES(1, 1);INSERT 0 1gaussdb=# INSERT INTO part_tab1 VALUES(1, 11);INSERT 0 1gaussdb=# INSERT INTO part_tab1 VALUES(1, 21);INSERT 0 1gaussdb=# UPDATE part_tab1 SET a = 2 WHERE b = 1;UPDATE 1gaussdb=# UPDATE part_tab1 SET a = 3 WHERE b = 11;UPDATE 1gaussdb=# UPDATE /*+ indexscan(part_tab1) */ part_tab1 SET a = 4 WHERE b = 21;UPDATE 1gaussdb=# DELETE FROM part_tab1;DELETE 3gaussdb=# ANALYZE part_tab1;ANALYZEgaussdb=# VACUUM part_tab1;VACUUMgaussdb=# INSERT INTO subpart_tab1 VALUES('201902', '1', '1', 1);INSERT 0 1gaussdb=# INSERT INTO subpart_tab1 VALUES('201902', '2', '2', 1);INSERT 0 1gaussdb=# INSERT INTO subpart_tab1 VALUES('201903', '1', '3', 1);INSERT 0 1gaussdb=# INSERT INTO subpart_tab1 VALUES('201903', '2', '4', 1);INSERT 0 1gaussdb=# UPDATE subpart_tab1 SET sales_amt = 2 WHERE user_no='1';UPDATE 1gaussdb=# UPDATE subpart_tab1 SET sales_amt = 3 WHERE user_no='2';UPDATE 1gaussdb=# UPDATE subpart_tab1 SET sales_amt = 4 WHERE user_no='3';UPDATE 1gaussdb=# UPDATE /*+ indexscan(subpart_tab1) */ subpart_tab1 SET sales_amt = 5 WHERE user_no='4';UPDATE 1gaussdb=# DELETE FROM subpart_tab1;DELETE 4gaussdb=# ANALYZE subpart_tab1;ANALYZEgaussdb=# VACUUM subpart_tab1;VACUUMgaussdb=# SELECT * FROM gs_stat_all_partitions; partition_oid | schemaname |   relname    | partition_name | sub_partition_name | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup |          last_vacuum          |    last_autovacuum     |         last_analyze          |    last_autoanalyze    | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count ---------------+------------+--------------+----------------+--------------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+-------------------------------+------------------------+-------------------------------+------------------------+--------------+------------------+---------------+-------------------         16964 | public     | subpart_tab1 | p_201902       | p_201902_b         |        5 |            1 |        4 |             1 |         1 |         1 |         1 |             1 |          0 |          1 | 2023-05-15 20:36:45.293965+08 | 2000-01-01 08:00:00+08 | 2023-05-15 20:36:44.688861+08 | 2000-01-01 08:00:00+08 |            1 |                0 |             1 |                 0         16963 | public     | subpart_tab1 | p_201902       | p_201902_a         |        5 |            1 |        4 |             0 |         1 |         1 |         1 |             1 |          0 |          1 | 2023-05-15 20:36:45.291022+08 | 2000-01-01 08:00:00+08 | 2023-05-15 20:36:44.688843+08 | 2000-01-01 08:00:00+08 |            1 |                0 |             1 |                 0         16961 | public     | subpart_tab1 | p_201901       | p_201901_b         |        5 |            1 |        4 |             0 |         1 |         1 |         1 |             1 |          0 |          1 | 2023-05-15 20:36:45.288037+08 | 2000-01-01 08:00:00+08 | 2023-05-15 20:36:44.688829+08 | 2000-01-01 08:00:00+08 |            1 |                0 |             1 |                 0         16960 | public     | subpart_tab1 | p_201901       | p_201901_a         |        5 |            1 |        4 |             0 |         1 |         1 |         1 |             1 |          0 |          1 | 2023-05-15 20:36:45.285311+08 | 2000-01-01 08:00:00+08 | 2023-05-15 20:36:44.688802+08 | 2000-01-01 08:00:00+08 |            1 |                0 |             1 |                 0         16954 | public     | part_tab1    | p3             |                    |        2 |            1 |        1 |             1 |         1 |         1 |         1 |             1 |          0 |          1 | 2023-05-15 20:36:29.490636+08 | 2000-01-01 08:00:00+08 | 2023-05-15 20:36:28.540115+08 | 2000-01-01 08:00:00+08 |            1 |                0 |             1 |                 0         16953 | public     | part_tab1    | p2             |                    |        4 |            1 |        1 |             0 |         1 |         1 |         1 |             1 |          0 |          1 | 2023-05-15 20:36:29.487914+08 | 2000-01-01 08:00:00+08 | 2023-05-15 20:36:28.540098+08 | 2000-01-01 08:00:00+08 |            1 |                0 |             1 |                 0         16952 | public     | part_tab1    | p1             |                    |        5 |            1 |        1 |             0 |         1 |         1 |         1 |             1 |          0 |          1 | 2023-05-15 20:36:29.48536+08  | 2000-01-01 08:00:00+08 | 2023-05-15 20:36:28.540071+08 | 2000-01-01 08:00:00+08 |            1 |                0 |             1 |                 0(7 rows)gaussdb=# SELECT * FROM gs_statio_all_partitions; partition_oid | schemaname |   relname    | partition_name | sub_partition_name | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit ---------------+------------+--------------+----------------+--------------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------         16964 | public     | subpart_tab1 | p_201902       | p_201902_b         |              4 |             8 |             2 |           21 |                 |                |                |           16963 | public     | subpart_tab1 | p_201902       | p_201902_a         |              4 |             8 |             2 |           21 |                 |                |                |           16961 | public     | subpart_tab1 | p_201901       | p_201901_b         |              4 |             8 |             2 |           21 |                 |                |                |           16960 | public     | subpart_tab1 | p_201901       | p_201901_a         |              4 |             8 |             2 |           21 |                 |                |                |           16954 | public     | part_tab1    | p3             |                    |              4 |             8 |             2 |           15 |                 |                |                |           16953 | public     | part_tab1    | p2             |                    |              4 |             8 |             2 |           15 |                 |                |                |           16952 | public     | part_tab1    | p1             |                    |              4 |             8 |             2 |           15 |                 |                |                |  (7 rows)gaussdb=# SELECT * FROM gs_stat_get_partition_stats(16952); partition_oid | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup |         last_vacuum          |    last_autovacuum     |         last_analyze          |    last_autoanalyze    | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count |   last_data_changed    | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | tup_fetch | block_fetch ---------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+------------------------------+------------------------+-------------------------------+------------------------+--------------+------------------+---------------+-------------------+------------------------+----------------+---------------+---------------+--------------+-----------+-------------         16952 |        5 |            1 |        1 |             0 |         1 |         1 |         1 |             1 |          0 |          1 | 2023-05-15 20:36:29.48536+08 | 2000-01-01 08:00:00+08 | 2023-05-15 20:36:28.540071+08 | 2000-01-01 08:00:00+08 |            1 |                0 |             1 |                 0 | 2000-01-01 08:00:00+08 |              4 |             8 |             2 |           21 |         0 |          12(1 row)

  事务内统计信息查询：

  gaussdb=# BEGIN;BEGINgaussdb=# INSERT INTO part_tab1 VALUES(1, 1);INSERT 0 1gaussdb=# INSERT INTO part_tab1 VALUES(1, 11);INSERT 0 1gaussdb=# INSERT INTO part_tab1 VALUES(1, 21);INSERT 0 1gaussdb=# UPDATE part_tab1 SET a = 2 WHERE b = 1;UPDATE 1gaussdb=# UPDATE part_tab1 SET a = 3 WHERE b = 11;UPDATE 1gaussdb=# UPDATE /*+ indexscan(part_tab1) */ part_tab1 SET a = 4 WHERE b = 21;UPDATE 1gaussdb=# DELETE FROM part_tab1;DELETE 3gaussdb=# INSERT INTO subpart_tab1 VALUES('201902', '1', '1', 1);INSERT 0 1gaussdb=# INSERT INTO subpart_tab1 VALUES('201902', '2', '2', 1);INSERT 0 1gaussdb=# INSERT INTO subpart_tab1 VALUES('201903', '1', '3', 1);INSERT 0 1gaussdb=# INSERT INTO subpart_tab1 VALUES('201903', '2', '4', 1);INSERT 0 1gaussdb=# UPDATE subpart_tab1 SET sales_amt = 2 WHERE user_no='1';UPDATE 1gaussdb=# UPDATE subpart_tab1 SET sales_amt = 3 WHERE user_no='2';UPDATE 1gaussdb=# UPDATE subpart_tab1 SET sales_amt = 4 WHERE user_no='3';UPDATE 1gaussdb=# UPDATE /*+ indexscan(subpart_tab1) */ subpart_tab1 SET sales_amt = 5 WHERE user_no='4';UPDATE 1gaussdb=# DELETE FROM subpart_tab1;DELETE 4gaussdb=# SELECT * FROM gs_stat_xact_all_partitions; partition_oid | schemaname |   relname    | partition_name | sub_partition_name | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd ---------------+------------+--------------+----------------+--------------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------         16964 | public     | subpart_tab1 | p_201902       | p_201902_b         |        4 |            4 |        1 |             2 |         1 |         1 |         1 |             1         16963 | public     | subpart_tab1 | p_201902       | p_201902_a         |        4 |            4 |        1 |             0 |         1 |         1 |         1 |             1         16961 | public     | subpart_tab1 | p_201901       | p_201901_b         |        4 |            4 |        1 |             0 |         1 |         1 |         1 |             1         16960 | public     | subpart_tab1 | p_201901       | p_201901_a         |        4 |            4 |        1 |             0 |         1 |         1 |         1 |             1         16954 | public     | part_tab1    | p3             |                    |        1 |            1 |        1 |             2 |         1 |         1 |         1 |             1         16953 | public     | part_tab1    | p2             |                    |        3 |            2 |        0 |             0 |         1 |         1 |         1 |             1         16952 | public     | part_tab1    | p1             |                    |        4 |            2 |        0 |             0 |         1 |         1 |         1 |             1(7 rows)gaussdb=# SELECT * FROM gs_stat_get_xact_partition_stats(16952); partition_oid | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | tup_fetch ---------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+-----------         16952 |        4 |            2 |        0 |             0 |         1 |         1 |         1 |             1 |         0(1 row)

• gs_stat_get_partition_analyze_count(oid)

  描述：用户在该分区上启动分析的次数

  返回值类型：bigint

• gs_stat_get_partition_autoanalyze_count(oid)

  描述：autovacuum守护进程在该分区上启动分析的次数。

  返回值类型：bigint

• gs_stat_get_partition_autovacuum_count(oid)

  描述：autovacuum守护进程在该分区上启动清理的次数。

  返回值类型：bigint

• gs_stat_get_partition_last_analyze_time(oid)

  描述：用户在该分区上最后一次手动启动分析或者autovacuum线程启动分析的时间。

  返回值类型：timestamptz

• gs_stat_get_partition_last_autoanalyze_time(oid)

  描述：autovacuum守护进程在该分区上最后一次启动分析的时间。

  返回值类型：timestamptz

• gs_stat_get_partition_last_autovacuum_time(oid)

  描述：autovacuum守护进程在该分区上最后一次启动清理的时间。

  返回值类型：timestamptz

• gs_stat_get_partition_last_data_changed_time(oid)

  描述：对于在分区上的修改insert/update/delete/truncate，在该表上最后一次操作的时间。当前暂不支持。

  返回值类型：timestamptz

• gs_stat_get_partition_last_vacuum_time(oid)

  描述：用户在该分区上最后一次手动启动清理或者autovacuum线程启动清理的时间。

  返回值类型：timestamptz

• gs_stat_get_partition_numscans(oid)

  描述：分区顺序扫描读取的行数目。

  返回值类型：bigint

• gs_stat_get_partition_tuples_returned(oid)

  描述：分区顺序扫描读取的行数目。

  返回值类型：bigint

• gs_stat_get_partition_tuples_fetched(oid)

  描述：分区位图扫描抓取的行数目。

  返回值类型：bigint

• gs_stat_get_partition_vacuum_count(oid)

  描述：用户在该分区上启动清理的次数。

  返回值类型：bigint

• gs_stat_get_xact_partition_tuples_fetched(oid)

  描述：事务中扫描的tuple行数。

  返回值类型：bigint

• gs_stat_get_xact_partition_numscans(oid)

  描述：当前事务中分区执行的顺序扫描次数。

  返回值类型：bigint

• gs_stat_get_xact_partition_tuples_returned(oid)

  描述：当前事务中分区通过顺序扫描读取的行数。

  返回值类型：bigint

• gs_stat_get_partition_blocks_fetched(oid)

  描述：分区的磁盘块抓取请求的数量。

  返回值类型：bigint

• gs_stat_get_partition_blocks_hit(oid)

  描述：在缓冲区中找到的分区的磁盘块请求数目。

  返回值类型：bigint

• pg_stat_get_partition_tuples_inserted(oid)

  描述：插入相应表分区中行的数量。

  返回值类型：bigint

• pg_stat_get_partition_tuples_updated(oid)

  描述：在相应表分区中已更新行的数量。

  返回值类型：bigint

• pg_stat_get_partition_tuples_deleted(oid)

  描述：从相应表分区中删除行的数量。

  返回值类型：bigint

• pg_stat_get_partition_tuples_changed(oid)

  描述：该表分区上一次analyze或autoanalyze之后插入、更新、删除行的总数量。

  返回值类型：bigint

• pg_stat_get_partition_live_tuples(oid)

  描述：分区表活行数。

  返回值类型：bigint

• pg_stat_get_partition_dead_tuples(oid)

  描述：分区表死行数。

  返回值类型：bigint

• pg_stat_get_xact_partition_tuples_inserted(oid)

  描述：表分区相关的活跃子事务中插入的tuple数。

  返回值类型：bigint

• pg_stat_get_xact_partition_tuples_deleted(oid)

  描述：表分区相关的活跃子事务中删除的tuple数。

  返回值类型：bigint

• pg_stat_get_xact_partition_tuples_hot_updated(oid)

  描述：表分区相关的活跃子事务中热更新的tuple数。

  返回值类型：bigint

• pg_stat_get_xact_partition_tuples_updated(oid)

  描述：表分区相关的活跃子事务中更新的tuple数。

  返回值类型：bigint

• pg_stat_get_partition_tuples_hot_updated

  描述：返回给定分区id的分区热更新元组数的统计。

  参数：oid

  返回值类型：bigint