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 has the following syntax:

{
$planCacheStats: {
allHosts: <boolean>
}
}

The $planCacheStats aggregation stage has the following options:

Option
Description
allHosts

Configures how the $planCacheStats aggregation stage targets nodes in a sharded cluster.

  • If true, mongos broadcasts the $planCacheStats aggregation stage to all nodes (primary and secondaries) for each affected shard that contains one or more chunks from the target collection.

  • If false, the $planCacheStats aggregation stage follows the Read Preference and only retrieves the plan cache from the targeted replica set primary.

Note

Replica sets and standalone servers return an error during pipeline parsing if allHosts is set to true. The option is only available to sharded clusters.

Default: false

New in version 7.1.

Tip

See also:

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

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.

When the allHosts option is set to false, $planCacheStats follows 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) →