Docs Menu
Docs Home
/
MongoDB Manual
/
Aggregation Operations
/
Reference
/
Stages

$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

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 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:

Query Plans

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

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.

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:

For queries that use the classic execution engine, $planCacheStats returns a document similar to the following:

{
   "version" : 1,
   "createdFromQuery" : <document>,
   "planCacheShapeHash" : <hexadecimal string>,
   "planCacheKey" : <hexadecimal string>,
   "isActive" :  <boolean>,
   "works" : <NumberLong>,
   "cachedPlan" : {
      "stage" : <STAGE1>,
      "filter" : <document>,
      "inputStage" : {
         "stage" : <STAGE2>,
         ...
      }
   },
   "timeOfCreation" : <date>,
   "creationExecStats" : [   // Exec Stats Document for each candidate plan
      {
         "nReturned" : <num>,
         "executionTimeMillisEstimate" : <num>,
         "totalKeysExamined" : <num>,
         "totalDocsExamined" :<num>,
         "executionStages" : {
            "stage" : <STAGE A>,
            ...
            "inputStage" : {
               "stage" : <STAGE B>,
               ...
            }
         }
      },
      ...
   ],
   "candidatePlanScores" : [
      <number>,
      ...
   ],
   "indexFilterSet" : <boolean>,
   "estimatedSizeBytes" : <num>,
   "querySettings" : <document>,
   "host" : <string>,
   "shard" : <string>
}

Each document includes various query plan and execution stats, including:

Field
Description
version

A number that indicates the query engine used to complete the query.

createdFromQuery

A document that contains the specific query that resulted in this cache entry. For example:

{
  "query" : <document>,
  "sort" : <document>,
  "projection" : <document>
}
isActive

A boolean that indicates whether the entry is active or inactive.

  • If active, the query planner is currently using the entry to generate query plans.

  • If inactive, the query planner is not currently using the entry to generate query plans.

See Plan Cache Entry State.

planCacheShapeHash

A hexadecimal string that represents the hash of the query shape. See explain.queryPlanner.planCacheShapeHash.

Starting in MongoDB 8.0, the pre-existing queryHash field is renamed to planCacheShapeHash. If you're using an earlier MongoDB version, you'll see queryHash instead of planCacheShapeHash.

planCacheKey

A hexadecimal string that represents the hash of the key used to find the plan cache entry associated with this query. The plan cache key is a function of both the plan cache query shape and the currently available indexes for that shape. See explain.queryPlanner.planCacheKey.

cachedPlan

The details of the cached plan. The fields included in the cachedPlan vary based on whether the query was completed using the classic engine or the slot-based query execution engine. For more information on query plans, see explain.queryPlanner.

works

The number of "work units" performed by the query execution plan during the trial period when the query planner evaluates candidate plans. For more information, see explain.executionStats.executionStages.works.

timeOfCreation
Time of creation for the entry.
creationExecStats

An array of execution stats documents. The array contains a document for each candidate plan.

For details on the execution stats, see explain.executionStats.

candidatePlanScores

An array of scores for the candidate plans listed in the creationExecStats array.

indexFilterSet

A boolean that indicates whether an index filter exists for the plan cache query shape.

estimatedSizeBytes

The estimated size in bytes of a plan cache entry.

querySettings

New in version 8.0.

A document that contains query settings previously set using setQuerySettings:

querySettings: {
   indexHints: [ {
      ns: { db: <string>, coll: <string> },
      allowedIndexes: <array>
   }, ... ],
   queryFramework: <string>
}

querySettings fields:

Field
Type
Description
indexHints.ns
document

Namespace for index hints.

db
string
Name of the database for index hints.
coll
string
Name of the collection for index hints.
indexHints.allowedIndexes
array
Array of indexes for index hints. For more details, see Indexes and hint().
queryFramework
string

Query framework string can be:

host

The hostname and port of the mongod instance from which the plan cache information was returned.

When run on a sharded cluster, the operation returns plan cache entry information from a single member in each shard replica set. This member is identified with the shard and host fields. See also Redaction.

shard

The name of the shard from which $planCacheStats retrieved the cache entry.

Only available if run on a sharded cluster.

For queries that use the slot-based query execution engine, $planCacheStats returns a document similar to the following:

{
   "version" : 2,
   "planCacheShapeHash" : <hexadecimal string>,
   "planCacheKey" : <hexadecimal string>,
   "isActive" :  <boolean>,
   "works" : <NumberLong>,
   "cachedPlan" : {
      "slots" : <string>,
      "stages": <string>
   },
   "indexFilterSet" : <boolean>,
   "estimatedSizeBytes" : <num>,
   "querySettings" : <document>,
   "host" : <string>
}

Each document includes various query plan and execution stats, including:

Field
Description
version

A number that indicates the query engine used to complete the query.

planCacheShapeHash

A hexadecimal string that represents the hash of the query shape. See explain.queryPlanner.planCacheShapeHash.

Starting in MongoDB 8.0, the pre-existing queryHash field is renamed to planCacheShapeHash. If you're using an earlier MongoDB version, you'll see queryHash instead of planCacheShapeHash.

planCacheKey

A hexadecimal string that represents the hash of the key used to find the plan cache entry associated with this query. The plan cache key is a function of both the plan cache query shape and the currently available indexes for that shape. See explain.queryPlanner.planCacheKey.

isActive

A boolean that indicates whether the entry is active or inactive.

  • If active, the query planner is currently using the entry to generate query plans.

  • If inactive, the query planner is not currently using the entry to generate query plans.

See Plan Cache Entry State.

works

The number of "work units" performed by the query execution plan during the trial period when the query planner evaluates candidate plans. For more information, see explain.executionStats.executionStages.works.

cachedPlan

The details of the cached plan. The fields included in the cachedPlan vary based on whether the query was completed using the classic engine or the slot-based query execution engine. For more information on query plans, see explain.queryPlanner.

indexFilterSet

A boolean that indicates whether an index filter exists for the plan cache query shape.

estimatedSizeBytes

The estimated size in bytes of a plan cache entry.

querySettings

New in version 8.0.

A document that contains query settings previously set using setQuerySettings:

querySettings: {
   indexHints: [ {
      ns: { db: <string>, coll: <string> },
      allowedIndexes: <array>
   }, ... ],
   queryFramework: <string>
}

querySettings fields:

Field
Type
Description
indexHints.ns
document

Namespace for index hints.

db
string
Name of the database for index hints.
coll
string
Name of the collection for index hints.
indexHints.allowedIndexes
array
Array of indexes for index hints. For more details, see Indexes and hint().
queryFramework
string

Query framework string can be:

host

The hostname and port of the mongod instance from which the plan cache information was returned.

When run on a sharded cluster, the operation returns plan cache entry information from a single member in each shard replica set. This member is identified with the shard and host fields. See also Redaction.

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',
    planCacheShapeHash: '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',
       planCacheShapeHash: '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',
       planCacheShapeHash: '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',
       planCacheShapeHash: '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'
     }
   ],

Warning

Starting in MongoDB 8.0, the pre-existing queryHash field is renamed to planCacheShapeHash. If you're using an earlier MongoDB version, you'll see queryHash instead of planCacheShapeHash.

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',
    planCacheShapeHash: '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'
  }
]

Warning

Starting in MongoDB 8.0, the pre-existing queryHash field is renamed to planCacheShapeHash. If you're using an earlier MongoDB version, you'll see queryHash instead of planCacheShapeHash.

See also planCacheKey and planCacheShapeHash.

Back

$out