Help Center > > Developer Guide> SQL Reference> Functions and Operators> Statistics Information Functions

Statistics Information Functions

Updated at: Jul 15, 2020 GMT+08:00

Statistics information functions are divided into the following two categories: functions that access databases, using the OID of each table or index in a database to mark the database for which statistics are generated; functions that access servers, identified by the server process ID, whose value ranges from 1 to the number of currently active servers.

  • pg_stat_get_db_numbackends(oid)

    Description: Number of active server processes for a database

    Return type: integer

  • pg_stat_get_db_xact_commit(oid)

    Description: Number of transactions committed in a database

    Return type: bigint

  • pg_stat_get_db_xact_rollback(oid)

    Description: Number of transactions rolled back in a database

    Return type: bigint

  • pg_stat_get_db_blocks_fetched(oid)

    Description: Number of disk blocks fetch requests for a database

    Return type: bigint

  • pg_stat_get_db_blocks_hit(oid)

    Description: Number of disk block fetch requests found in cache for a database

    Return type: bigint

  • pg_stat_get_db_tuples_returned(oid)

    Description: Number of tuples returned for a database

    Return type: bigint

  • pg_stat_get_db_tuples_fetched(oid)

    Description: Number of tuples fetched for a database

    Return type: bigint

  • pg_stat_get_db_tuples_inserted(oid)

    Description: Number of tuples inserted in a database

    Return type: bigint

  • pg_stat_get_db_tuples_updated(oid)

    Description: Number of tuples updated in a database

    Return type: bigint

  • pg_stat_get_db_tuples_deleted(oid)

    Description: Number of tuples deleted in a database

    Return type: bigint

  • pg_stat_get_db_conflict_lock(oid)

    Description: Number of lock conflicts in a database

    Return type: bigint

  • pg_stat_get_db_deadlocks(oid)

    Description: Number of deadlocks in a database

    Return type: bigint

  • pg_stat_get_numscans(oid)

    Description: Number of sequential row scans done if parameters are in a table

    or number of index scans done if parameters are in an index

    Return type: bigint

  • pg_stat_get_tuples_returned(oid)

    Description: Number of sequential row scans done if parameters are in a table

    or number of index entries returned if parameters are in an index

    Return type: bigint

  • pg_stat_get_tuples_fetched(oid)

    Description: Number of table rows fetched by bitmap scans if parameters are in a table,

    or table rows fetched by simple index scans using the index if parameters are in an index

    Return type: bigint

  • pg_stat_get_tuples_inserted(oid)

    Description: Number of rows inserted into table

    Return type: bigint

  • pg_stat_get_tuples_updated(oid)

    Description: Number of rows updated in table

    Return type: bigint

  • pg_stat_get_tuples_deleted(oid)

    Description: Number of rows deleted from table

    Return type: bigint

  • pg_stat_get_tuples_changed(oid)

    Description: Total number of inserted, updated, and deleted rows after the table was last analyzed or autoanalyzed

    Return type: bigint

  • pg_stat_get_tuples_hot_updated(oid)

    Description: Number of rows HOT-updated in table

    Return type: bigint

  • pg_stat_get_live_tuples(oid)

    Description: Number of live rows in table

    Return type: bigint

  • pg_stat_get_dead_tuples(oid)

    Description: Number of dead rows in table

    Return type: bigint

  • pg_stat_get_blocks_fetched(oid)

    Description: Number of disk block fetch requests for table or index

    Return type: bigint

  • pg_stat_get_blocks_hit(oid)

    Description: Number of disk block requests found in cache for table or index

    Return type: bigint

  • pg_stat_get_partition_tuples_inserted(oid)

    Description: Number of rows in the corresponding table partition

    Return type: bigint

  • pg_stat_get_partition_tuples_updated(oid)

    Description: Number of rows that have been updated in the corresponding table partition

    Return type: bigint

  • pg_stat_get_partition_tuples_deleted(oid)

    Description: Number of rows deleted from the corresponding table partition

    Return type: bigint

  • pg_stat_get_partition_tuples_changed(oid)

    Description: Total number of inserted, updated, and deleted rows after the table partition was last analyzed or autoanalyzed

    Return type: bigint

  • pg_stat_get_partition_live_tuples(oid)

    Description: Number of live rows in a table partition

    Return type: bigint

  • pg_stat_get_partition_dead_tuples(oid)

    Description: Number of dead rows in a table partition

    Return type: bigint

  • pg_stat_get_xact_tuples_inserted(oid)

    Description: Number of tuple inserted into the active subtransactions related to the table.

    Return type: bigint

  • pg_stat_get_xact_tuples_deleted(oid)

    Description: Number of deleted tuples in the active subtransactions related to a table

    Return type: bigint

  • pg_stat_get_xact_tuples_hot_updated(oid)

    Description: Number of hot updated tuples in the active subtransactions related to a table

    Return type: bigint

  • pg_stat_get_xact_tuples_updated(oid)

    Description: Number of updated tuples in the active subtransactions related to a table

    Return type: bigint

  • pg_stat_get_xact_partition_tuples_inserted(oid)

    Description: Number of inserted tuples in the active subtransactions related to a table partition

    Return type: bigint

  • pg_stat_get_xact_partition_tuples_deleted(oid)

    Description: Number of deleted tuples in the active subtransactions related to a table partition

    Return type: bigint

  • pg_stat_get_xact_partition_tuples_hot_updated(oid)

    Description: Number of hot updated tuples in the active subtransactions related to a table partition

    Return type: bigint

  • pg_stat_get_xact_partition_tuples_updated(oid)

    Description: Number of updated tuples in the active subtransactions related to a table partition

    Return type: bigint

  • pg_stat_get_last_vacuum_time(oid)

    Description: Last time when the autovacuum thread is manually started to clear a table

    Return type: timestamptz

  • pg_stat_get_last_autovacuum_time(oid)

    Description: Time of the last vacuum initiated by the autovacuum daemon on this table

    Return type: timestamptz

  • pg_stat_get_vacuum_count(oid)

    Description: Number of times a table is manually cleared

    Return type: bigint

  • pg_stat_get_autovacuum_count(oid)

    Description: Number of times the autovacuum daemon is started to clear a table

    Return type: bigint

  • pg_stat_get_last_analyze_time(oid)

    Description: Last time when a table starts to be analyzed manually or by the autovacuum thread

    Return type: timestamptz

  • pg_stat_get_last_autoanalyze_time(oid)

    Description: Time of the last analysis initiated by the autovacuum daemon on this table

    Return type: timestamptz

  • pg_stat_get_analyze_count(oid)

    Description: Number of times a table is manually analyzed

    Return type: bigint

  • pg_stat_get_autoanalyze_count(oid)

    Description: Number of times the autovacuum daemon analyzes a table

    Return type: bigint

  • pg_total_autovac_tuples(bool,bool)

    Description: Returns tuple records related to the total autovac, such as nodename, nspname, relname, and tuple IUDs. The input parameters specify whether to query relation and local information, respectively.

    Return type: setofrecord

  • pg_autovac_status(oid)

    Description: Returns autovac information, such as nodename, nspname, relname, analyze, vacuum, thresholds of analyze and vacuum, and the number of analyzed or vacuumed tuples.

    Return type: setofrecord

  • pg_autovac_timeout(oid)

    Description: Returns the number of consecutive timeouts during the autovac operation on a table. If the table information is invalid or the node information is abnormal, NULL will be returned.

    Return type: bigint

  • pg_autovac_coordinator(oid)

    Description: Returns the name of the CN performing the autovac operation on a table. If the table information is invalid or the node information is abnormal, NULL will be returned.

    Return type: text

  • pgxc_get_wlm_session_info_bytime(text, timestamp without time zone, timestamp without time zone, int)

    Description: The query performance of the PGXC_WLM_SESSION_INFO view is poor if the view contains a large number of records. In this case, you are advised to use this function to filter the query. The input parameters are time column (start_time or finish_time), start time, end time, and maximum number of records returned for each CN. The return result is a subset of records in the GS_WLM_SESSION_HISTORY view.

    Return type: setofrecord

  • pgxc_get_wlm_current_instance_info(text, int default null)

    Description: Queries the current resource usage of each node in the cluster on the CN and reads the data that is not stored in the GS_WLM_INSTANCE_HISTORY system catalog in the memory. The input parameters are the node name (ALL, C, D, or instance name) and the maximum number of records returned by each node. The returned value is GS_WLM_INSTANCE_HISTORY.

    Return type: setofrecord

  • pgxc_get_wlm_history_instance_info(text, TIMESTAMP, TIMESTAMP, int default null)

    Description: Queries the historical resource usage of each cluster node on the CN node and reads data from the GS_WLM_INSTANCE_HISTORY system catalog. The input parameters are as follows: node name (ALL, C, D, or instance name), start time, end time, and maximum number of records returned for each instance. The returned value is GS_WLM_INSTANCE_HISTORY.

    Return type: setofrecord

  • pg_stat_get_last_data_changed_time(oid)

    Description: Returns the time when INSERT, UPDATE, DELETE, or EXCHANGE/TRUNCATE/DROP PARTITION was performed last time on a table. The data in the last_data_changed column of the PG_STAT_ALL_TABLES view is calculated by using this function. The performance of obtaining the last modification time by using the view is poor when the table has a large amount of data. In this case, you are advised to use the function.

    Return type: timestamptz

  • pg_stat_set_last_data_changed_time(oid)

    Description: Manually changes the time when INSERT, UPDATE, DELETE, or EXCHANGE/TRUNCATE/DROP PARTITION was performed last time.

    Return type: void

  • pg_backend_pid()

    Description: Thread ID of the server thread attached to the current session

    Return type: integer

  • pv_session_time()

    Description: Collects statistics on the running time of each session thread on the current node and the time consumed in each execution phase.

    Return type: record

  • pv_instance_time()

    Description: Collects statistics on the running time of the current node and the time consumed in each execution phase.

    Return type: record

  • pg_stat_get_activity(integer)

    Description: Returns a record about the backend with the specified PID. A record for each active backend in the system is returned if NULL is specified. The return result is a subset of records (excluding the connection_info column) in the PG_STAT_ACTIVITY view.

    Return type: setofrecord

  • pg_stat_get_activity_with_conninfo(integer)

    Description: Returns a record about the backend with the specified PID. A record for each active backend in the system is returned if NULL is specified. The return result is a subset of records in the PG_STAT_ACTIVITY view.

    Return type: setofrecord

  • pg_user_iostat(text)

    Description: Displays the I/O load management information about the job currently executed by the user.

    Return type: record

    The following table describes return fields.

    Name

    Type

    Description

    userid

    oid

    User ID

    min_curr_iops

    int4

    Minimum I/O of the current user across DNs. The IOPS is counted by ones for column storage and by thousands for row storage.

    max_curr_iops

    int4

    Maximum I/O of the current user across DNs. The IOPS is counted by ones for column storage and by thousands for row storage.

    min_peak_iops

    int4

    Minimum peak I/O of the current user across DNs. The IOPS is counted by ones for column storage and by thousands for row storage.

    max_peak_iops

    int4

    Maximum peak I/O of the current user across DNs. The IOPS is counted by ones for column storage and by thousands for row storage.

    io_limits

    int4

    io_limits set for the resource pool specified by the user. The IOPS is counted by ones for column storage and by thousands for row storage.

    io_priority

    text

    io_priority set for the user. The IOPS is counted by ones for column storage and by thousands for row storage.

  • pg_stat_get_function_calls(oid)

    Description: Number of times the function has been called

    Return type: bigint

  • pg_stat_get_function_time(oid)

    Description: Total wall clock time spent in the function, in microseconds. Includes the time spent in functions called by this one.

    Return type: bigint

  • pg_stat_get_function_self_time(oid)

    Description: Time spent in only this function. Time spent in called functions is excluded.

    Return type: bigint

  • pg_stat_get_backend_idset()

    Description: Set of currently active server process numbers (from 1 to the number of active server processes)

    Return type: setofinteger

  • pg_stat_get_backend_pid(integer)

    Description: Thread ID of the given server thread

    Return type: bigint

  • pg_stat_get_backend_dbid(integer)

    Description: ID of the database connected to the given server process

    Return type: OID

  • pg_stat_get_backend_userid(integer)

    Description: User ID of the given server process

    Return type: OID

  • pg_stat_get_backend_activity(integer)

    Description: Active command of the given server process, but only if the current user is a system administrator or the same user as that of the session being queried and track_activities is on

    Return type: text

  • pg_stat_get_backend_waiting(integer)

    Description: True if the given server process is waiting for a lock, but only if the current user is a system administrator or the same user as that of the session being queried and track_activities is on

    Return type: Boolean

  • pg_stat_get_backend_activity_start(integer)

    Description: The time at which the given server process's currently executing query was started, but only if the current user is a system administrator or the same user as that of the session being queried and track_activities is on

    Return type: timestamp with time zone

  • pg_stat_get_backend_xact_start(integer)

    Description: The time at which the given server process's currently executing transaction was started, but only if the current user is a system administrator or the same user as that of the session being queried and track_activities is on

    Return type: timestamp with time zone

  • pg_stat_get_backend_start(integer)

    Description: The time at which the given server process was started, or NULL if the current user is neither a system administrator nor the same user as that of the session being queried

    Return type: timestamp with time zone

  • pg_stat_get_backend_client_addr(integer)

    Description: IP address of the client connected to the given server process.

    If the connection is over a Unix domain socket, or if the current user is neither a system administrator nor the same user as that of the session being queried, NULL will be returned.

    Return type: inet

  • pg_stat_get_backend_client_port(integer)

    Description: TCP port number of the client connected to the given server process

    If the connection is over a Unix domain socket, -1 will be returned. If the current user is neither a system administrator nor the same user as that of the session being queried, NULL will be returned.

    Return type: integer

  • pg_stat_get_bgwriter_timed_checkpoints()

    Description: The number of times the background writer has started timed checkpoints (because the checkpoint_timeout time has expired)

    Return type: bigint

  • pg_stat_get_bgwriter_requested_checkpoints()

    Description: The number of times the background writer has started checkpoints based on requests from the backend because checkpoint_segments has been exceeded or the CHECKPOINT command has been executed

    Return type: bigint

  • pg_stat_get_bgwriter_buf_written_checkpoints()

    Description: The number of buffers written by the background writer during checkpoints

    Return type: bigint

  • pg_stat_get_bgwriter_buf_written_clean()

    Description: The number of buffers written by the background writer for routine cleaning of dirty pages

    Return type: bigint

  • pg_stat_get_bgwriter_maxwritten_clean()

    Description: The number of times the background writer has stopped its cleaning scan because it has written more buffers than specified in the bgwriter_lru_maxpages parameter

    Return type: bigint

  • pg_stat_get_buf_written_backend()

    Description: The number of buffers written by the backend because they needed to allocate a new buffer

    Return type: bigint

  • pg_stat_get_buf_alloc()

    Description: The total number of buffer allocations

    Return type: bigint

  • pg_stat_clear_snapshot()

    Description: Discards the current statistics snapshot.

    Return type: void

  • pg_stat_reset()

    Description: Resets all statistics counters for the current database to zero (requires system administrator permissions).

    Return type: void

  • pg_stat_reset_shared(text)

    Description: Resets all statistics counters for the current database in each node in a shared cluster to zero (requires system administrator permissions).

    Return type: void

  • pg_stat_reset_single_table_counters(oid)

    Description: Resets statistics for a single table or index in the current database to zero (requires system administrator permissions).

    Return type: void

  • pg_stat_reset_single_function_counters(oid)

    Description: Resets statistics for a single function in the current database to zero (requires system administrator permissions).

    Return type: void

  • pg_stat_session_cu(int, int, int)

    Description: Obtains the compression unit (CU) hit statistics of sessions running on the current node.

    Return type: record

  • gs_get_stat_session_cu(text, int, int, int)

    Description: Obtains the CU hit statistics of all sessions running in a cluster.

    Return type: record

  • gs_get_stat_db_cu(text, text, int, int, int)

    Description: Obtains the CU hit statistics of a database in a cluster.

    Return type: record

  • pg_stat_get_cu_mem_hit(oid)

    Description: Obtains the number of CU memory hits of a column storage table in the current database of the current node.

    Return type: bigint

  • pg_stat_get_cu_hdd_sync(oid)

    Description: Obtains the times CU is synchronously read from a disk by a column storage table in the current database of the current node.

    Return type: bigint

  • pg_stat_get_cu_hdd_asyn(oid)

    Description: Obtains the times CU is asynchronously read from a disk by a column storage table in the current database of the current node.

    Return type: bigint

  • pg_stat_get_db_cu_mem_hit(oid)

    Description: Obtains the CU memory hit in a database of the current node.

    Return type: bigint

  • pg_stat_get_db_cu_hdd_sync(oid)

    Description: Obtains the times CU is synchronously read from a disk by a database of the current node.

    Return type: bigint

  • pg_stat_get_db_cu_hdd_asyn(oid)

    Description: Obtains the times CU is asynchronously read from a disk by a database of the current node.

    Return type: bigint

  • pgxc_fenced_udf_process()

    Description: Shows the number of UDF Master and Work processes.

    Return type: record

  • pgxc_terminate_all_fenced_udf_process()

    Description: Kills all UDF Work processes.

    Return type: bool

  • GS_ALL_NODEGROUP_CONTROL_GROUP_INFO(text)
    Description: Provides Cgroup information for all logical clusters. Before invoking this function, you need to specify the name of a logical cluster to be queried. For example, to query the Cgroup information for the installation logical cluster, run the following command:
    1
    SELECT * FROM GS_ALL_NODEGROUP_CONTROL_GROUP_INFO('installation')
    

    Return type: record

    The following table describes return fields.

    Name

    Type

    Description

    name

    text

    Name of a Cgroup

    type

    text

    Type of the Cgroup

    gid

    bigint

    Cgroup ID

    classgid

    bigint

    ID of the Class Cgroup where a Workload Cgroup belongs

    class

    text

    Class Cgroup

    workload

    text

    Workload Cgroup

    shares

    bigint

    CPU quota allocated to a Cgroup

    limits

    bigint

    Limit of CPUs allocated to a Cgroup

    wdlevel

    bigint

    Workload Cgroup level

    cpucores

    text

    Usage of CPU cores in a Cgroup

  • gs_get_nodegroup_tablecount(name)

    Description: Total number of user tables in all the databases in a logical cluster

    Return type: integer

  • pgxc_max_datanode_size(name)

    Description: Maximum disk space occupied by database files in all the DNs of a logical cluster. The unit is byte.

    Return type: bigint

  • gs_check_logic_cluster_consistency()

    Description: Checks whether the system information of all logical clusters in the system is consistent. If no record is returned, the information is consistent. Otherwise, the Node Group information on CNs and DNs in the logical cluster is inconsistent. This function cannot be invoked during redistribution in a scale-in or scale-out.

    Return type: record

  • gs_check_tables_distribution()

    Description: Checks whether the user table distribution in the system is consistent. If no record is returned, table distribution is consistent. This function cannot be invoked during redistribution in a scale-in or scale-out.

    Return type: record

  • pg_stat_bad_block(text, int, int, int, int, int, timestamp with time zone, timestamp with time zone)

    Description: Obtains damage information about pages or CUs after the current node is started.

    Return type: record

  • pgxc_stat_bad_block(text, int, int, int, int, int, timestamp with time zone, timestamp with time zone)

    Description: Obtains damage information about pages or CUs after all the nodes in the cluster are started.

    Return type: record

  • pg_stat_bad_block_clear()

    Description: Deletes the page and CU damage information that is read and recorded on the node. (System administrator rights are required.)

    Return type: void

  • pgxc_stat_bad_block_clear

    Description: Deletes the page and CU damage information that is read and recorded on all the nodes in the cluster. (System administrator rights are required.)

    Return type: void

  • gs_respool_exception_info(pool text)

    Description: Queries for the query rule of a specified resource pool.

    Return type: record

  • gs_control_group_info(pool text)

    Description: Queries for information about Cgroups associated with a resource pool.

    Return type: record

    The following information is displayed:

    Attribute

    Value

    Description

    name

    class_a:workload_a1

    Class name and workload name

    class

    class_a

    Class Cgroup name

    workload

    workload_a1

    Workload Cgroup name

    type

    DEFWD

    Cgroup type (Top, CLASS, BAKWD, DEFWD, and TSWD)

    gid

    87

    Cgroup ID

    shares

    30

    Percentage of CPU resources to those on the parent node

    limits

    0

    Percentage of CPU cores to those on the parent node

    rate

    0

    Allocation raio in Timeshare

    cpucores

    0-3

    Number of CPU cores

  • gs_wlm_user_resource_info(name text)

    Description: Queries for a user's resource quota and resource usage.

    Return type: record

For example:

The function pg_backend_pid shows the ID of the current server thread.

1
2
3
4
5
SELECT pg_backend_pid();
 pg_backend_pid  
-----------------
 139706243217168
(1 row)

The function pg_stat_get_backend_pid shows the ID of a given server thread.

1
2
3
4
5
SELECT pg_stat_get_backend_pid(1);
 pg_stat_get_backend_pid 
-------------------------
         139706243217168
(1 row)

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel