pg_stat_sys_tables 是一个 统计视图，显示当前数据库中每个系统表的访问统计信息。
pg_stat_sys_tables 在 PostgreSQL 7.2 中添加。
用法
pg_stat_sys_tables 列出了所有系统表的统计信息（即 pg_catalog、information_schema 和 pg_toast 模式中的表）。
请注意，将显示所有表的统计信息，无论用户是否具有读取该表的权限。
按 PostgreSQL 版本定义
pg_stat_sys_tables (PostgreSQL 17)
View "pg_catalog.pg_stat_sys_tables"
Column | Type | Collation | Nullable | Default
---------------------+--------------------------+-----------+----------+---------
relid | oid | | |
schemaname | name | | |
relname | name | | |
seq_scan | bigint | | |
last_seq_scan | timestamp with time zone | | |
seq_tup_read | bigint | | |
idx_scan | bigint | | |
last_idx_scan | timestamp with time zone | | |
idx_tup_fetch | bigint | | |
n_tup_ins | bigint | | |
n_tup_upd | bigint | | |
n_tup_del | bigint | | |
n_tup_hot_upd | bigint | | |
n_tup_newpage_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 | | |
pg_stat_sys_tables (PostgreSQL 16)
View "pg_catalog.pg_stat_sys_tables"
Column | Type | Collation | Nullable | Default
---------------------+--------------------------+-----------+----------+---------
relid | oid | | |
schemaname | name | | |
relname | name | | |
seq_scan | bigint | | |
last_seq_scan | timestamp with time zone | | |
seq_tup_read | bigint | | |
idx_scan | bigint | | |
last_idx_scan | timestamp with time zone | | |
idx_tup_fetch | bigint | | |
n_tup_ins | bigint | | |
n_tup_upd | bigint | | |
n_tup_del | bigint | | |
n_tup_hot_upd | bigint | | |
n_tup_newpage_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 | | |
pg_stat_sys_tables (PostgreSQL 15)
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 | | |
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 | | |
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 | | |
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 | | |
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 | | |
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 | | |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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;
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;
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]);
变更历史
示例
在 PostgreSQL 12 安装上 pg_stat_sys_tables 的示例内容
postgres=# SELECT * FROM pg_stat_sys_tables WHERE relname='pg_class' \gx 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
参考文献
- PostgreSQL 文档: pg_stat_sys_tables