stackrox
diff --git a/‎central/debug/service/service.go‎
Lines changed: 10 additions & 1 deletion b/‎central/debug/service/service.go‎
Lines changed: 10 additions & 1 deletion
diff --git a/‎pkg/postgres/stats/stats.go‎
Lines changed: 52 additions & 0 deletions b/‎pkg/postgres/stats/stats.go‎
Lines changed: 52 additions & 0 deletions
diff --git a/‎pkg/postgres/stats/stats_pg_test.go‎
Lines changed: 29 additions & 0 deletions b/‎pkg/postgres/stats/stats_pg_test.go‎
Lines changed: 29 additions & 0 deletions
@@ -512,7 +512,16 @@ func getCentralDBData(ctx context.Context, zipWriter *zipWriter) error {
 	if activities.Error != "" {
 		log.Errorw("error retrieving pg_stat_activity", logging.Err(errors.New(activities.Error)))
 	}
-	return addJSONToZip(zipWriter, "central-db-pg-activity.json", activities)
+	if err := addJSONToZip(zipWriter, "central-db-pg-activity.json", activities); err != nil {
+		return err
+	}
+
+	// Get the index health stats
+	indexStats := stats.GetPGIndexStats(ctx, db, pgStatStatementsMax)
+	if indexStats.Error != "" {
+		log.Errorw("error retrieving pg_stat_user_indexes", logging.Err(errors.New(indexStats.Error)))
+	}
+	return addJSONToZip(zipWriter, "central-db-pg-index-stats.json", indexStats)
 }
 
 func (s *serviceImpl) getLogImbue(ctx context.Context, zipWriter *zipWriter) error {
 
@@ -204,3 +204,55 @@ func GetPGStatActivities(ctx context.Context, db postgres.DB, limit int) *PGStat
 	}
 	return &activities
 }
+
+// PGIndexStat is the data model for a single index from pg_stat_user_indexes joined with pg_index
+type PGIndexStat struct {
+	TableName      string
+	IndexName      string
+	IndexType      string
+	IndexSizeBytes int64
+	IsValid        bool
+	IsReady        bool
+	IndexScans     int64
+}
+
+// PGIndexStats is a wrapper around PGIndexStat
+type PGIndexStats struct {
+	Indexes []*PGIndexStat
+	Error   string
+}
+
+// GetPGIndexStats returns index health information from pg_stat_user_indexes joined with pg_index.
+// pg_stat_user_indexes is used to scope results to the current user's indexes only,
+// avoiding exposure of indexes from management or infrastructure schemas on external databases.
+func GetPGIndexStats(ctx context.Context, db postgres.DB, limit int) *PGIndexStats {
+	var indexStats PGIndexStats
+	rows, err := db.Query(ctx,
+		`SELECT s.relname, s.indexrelname, am.amname,
+			pg_relation_size(ix.indexrelid), ix.indisvalid, ix.indisready,
+			s.idx_scan
+		FROM pg_stat_user_indexes s
+		JOIN pg_index ix ON ix.indexrelid = s.indexrelid
+		JOIN pg_class i ON i.oid = ix.indexrelid
+		JOIN pg_am am ON am.oid = i.relam
+		ORDER BY pg_relation_size(ix.indexrelid) DESC
+		LIMIT $1`, limit)
+	if err != nil {
+		indexStats.Error = err.Error()
+		return &indexStats
+	}
+	defer rows.Close()
+
+	for rows.Next() {
+		var idx PGIndexStat
+		if err := rows.Scan(&idx.TableName, &idx.IndexName, &idx.IndexType, &idx.IndexSizeBytes, &idx.IsValid, &idx.IsReady, &idx.IndexScans); err != nil {
+			indexStats.Error = errors.Wrap(err, "error scanning rows from pg_stat_user_indexes").Error()
+			return &indexStats
+		}
+		indexStats.Indexes = append(indexStats.Indexes, &idx)
+	}
+	if err := rows.Err(); err != nil {
+		indexStats.Error = err.Error()
+	}
+	return &indexStats
+}
@@ -0,0 +1,29 @@
+//go:build sql_integration
+
+package stats
+
+import (
+	"context"
+	"testing"
+
+	"github.com/stackrox/rox/pkg/postgres/pgtest"
+	"github.com/stackrox/rox/pkg/sac"
+	"github.com/stretchr/testify/assert"
+	"github.com/stretchr/testify/require"
+)
+
+func TestGetPGIndexStats(t *testing.T) {
+	ctx := sac.WithAllAccess(context.Background())
+	tp := pgtest.ForT(t)
+	defer tp.Close()
+
+	result := GetPGIndexStats(ctx, tp.DB, 100)
+	require.Empty(t, result.Error)
+	assert.NotNil(t, result.Indexes)
+	for _, idx := range result.Indexes {
+		assert.NotEmpty(t, idx.TableName)
+		assert.NotEmpty(t, idx.IndexName)
+		assert.NotEmpty(t, idx.IndexType)
+		assert.True(t, idx.IsValid, "index %s on %s should be valid", idx.IndexName, idx.TableName)
+	}
+}