Updated on 2025-05-29 GMT+08:00

PG_STAT_USER_TABLES

PG_STAT_USER_TABLES displays status 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 columns are described as follows:

Table 1 PG_STAT_USER_TABLES columns

Name

Type

Description

relid

oid

OID of a table.

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 when the table was analyzed by the AUTOVACUUM daemon.

vacuum_count

bigint

Number of times the table has been manually vacuumed (not counting VACUUM FULL).

autovacuum_count

bigint

Number of times the table has been vacuumed by the AUTOVACUUM daemon.

analyze_count

bigint

Number of times 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