MongoDB query plan cache and replanning troubleshooting - ApsaraDB for MongoDB

Learn how MongoDB query plans work, why replanning occurs, and how to resolve replanning issues.

Query planner

The MongoDB query planner selects and caches the most efficient query plan for each query based on available indexes.

The query planner evaluates candidate plans by the number of work units (works) each requires, then caches the winning plan. Cached entries are reused for queries with the same query shape.

Plan cache entries have three states:

Missing: No plan exists in the cache.
Inactive: The plan exists in the cache with an evaluated works value and can be promoted to active.
Active: The winning plan in the cache. It can be demoted to inactive.

The plan cache is stored in memory and not persisted to disk. It is cleared when the MongoDB instance restarts or when a collection or index is dropped. The cache has a size limit and uses LRU eviction.

Use the following commands to manage query plans:

Clear the plan cache for a collection.
```
db.<collection>.getPlanCache().clear()
```

List all query shapes for a collection.

db.<collection>.getPlanCache().listQueryShapes()

View cached plans for a query shape.

db.<collection>.getPlanCache().getPlansByQuery({"query": {"name": "testname"}, "sort": { "name": 1 })

QueryHash and planCacheKey

Starting with MongoDB 4.2, each query shape gets a unique queryHash. The planCacheKey depends on both the query shape and available indexes. Adding or removing an index that supports the query shape changes the planCacheKey but not the queryHash.

Example collection with the following indexes and query shapes:

Indexes

db.foo.createIndex( { x: 1 } )
db.foo.createIndex( { x: 1, y: 1 } )
db.foo.createIndex( { x: 1, z: 1 }, { partialFilterExpression: { x: { $gt: 10 } } } )

Query shapes

db.foo.explain().find( { x: { $gt: 5 } } )  // Query operation 1
db.foo.explain().find( { x: { $gt: 20 } } ) // Query operation 2

The third index supports query 2 but not query 1, so the two queries have different plan cache keys. Adding index {x:1, a:1} updates both cache keys.

Query replanning

As collection data changes, a cached query plan may become suboptimal and must adapt.

When a query matches a cached plan, the planner reuses it directly while monitoring performance. If the cached plan becomes significantly less efficient (for example, more than 10 times slower) than an alternative, the planner aborts execution, evicts the plan, and re-evaluates all candidates. This is called query replanning.

Impact and solutions

The keyword "replanned":true in the slow query log indicates that the planner cannot find a consistently effective plan for a specific query shape.

Example slow query log entry:

"replanned":true,"replanReason":"cached plan was less efficient than expected: expected trial execution to take X works but it took at least 10X works"

Impacts

Degrades query performance.
Causes mutex lock contention and high CPU utilization.

Solutions

Temporarily upgrade the instance specification to relieve database load.
Clear the plan cache and check whether the planner selects a better plan.
Use hint() in your application to specify an index for query conditions that cause replanning:
```
db.<collection>.find({a:"ABC"},{b:1,_id:0}).sort({c:1}).hint({ a: 1, c: 1, b: 1} )
```
Note
A hint overrides the query planner's default behavior.
Use an index filter to restrict indexes for queries that trigger replanning:
```
// Check if the index exists
db.<collection>.getIndexes()

// Set the index filter
db.runCommand(
   {
      planCacheSetFilter: "<collection>",
      query: { a: "ABC" },
      projection: { b: 1, _id: 0 },
      sort: { c: 1 },
      indexes: [
         { a: 1, c: 1 , b: 1 }
      ]
   }
)
// Remove the previous filter
db.runCommand(
   {
      planCacheClearFilters: "<collection>"
   }
)
```
Note
- Index filters override the query planner's default behavior.
- If a query has both a hint and an index filter, the index filter takes precedence. Use index filters with caution. Index Filters.
- The query optimizer uses either a collection scan (COLLSCAN) or the index specified in planCacheSetFilter. If the specified index does not exist or is hidden, the optimizer falls back to a collection scan, which can cause high CPU usage and I/O spikes. Before running planCacheSetFilter, use db.<collection>.getIndexes() to verify the index exists. planCacheSetFilter.
(Recommended) Optimize your queries and indexes to prevent replanning.

Note
Hints and index filters are workarounds. Reviewing your queries, indexes, and document schema typically yields better results.
(Recommended) For instances running major version 4.2 or 4.4, update to the latest kernel minor version to reduce mutex lock contention (SERVER-40805), or upgrade to major version 5.0 or 6.0. Upgrade the minor version of a database. Upgrade the major version of a database.

If none of these solutions resolve the issue, you can submit a ticket for technical support.

ApsaraDB for MongoDB:Query plans and query replanning