Ever wonder if your indices are properly used in your postgres database? Wonder no more!

SELECT relname, seq_tup_read, idx_tup_fetch,
        cast(idx_tup_fetch AS numeric) / (idx_tup_fetch + seq_tup_read)
          AS idx_tup_pct
FROM pg_stat_user_tables
WHERE (idx_tup_fetch + seq_tup_read) > 0
ORDER BY idx_tup_pct;

Where

  • seq_tup_read: Number of live rows fetched by sequential scans
  • idx_tup_fetch: Number of live rows fetched by index scans

The official doc is pretty comprehensive!