MongoDB $indexStats Unused & Redundant Index Hunter (mongosh)

// MongoDB Unused & Left-Prefix Redundant Index Auditor (mongosh 8.0+)
// Execute: mongosh "$MONGODB_URI" --quiet mongodb-index-hunter.js

const targetDbName = db.getName();
const minAgeDays = 7;
const cutoffMs = Date.now() - minAgeDays * 86400 * 1000;

function keySignature(keyObj) {
  return Object.entries(keyObj).map(([k, v]) => `${k}:${v}`);
}

function isLeftPrefix(shorter, longer) {
  if (shorter.length >= longer.length) return false;
  return shorter.every((entry, idx) => longer[idx] === entry);
}

print(`=== Auditing Indexes in Database: ${targetDbName} (min age: ${minAgeDays}d) ===\n`);

for (const collInfo of db.getCollectionInfos({ type: 'collection' })) {
  const collName = collInfo.name;
  if (collName.startsWith('system.')) continue;

  const coll = db.getCollection(collName);
  const stats = coll.aggregate([{ $indexStats: {} }]).toArray();
  const specs = new Map(coll.getIndexes().map((ix) => [ix.name, ix]));

  const parsed = stats.map((st) => ({
    name: st.name,
    ops: Number(st.accesses?.ops || 0),
    since: new Date(st.accesses?.since || Date.now()),
    sig: keySignature(st.key),
    spec: specs.get(st.name) || {},
  }));

  for (const idx of parsed) {
    if (idx.name === '_id_') continue;
    // Never auto-hide unique constraints or TTL indexes!
    if (idx.spec.unique || typeof idx.spec.expireAfterSeconds === 'number' || idx.spec.hidden) {
      continue;
    }

    const coveringIndex = parsed.find(
      (other) => other.name !== idx.name && !other.spec.sparse && !other.spec.partialFilterExpression && isLeftPrefix(idx.sig, other.sig)
    );

    if (coveringIndex) {
      print(`[REDUNDANT PREFIX] ${collName}.${idx.name} covered by ${coveringIndex.name}`);
      print(`  -> db.getCollection("${collName}").hideIndex("${idx.name}");\n`);
      continue;
    }

    if (idx.ops === 0 && idx.since.getTime() <= cutoffMs) {
      print(`[UNUSED 0 OPS] ${collName}.${idx.name} (0 reads since ${idx.since.toISOString()})`);
      print(`  -> db.getCollection("${collName}").hideIndex("${idx.name}");\n`);
    }
  }
}