When MongoDB receives a query, it performs the following steps:
- Evaluate the available indexes that could be used.
- Generate and test multiple execution plans using candidate indexes.
- Measure their performance during a trial phase.
- Select the fastest plan (the winning plan) and execute the query.
These steps are known as query planning, and they are expensive in terms of CPU, memory, and potentially disk utilization. Calculating a query plan for every single query would significantly impact database performance.
Fortunately, MongoDB does not repeat this process for every query.
Once a query plan has been generated, it is stored in an in-memory cache associated with the collection: the Query Plan Cache. Each collection has its own independent cache.
The Query Plan Cache stores:
- The query shape (filter conditions, projection, sort, etc.)
- The winning plan (indexes and access methods)
- Additional metadata, such as the expected performance of the plan (measured in works, an abstract unit of measurement that MongoDB uses internally to quantify the resource consumption of a query execution plan)
Every subsequent query with the same shape retrieves the winning plan directly from the cache instead of triggering query planning again.
- Benefits are:
- Less CPU utilization
- Reduced latencyFaster recurring queries
- Better overall scalability
Let’s test it
Let’s explore the Query Plan Cache and see how to inspect and manage it.
Create a Test Collection
Create an orders collection with 1 million documents using the following script in the shell:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
use queryCacheLab db.orders.drop() const statuses = ["NEW", "PAID", "SHIPPED", "CANCELLED"] const TOTAL_DOCS = 1000000 const BATCH_SIZE = 10000 let bulk = [] let batchNumber = 0 let insertedDocs = 0 const startTime = new Date() for (let i = 0; i < TOTAL_DOCS; i++) { bulk.push({ orderId: i, customerId: Math.floor(Math.random() * 50000), status: statuses[Math.floor(Math.random() * 4)], total: Math.floor(Math.random() * 1000), createdAt: new Date(2024, 0, 1 + Math.floor(Math.random() * 365)) }) if (bulk.length === BATCH_SIZE) { db.orders.insertMany(bulk, { ordered: false }) batchNumber++ insertedDocs += bulk.length bulk = [] const elapsed = (new Date() - startTime) / 1000 const percent = ((insertedDocs / TOTAL_DOCS) * 100).toFixed(2) print( `Batch ${batchNumber} completato | ` + `Inserted documents: ${insertedDocs}/${TOTAL_DOCS} (${percent}%) | ` + `Time elapsed: ${elapsed.toFixed(2)} sec` ) } } // Insert eventual remaining documents if (bulk.length > 0) { db.orders.insertMany(bulk, { ordered: false }) insertedDocs += bulk.length batchNumber++ } const totalTime = (new Date() - startTime) / 1000 print("====================================") print(`Inserts completed`) print(`Total batches: ${btchNumber}`) print(`Total documents: ${insertedDos}`) print(`Time: ${totalTime.toFixed(2)} ses`) print("====================================") |
Create Indexes
Create secondary indexes, so MongoDB can evaluate multiple execution plans:
|
1 2 3 |
db.orders.createIndex({ status: 1 }) db.orders.createIndex({ customerId: 1 }) db.orders.createIndex({ status: 1, customerId: 1 }) |
Generate and Inspect a Cache Plan
Run the query a few times to ensure the plan is cached:
|
1 2 3 4 |
db.orders.find({ status: "PAID", customerId: 1234 }) |
Inspect the execution plan:
|
1 2 3 4 |
db.orders.find({ status: "PAID", customerId: 1234 }).explain("executionStats") |
Relevant details:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
… winningPlan: { isCached: true, stage: 'FETCH', inputStage: { stage: 'IXSCAN', keyPattern: { status: 1, customerId: 1 }, indexName: 'status_1_customerId_1', … executionStats: { executionSuccess: true, nReturned: 6, executionTimeMillis: 2, totalKeysExamined: 6, totalDocsExamined: 6, … |
The winning plan:
- is cached
- uses the compound index
- shows good execution metrics
The Query Plan Cache is now populated with this optimal plan.
Inspecting the Query Plan Cache
You can inspect the cache using :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
db.orders.getPlanCache().list() [ { version: '1', queryHash: '706C5F81', planCacheShapeHash: '706C5F81', planCacheKey: '2189DB6F', isActive: true, works: Long('7'), worksType: 'works', timeOfCreation: ISODate('2026-02-12T13:53:05.349Z'), createdFromQuery: { query: { status: 'PAID', customerId: 1234 }, sort: {}, projection: {} }, cachedPlan: { stage: 'FETCH', inputStage: { stage: 'IXSCAN', keyPattern: { status: 1, customerId: 1 }, indexName: 'status_1_customerId_1', isMultiKey: false, multiKeyPaths: { status: [], customerId: [] }, isUnique: false, isSparse: false, isPartial: false, indexVersion: 2, direction: 'forward', indexBounds: { status: [ '["PAID", "PAID"]' ], customerId: [ '[1234, 1234]' ] } } }, … … |
Alternatively, via aggregation:
|
1 2 3 |
db.orders.aggregate( [ { $planCacheStats: { } } ] ) |
From the cache, you can see:
- Unique identifiers for the query shape (queryHash, planCacheKey)
- The cached execution plan (cachedPlan)
- The expected cost (works)
Reusing the Cached Plan
Now run the same query shape with different values:
|
1 2 3 4 |
db.orders.find({ status: "PAID", customerId: 4000 }).explain("executionStats") |
From explain(), you will see:
|
1 2 3 4 5 6 7 8 9 10 |
winningPlan: { isCached: true, ... } executionStages: { isCached: true, ... } queryHash: '4C4D18FFE78D0BC85621B75A7F1C338AEB0F0A2D5E438A5A6DF815198F64283D' planCacheKey: '2189DB6F' |
This confirms the plan was reused from the cache (isCached: true and look at the referenced planCacheKey).
Verifying Cache Usage with the Profiler
If the profiler is enabled, you can check into the system.profile collection of your database:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
db.system.profile.find({}).sort({ts: -1}).limit(1) [ { op: 'query', ns: 'queryCacheLab.orders', command: { find: 'orders', filter: { status: 'PAID', customerId: 1234 }, lsid: { id: UUID('3a1f019f-1836-4c76-96c8-3ae596d01108') }, '$clusterTime': { clusterTime: Timestamp({ t: 1770912932, i: 1 }), signature: { hash: Binary.createFromBase64('QOFvrL9a4DjdLUE0PEzVLvxHukI=', 0), keyId: Long('7605947023161819142') } }, '$db': 'queryCacheLab' }, keysExamined: 13, docsExamined: 13, fromPlanCache: true, nBatches: 1, cursorExhausted: true, numYield: 0, nreturned: 13, planCacheShapeHash: '706C5F81', queryHash: '706C5F81', planCacheKey: '2189DB6F', queryShapeHash: '4C4D18FFE78D0BC85621B75A7F1C338AEB0F0A2D5E438A5A6DF815198F64283D', queryFramework: 'classic', … … |
This also confirms that the cached plan was used (fromPlanCache: true and look at the referenced planCacheKey).
When the Cached Plan becomes Suboptimal
Let’s degrade the selectivity by setting all orders to status=’PAID’.
|
1 2 3 4 |
db.orders.updateMany( {}, { $set: { status: "PAID" } } ) |
Now status is no longer selective.
Running the original query again:
|
1 2 3 4 |
db.orders.find({ status: "PAID", customerId: 1234 }).explain("executionStats") |
You may notice:
- Higher keysExamined
- Increased executionTimeMillis
MongoDB is still using the cached plan, even though data distribution has changed.
This could be the main concern about the cached query plans. If the data distribution changes without being so bad, MongoDB continues to use that plan. This way it could happen that the cached plan is no longer the best one.
Manually clearing the cache
Clear all cached plans and check it is really empty:
|
1 2 |
db.orders.getPlanCache().clear() db.orders.getPlanCache().list() |
Or remove a specific plan by providing the query shape:
|
1 2 3 4 |
db.orders.getPlanCache().clearPlansByQuery({ status: 'PAID', customerId: 1234 }) |
Query Plan Cache invalidation
Since the data changes over the time, the query plan for a specific query can change as well. The winning plan depends on:
- Available indexes
- Data distribution.
Doing massive inserts, updates or deletions can alter the distribution of the data and a plan that was optimal yesterday, cannot be optimal today. This means the query plan needs to be recalculated from time to time to make sure MongoDB can rely on the most up-to-date and optimized one.
Plans are recalculated only when invalidated, not on a schedule.
Invalidation happens when:
- An index is created
- An index is dropped
- mongod restarts
- The cached plan becomes inefficient. MongoDB compares actual performance with expected performance (works). If the deviation is large (different order of magnitude), the plan is invalidated and replanned
The Re-Planning issue
Everything works well until you face the re-planning issue.
It could happen that every query triggers the invalidation of the cached plan, and so a re-planning is needed continuously. You can easily identify when this happens because usually you can notice a significant CPU utilization increase.
You can also verify in the mongod log what is happening:
|
1 2 3 4 5 6 7 8 9 10 |
{"t":{"$date":"2025-12-29T18:50:17.970+00:00"},"s":"I", "c":"COMMAND", "id":51803, "ctx":"conn4577913","msg":"Slow query","attr":{"type":"command",...... "planningTimeMicros":215107053,"keysExamined":46283,"docsExamined":46283, "hasSortStage":true,"fromMultiPlanner":true, "replanned":true, "replanReason":"cached plan was less efficient than expected: expected trial execution to take 2 works but it took at least 20 works", "nBatches":1,"cursorExhausted":true,"numYields":15603,"nreturned":0, "queryHash":"6950AFA3","planCacheKey":"F8B9BF2F", "queryFramework":"classic",......."durationMillis":215107}} |
If you see fields like “replanned”:true, and “replanReason”:”cached plan was less efficient than expected: expected trial execution to take 2 works but it took at least 20 works”, it means the query required re-planning.
Look at the “planningTimeMicros”:215107053 and compare it with “durationMillis”:215107; almost the entire time was spent in the query planning.
Usually, a query replanned is also a slow query. Then the occurrences are easy to identify by filtering the log with “id”:51803. This is the unique identifier for all slow query entries in the log.
Common causes of continuous re-planning:
- Having unstable queries that are data-dependent
- Indexed values are not evenly distributed: a plan is good for specific values of the filter conditions and not for others
- Having right indexes but not selective enough
- There are 2 or more plans providing the same performance. Little variations in the data can change the real performance of the queries
- The cardinality of indexed fields changes rapidly. This could happen when:
- Doing massive inserts
- Doing massive deletes
- Having TTL indexes that delete lots of documents
- Using capped collection that overwrites lots of documents
- Using $or, $in or $regex operators can lead to completely different results depending on the number of items you specify in the query
Write-intensive workloads are generally more sensitive than read-intensive ones.
In short: the re-planning loop occurs when data changes faster than the planner can adapt.
Mitigating Re-Planning
There is no universal fix, but you can
- Analyze the affected query and create more specific and selective indexes
- Avoid overly generic queries that can be affected by the uneven data distribution
- Reduce the number of items when using $or or $in operators. Eventually split the query in multiple smaller queries
- Regularly analyze explain(“executionStats”) for monitoring the execution plan
- Use hint() to force the utilization of a specific index in extreme cases.
- Using hint() in production is not ideal, but in pathological situations it can immediately stabilize performance while you investigate properly.
About hint(), there is a new feature available in MongoDB 8.0 that provides a way to hint a specific index for any query shape for the entire cluster. More details on this page.
Query Plan Cache is Local
Important to remember is that the Query Plan Cache is:
- Per collection
- Local to each mongod instance
There is no cluster-wide global cache.
In a sharded cluster:
- Each shard may have a different plan for every query
- PRIMARY and SECONDARY nodes may differ as well if you created different indexes
- Chunk migrations can influence the plan selection
Starting from version 6.3 it is possible configuring the size of the Query Plan Cache using the parameter planCacheSize. It can be set to a percentage of available memory like for example “8.5%” or to a fixed size in MB or GB, like for example “100MB” or “1GB”. It defaults to 5%.
For more details look at this page.
Conclusion
The Query Plan Cache is a powerful MongoDB feature that stabilizes and improves query performance by avoiding repeated planning. This also helps to increase scalability.
However, in specific scenarios—especially with unstable data distribution or low-selectivity indexes—the re-planning loop can cause high CPU usage and degraded performance.
Understanding how the cache works, how invalidation happens, and how to detect re-planning issues is essential to maintaining predictable and efficient query performance.
About the Author
Corrado PandianiPrior to joining Percona as a Senior Consultant, Corrado spent more than 20 years in developing web sites and designing and administering MySQL. He is a MySQL enthusiast since version 3.23 and his skills are focused on performances and architectural design. He's also a trainer and a MongoDB consultant.
