Docs Menu

Docs HomeDevelop ApplicationsMongoDB Manual

$planCacheStats

On this page

  • Definition
  • Considerations
  • Pipeline
  • Restrictions
  • Access Control
  • Redaction
  • Read Preference
  • Output
  • Examples
  • Return Information for All Entries in the Query Cache
  • Find Cache Entry Details for a Query Hash
$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:

$planCacheStats must be the first stage in an aggregation pipeline.

  • $planCacheStats is not allowed in:

  • $planCacheStats requires read concern level "local".

On systems running with authorization, the user must have the planCacheRead privilege for the collection.

When using Queryable Encryption, the $planCacheStats stage omits operations against encrypted collections, even though the operations are cached as normal.

$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.

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:

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.

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.

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.

←  $out (aggregation)$project (aggregation) →