ApsaraDB for MongoDB:Query plans and query replanning

Plan cache entry states

A plan cache entry can be in one of the following states:

Plan cache behavior

The plan cache is stored entirely in memory and does not persist across restarts. The cache is cleared in the following situations:

• The MongoDB database is restarted.

• Collections or indexes are deleted.

The plan cache also has a size limit and follows the least recently used (LRU) cache replacement mechanism. Entries that are less frequently accessed are evicted from the cache over time.

Plan cache management commands

You can run the following commands to manage plan cache entries for a specific collection:

Example of retrieving a cached query plan:

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

queryHash and planCacheKey

MongoDB 4.2 and later introduces queryHash to identify query shapes. Each query shape is associated with a queryHash value. MongoDB 4.2 also introduces planCacheKey, which differs from queryHash in that it is a function of both the query shape and the available indexes for that shape. If indexes that can support the query shape are created or deleted, the planCacheKey value may change, but the queryHash value remains unchanged.

Example

Consider a 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 uses a partial filter expression that requires x > 10. Therefore, this index can support query operation 2 (x > 20) but not query operation 1 (x > 5). As a result, the two queries have different planCacheKey values. If a new index {x:1, a:1} is created, the planCacheKey values for both query operations change.

Impacts

• Frequent query replanning can degrade query performance.

• Excessive query replanning can lead to contention for mutex locks, resulting in high CPU utilization.

Identifying replanning

You may encounter the "replanned":true keyword in the slow query log. This keyword indicates that the query planner could not maintain a consistently efficient plan for a given 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"

Solutions

The following solutions are organized from recommended long-term fixes to immediate mitigations and last-resort measures.

db.<collection>.find({a:"ABC"},{b:1,_id:0}).sort({c:1}).hint({ a:1, c:1, b:1} )

// Set an 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 existing index filters.
db.runCommand(
   {
      planCacheClearFilters: "<collection>"
   }
)