Docs Menu
Docs Home
/
Database Manual
/
Reference
/
Operators
/
Aggregation Pipeline Stages

$planCacheStats

Definition

$planCacheStats

New in version 4.2.

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

Note

4.4 Changes

Starting in version 4.4,

Tip

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.

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

For each plan cache entry, the $planCacheStats stage returns a document similar to the following:

{
   "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>, // Available starting in MongoDB 5.0, 4.4.3, 4.2.12
   "host" : <string>,   // Available starting in MongoDB 4.4
   "shard" : <string>   // Available starting in MongoDB 4.4 if run on sharded cluster
}

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

Field
Description

createdFromQuery

A document that contains the specific query that resulted in this cache entry; i.e.

{
  "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.

Tip

Plan Cache Entry State

queryHash

A hexadecimal string that represents the hash of the query shape. For more information, 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. For more information, see explain.queryPlanner.planCacheKey

cachedPlan

The details of the cached plan. 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 the an index filter exists for the query shape.

estimatedSizeBytes

A number that describes the estimated size in bytes of a plan cache entry.

New in version 5.0.

Starting in MongoDB 5.0, 4.4.3, and 4.2.12, this field is available.

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 Read Preference.

New in version 4.4.

shard

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

Only available if run on a sharded cluster.

New in version 4.4.

Examples

The examples in this section use the following orders collection:

db.orders.insertMany( [
    { "_id" : 1, "item" : "abc", "price" : Decimal128("12"), "quantity" : 2, "type": "apparel" },
    { "_id" : 2, "item" : "jkl", "price" : Decimal128("20"), "quantity" : 1, "type": "electronics" },
    { "_id" : 3, "item" : "abc", "price" : Decimal128("10"), "quantity" : 5, "type": "apparel" },
    { "_id" : 4, "item" : "abc", "price" : Decimal128("8"), "quantity" : 10, "type": "apparel" },
    { "_id" : 5, "item" : "jkl", "price" : Decimal128("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: Decimal128("10")} } }
);

Note

Index { item: 1, price: 1 } is a partial index and only indexes documents with price field greater than or equal to Decimal128("10").

Run some queries against the collection:

db.orders.find( { item: "abc", price: { $gte: Decimal128("10") } } )
db.orders.find( { item: "abc", price: { $gte: Decimal128("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 about 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'
      }
    ]

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'
  }
]

See also planCacheKey and queryHash.

To use the MongoDB Node.js driver to add a $planCacheStats stage to an aggregation pipeline, use the $planCacheStats operator in a pipeline object.

Return Information for All Entries in the Query Cache

The following example creates a pipeline stage that returns information about the plan cache entries for the collection. The example then runs the aggregation pipeline:

const pipeline = [{ $planCacheStats: {} }];
const cursor = collection.aggregate(pipeline);
return cursor;

Find Cache Entry Details for a Query Hash

To return plan cache information for a particular query hash, include a $match stage that checks for a specific query hash in the planCacheKey field.

The following example creates a pipeline that returns information for a query hash value of "B1435201". The example then runs the aggregation pipeline:

const pipeline = [
 $planCacheStats: {} },
  { $match: { planCacheKey: "B1435201"} }
];
const cursor = collection.aggregate(pipeline);
return cursor;

Back

$out

Next

$project

On this page