This topic describes the views of PolarDB O Edition. You can query views to obtain object information. This way, you can identify performance issues.

Databases

To obtain database information, query the views described in this topic.

  • pg_stat_database
    The pg_stat_database view displays the statistics about each row in each database of a cluster. The following table describes the parameters in this view.
    Parameter Type Description
    datid oid The object identifier (OID) of the database.
    datname name The name of the database.
    numbackends integer The number of backends connected to the database. In the view, this column is the only column that returns the current status. The other columns return the values that have been accumulated since the latest value reset.
    xact_commit bigint The number of committed transactions in the database.
    xact_rollback bigint The number of transactions that have been rolled back in the database.
    blks_read bigint The number of disk blocks read in the database.
    blks_hit bigint The number of times that disk blocks have been found in the buffer. The data blocks do not need to be read. The number of cache hits indicates only the hits in the PolarDB-O buffer. The hits in the file system buffer of the OS are ignored.
    tup_returned bigint The number of rows returned by queries in the database.
    tup_fetched bigint The number of rows fetched by queries in the database.
    tup_inserted bigint The number of rows inserted by queries in the database.
    tup_updated bigint The number of rows updated by queries in the database.
    tup_deleted bigint The number of rows deleted by queries in the database.
    conflicts bigint The number of queries canceled in the database due to data recovery conflicts.
    temp_files bigint The number of temporary files created by queries in the database. All temporary files are counted regardless of why the files are created, such as sorting or hashing, and regardless of the log_temp_files settings.
    temp_bytes bigint The total amount of data written to temporary files by queries in the database. All temporary files are counted regardless of why the files are created and regardless of the log_temp_files settings.
    deadlocks bigint The number of deadlocks detected in the database.
    blk_read_time double precision The time that backends in the database consume to read data blocks. Unit: milliseconds.
    blk_write_time double precision The time that backends in the database consume to write data blocks. Unit: milliseconds.
    stats_reset timestamp with time zone The latest time when the statistics were reset.
  • pg_stat_database_conflicts
    The pg_stat_database_conflicts view displays the statistics about queries that are canceled in the database due to data recovery conflicts on the secondary server. The pg_stat_database_conflicts view displays the information only about the secondary server. This is because the conflicts do not occur on the primary server. The following table describes the parameters in this view.
    Parameter Type Description
    datid oid The OID of the database.
    datname name The name of the database.
    confl_tablespace bigint The number of queries canceled in the database due to deleted tablespaces.
    confl_lock bigint The number of queries canceled in the database due to lock timeout.
    confl_snapshot bigint The number of queries canceled in the database due to old snapshots.
    confl_bufferpin bigint The number of queries canceled in the database due to the pinned buffer.
    confl_deadlock bigint The number of queries canceled in the database due to deadlocks.

Table

  • pg_stat_all_tables
    The pg_stat_all_tables view displays the access statistics for each table in the current database. The following table describes the parameters in this view.
    Parameter Type Description
    relid oid The OID of the table.
    schemaname name The name of the schema to which the table belongs.
    relname name The name of the table.
    seq_scan bigint The number of sequential scans initiated on the table.
    seq_tup_read bigint The number of live rows fetched by sequential scans.
    idx_scan bigint The number of index scans initiated on the table.
    idx_tup_fetch bigint The number of live rows fetched by index scans.
    n_tup_ins bigint The number of inserted rows.
    n_tup_upd bigint The number of updated rows. This includes the rows that are updated by the Heap Only Tuple (HOT) feature.
    n_tup_del bigint The number of rows deleted from the table.
    n_tup_hot_upd bigint The number of rows updated by the HOT feature. The rows are updated without the need to separately update indexes.
    n_live_tup bigint The estimated number of live rows.
    n_dead_tup bigint The estimated number of dead rows.
    n_mod_since_analyze bigint The estimated number of rows that are modified after the table was last analyzed.
    last_vacuum timestamp with time zone The latest time when the table was manually vacuumed. The time consumed by the VACUUM FULL statement is not counted.
    last_autovacuum timestamp with time zone The latest time when the table was automatically vacuumed by the daemon process of automatic vacuuming.
    last_analyze timestamp with time zone The latest time when the table was manually analyzed.
    last_autoanalyze timestamp with time zone The latest time when the table was automatically analyzed by the daemon process of automatic vacuuming.
    vacuum_count bigint The number of times that the table is manually vacuumed. This value does not include the number of times that the VACUUM FULL statement is executed to vacuum the table.
    autovacuum_count bigint The number of times that the table is vacuumed by the daemon process of automatic vacuum.
    analyze_count bigint The number of times that the table is manually analyzed.
    autoanalyze_count bigint The number of times that the table is analyzed by the daemon process of automatic vacuum.
  • pg_statio_all_tables
    The pg_statio_all_tables view specifies I/O statistics for each table in the current database. The following table describes the parameters in this view.
    Parameter Type Description
    relid oid The OID of the table.
    schemaname The name of the schema to which the table belongs.
    relname name The name of the table.
    heap_blks_read bigint The number of disk blocks read from the table.
    heap_blks_hit bigint The number of buffer cache hits in the table.
    idx_blks_read bigint The number of disk blocks that are read from all the indexes in the table.
    idx_blks_hit bigint The number of buffer cache hits for all the indexes in the table.
    toast_blks_read bigint The number of disk blocks that are read from the TOAST table associated with the table.
    toast_blks_hit bigint The number of buffer cache hits in the TOAST table associated with the table.
    tidx_blks_read bigint The number of disk blocks that are read from the indexes for the TOAST table associated with the table.
    tidx_blks_hit bigint The number of buffer cache hits for the indexes for the TOAST table associated with the table.

Indexes

  • pg_stat_all_indexes

    To obtain the access statistics for each index in the current database, query the pg_stat_all_indexes view.

    You can perform simple index scans or bitmap scans to access indexes. You can also access indexes by using optimizers. In a bitmap scan, the output for multiple indexes can be combined by using AND or OR rules. When a bitmap scan is used, individual rows that are fetched cannot be associated with specific indexes. Therefore, a bitmap scan increases the value specified by pg_stat_all_indexes.idx_tup_read for the indexes, and also increases the value specified by pg_stat_all_tables.idx_tup_fetch for each table. The value specified by pg_stat_all_indexes.idx_tup_fetch is not affected. If the provided constant values do not fall within the range of the optimizer statistics, the optimizer still accesses the indexes to check the values.
    Parameter Type Description
    relid oid The OID of the table for the index.
    indexrelid oid The OID of the index.
    schemaname name The name of the schema to which the index belongs.
    relname name The name of the table for the index.
    indexrelname name The name of the index.
    idx_scan bigint The number of scans initiated on the index.
    idx_tup_read bigint The number of index items returned by scans on the index.
    idx_tup_fetch bigint The number of live table rows that simple index scans fetch by using this index.
  • pg_statio_all_indexes
    This view displays the total number of read disk blocks and the number of buffer cache hits for each index in the current database. This view also displays the I/O statistics about a specified index in the current database. The following table describes the index parameters.
    Parameter Type Description
    relid oid The OID of the table for the index.
    indexrelid oid The OID of the index.
    schemaname name The name of the schema in which the index resides.
    relname name The name of the table for the index.
    indexrelname name The name of the index.
    idx_blks_read bigint The number of disk blocks read from the index.
    idx_blks_hit bigint The number of buffer cache hits for the index.

Sequences

pg_statio_all_sequences

This view displays the information about the sequences related to the current database, and the I/O statistics about a specified sequence. The following table describes the parameters in this view.
Parameter Type Description
relid oid The OID of the sequence.
schemaname name The name of the schema to which the sequence belongs.
relname name The name of the sequence.
blks_read bigint The number of disk blocks read from the sequence.
blks_hit bigint The number of buffer cache hits in the sequence.

Functions

pg_stat_user_functions

This view displays the statistics about tracked functions.
Parameter Type Description
funcid oid The OID of the function.
schemaname name The name of the schema to which the function belongs.
funcname name The name of the function.
calls bigint The number of times that the function is invoked.
total_time double precision The total time consumed to invoke the function and the functions that this function invokes. Unit: milliseconds.
self_time double precision The total time consumed to invoke the function. The total time does not include the time consumed to invoke the functions that this function invokes. Unit: milliseconds.