pg_stat_all_indexes

The pg_stat_all_indexes view shows one row for each index in the current database that displays 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.

In Greenplum Database 5, the pg_stat_*_indexes views display access statistics for indexes only from the master instance. Access statistics from segment instances are ignored. You can create views that display access statistics that combine statistics from the master and the segment instances, see Index Access Statistics from the Master and Segment Instances.

ColumnTypeDescription
relidoidOID of the table for this index
indexrelidoidOID of this index
schemanamenameName of the schema this index is in
relnamenameName of the table for this index
indexrelnamenameName of this index
idx_scanbigintTotal number of index scans initiated on this index from all segment instances
idx_tup_readbigintNumber of index entries returned by scans on this index
idx_tup_fetchbigintNumber of live table rows fetched by simple index scans using this index

Index Access Statistics from the Master and Segment Instances

To display index access statistics that combine statistics from the master and the segment instances, you can create these views. A user requires SELECT privilege on the views to use them.

  1. -- Create these index access statistics views
  2. --   pg_stat_all_indexes_gpdb5
  3. --   pg_stat_sys_indexes_gpdb5
  4. --   pg_stat_user_indexes_gpdb5
  6.  CREATE VIEW pg_stat_all_indexes_gpdb5 AS
  7.  SELECT
  8.      s.relid,
  9.      s.indexrelid,
  10.      s.schemaname,
  11.      s.relname,
  12.      s.indexrelname,
  13.      m.idx_scan,
  14.      m.idx_tup_read,
  15.      m.idx_tup_fetch
  16.  FROM
  17.      (SELECT
  18.           relid,
  19.           indexrelid,
  20.           schemaname,
  21.           relname,
  22.           indexrelname,
  23.           sum(idx_scan) as idx_scan,
  24.           sum(idx_tup_read) as idx_tup_read,
  25.           sum(idx_tup_fetch) as idx_tup_fetch
  26.       FROM gp_dist_random('pg_stat_all_indexes')
  27.       WHERE relid >= 16384
  28.       GROUP BY relid, indexrelid, schemaname, relname, indexrelname
  29.       UNION ALL
  30.       SELECT *
  31.       FROM pg_stat_all_indexes
  32.       WHERE relid < 16384) m, pg_stat_all_indexes s
  33.  WHERE m.relid = s.relid;
  36.  CREATE VIEW pg_stat_sys_indexes_gpdb5 AS 
  37.      SELECT * FROM pg_stat_all_indexes_gpdb5 
  38.      WHERE schemaname IN ('pg_catalog', 'information_schema') OR
  39.            schemaname ~ '^pg_toast';
  42.  CREATE VIEW pg_stat_user_indexes_gpdb5 AS 
  43.      SELECT * FROM pg_stat_all_indexes_gpdb5 
  44.      WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
  45.            schemaname !~ '^pg_toast';

Parent topic: System Catalogs Definitions