$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

Considerations

Pipeline

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

Restrictions

$planCacheStats is not allowed in:
- transactions
- $facet aggregation stage
$planCacheStats requires read concern level "local".

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:

1 indicates that the classic engine was used.
2 indicates that the slot-based query execution engine was used.

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

{
   "version" : 1,
   "createdFromQuery" : <document>,
   "queryHash" : <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>,
   "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.

1 indicates that the classic engine was used.
2 indicates that the slot-based query execution engine was used.

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.

queryHash

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

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 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 query shape.

estimatedSizeBytes

The estimated size in bytes of a plan cache entry.

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,
   "queryHash" : <hexadecimal string>,
   "planCacheKey" : <hexadecimal string>,
   "isActive" :  <boolean>,
   "works" : <NumberLong>,
   "cachedPlan" : {
      "slots" : <string>,
      "stages": <string>
   },
   "indexFilterSet" : <boolean>,
   "estimatedSizeBytes" : <num>,
   "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. `1` indicates that the classic engine was used. `2` indicates that the slot-based query execution engine was used.
`queryHash`	A hexadecimal string that represents the hash of the query shape. See `explain.queryPlanner.queryHash`.
`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 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 query shape.
`estimatedSizeBytes`	The estimated size in bytes of a plan cache entry.
`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',
    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'
     }
   ],

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.

Back

$out

$project

Definition

Note

Tip

See also:

Considerations

Pipeline

Restrictions

Access Control

Redaction

Read Preference

Output

Examples

Note

Return Information for All Entries in the Query Cache

Find Cache Entry Details for a Query Hash