This topic describes the views of PolarDB for PostgreSQL(Compatible with Oracle). You can query views to obtain object information. This way, you can identify performance issues.
Tables
- pg_stat_all_tablesThe 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 the manually vacuum table operations. 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 the automatically vacuum table operations by autovacuum daemon. analyze_count
bigint The number of the manually analyze table operations. autoanalyze_count
bigint The number of the analysis operations on the table vacuumed by autovacuum daemon. - pg_statio_all_tablesThe 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
name The name of the schema to which the table belongs. relname
name The name of the metatable. 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_indexesThis 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 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.
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 calls for the function. |
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. |