0710-pg_index.yml

#==============================================================#
# 0710 pg_index
#==============================================================#
pg_index:
  name: pg_index
  desc: PostgreSQL index metrics
  query: |-
    SELECT CURRENT_CATALOG AS datname, schemaname || '.' || indexrelname AS idxname, schemaname || '.' || relname AS relname ,indexrelid AS relid,
        relpages, reltuples, idx_scan, idx_tup_read, idx_tup_fetch, idx_blks_read, idx_blks_hit
    FROM pg_stat_user_indexes psui, LATERAL (SELECT idx_blks_read, idx_blks_hit FROM pg_statio_user_indexes psio WHERE psio.indexrelid = psui.indexrelid LIMIT 1) p2,
        LATERAL (SELECT relpages,reltuples FROM pg_class c WHERE c.oid = psui.indexrelid LIMIT 1) p3
    WHERE schemaname !~ '^pg_' AND schemaname !~ '^_' AND schemaname !~ '^timescaledb' AND schemaname !~ '^citus' AND schemaname !~ '^columnar' AND schemaname NOT IN ('pg_catalog','information_schema','pg_toast','repack','monitor')
    ORDER BY idx_tup_read DESC LIMIT 512;

  ttl: 10
  timeout: 1
  min_version: 090400
  metrics:
    - datname:             { usage: LABEL    ,description: "Database name of this index" }
    - idxname:             { usage: LABEL    ,description: "Name of this index (full-qualified schema name)" }
    - relname:             { usage: LABEL    ,description: "Name of the table for this index (full-qualified schema name)" }
    - relid:               { usage: LABEL    ,description: "Relation oid of this index" }
    - relpages:            { usage: GAUGE    ,description: "Size of the on-disk representation of this index in pages" }
    - reltuples:           { usage: GAUGE    ,description: "Estimate relation tuples" }
    - idx_scan:            { usage: COUNTER  ,description: "Number of index scans initiated on this index" }
    - idx_tup_read:        { usage: COUNTER  ,description: "Number of index entries returned by scans on this index" }
    - idx_tup_fetch:       { usage: COUNTER  ,description: "Number of live table rows fetched by simple index scans using this index" }
    - idx_blks_read:       { usage: COUNTER  ,description: "Number of disk blocks read from this index" }
    - idx_blks_hit:        { usage: COUNTER  ,description: "Number of buffer hits in this index" }