Unused index detection with $indexStats

Published reference · snapshot 2026-10-20 · skill mongodb-expert/references/unused-index-detection-with-indexstats.md

Also known as: $indexStats usage analysis, Redundant and unused index pruning with $indexStats, Redundant-prefix index detection

↓ Facts as markdown all context files

Source-anchored research for Unused index detection with $indexStats. Run status: COMPLETED.

These notes link each claim to its source. Some citations identify archived research reports that are no longer hosted on this site. A published reference means the content is available; it does not certify independent review or accuracy.Read the editorial policy and follow the sources before relying on a claim.

Definitions

A usage count alone is not a drop decision. Studio 3T says the drop decision is rarely a simple question of whether the index was used recently. AWS says never to drop an index without discussing it with all interested parties and testing performance. Atlas says to consider hiding an index before dropping it. (medium confidence) [source]

How it works

`$indexStats` is an aggregation stage that returns usage statistics for each index of a collection. Per index it returns `name`, `key`, `host`, `accesses` (`ops` and `since`), `shard` (sharded clusters only), `spec`, and `building` (only when true). (high confidence) [source]
`accesses.since` is the time from which MongoDB gathered the statistics for that index. It is populated even when `ops` is 0: the manual's sample output shows `_id_` and `price_1` with `ops: 0` and a set `since`. Read `ops` against the length of the window that `since` opens. (high confidence) [source]
Index statistics reset on `mongod` restart, on index drop and recreation, and whenever `collMod` modifies the index. Changing `hidden` (only when the value actually changes) or `expireAfterSeconds` resets them; hiding an already hidden index or unhiding an already visible one does not. (high confidence) [source]
Unnecessary indexes degrade write performance and consume disk and memory, because inserts and indexed-field updates must maintain every index. The Atlas Performance Advisor suggests dropping unused, redundant and hidden indexes 'to improve write performance and increase storage space'. (high confidence) [source]
`$indexStats` lists hidden indexes too, and its `spec` carries `hidden: true` only for hidden indexes. Hidden indexes stay in statistics output because they are still maintained on every write and still use disk and memory. (high confidence) [source]
Basic detection recipe: run `db.<coll>.aggregate([{ $indexStats: {} }])` per collection, keep entries with `accesses.ops == 0`, and skip `_id_` because `dropIndexes` never drops the `_id` index. The stage is per collection, so survey a deployment by looping `getSiblingDB(db).getCollectionNames()` and printing zero-ops indexes. (high confidence) [source]
Judge zero `ops` only over a window that covers the workload's full cycle. Netdata asks for 24+ hours of representative load including peak and off-peak and nightly batches; Percona warns an index used once a month for heavy reporting looks unused otherwise; Pythian says to compare the window with `db.serverStatus().uptime`. (high confidence) [source]
Hide-then-drop is MongoDB's documented flow: use `$indexStats` to shortlist, hide each candidate (`db.coll.hideIndex(name)` or `collMod` with `hidden: true`) so the planner stops using it while writes still maintain it, watch for regressions, then drop it (a hidden index can be dropped without unhiding) or unhide it at once if queries regress. (high confidence) [source]
PMM ships an advisor check, 'MongoDB unused indexes', that warns when any collection has an unused index. It needs the indexStats metric collector enabled, works from `accesses.ops` since server start, and carries the per-server and restart caveats. (medium confidence) (verified as of 2025-11-03) [source]
Exporting the counters to a time-series store keeps history across `mongod` restarts, so the observation window can exceed the in-process window. Percona says the retention period must match or exceed the application's complete cycle. (low confidence) [source]
Leading-prefix redundancy: an index is redundant when its keys are a leading prefix of another index on the same collection ({ a: 1 } against { a: 1, b: -1 }); { b: -1 } is not. `$indexStats` does not detect this, so compare key patterns. The Performance Advisor does and lists the covering indexes so you can verify the drop. (high confidence) [source]
Do not drop a TTL index because `ops` is 0. Expiry deletions by the TTL monitor are not counted, so an index that is actively expiring documents can report zero. (high confidence) [source]
Do not drop a unique index because `ops` is 0. It can enforce uniqueness on writes with no reads. Percona says a uniqueness check on INSERT does not increment the counter; Netdata warns that dropping it removes the constraint and can allow duplicate keys. (medium confidence) [source]
`$indexStats` is a collection-level aggregation stage: `db.<coll>.aggregate([{ $indexStats: {} }])`. It returns one document per index with `name`, `key`, `host` and `accesses` (`ops` and `since`). A sharded cluster adds `shard`. `spec` holds the full index definition and lists `hidden` only when it is true. `building` appears only while the index is building. (high confidence) [source]
An index's statistics reset when `mongod` restarts, when the index is dropped and recreated, and when `collMod` modifies the index. After a reset, `since` moves forward and `ops` starts again from zero. (high confidence) [source]
A usage sweep must loop over every database and collection, because `$indexStats` runs per collection. Pythian's shell loop prints indexes with `accesses.ops == 0` and skips the `admin` database and the `_id_` index. Percona's PMM advisor script skips `admin`, `config` and `system.profile`. Percona Toolkit `pt-mongodb-index-check check-unused` runs the same `accesses.ops = 0` test in one command, with `--all-databases`, `--databases`, `--collections` and `--json`. (medium confidence) (verified as of 2026-10-20) [source]
Amazon DocumentDB supports `$indexStats` with the same core output (`name`, `key`, `host`, `accesses.ops`, `accesses.since`). AWS ships an `index-review` tool (awslabs/amazon-documentdb-tools) that lists collections, indexes, usage, and unused or redundant indexes. AWS says to run it on the primary and on every secondary or read replica, using instance endpoints, because that is critical for a complete review of index usage. The per-instance JSON outputs can then be merged for a cumulative analysis. (high confidence) [source]
Let the counters span the longest real workload cycle before trusting a zero. A monthly reporting job, a nightly batch or a rarely run geo query can be the only user of an index. Compare `since`, and `db.serverStatus().uptime`, against that cycle. (high confidence) [source]
Counters do not survive a restart, so export them. Sample `$indexStats` on a schedule into a monitoring system, for example PMM through `mongodb_exporter`. Percona notes the history then survives an instance restart, provided retention is at least one full application cycle. (medium confidence) [source]
Do not drop on a short or freshly reset window. Restart, drop and recreate, `collMod` and hide/unhide all reset an index's counter, so a young `since` makes active indexes look unused. Rules of thumb differ: Atlas needs 7 or more days, Netdata asks for at least 24 hours of uptime under a representative workload. Choose the window from your own workload cycle. (high confidence) (verified as of 2026-10-18) [source]
An active TTL index can show `ops: 0`, because TTL deletions are not counted in `accesses.ops`. A hidden TTL index still expires documents, so neither the counter nor a hide test exposes the dependency. Dropping the index stops expiry. (medium confidence) [source]
`$indexStats` needs authorization. The manual requires at least the `clusterMonitor` role when access control is on. AWS's index-review README lists `indexStats` among the permissions it needs, with collStats, listCollections, listDatabases and serverStatus. (medium confidence) [source]

Problems, failure modes and limitations

A hide trial cannot be judged with `$indexStats`: the planner cannot use a hidden index, so its `ops` cannot rise while hidden, and hiding resets its counters. Judge the trial by query latency, slow-query log COLLSCAN entries and `explain()` on critical query shapes. This follows from the Hidden Indexes behavior rules; no source states it as one sentence. (medium confidence) [source]
Percona's `mongodb_exporter` (used by PMM) exports `$indexStats` as the metric `mongodb_indexstats_accesses_ops` through an opt-in `indexstats` collector: `--collector.indexstats`, `--mongodb.indexstats-colls=db1.col1,db2.col2` to limit collections, or `--collect-all`. PMM runs only the `diagnosticdata` and `replicasetstatus` collectors by default, and the Percona author says the collector is off by default because it runs an extra aggregate. (medium confidence) (verified as of 2026-10-20) [source]
Shard-key guard: since MongoDB 6.0, `dropIndexes` and `db.collection.dropIndexes()` raise an error if asked to remove the last remaining shard-key-compatible index, so a shard-key index with zero `ops` cannot be dropped as the last one. (high confidence) [source]
If many indexes show zero `ops`, compare each `accesses.since` and `db.serverStatus().uptime` with the last restart before trusting the list. A recent restart makes active indexes look unused, and PMM's advisor documents the same caveat. (medium confidence) [source]
Percona's `mongodb_exporter` collects `$indexStats` only when started with `--collector.indexstats`; limit it with `--mongodb.indexstats-colls=db1.col1,db2.col2`. The metric is `mongodb_indexstats_accesses_ops`. PMM starts the exporter with only the diagnosticdata and replicasetstatus collectors, so the metric is missing until the collector is enabled. PMM's 'MongoDB unused indexes' advisor check needs this collector. The 2018 Percona post used the older flag `-collect.indexusage`. (medium confidence) (verified as of 2026-10-20) [source]
Hiding tests only dependence on reads. A hidden index is invisible to the query planner and cannot be named in `hint()`. It is still maintained on every write, still enforces a unique constraint, and still expires documents if it is a TTL index. A clean hide period therefore says nothing about whether a unique or TTL index is still needed. (medium confidence) [source]
Hiding an unhidden index, or unhiding a hidden one, resets its `$indexStats` counters. Hiding an already hidden index, or unhiding an already unhidden one, does not. While an index is hidden, judge impact from query latency, slow-query log entries and `explain()` plans, not from the hidden index's own counter. (medium confidence) [source]
Verify after a drop. Check the slow-query log for new `COLLSCAN` entries on the collection. Run `explain('executionStats')` on critical query shapes. Compare write latency (`opLatencies.writes`) and the WiredTiger cache dirty ratio before and after. `explain` in `allPlansExecution` mode shows partial statistics from plan selection, which helps when a query may fall back to another index. (medium confidence) [source]
Use of the shard key's supporting index by chunk split and migration is not counted in `ops`, so a zero does not prove that index is idle. Since MongoDB 6.0, `dropIndexes` raises an error if you try to drop the last remaining shard-key-compatible index. (medium confidence) [source]
Continuous `$indexStats` scraping is an extra aggregate query and can add load. Percona keeps the collector off by default for that reason. On instances with many collections, `--collector.collstats-limit=N` disables the collstats, dbstats, topmetrics and indexstats collectors when the collection count exceeds N. `--mongodb.indexstats-colls` restricts scraping to named collections. (medium confidence) (verified as of 2026-10-20) [source]

Comparisons and alternatives

After a drop, Netdata checks the slow-query log for new COLLSCAN entries, runs `explain('executionStats')` on critical query shapes (a query may move to another index instead of scanning), and compares `opLatencies.writes` before and after to confirm the write-cost gain. (low confidence) [source]
MongoDB's documented removal sequence: find rarely used indexes with `$indexStats`, hide candidates with `db.collection.hideIndex()`, watch the effect, then drop with `db.collection.dropIndexes()`. If the impact is negative, unhide the index instead of rebuilding it. A hidden index can be dropped directly without unhiding it first. (high confidence) [source]

Facts and statements

`accesses.ops` counts user operations that used the index. It excludes internal operations: TTL-index deletion of expired documents, and index use during chunk split, move and post-move cleanup. (high confidence) [source]
The counters are per node. The `accesses` field reports usage only for the mongod where the aggregation runs, and MongoDB says to run `$indexStats` on each node in the cluster for fuller usage data. Output rows carry `host` (and `shard` on sharded clusters) so you can tell nodes apart. (high confidence) [source]
The Atlas Data Explorer index `Usage` column counts lookups by the primary node only, and the Compass `Usage` column counts only the node Compass is connected to. Both pages call the number informational and send you to `$indexStats` on each node for decisions. (high confidence) (verified as of 2026-10-14) [source]
`$indexStats` dates from MongoDB 3.2: Pythian says 3.2 introduced it, and the 3.2.0 JIRA tickets SERVER-21815 (user operations only) and SERVER-22048 (`$match` recording) both describe its behavior as of 3.2.0. (medium confidence) [source]
`$indexStats` must be the first stage of an aggregation pipeline, is not allowed in transactions, and under access control needs a user with at least the `clusterMonitor` role. (high confidence) [source]
A drop candidate must show zero `ops` on every node that can serve reads: all replica-set members (secondaries can serve reads the primary never sees) and every shard's nodes. Netdata says to sum `accesses.ops` across shards before calling an index globally unused. (high confidence) [source]
Before dropping, take a backup of affected collections (Pythian) and record the current index definitions with `getIndexes()` so a dropped index can be recreated quickly (Netdata). (medium confidence) [source]
`dropIndexes` and `db.collection.dropIndexes()` take an exclusive lock on the specified collection for the duration of the operation, and later operations on that collection wait. Drop during low traffic or a maintenance window. (high confidence) [source]
Prioritise by size: combine zero-ops indexes with `collStats` `indexSizes` and `totalIndexSize` or `dbStats` `indexSize` to find the removals that free the most space. (medium confidence) [source]
Atlas Performance Advisor (M10 and larger clusters) suggests dropping unused, redundant (leading-prefix duplicate) and hidden indexes. The same suggestions are exposed through the Atlas CLI and API as `listDropIndexSuggestions`. (high confidence) (verified as of 2026-10-14) [source]
Performance Advisor thresholds: an index is 'unused' if no query used it for 7 or more days after it was created or the server last restarted; only the 20 most active collections are inspected for unused indexes; hidden indexes are always recommended for dropping. (low confidence) (verified as of 2026-10-14) [source]
Replica-set members can carry different index sets (for example tagged or low-priority nodes), so per-node `$indexStats` lets you right-size indexes per node role, not only per collection. (low confidence) [source]
Do not drop on a short window. After a restart, rebuild or any `collMod` (including hide/unhide) every counter restarts at zero, so active indexes look unused. Netdata says not to drop within 24 hours of a restart; Pythian says to check `db.serverStatus().uptime` first. (high confidence) [source]
Hiding an index does not suspend its constraints: a hidden unique index still enforces uniqueness and a hidden TTL index still expires documents. A hide trial therefore tests only planner use, not whether the constraint or expiry is still needed. (low confidence) [source]
Old-version undercount: SERVER-22048, reported against 3.2.0 and fixed in 3.2.3 and 3.3.1, added `$indexStats` recording for aggregation `$match` index use (its title also names mapReduce `query` index use). Counters from 3.2.0 to 3.2.2 can show zero for indexes that `$match` stages used. (low confidence) [source]
`accesses.ops` counts user operations that used the index. `accesses.since` is the time from which MongoDB gathered statistics for that index; it is not the time of last use. So `ops: 0` means the index was not used since `since`. It does not mean the index was never used. (high confidence) [source]
`accesses.ops` excludes internal operations: deletions by TTL indexes, and chunk split, chunk migration and post-move deletion. MongoDB engineers recorded this in SERVER-21815 (affects 3.2.0, closed Done, no fix version). The manual now documents the exclusion. (high confidence) [source]
The counters are node-local. `$indexStats` reports `accesses` only for the `mongod` that runs the aggregation. A read served by a secondary does not raise the primary's count. For cluster-wide usage, run `$indexStats` on every node: each replica-set member and each member of each shard. (high confidence) [source]
`$indexStats` must be the first stage of the aggregation pipeline. It is not allowed in transactions. (high confidence) [source]
Unused indexes cost write throughput, storage and memory. Every insert and delete maintains every index on the collection. Every update that changes an indexed field maintains each index containing that field. No source read quantifies the saving. (high confidence) [source]
Atlas Performance Advisor makes three kinds of drop recommendation: unused indexes, redundant indexes (a prefix of another index in the same collection) and hidden indexes. The Performance Advisor is available only on M10 and larger clusters. (high confidence) (verified as of 2026-10-19) [source]
Atlas flags an index as unused if it has not supported a query in 7 or more days after creation or the last server restart. For unused indexes it inspects only the 20 most active collections. The Performance Advisor page lists hidden indexes from the top 20 most active namespaces at page load, and always recommends dropping them. (low confidence) (verified as of 2026-10-14) [source]
Atlas returns the same drop suggestions through the Admin API v2 operation 'Return All Suggested Indexes to Drop' and the CLI command `atlas api performanceAdvisor listDropIndexSuggestions --clusterName <name> --groupId <id>`. The response holds three arrays: `hiddenIndexes`, `redundantIndexes` and `unusedIndexes`. Each item has `accessCount` (described as 'Usage count (since last restart) of index'), `since`, `sizeBytes`, `namespace`, `name`, `index`, `shards` and `collation`. (high confidence) (verified as of 2026-10-20) [source]
Atlas Data Explorer and MongoDB Compass show a per-index Usage column: lookups since index creation or the last restart. Data Explorer reports the primary node only. Compass reports only the node it is connected to. MongoDB labels both informational and points to `$indexStats` on each node for full coverage. (high confidence) (verified as of 2026-10-20) [source]
Combine evidence across nodes before declaring an index unused. Collect `$indexStats` from every replica-set member and every shard, and keep the index if any node shows use. Percona's advisor says to analyze every member before dropping. Netdata says to sum `accesses.ops` across shards. AWS's tool merges per-instance results. Pythian says to confirm the index is not used on secondaries. (high confidence) [source]
Find redundant (prefix) indexes from index definitions, not from usage counters. An index is redundant if its keys are a prefix of another index in the same collection: `{ a: 1 }` is redundant next to `{ a: 1, b: -1 }`, but `{ b: -1 }` is not. Keep the compound index and drop the prefix index. Percona Toolkit `check-duplicated`, Atlas's Redundant badge and AWS's index-review each run this check separately from the zero-ops scan. (high confidence) [source]
A unique index can show `ops: 0` while still enforcing integrity. Percona reports that the uniqueness check on insert does not increment `ops`. Dropping the index removes the constraint and allows duplicate keys. Exclude unique indexes from zero-ops drop lists. A hidden unique index keeps enforcing its constraint too. (medium confidence) [source]
Old servers can undercount. `$indexStats` exists since MongoDB 3.2. In 3.2.0 and 3.2.1, index use by aggregation `$match` and by mapReduce was not recorded (SERVER-22048; SERVER-22183 is a duplicate report). The fix shipped in 3.2.3 and 3.3.1. A zero from such a build is not reliable. (medium confidence) [source]
`dropIndexes` takes an exclusive lock on the collection for its duration, and later operations on that collection wait. Schedule drops for low traffic and watch lock waits. (high confidence) (verified as of 2026-10-14) [source]

Children

Atlas Performance Advisor drop-index recommendations (frontier, viability 3.33 / 5)
Exporting $indexStats metrics with mongodb_exporter and PMM (frontier, viability 3.77 / 5)
Hidden indexes and safe index removal
Hidden indexes as a safe drop trial (frontier, viability 4.00 / 5)
Leading-prefix redundant index detection (frontier, viability 4.17 / 5)
Per-node and per-shard $indexStats aggregation across replica sets and shards (frontier, viability 4.17 / 5)
Persisting index usage metrics with PMM and mongodb_exporter (frontier, viability 3.77 / 5)
Redundant and prefix index detection (frontier, viability 3.65 / 5)
Safe index removal: locks, rollback and regression checks (frontier, viability 4.08 / 5)
Unique, TTL and shard-key indexes that read as unused (frontier, viability 4.20 / 5)
Write amplification and cache cost of unused indexes (frontier, viability 3.95 / 5)
dropIndexes locking and rollback by rebuild

Frontier under this node: Atlas Performance Advisor drop-index recommendations (3.33), Exporting $indexStats metrics with mongodb_exporter and PMM (3.77), Hidden indexes as a safe drop trial (4.00), Leading-prefix redundant index detection (4.17), Per-node and per-shard $indexStats aggregation across replica sets and shards (4.17), Persisting index usage metrics with PMM and mongodb_exporter (3.77), Redundant and prefix index detection (3.65), Safe index removal: locks, rollback and regression checks (4.08), Unique, TTL and shard-key indexes that read as unused (4.20), Write amplification and cache cost of unused indexes (3.95)

← the whole tree · 3D view· how to read this page