• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. SQL Reference
  5. Functions and Operators
  6. Statistics Information Functions

Statistics Information Functions

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 database

    Return type: integer

  • pg_stat_get_db_xact_commit(oid)

    Description: Number of transactions committed in database

    Return type: bigint

  • pg_stat_get_db_xact_rollback(oid)

    Description: Number of transactions rolled back in database

    Return type: bigint

  • pg_stat_get_db_blocks_fetched(oid)

    Description: Number of disk blocks fetch requests for database

    Return type: bigint

  • pg_stat_get_db_blocks_hit(oid)

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

    Return type: bigint

  • pg_stat_get_db_tuples_returned(oid)

    Description: Number of tuples returned for database

    Return type: bigint

  • pg_stat_get_db_tuples_fetched(oid)

    Description: Number of tuples fetched for database

    Return type: bigint

  • pg_stat_get_db_tuples_inserted(oid)

    Description: Number of tuples inserted in database

    Return type: bigint

  • pg_stat_get_db_tuples_updated(oid)

    Description: Number of tuples updated in database

    Return type: bigint

  • pg_stat_get_db_tuples_deleted(oid)

    Description: Number of tuples deleted in 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_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_last_vacuum_time(oid)

    Description: Time of the last vacuum initiated by the user on this 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_last_analyze_time(oid)

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

    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_backend_pid()

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

    Return type: integer

  • pg_stat_get_activity(integer)

    Description: Returns a record of information about the backend with the specified pid, or one record for each active backend in the system if NULL is specified. The returned results are a subnet of those in the PG_STAT_ACTIVITY view.

    Return type: setofrecord

  • 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' 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' 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.

    Null if the connection is over a Unix domain socket.

    Also null if the current user is neither a system administrator nor the same user as that of the session being queried

    Return type: inet

  • pg_stat_get_backend_client_port(integer)

    Description:

    TCP port number of the client connected to the given server process.

    -1 if the connection is over a Unix domain socket.

    Also null if the current user is neither a system administrator nor the same user as that of the session being queried

    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 backends because the checkpoint_segments has been exceeded or because the CHECKPOINT command has been issued

    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 backends 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

  • gs_stat_reset()

    Description: Resets all statistics counters for the current database in each cluster node 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

For example:

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

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.

SELECT pg_stat_get_backend_pid(1);
 pg_stat_get_backend_pid 
-------------------------
         139706243217168
(1 row)