Updated on 2025-05-29 GMT+08:00

Functions for Collecting Statistics in Partitioned Tables

gs_stat_get_partition_stats(oid)

Description: Obtains the statistics of a specific partition.

Return type: record

gs_stat_get_xact_partition_stats(oid)

Description: Obtains transaction statistics of a specific partition.

Return type: record

gs_stat_get_all_partitions_stats()

Description: Obtains the statistics of all partitions.

Return type: SETOF record

gs_stat_get_xact_all_partitions_stats()

Description: Obtains transaction statistics of all partitions.

Return type: SETOF record

gs_statio_get_all_partitions_stats()

Description: Obtains the I/O statistics of all partitions.

Return type: SETOF record

Examples of the preceding five functions

Statistics are reported asynchronously during execution. Based on UDP, delay and packet loss may occur during backend thread processing. The following example is for reference only.

Querying out-of-transaction statistics:

gaussdb=# CREATE TABLE part_tab1
gaussdb-# (
gaussdb(#     a int, b int
gaussdb(# )
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 TABLE
gaussdb=# CREATE TABLE subpart_tab1
gaussdb-# (
gaussdb(#     month_code VARCHAR2 ( 30 ) NOT NULL ,
gaussdb(#     dept_code  VARCHAR2 ( 30 ) NOT NULL ,
gaussdb(#     user_no    VARCHAR2 ( 30 ) NOT NULL ,
gaussdb(#     sales_amt  int
gaussdb(# )
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 TABLE
gaussdb=# CREATE INDEX index_part_tab1 ON part_tab1(b) LOCAL
gaussdb-# (
gaussdb(# PARTITION b_index1,
gaussdb(# PARTITION b_index2,
gaussdb(# PARTITION b_index3
gaussdb(# );
CREATE INDEX
gaussdb=# CREATE INDEX idx_user_no ON subpart_tab1(user_no) LOCAL;
CREATE INDEX
gaussdb=# INSERT INTO part_tab1 VALUES(1, 1);
INSERT 0 1
gaussdb=# INSERT INTO part_tab1 VALUES(1, 11);
INSERT 0 1
gaussdb=# INSERT INTO part_tab1 VALUES(1, 21);
INSERT 0 1
gaussdb=# UPDATE part_tab1 SET a = 2 WHERE b = 1;
UPDATE 1
gaussdb=# UPDATE part_tab1 SET a = 3 WHERE b = 11;
UPDATE 1
gaussdb=# UPDATE /*+ indexscan(part_tab1) */ part_tab1 SET a = 4 WHERE b = 21;
UPDATE 1
gaussdb=# DELETE FROM part_tab1;
DELETE 3
gaussdb=# ANALYZE part_tab1;
ANALYZE
gaussdb=# VACUUM part_tab1;
VACUUM
gaussdb=# INSERT INTO subpart_tab1 VALUES('201902', '1', '1', 1);
INSERT 0 1
gaussdb=# INSERT INTO subpart_tab1 VALUES('201902', '2', '2', 1);
INSERT 0 1
gaussdb=# INSERT INTO subpart_tab1 VALUES('201903', '1', '3', 1);
INSERT 0 1
gaussdb=# INSERT INTO subpart_tab1 VALUES('201903', '2', '4', 1);
INSERT 0 1
gaussdb=# UPDATE subpart_tab1 SET sales_amt = 2 WHERE user_no='1';
UPDATE 1
gaussdb=# UPDATE subpart_tab1 SET sales_amt = 3 WHERE user_no='2';
UPDATE 1
gaussdb=# UPDATE subpart_tab1 SET sales_amt = 4 WHERE user_no='3';
UPDATE 1
gaussdb=# UPDATE /*+ indexscan(subpart_tab1) */ subpart_tab1 SET sales_amt = 5 WHERE user_no='4';
UPDATE 1
gaussdb=# DELETE FROM subpart_tab1;
DELETE 4
gaussdb=# ANALYZE subpart_tab1;
ANALYZE
gaussdb=# VACUUM subpart_tab1;
VACUUM
gaussdb=# 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 | t
idx_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_re
ad | 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+0
8 | 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)

Querying statistics within a transaction

gaussdb=# BEGIN;
BEGIN
gaussdb=# INSERT INTO part_tab1 VALUES(1, 1);
INSERT 0 1
gaussdb=# INSERT INTO part_tab1 VALUES(1, 11);
INSERT 0 1
gaussdb=# INSERT INTO part_tab1 VALUES(1, 21);
INSERT 0 1
gaussdb=# UPDATE part_tab1 SET a = 2 WHERE b = 1;
UPDATE 1
gaussdb=# UPDATE part_tab1 SET a = 3 WHERE b = 11;
UPDATE 1
gaussdb=# UPDATE /*+ indexscan(part_tab1) */ part_tab1 SET a = 4 WHERE b = 21;
UPDATE 1
gaussdb=# DELETE FROM part_tab1;
DELETE 3
gaussdb=# INSERT INTO subpart_tab1 VALUES('201902', '1', '1', 1);
INSERT 0 1
gaussdb=# INSERT INTO subpart_tab1 VALUES('201902', '2', '2', 1);
INSERT 0 1
gaussdb=# INSERT INTO subpart_tab1 VALUES('201903', '1', '3', 1);
INSERT 0 1
gaussdb=# INSERT INTO subpart_tab1 VALUES('201903', '2', '4', 1);
INSERT 0 1
gaussdb=# UPDATE subpart_tab1 SET sales_amt = 2 WHERE user_no='1';
UPDATE 1
gaussdb=# UPDATE subpart_tab1 SET sales_amt = 3 WHERE user_no='2';
UPDATE 1
gaussdb=# UPDATE subpart_tab1 SET sales_amt = 4 WHERE user_no='3';
UPDATE 1
gaussdb=# UPDATE /*+ indexscan(subpart_tab1) */ subpart_tab1 SET sales_amt = 5 WHERE user_no='4';
UPDATE 1
gaussdb=# DELETE FROM subpart_tab1;
DELETE 4
gaussdb=# 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)

Description: Specifies the number of times that a user starts analysis on a partition.

Return type: bigint

gs_stat_get_partition_autoanalyze_count(oid)

Description: Specifies the number of times that the autovacuum daemon thread starts analysis in a partition.

Return type: bigint

gs_stat_get_partition_autovacuum_count(oid)

Description: Specifies the number of times that the autovacuum daemon thread starts vacuum in a partition.

Return type: bigint

gs_stat_get_partition_last_analyze_time(oid)

Description: Specifies the last time when a partition starts to be analyzed manually or by the autovacuum thread.

Return type: timestamptz

gs_stat_get_partition_last_autoanalyze_time(oid)

Description: Specifies the time when the last analysis initiated by the autovacuum daemon thread in a partition.

Return type: timestamptz

gs_stat_get_partition_last_autovacuum_time(oid)

Description: Specifies the time of the last vacuum initiated by the autovacuum daemon thread in a partition.

Return type: timestamptz

gs_stat_get_partition_last_data_changed_time(oid)

Description: Specifies the last time of a modification in a partition, such as insert, update, delete, and truncate. Currently, this parameter is not supported.

Return type: timestamptz

gs_stat_get_partition_last_vacuum_time(oid)

Description: Specifies the most recent time when the user manually cleared a table or when the autovacuum thread was started to clear a partition.

Return type: timestamptz

gs_stat_get_partition_numscans(oid)

Description: Specifies the number of sequential scans in a partition.

Return type: bigint

gs_stat_get_partition_tuples_returned(oid)

Description: Specifies the number of rows read by sequential scan in a partition.

Return type: bigint

gs_stat_get_partition_tuples_fetched(oid)

Description: Specifies the number of rows fetched by bitmap scans in a partition.

Return type: bigint

gs_stat_get_partition_vacuum_count(oid)

Description: Specifies the number of times that a user starts vacuum in a partition.

Return type: bigint

gs_stat_get_xact_partition_tuples_fetched(oid)

Description: Specifies the number of tuple rows scanned in a transaction.

Return type: bigint

gs_stat_get_xact_partition_numscans(oid)

Description: Specifies the number of sequential scans performed on a partition in the current transaction.

Return type: bigint

gs_stat_get_xact_partition_tuples_returned(oid)

Description: Specifies the number of rows read through sequential scans in a partition in the current transaction.

Return type: bigint

gs_stat_get_partition_blocks_fetched(oid)

Description: Specifies the number of disk block fetch requests for a partition.

Return type: bigint

gs_stat_get_partition_blocks_hit(oid)

Description: Specifies the number of disk block requests found in cache for a partition.

Return type: bigint

pg_stat_get_partition_tuples_inserted(oid)

Description: Specifies the number of rows inserted into a specified table partition.

Return type: bigint

pg_stat_get_partition_tuples_updated(oid)

Description: Specifies the number of rows updated in a specified table partition.

Return type: bigint

pg_stat_get_partition_tuples_deleted(oid)

Description: Specifies the number of rows deleted from a specified table partition.

Return type: bigint

pg_stat_get_partition_tuples_changed(oid)

Description: Specifies the total number of inserted, updated, and deleted rows after a table partition was last analyzed or autoanalyzed.

Return type: bigint

pg_stat_get_partition_live_tuples(oid)

Description: Specifies the number of live rows in a partitioned table.

Return type: bigint

pg_stat_get_partition_dead_tuples(oid)

Description: Specifies the number of dead rows in a partitioned table. It indicates the number of inactive row pointers in Ustore tables.

Return type: bigint

pg_stat_get_xact_partition_tuples_inserted(oid)

Description: Specifies the number of inserted tuples in the active sub-transactions related to a table partition.

Return type: bigint

pg_stat_get_xact_partition_tuples_deleted(oid)

Description: Specifies the number of deleted tuples in the active sub-transactions related to a table partition.

Return type: bigint

pg_stat_get_xact_partition_tuples_hot_updated(oid)

Description: Specifies the number of hot updated tuples in the active sub-transactions related to a table partition.

Return type: bigint

pg_stat_get_xact_partition_tuples_updated(oid)

Description: Specifies the number of updated tuples in the active sub-transactions related to a table partition.

Return type: bigint

pg_stat_get_partition_tuples_hot_updated(oid)

Description: Returns statistics on the number of hot updated tuples in a partition with a specified partition ID.

Parameter: oid

Return type: bigint

gs_session_alt_status()

Description: Queries the recoverable status of unplanned ALT sessions executed by the current user. Session status changes caused by unsupported driver API calling are not included. Only basic categories are provided for unrecoverable causes. If GUC parameters, SQL statements, or system functions are not supported, query the list supported by the ALT to check whether the requests called by the application are supported. For details, see section "Unplanned Application Lossless and Transparent" in Feature Guide. ALT support policy list description for SQL syntax and system functions.

Return type: SETOF record

Name

Type

Description

sessionid

integer

Session ID.

sessionreplaystatus

text

ALT session recovery status.

  • enable: recoverable.
  • disable: unrecoverable.

xactreplaystatus

text

ALT transaction replay status.

  • enable: recoverable.
  • disable: unrecoverable.

reason

text

Description of the reason why the fault cannot be rectified.

Example:

1
2
3
4
5
6
gaussdb=# SELECT * FROM GS_SESSION_ALT_STATUS();
    sessionid    | sessionreplaystatus | xactreplaystatus |                                         reason                                         
-----------------+---------------------+------------------+----------------------------------------------------------------------------------------
 140564166014720 | disable             | disable          | Session data types(valid after transaction is finished):unsupported set from current, +
                 |                     |                  | Transaction non-replayable types:advisory lock, 
(1 row)