Docs Home → Develop Applications → MongoDB Manual
$planCacheStats
On this page
Definition
$planCacheStats
Returns plan cache information for a collection. The stage returns a document for each plan cache entry.
The
$planCacheStats
stage must be the first stage in the pipeline. The stage takes an empty document as a parameter and has the following syntax:{ $planCacheStats: { } }
Tip
See also:
Considerations
Pipeline
$planCacheStats
must be the first stage in an aggregation
pipeline.
Restrictions
Access Control
On systems running with authorization
, the user
must have the planCacheRead
privilege for the collection.
Redaction
When using Queryable Encryption, the
$planCacheStats
stage omits operations against encrypted collections, even though the operations are cached as normal.
Read Preference
$planCacheStats
observes the read preference in selecting the host(s) from which to return
the plan cache information.
Applications may target different members of a replica set. As such,
each replica set member might receive different read commands and have
plan cache information that differs from other members. Nevertheless,
running $planCacheStats
on a replica set or a sharded
cluster obeys the normal read preference rules. That is, on a replica
set, the operation gathers plan cache information from just one member
of replica set, and on a sharded cluster, the operation gathers plan
cache information from just one member of each shard replica set.
Output
Changed in version 7.0.
The output of $planCacheStats
depends on the query engine used to
complete the query. The value of the version
field of the
$planCacheStats
indicates which query engine was used:
1
indicates that the classic engine was used.2
indicates that the slot-based query execution engine was used.
Examples
The examples in this section use the following orders
collection:
db.orders.insertMany( [ { "_id" : 1, "item" : "abc", "price" : NumberDecimal("12"), "quantity" : 2, "type": "apparel" }, { "_id" : 2, "item" : "jkl", "price" : NumberDecimal("20"), "quantity" : 1, "type": "electronics" }, { "_id" : 3, "item" : "abc", "price" : NumberDecimal("10"), "quantity" : 5, "type": "apparel" }, { "_id" : 4, "item" : "abc", "price" : NumberDecimal("8"), "quantity" : 10, "type": "apparel" }, { "_id" : 5, "item" : "jkl", "price" : NumberDecimal("15"), "quantity" : 15, "type": "electronics" } ] )
Create the following indexes on the collection:
db.orders.createIndex( { item: 1 } ); db.orders.createIndex( { item: 1, quantity: 1 } ); db.orders.createIndex( { quantity: 1 } ); db.orders.createIndex( { quantity: 1, type: 1 } ); db.orders.createIndex( { item: 1, price: 1 }, { partialFilterExpression: { price: { $gte: NumberDecimal("10")} } } );
Note
Index { item: 1, price: 1 }
is a partial index and only indexes documents with price
field greater than or equal to NumberDecimal("10")
.
Run some queries against the collection:
db.orders.find( { item: "abc", price: { $gte: NumberDecimal("10") } } ) db.orders.find( { item: "abc", price: { $gte: NumberDecimal("5") } } ) db.orders.find( { quantity: { $gte: 20 } } ) db.orders.find( { quantity: { $gte: 5 }, type: "apparel" } )
The preceding queries are completed using the slot-based query execution engine.
Return Information for All Entries in the Query Cache
The following aggregation pipeline uses $planCacheStats
to
return information on the plan cache entries for the collection:
db.orders.aggregate( [ { $planCacheStats: { } } ] )
Output:
[ { // Plan Cache Entry 1 version: '2', queryHash: '478AD696', planCacheKey: '21AE23AD', isActive: true, works: Long("7"), timeOfCreation: ISODate("2023-05-22T20:33:49.031Z"), cachedPlan: { ... }, indexFilterSet: false, isPinned: false, estimatedSizeBytes: Long("8194"), host: 'mongodb1.example.net:27018' }, { // Plan Cache Entry 2 version: '2', queryHash: '3D8AFDC6', planCacheKey: '1C2C4360', isActive: true, works: Long("6"), timeOfCreation: ISODate("2023-05-22T20:33:50.584Z"), cachedPlan: { ... }, indexFilterSet: false, isPinned: false, estimatedSizeBytes: Long("11547"), host: 'mongodb1.example.net:27018' }, { // Plan Cache Entry 3 version: '2', queryHash: '27285F9B', planCacheKey: '20BB9404', isActive: true, works: Long("1"), timeOfCreation: ISODate("2023-05-22T20:33:49.051Z"), cachedPlan: { ... }, indexFilterSet: false, isPinned: false, estimatedSizeBytes: Long("7406"), host: 'mongodb1.example.net:27018' }, { // Plan Cache Entry 4 version: '2', queryHash: '478AD696', planCacheKey: 'B1435201', isActive: true, works: Long("5"), timeOfCreation: ISODate("2023-05-22T20:33:49.009Z"), cachedPlan: { ... }, indexFilterSet: false, isPinned: false, estimatedSizeBytes: Long("7415"), host: 'mongodb1.example.net:27018' } ],
See also planCacheKey
.
Find Cache Entry Details for a Query Hash
To return plan cache information for a particular query hash, the
$planCacheStats
stage can be followed by a
$match
on the planCacheKey
field.
The following aggregation pipeline uses $planCacheStats
followed by a $match
stage to return specific information
for a particular query hash:
db.orders.aggregate( [ { $planCacheStats: { } }, { $match: { planCacheKey: "B1435201"} } ] )
Output:
[ { version: '2', queryHash: '478AD696', planCacheKey: 'B1435201', isActive: true, works: Long("5"), timeOfCreation: ISODate("2023-05-22T20:33:49.009Z"), cachedPlan: { slots: '$$RESULT=s11 env: { s3 = 1684787629009 (NOW), s6 = Nothing, s5 = Nothing, s1 = TimeZoneDatabase(Asia/Kuwait...Etc/UCT) (timeZoneDB), s10 = {"item" : 1, "price" : 1}, s2 = Nothing (SEARCH_META) }', stages: '[2] nlj inner [] [s4, s7, s8, s9, s10] \n' + ' left \n' + ' [1] cfilter {(exists(s5) && exists(s6))} \n' + ' [1] ixseek s5 s6 s9 s4 s7 s8 [] @"358822b7-c129-47b7-ad7f-40017a51b03c" @"item_1_price_1" true \n' + ' right \n' + ' [2] limit 1 \n' + ' [2] seek s4 s11 s12 s7 s8 s9 s10 none none [] @"358822b7-c129-47b7-ad7f-40017a51b03c" true false \n' }, indexFilterSet: false, isPinned: false, estimatedSizeBytes: Long("7415"), host: 'mongodb1.example.net:27018' } ]
See also planCacheKey
and queryHash
.