tencent cloud

TencentDB for MongoDB

문서TencentDB for MongoDBPractical TutorialMongoDB Plan Drift and Index Pinning Practices

MongoDB Plan Drift and Index Pinning Practices

Download
포커스 모드
폰트 크기
마지막 업데이트 시간: 2026-07-10 21:53:35

I. What Is Plan Drift

A common phenomenon in business scenarios is that the same type of query (with identical fields and query patterns, differing only in parameter values) returns results in a few milliseconds one day, but takes hundreds of milliseconds or even seconds the next. By checking with explain, it is found that the originally used efficient index has been replaced by another inefficient one. This phenomenon, where the execution plan switches repeatedly between different indexes and query latency fluctuates significantly, is referred to as index drift (Plan Drift). The key characteristics of drift are:
For the same type of query, the business code remains unchanged, but the query performance suddenly degrades.
explain shows that the index currently in use differs from the historical one.
This is commonly observed for a period of time after events such as instance restart, primary/secondary switchover, or index creation/deletion.
Some queries may automatically recover after a re-evaluation triggered by MongoDB's internal replan mechanism, but they may still drift again later.

II. Why Index Drift Occurs

To facilitate understanding of MongoDB's index selection mechanism, you can use the following analogy:
When MongoDB executes a certain type of query for the first time, it sends all candidate execution plans (which typically correspond to different indexes) into a trial run. The trial run ends after a candidate plan returns a sufficient number of results (101 by default) or reaches the end of the data. MongoDB then calculates a score based on the number of results returned and the execution cost during the trial run. The plan with the highest score is written to the Plan Cache. Subsequent queries of the same pattern preferentially reuse this cache. The drift problem originates from this "initial evaluation + subsequent reuse" mechanism. The following four types of scenarios can all cause the cached index to not be optimal in the long term.

2.1 Initial Parameter Values Are Not Representative

This is a relatively common cause of drift in production. For example, the orders table orders has two indexes:
{ status: 1 }
{ uid: 1, createTime: -1 }
For the business query find({ status, uid }).sort({ createTime: -1 }), which has a consistent query pattern, performance can vary significantly when parameters differ:
Parameter
status Percentage in Full Table
More Suitable Index
status="cancelled"
0.1%
{ status: 1 } (The proportion of cancelled orders is low, and scanning by the status index incurs a lower cost.)
status="paid"
80%
{ uid: 1, createTime: -1 } (Scanning by status requires traversing most of the data, whereas scanning by uid enables precise targeting.)
If the first execution happens to query cancelled data, MongoDB caches the execution plan for this query pattern. Subsequent requests that query data with status=paid all use this index, causing query latency to increase significantly.

2.2 Data Distribution Changes Over Time

After a business has been running for a period of time, the distribution of field values changes:
The distribution of field values shifts: for example, a field's values may initially be concentrated on pending and gradually shift to being predominantly paid as the business develops.
Collection scale change: a user's order volume grows from the hundred-thousand level to the hundred-million level.
Historical data archiving: the collection size drops sharply after archiving.
The Plan Cache stores the execution plan that was optimal at the time of evaluation. After the data distribution changes, the original plan may no longer be applicable.

2.3 Candidate Plan Scores Are Close

When selecting an index, MongoDB scores candidates based on their "trial run productivity" (the number of results a single operation can advance). When the productivity of two indexes is close, the winning plan can easily be influenced by the distribution of data returned during the trial run. For example, Plan A might win in one evaluation, but after a restart and a re-evaluation, Plan B could win. This type of scenario is more likely to cause drift after the cache is cleared.

2.4 The "Re-evaluation Window" After Cache Flush

Common events that trigger Plan Cache clearance or re-evaluation include:
Restart of the mongod process
Adding or deleting any index on a collection (even if the index is unrelated to the target query).
Explicitly call db.collection.getPlanCache().clear()
After the cache is cleared, queries of the same shape that arrive at a new instance will participate in re-evaluation and determine the execution plan for subsequent caching. If the queries involved in the evaluation happen to be Ops health checks, monitoring probes, or queries with unrepresentative parameters, subsequent normal business queries may all be affected.
Summary: The root cause of drift lies in the Plan Cache being keyed by "query shape" rather than by "parameter values". Under the same shape, the optimal index for different parameters may differ.

III. Solution: Index Pinning

For critical business queries where drift has been observed or where there is a strong requirement for query plan stability, you can bypass MongoDB's automatic selection mechanism by manually specifying the index to use. This practice is called index pinning (Index Pinning).
MongoDB 8.0 provides four pinning capabilities, which are compared as follows:
Method
Scope
Cluster-Level Effective
Persistent After Restart
Applicable Scenarios
Add hint to commands.
Single Query
Yes
Yes
Temporary troubleshooting
Embed hint in application code.
Business-specified Queries
Yes
Yes (with code)
Critical business path
planCacheSetFilter (Deprecated)
All Queries of the Same Pattern
No (set per node)
Yes
Not recommended
setQuerySettings (Recommended from version 8.0)
All Queries of the Same Pattern
Yes
Yes
Recommended solution
Note:
Starting with MongoDB 8.0, planCacheSetFilter (Index Filter) has been officially marked as deprecated. This command has two inherent limitations: its settings are lost after a restart and must be configured individually on each node, leading to high operational costs in cluster environments. The official recommendation is to use setQuerySettings instead. This command supports cluster-wide application and persistence across restarts, making it suitable for both replica sets and sharded clusters.

3.1 Attaching hint to Commands

db.orders.find({ uid: 1001, status: "paid" })
         .hint({ uid: 1, createTime: -1 })
         .sort({ createTime: -1 })
It takes effect only for the current query and is suitable for temporary handling during canary testing or for online issues.

3.2 Hardcoding hint in Application Code

# Python Driver Example
cursor = collection.find(
    {"uid": uid, "status": status},
    hint=[("uid", 1), ("createTime", -1)],
).sort([("createTime", -1)])
Embedding index selection into the application code allows it to be managed with code versions, supports code reviews, and facilitates rollbacks. This approach is suitable for core business queries with strict deployment processes.

3.3 setQuerySettings (Recommended from 8.0)

After setQuerySettings is executed once, the settings are applied to matching query structures across the entire cluster (applicable to both replica sets and sharded clusters) and persist after a restart:
db.adminCommand({
  setQuerySettings: {
    find: "orders",
    filter: { uid: 1, status: "paid" },
    sort:   { createTime: -1 },
    $db:    "shop"
  },
  settings: {
    indexHints: [{
      ns: { db: "shop", coll: "orders" },
      allowedIndexes: [ { uid: 1, createTime: -1 } ]
    }]
  }
})
View the configured rules:
db.aggregate([{ $querySettings: {} }])
Remove rules:
db.adminCommand({
  removeQuerySettings: {
    find: "orders",
    filter: { uid: 1, status: "paid" },
    sort:   { createTime: -1 },
    $db:    "shop"
  }
})

3.4 Recommendations for Plan Drift Selection

Scenario
Recommended Solution
Persistence requiring cluster-level, long-term persistence for version 8.0 and above.
setQuerySettings (Recommended solution)
Critical transaction path requiring management with code versions.
Embed hint in application code.
Temporary troubleshooting and canary testing
Add hint to commands.
Non-critical queries that tolerate occasional plan drifts.
Do not fix plans, retain adaptive capability.
Do not pin all queries within a collection. MongoDB's adaptive capabilities can respond to changes in data distribution, and excessive pinning will cause you to lose this ability. It is recommended to pin only those queries whose drift would cause an online incident.

IV. Practical Steps

Step 1: Detecting Drift

The most direct metric for identifying query plan drift is the replan counter. Each time drift occurs, MongoDB triggers a re-evaluation, and this counter increments:
// Classic Engine replan Count
db.serverStatus().metrics.query.planCache.classic.replanned

// SBE Engine replan Count
db.serverStatus().metrics.query.planCache.sbe.replanned
A continuously increasing value indicates that there may be queries within the instance that are repeatedly drifting. If the replanReason field appears in the slow log output, it indicates that the query has undergone replan in the past:
"replanReason": "cached plan was less efficient than expected: ..."

Step 2: Confirming the Target Index

Use explain("allPlansExecution") to examine the execution of all candidate plans. Compare executionTimeMillis, totalKeysExamined, and totalDocsExamined, and incorporate representative business parameter values to identify the index that performs stably in the primary business scenarios. This index is then designated as the pinning target.

Step 3: Setting Pinning Rules

Using the MongoDB 8.0 recommended method setQuerySettings as an example:
db.adminCommand({
  setQuerySettings: {
    find: "orders",
    filter: { uid: 1, status: "paid" },
    sort:   { createTime: -1 },
    $db:    "shop"
  },
  settings: {
    indexHints: [{
      ns: { db: "shop", coll: "orders" },
      allowedIndexes: [ { uid: 1, createTime: -1 } ]
    }]
  }
})

Step 4: Verification

db.orders.find({ uid: 1001, status: "paid" })
         .sort({ createTime: -1 })
         .explain("queryPlanner")
Confirm in the output:
queryPlanner.querySettings: Displays the applied settings.
queryPlanner.winningPlan: The index used is the target index.

Step 5: Continuous Observation

After pinning, it is recommended to observe for more than 7 days:
metrics.query.planCache.classic.replanned and metrics.query.planCache.sbe.replanned: The overall growth trend should decline. Furthermore, confirm by using the slow log query log that this type of query no longer frequently triggers replan.
The P99 latency for this type of query: remains stable at or better than the baseline level before pinning.

Step 6: Removing Pinning When Necessary

When the business model or data distribution undergoes a fundamental change, the originally pinned index may no longer be suitable. Follow the procedure below to remove it:
// 1. Collect the performance of all current candidate plans to identify the index that is more suitable under new data.
db.orders.find({ uid: 1001, status: "paid" })
         .sort({ createTime: -1 })
         .explain("allPlansExecution")

// 2. Remove the query settings.
db.adminCommand({
  removeQuerySettings: {
    find: "orders",
    filter: { uid: 1, status: "paid" },
    sort:   { createTime: -1 },
    $db:    "shop"
  }
})

// 3. Clear the corresponding Plan Cache to enable MongoDB to re-evaluate and select an execution plan based on the new data distribution.
db.orders.getPlanCache().clear()

V. Key Points Review

Focus
Conclusion
Root Cause of Plan Drift
Plan Cache caches queries by their "query shape", but the optimal index for different parameters under the same shape may differ.
Peak Occurrence Period
Within a period after events such as instance restart, index addition, or index deletion.
Detection Method
Locate via metrics.query.planCache.classic.replanned, metrics.query.planCache.sbe.replanned, and replanReason in logs.
Recommended Solution for MongoDB 8.0
setQuerySettings: Cluster-level effect, persists after restart, applicable to replica sets and sharded clusters.
Implementation Principles
Only fix plans for critical business queries, retain adaptive capability for non-critical queries.

Reference Documentation

For further details on query plans, Plan Cache, query shapes, and execution engines not covered in this document, refer to the following MongoDB official documentation:
MongoDB 8.0 Query Plans: This section introduces mechanisms such as the query planner, candidate plan evaluation, Plan Cache status, cache clearing, and index filters.
MongoDB 8.0 Query Structure: This section introduces concepts such as Query Shape, Plan Cache Query Shape, and planCacheShapeHash, which can be used to understand why queries of the same shape reuse execution plans.
MongoDB 8.0 explain Output Description: This section introduces fields such as queryPlanner, winningPlan, executionStats, totalKeysExamined, and totalDocsExamined, which can be used to troubleshoot slow log queries and index usage.
MongoDB 8.0 SBE Query Execution Engine: This section introduces the classic query engine and the SBE (Slot-Based Execution) query execution engine, as well as how to identify the execution engine used by a query through explain and slow log query logs.
MongoDB 8.0 setQuerySettings Command Documentation: This section introduces configuration items such as cluster-level query settings, indexHints, queryFramework, and reject.
MongoDB 8.0 planCacheSetFilter Command Documentation (Deprecated): This section introduces the usage of Index Filter and the deprecation notice starting from MongoDB 8.0.
MongoDB 8.0 $planCacheStats Aggregation Stage Documentation: This section describes how to view the Plan Cache status on a collection.
이전 주제: Performance Optimization and Troubleshooting다음 주제: Optimizing Indexes to Break Through Read/Write Performance Bottlenecks

도움말 및 지원

문제 해결에 도움이 되었나요?

피드백