pg_stat_all_tables — PostgreSQL statistics view

The PostgreSQL pg_stat_all_tables statistics view: full column reference (names, types, descriptions), catalog relationships and version support.

The Sev-1 Admin
The Sev-1 Admin  ·  4 min read

Summary

The pg_stat_all_tables view will contain one row for each table in the current database (including TOAST tables), showing statistics about accesses to that specific table. The pg_stat_user_tables and pg_stat_sys_tables views contain the same information, but filtered to only show user and system tables respectively.

(Description quoted from the official PostgreSQL documentation.)

Columns

The pg_stat_all_tables statistics view exposes the following columns (names, types and descriptions are taken verbatim from the PostgreSQL documentation):

  • relid oid
    OID of a table
  • schemaname name
    Name of the schema that this table is in
  • relname name
    Name of this table
  • seq_scan bigint
    Number of sequential scans initiated on this table
  • last_seq_scan timestamp with time zone
    The time of the last sequential scan on this table, based on the most recent transaction stop time
  • seq_tup_read bigint
    Number of live rows fetched by sequential scans
  • idx_scan bigint
    Number of index scans initiated on this table
  • last_idx_scan timestamp with time zone
    The time of the last index scan on this table, based on the most recent transaction stop time
  • idx_tup_fetch bigint
    Number of live rows fetched by index scans
  • n_tup_ins bigint
    Total number of rows inserted
  • n_tup_upd bigint
    Total number of rows updated. (This includes row updates counted in n_tup_hot_upd and n_tup_newpage_upd, and remaining non-HOT updates.)
  • n_tup_del bigint
    Total number of rows deleted
  • n_tup_hot_upd bigint
    Number of rows HOT updated. These are updates where no successor versions are required in indexes.
  • n_tup_newpage_upd bigint
    Number of rows updated where the successor version goes onto a new heap page, leaving behind an original version with a t_ctid field that points to a different heap page. These are always non-HOT updates.
  • n_live_tup bigint
    Estimated number of live rows
  • n_dead_tup bigint
    Estimated number of dead rows
  • n_mod_since_analyze bigint
    Estimated number of rows modified since this table was last analyzed
  • n_ins_since_vacuum bigint
    Estimated number of rows inserted since this table was last vacuumed (not counting VACUUM FULL)
  • last_vacuum timestamp with time zone
    Last time at which this table was manually vacuumed (not counting VACUUM FULL)
  • last_autovacuum timestamp with time zone
    Last time at which this table was vacuumed by the autovacuum daemon
  • last_analyze timestamp with time zone
    Last time at which this table was manually analyzed
  • last_autoanalyze timestamp with time zone
    Last time at which this table was analyzed by the autovacuum daemon
  • vacuum_count bigint
    Number of times this table has been manually vacuumed (not counting VACUUM FULL)
  • autovacuum_count bigint
    Number of times this table has been vacuumed by the autovacuum daemon
  • analyze_count bigint
    Number of times this table has been manually analyzed
  • autoanalyze_count bigint
    Number of times this table has been analyzed by the autovacuum daemon
  • total_vacuum_time double precision
    Total time this table has been manually vacuumed, in milliseconds (not counting VACUUM FULL). (This includes the time spent sleeping due to cost-based delays.)
  • total_autovacuum_time double precision
    Total time this table has been vacuumed by the autovacuum daemon, in milliseconds. (This includes the time spent sleeping due to cost-based delays.)
  • total_analyze_time double precision
    Total time this table has been manually analyzed, in milliseconds. (This includes the time spent sleeping due to cost-based delays.)
  • total_autoanalyze_time double precision
    Total time this table has been analyzed by the autovacuum daemon, in milliseconds. (This includes the time spent sleeping due to cost-based delays.)
  • stats_reset timestamp with time zone
    Time at which these statistics were last reset

Version applicability

Present in PostgreSQL 17, 18, 19 (verified against each release’s documentation). This is a long-standing system object that also exists in earlier PostgreSQL releases.

Related & references

Reference: PostgreSQL documentation — pg_stat_all_tables.