PG_STAT_USER_TABLES

PG_STAT_USER_TABLES displays information about user-defined ordinary tables and TOAST tables in the namespaces. You can use this view to monitor the execution of VACUUM on a specific table. Pay attention to the n_dead_tup and last_autovacuum columns. The parameters are described as follows:

**Table 1** PG_STAT_USER_TABLES columns
Name	Type	Description
relid	oid	Table OID.
schemaname	name	Name of the schema that the table is in
relname	name	Table name.
seq_scan	bigint	Number of sequential scans initiated on the table.
seq_tup_read	bigint	Number of live rows fetched by sequential scans.
idx_scan	bigint	Number of index scans initiated on the table.
idx_tup_fetch	bigint	Number of live rows fetched by index scans.
n_tup_ins	bigint	Number of rows inserted.
n_tup_upd	bigint	Number of rows updated.
n_tup_del	bigint	Number of rows deleted.
n_tup_hot_upd	bigint	Number of rows HOT updated (with no separate index update required).
n_live_tup	bigint	Estimated number of live rows.
n_dead_tup	bigint	Estimated number of dead rows.
last_vacuum	timestamp with time zone	Last time at which the table was manually vacuumed (excluding VACUUM FULL)
last_autovacuum	timestamp with time zone	Last time at which the table was vacuumed by the autovacuum daemon.
last_analyze	timestamp with time zone	Last time when the table was manually analyzed.
last_autoanalyze	timestamp with time zone	Last time at which the table was analyzed by the autovacuum daemon
vacuum_count	bigint	Number of times the table is manually vacuumed (excluding VACUUM FULL).
autovacuum_count	bigint	Number of times the table is cleared.
analyze_count	bigint	Number of times that the table has been manually analyzed.
autoanalyze_count	bigint	Number of times the table has been analyzed by the AUTOVACUUM daemon.
last_data_changed	timestamp with time zone	Last modification time of the table data.

Example:

gaussdb=# \x
Expanded display is on.
gaussdb=# select * from pg_stat_user_tables where relname='t2';
-[ RECORD 1 ]-----+------------------------------
relid             | 98316
schemaname        | public
relname           | t2
seq_scan          | 2
seq_tup_read      | 20002
idx_scan          |
idx_tup_fetch     |
n_tup_ins         | 10001
n_tup_upd         | 10001
n_tup_del         | 5001
n_tup_hot_upd     | 0
n_live_tup        | 5000
n_dead_tup        | 15002
last_vacuum       | 2025-02-09 10:16:47.317412-05
last_autovacuum   | 2025-02-09 10:16:47.317412-05
last_analyze      | 2025-02-09 10:16:37.491756-05
last_autoanalyze  | 2025-02-09 10:16:37.491756-05
vacuum_count      | 3
autovacuum_count  | 3
analyze_count     | 2
autoanalyze_count | 2
last_data_changed | 2025-02-09 10:16:34.660061-05

Parent topic: VACUUM

Previous topic: VACUUM

Next topic: PG_AUTOVAC_STATUS