pg_stat_sys_tables
pg_stat_sys_tables
is a statistics view showing statistics about accesses to each system table in the current database.
pg_stat_sys_tables
was added in PostgreSQL 7.2.
Usage
pg_stat_sys_tables
lists statistics for all system tables (i.e. those in the pg_catalog
, Information schema
and pg_toast
schemas).
Note that statistics will be displayed for all tables, regardless of whether the user has permission to read the table.
Definition by PostgreSQL version
pg_stat_sys_tables (PostgreSQL 14)
View "pg_catalog.pg_stat_sys_tables" Column | Type | Collation | Nullable | Default ---------------------+--------------------------+-----------+----------+--------- relid | oid | | | schemaname | name | | | relname | name | | | seq_scan | bigint | | | seq_tup_read | bigint | | | idx_scan | bigint | | | idx_tup_fetch | bigint | | | n_tup_ins | bigint | | | n_tup_upd | bigint | | | n_tup_del | bigint | | | n_tup_hot_upd | bigint | | | n_live_tup | bigint | | | n_dead_tup | bigint | | | n_mod_since_analyze | bigint | | | n_ins_since_vacuum | bigint | | | last_vacuum | timestamp with time zone | | | last_autovacuum | timestamp with time zone | | | last_analyze | timestamp with time zone | | | last_autoanalyze | timestamp with time zone | | | vacuum_count | bigint | | | autovacuum_count | bigint | | | analyze_count | bigint | | | autoanalyze_count | bigint | | |
Documentation: pg_stat_sys_tables
pg_stat_sys_tables (PostgreSQL 13)
View "pg_catalog.pg_stat_sys_tables" Column | Type | Collation | Nullable | Default ---------------------+--------------------------+-----------+----------+--------- relid | oid | | | schemaname | name | | | relname | name | | | seq_scan | bigint | | | seq_tup_read | bigint | | | idx_scan | bigint | | | idx_tup_fetch | bigint | | | n_tup_ins | bigint | | | n_tup_upd | bigint | | | n_tup_del | bigint | | | n_tup_hot_upd | bigint | | | n_live_tup | bigint | | | n_dead_tup | bigint | | | n_mod_since_analyze | bigint | | | n_ins_since_vacuum | bigint | | | last_vacuum | timestamp with time zone | | | last_autovacuum | timestamp with time zone | | | last_analyze | timestamp with time zone | | | last_autoanalyze | timestamp with time zone | | | vacuum_count | bigint | | | autovacuum_count | bigint | | | analyze_count | bigint | | | autoanalyze_count | bigint | | |
Documentation: pg_stat_sys_tables
pg_stat_sys_tables (PostgreSQL 12)
View "pg_catalog.pg_stat_sys_tables" Column | Type | Collation | Nullable | Default ---------------------+--------------------------+-----------+----------+--------- relid | oid | | | schemaname | name | | | relname | name | | | seq_scan | bigint | | | seq_tup_read | bigint | | | idx_scan | bigint | | | idx_tup_fetch | bigint | | | n_tup_ins | bigint | | | n_tup_upd | bigint | | | n_tup_del | bigint | | | n_tup_hot_upd | bigint | | | n_live_tup | bigint | | | n_dead_tup | bigint | | | n_mod_since_analyze | bigint | | | last_vacuum | timestamp with time zone | | | last_autovacuum | timestamp with time zone | | | last_analyze | timestamp with time zone | | | last_autoanalyze | timestamp with time zone | | | vacuum_count | bigint | | | autovacuum_count | bigint | | | analyze_count | bigint | | | autoanalyze_count | bigint | | |
Documentation: pg_stat_sys_tables
pg_stat_sys_tables (PostgreSQL 11)
View "pg_catalog.pg_stat_sys_tables" Column | Type | Collation | Nullable | Default ---------------------+--------------------------+-----------+----------+--------- relid | oid | | | schemaname | name | | | relname | name | | | seq_scan | bigint | | | seq_tup_read | bigint | | | idx_scan | bigint | | | idx_tup_fetch | bigint | | | n_tup_ins | bigint | | | n_tup_upd | bigint | | | n_tup_del | bigint | | | n_tup_hot_upd | bigint | | | n_live_tup | bigint | | | n_dead_tup | bigint | | | n_mod_since_analyze | bigint | | | last_vacuum | timestamp with time zone | | | last_autovacuum | timestamp with time zone | | | last_analyze | timestamp with time zone | | | last_autoanalyze | timestamp with time zone | | | vacuum_count | bigint | | | autovacuum_count | bigint | | | analyze_count | bigint | | | autoanalyze_count | bigint | | |
Documentation: pg_stat_sys_tables
pg_stat_sys_tables (PostgreSQL 10)
View "pg_catalog.pg_stat_sys_tables" Column | Type | Collation | Nullable | Default ---------------------+--------------------------+-----------+----------+--------- relid | oid | | | schemaname | name | | | relname | name | | | seq_scan | bigint | | | seq_tup_read | bigint | | | idx_scan | bigint | | | idx_tup_fetch | bigint | | | n_tup_ins | bigint | | | n_tup_upd | bigint | | | n_tup_del | bigint | | | n_tup_hot_upd | bigint | | | n_live_tup | bigint | | | n_dead_tup | bigint | | | n_mod_since_analyze | bigint | | | last_vacuum | timestamp with time zone | | | last_autovacuum | timestamp with time zone | | | last_analyze | timestamp with time zone | | | last_autoanalyze | timestamp with time zone | | | vacuum_count | bigint | | | autovacuum_count | bigint | | | analyze_count | bigint | | | autoanalyze_count | bigint | | |
Documentation: pg_stat_sys_tables
pg_stat_sys_tables (PostgreSQL 9.6)
View "pg_catalog.pg_stat_sys_tables" Column | Type | Modifiers ---------------------+--------------------------+----------- relid | oid | schemaname | name | relname | name | seq_scan | bigint | seq_tup_read | bigint | idx_scan | bigint | idx_tup_fetch | bigint | n_tup_ins | bigint | n_tup_upd | bigint | n_tup_del | bigint | n_tup_hot_upd | bigint | n_live_tup | bigint | n_dead_tup | bigint | n_mod_since_analyze | bigint | last_vacuum | timestamp with time zone | last_autovacuum | timestamp with time zone | last_analyze | timestamp with time zone | last_autoanalyze | timestamp with time zone | vacuum_count | bigint | autovacuum_count | bigint | analyze_count | bigint | autoanalyze_count | bigint |
Documentation: pg_stat_sys_tables
pg_stat_sys_tables (PostgreSQL 9.5)
View "pg_catalog.pg_stat_sys_tables" Column | Type | Modifiers ---------------------+--------------------------+----------- relid | oid | schemaname | name | relname | name | seq_scan | bigint | seq_tup_read | bigint | idx_scan | bigint | idx_tup_fetch | bigint | n_tup_ins | bigint | n_tup_upd | bigint | n_tup_del | bigint | n_tup_hot_upd | bigint | n_live_tup | bigint | n_dead_tup | bigint | n_mod_since_analyze | bigint | last_vacuum | timestamp with time zone | last_autovacuum | timestamp with time zone | last_analyze | timestamp with time zone | last_autoanalyze | timestamp with time zone | vacuum_count | bigint | autovacuum_count | bigint | analyze_count | bigint | autoanalyze_count | bigint |
Documentation: pg_stat_sys_tables
pg_stat_sys_tables (PostgreSQL 9.4)
View "pg_catalog.pg_stat_sys_tables" Column | Type | Modifiers ---------------------+--------------------------+----------- relid | oid | schemaname | name | relname | name | seq_scan | bigint | seq_tup_read | bigint | idx_scan | bigint | idx_tup_fetch | bigint | n_tup_ins | bigint | n_tup_upd | bigint | n_tup_del | bigint | n_tup_hot_upd | bigint | n_live_tup | bigint | n_dead_tup | bigint | n_mod_since_analyze | bigint | last_vacuum | timestamp with time zone | last_autovacuum | timestamp with time zone | last_analyze | timestamp with time zone | last_autoanalyze | timestamp with time zone | vacuum_count | bigint | autovacuum_count | bigint | analyze_count | bigint | autoanalyze_count | bigint |
Documentation: pg_stat_sys_tables
pg_stat_sys_tables (PostgreSQL 9.3)
View "pg_catalog.pg_stat_sys_tables" Column | Type | Modifiers -------------------+--------------------------+----------- relid | oid | schemaname | name | relname | name | seq_scan | bigint | seq_tup_read | bigint | idx_scan | bigint | idx_tup_fetch | bigint | n_tup_ins | bigint | n_tup_upd | bigint | n_tup_del | bigint | n_tup_hot_upd | bigint | n_live_tup | bigint | n_dead_tup | bigint | last_vacuum | timestamp with time zone | last_autovacuum | timestamp with time zone | last_analyze | timestamp with time zone | last_autoanalyze | timestamp with time zone | vacuum_count | bigint | autovacuum_count | bigint | analyze_count | bigint | autoanalyze_count | bigint |
Documentation: pg_stat_sys_tables
pg_stat_sys_tables (PostgreSQL 9.2)
View "pg_catalog.pg_stat_sys_tables" Column | Type | Modifiers -------------------+--------------------------+----------- relid | oid | schemaname | name | relname | name | seq_scan | bigint | seq_tup_read | bigint | idx_scan | bigint | idx_tup_fetch | bigint | n_tup_ins | bigint | n_tup_upd | bigint | n_tup_del | bigint | n_tup_hot_upd | bigint | n_live_tup | bigint | n_dead_tup | bigint | last_vacuum | timestamp with time zone | last_autovacuum | timestamp with time zone | last_analyze | timestamp with time zone | last_autoanalyze | timestamp with time zone | vacuum_count | bigint | autovacuum_count | bigint | analyze_count | bigint | autoanalyze_count | bigint |
Documentation: pg_stat_sys_tables
pg_stat_sys_tables (PostgreSQL 9.1)
View "pg_catalog.pg_stat_sys_tables" Column | Type | Modifiers -------------------+--------------------------+----------- relid | oid | schemaname | name | relname | name | seq_scan | bigint | seq_tup_read | bigint | idx_scan | bigint | idx_tup_fetch | bigint | n_tup_ins | bigint | n_tup_upd | bigint | n_tup_del | bigint | n_tup_hot_upd | bigint | n_live_tup | bigint | n_dead_tup | bigint | last_vacuum | timestamp with time zone | last_autovacuum | timestamp with time zone | last_analyze | timestamp with time zone | last_autoanalyze | timestamp with time zone | vacuum_count | bigint | autovacuum_count | bigint | analyze_count | bigint | autoanalyze_count | bigint |
Documentation: pg_stat_sys_tables
pg_stat_sys_tables (PostgreSQL 9.0)
View "pg_catalog.pg_stat_sys_tables" Column | Type | Modifiers ------------------+--------------------------+----------- relid | oid | schemaname | name | relname | name | seq_scan | bigint | seq_tup_read | bigint | idx_scan | bigint | idx_tup_fetch | bigint | n_tup_ins | bigint | n_tup_upd | bigint | n_tup_del | bigint | n_tup_hot_upd | bigint | n_live_tup | bigint | n_dead_tup | bigint | last_vacuum | timestamp with time zone | last_autovacuum | timestamp with time zone | last_analyze | timestamp with time zone | last_autoanalyze | timestamp with time zone |
Documentation: pg_stat_sys_tables
pg_stat_sys_tables (PostgreSQL 8.4)
View "pg_catalog.pg_stat_sys_tables" Column | Type | Modifiers ------------------+--------------------------+----------- relid | oid | schemaname | name | relname | name | seq_scan | bigint | seq_tup_read | bigint | idx_scan | bigint | idx_tup_fetch | bigint | n_tup_ins | bigint | n_tup_upd | bigint | n_tup_del | bigint | n_tup_hot_upd | bigint | n_live_tup | bigint | n_dead_tup | bigint | last_vacuum | timestamp with time zone | last_autovacuum | timestamp with time zone | last_analyze | timestamp with time zone | last_autoanalyze | timestamp with time zone | View definition: SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_tup_hot_upd, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze FROM pg_stat_all_tables WHERE (pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR pg_stat_all_tables.schemaname ~ '^pg_toast'::text;
Documentation: pg_stat_sys_tables
pg_stat_sys_tables (PostgreSQL 8.3)
View "pg_catalog.pg_stat_sys_tables" Column | Type | Modifiers ------------------+--------------------------+----------- relid | oid | schemaname | name | relname | name | seq_scan | bigint | seq_tup_read | bigint | idx_scan | bigint | idx_tup_fetch | bigint | n_tup_ins | bigint | n_tup_upd | bigint | n_tup_del | bigint | n_tup_hot_upd | bigint | n_live_tup | bigint | n_dead_tup | bigint | last_vacuum | timestamp with time zone | last_autovacuum | timestamp with time zone | last_analyze | timestamp with time zone | last_autoanalyze | timestamp with time zone | View definition: SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_tup_hot_upd, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze FROM pg_stat_all_tables WHERE (pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR pg_stat_all_tables.schemaname ~ '^pg_toast'::text;
Documentation: pg_stat_sys_tables
pg_stat_sys_tables (PostgreSQL 8.2)
View "pg_catalog.pg_stat_sys_tables" Column | Type | Modifiers ------------------+--------------------------+----------- relid | oid | schemaname | name | relname | name | seq_scan | bigint | seq_tup_read | bigint | idx_scan | bigint | idx_tup_fetch | bigint | n_tup_ins | bigint | n_tup_upd | bigint | n_tup_del | bigint | last_vacuum | timestamp with time zone | last_autovacuum | timestamp with time zone | last_analyze | timestamp with time zone | last_autoanalyze | timestamp with time zone | View definition: SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze FROM pg_stat_all_tables WHERE pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'pg_toast'::name, 'information_schema'::name]);
Documentation: pg_stat_sys_tables
Change history
See pg_stat_all_tables
.
Examples
Sample contents of pg_stat_sys_tables
on a PostgreSQL 12 installation:
postgres=# SELECT * FROM pg_stat_sys_tables WHERE relname='pg_class' \x\g\x Expanded display is on. -[ RECORD 1 ]-------+------------------------------ relid | 1259 schemaname | pg_catalog relname | pg_class seq_scan | 1904893 seq_tup_read | 1000255415 idx_scan | 50898530 idx_tup_fetch | 46094458 n_tup_ins | 964 n_tup_upd | 1514 n_tup_del | 595 n_tup_hot_upd | 655 n_live_tup | 764 n_dead_tup | 1815 n_mod_since_analyze | 38 last_vacuum | last_autovacuum | 2020-12-22 08:54:19.140435+01 last_analyze | last_autoanalyze | 2020-11-16 00:22:23.025793+01 vacuum_count | 0 autovacuum_count | 381323 analyze_count | 0 autoanalyze_count | 19
References
- PostgreSQL documentation: pg_stat_sys_tables