pg_stat_all_indexes — PostgreSQL statistics view

Summary

The pg_stat_all_indexes view will contain one row for each index in the current database, showing statistics about accesses to that specific index. The pg_stat_user_indexes and pg_stat_sys_indexes views contain the same information, but filtered to only show user and system indexes respectively.

(Description quoted from the official PostgreSQL documentation.)

Columns

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

• relid oid
  OID of the table for this index
• indexrelid oid
  OID of this index
• schemaname name
  Name of the schema this index is in
• relname name
  Name of the table for this index
• indexrelname name
  Name of this index
• idx_scan bigint
  Number of index scans initiated on this index
• last_idx_scan timestamp with time zone
  The time of the last scan on this index, based on the most recent transaction stop time
• idx_tup_read bigint
  Number of index entries returned by scans on this index
• idx_tup_fetch bigint
  Number of live table rows fetched by simple index scans using this index
• 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

• PostgreSQL system catalog & view index
• Built-in function reference

Reference: PostgreSQL documentation — pg_stat_all_indexes.