• Reference >
• Operators >
• Aggregation Pipeline Stages >
• $planCacheStats

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

copy

{ $planCacheStats: { } }

Note

The $planCacheStats aggregation stage is preferred over the following methods and commands, which have been deprecated in 4.2:

• PlanCache.getPlansByQuery() method/planCacheListPlans command, and
• PlanCache.listQueryShapes() method/planCacheListQueryShapes command.

See also

Query Plans

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.
• $planCacheStats cannot be run on mongos instances.

Access Control

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

Output

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

copy

{
   "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 4.2.12, 4.0.23, 3.6.23
}

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. See also 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 4.2.12: (and 4.0.23 and 3.6.23)

Examples

The examples in this section use the following orders collection:

copy

db.orders.insert([
   { "_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:

copy

db.orders.createIndex( { item: 1 } );
db.orders.createIndex( { item: 1, quantity: 1 } );
db.orders.createIndex( { item: 1, price: 1 }, { partialFilterExpression: { price: { $gte: NumberDecimal("10")} } } );
db.orders.createIndex( { quantity: 1 } );
db.orders.createIndex( { quantity: 1, type: 1 } );

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:

copy

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" } )

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:

copy

db.orders.aggregate( [
   { $planCacheStats: { } }
] )

The operation returns all entries in the cache:

copy

{                                               // Plan Cache Entry 1
   "createdFromQuery" : {
      "query" : { "quantity" : { "$gte" : 5 }, "type" : "apparel" },
      "sort" : { },
      "projection" : { }
   },
   "queryHash" : "4D151C4C",
   "planCacheKey" : "DD67E353",
   "isActive" : true,
   "works" : NumberLong(4),
   "cachedPlan" : {
      ...
   },
   "timeOfCreation" : ISODate("2019-02-04T20:30:10.414Z"),
   "creationExecStats" : [
      {
         ... // Exec Stats for Candidate 1
      },
      {
         ... // Exec Stats for Candidate 2
      }
   ],
   "candidatePlanScores" : [
      1.5003000000000002,
      1.5003000000000002
   ],
   "indexFilterSet" : false,
   "estimatedSizeBytes" : NumberLong(3160)      // Available starting in MongoDB 4.2.12, 4.0.23, 3.6.23
}
{                                               // Plan Cache Entry 2
   "createdFromQuery" : {
      "query" : { "quantity" : { "$gte" : 20 } },
      "sort" : { },
      "projection" : { }
   },
   "queryHash" : "23B19B75",
   "planCacheKey" : "6F23F858",
   "isActive" : true,
   "works" : NumberLong(1),
   "cachedPlan" : {
      ...
   },
   "timeOfCreation" : ISODate("2019-02-04T20:30:10.412Z"),
   "creationExecStats" : [
      {
         ... // Exec Stats for Candidate 1
      },
      {
         ... // Exec Stats for Candidate 2
      }
   ],
   "candidatePlanScores" : [
      1.0003000000000002,
      1.0003000000000002
   ],
   "indexFilterSet" : false,
   "estimatedSizeBytes" : NumberLong(2539)      // Available starting in MongoDB 4.2.12, 4.0.23, 3.6.23
}
{                                               // Plan Cache Entry 3
   "createdFromQuery" : {
      "query" : { "item" : "abc", "price" : { "$gte" : NumberDecimal("5") } },
      "sort" : { },
      "projection" : { }
   },
   "queryHash" : "117A6B10",
   "planCacheKey" : "A1824628",
   "isActive" : true,
   "works" : NumberLong(4),
   "cachedPlan" : {
      ...
   },
   "timeOfCreation" : ISODate("2019-02-04T20:30:10.410Z"),
   "creationExecStats" : [
      {
         ... // Exec Stats for Candidate 1
      },
      {
         ... // Exec Stats for Candidate 2
      }
   ],
   "candidatePlanScores" : [
      1.7503000000000002,
      1.7503000000000002
   ],
   "indexFilterSet" : false,
   "estimatedSizeBytes" : NumberLong(3183)      // Available starting in MongoDB 4.2.12, 4.0.23, 3.6.23
}
{                                               // Plan Cache Entry 4
   "createdFromQuery" : {
      "query" : { "item" : "abc", "price" : { "$gte" : NumberDecimal("10") } },
      "sort" : { },
      "projection" : { }
   },
   "queryHash" : "117A6B10",
   "planCacheKey" : "2E6E536B",
   "isActive" : true,
   "works" : NumberLong(3),
   "cachedPlan" : {
      ...
   },
   "timeOfCreation" : ISODate("2019-02-04T20:30:10.408Z"),
   "creationExecStats" : [
      {
         ... // Exec Stats for Candidate 1
      },
      {
         ... // Exec Stats for Candidate 2
      },
      {
         ... // Exec Stats for Candidate 3
      }
   ],
   "candidatePlanScores" : [
      1.6669666666666663,
      1.6669666666666665,
      1.6669666666666665
   ],
   "indexFilterSet" : false,
   "estimatedSizeBytes" : NumberLong(4653)     // Available starting in MongoDB 4.2.12, 4.0.23, 3.6.23
}

See also planCacheKey.

List Query Shapes

Like the planCacheListQueryShapes command, the $planCacheStats stage can be used to obtain a list of all of the query shapes for which there is a cached plan. The $planCacheStats aggregation stage is preferred over the deprecated PlanCache.listQueryShapes() method and the deprecated planCacheListQueryShapes command.

For example, the following uses the $project stage to only output the createdFromQuery field and the queryHash field.

copy

db.orders.aggregate( [ { $planCacheStats: { } } , { $project: {createdFromQuery: 1, queryHash: 1 } } ] )

The operation returns the following query shapes:

{ "createdFromQuery" : { "query" : { "item" : "abc", "price" : { "$gte" : NumberDecimal("5") } }, "sort" : {  }, "projection" : {  } }, "queryHash" : "117A6B10" }
{ "createdFromQuery" : { "query" : { "quantity" : { "$gte" : 5 }, "type" : "apparel" }, "sort" : {  }, "projection" : {  } }, "queryHash" : "4D151C4C" }
{ "createdFromQuery" : { "query" : { "quantity" : { "$gte" : 20 } }, "sort" : {  }, "projection" : {  } }, "queryHash" : "23B19B75" }
{ "createdFromQuery" : { "query" : { "item" : "abc", "price" : { "$gte" : NumberDecimal("10") } }, "sort" : {  }, "projection" : {  } }, "queryHash" : "117A6B10" }

Find Cache Entry Details for a Query Shape

To return plan cache information for a particular query shape, the $planCacheStats stage can be followed by a $match on the planCacheKey field.

The following aggregation pipeline uses $planCacheStats followed by a $match and $project to return specific information for a particular query shape:

copy

db.orders.aggregate( [
   { $planCacheStats: { } },
   { $match: { planCacheKey: "DD67E353"} }
] )

The operation returns the following:

{
   "createdFromQuery" : {
      "query" : { "quantity" : { "$gte" : 5 }, "type" : "apparel" },
      "sort" : { },
      "projection" : { }
   },
   "queryHash" : "4D151C4C",
   "planCacheKey" : "DD67E353",
   "isActive" : false,
   "works" : NumberLong(4),
   "cachedPlan" : {
      "stage" : "FETCH",
      "inputStage" : {
         "stage" : "IXSCAN",
         "keyPattern" : {
            "quantity" : 1,
            "type" : 1
         },
         "indexName" : "quantity_1_type_1",
         "isMultiKey" : false,
         "multiKeyPaths" : {
            "quantity" : [ ],
            "type" : [ ]
         },
         "isUnique" : false,
         "isSparse" : false,
         "isPartial" : false,
         "indexVersion" : 2,
         "direction" : "forward",
         "indexBounds" : {
            "quantity" : [
               "[5.0, inf.0]"
            ],
            "type" : [
               "[\"apparel\", \"apparel\"]"
            ]
         }
      }
   },
   "timeOfCreation" : ISODate("2019-07-10T21:39:04.580Z"),
   "creationExecStats" : [
      {
         "nReturned" : 2,
         "executionTimeMillisEstimate" : 0,
         "totalKeysExamined" : 3,
         "totalDocsExamined" : 2,
         "executionStages" : {
            "stage" : "FETCH",
            "nReturned" : 2,
            "executionTimeMillisEstimate" : 0,
            "works" : 4,
            "advanced" : 2,
            "needTime" : 1,
            "needYield" : 0,
            "saveState" : 0,
            "restoreState" : 0,
            "isEOF" : 1,
            "docsExamined" : 2,
            "alreadyHasObj" : 0,
            "inputStage" : {
               "stage" : "IXSCAN",
               "nReturned" : 2,
               "executionTimeMillisEstimate" : 0,
               "works" : 4,
               "advanced" : 2,
               "needTime" : 1,
               "needYield" : 0,
               "saveState" : 0,
               "restoreState" : 0,
               "isEOF" : 1,
               "keyPattern" : {
                  "quantity" : 1,
                  "type" : 1
               },
               "indexName" : "quantity_1_type_1",
               "isMultiKey" : false,
               "multiKeyPaths" : {
                  "quantity" : [ ],
                  "type" : [ ]
               },
               "isUnique" : false,
               "isSparse" : false,
               "isPartial" : false,
               "indexVersion" : 2,
               "direction" : "forward",
               "indexBounds" : {
                  "quantity" : [
                     "[5.0, inf.0]"
                  ],
                  "type" : [
                     "[\"apparel\", \"apparel\"]"
                  ]
               },
               "keysExamined" : 3,
               "seeks" : 2,
               "dupsTested" : 0,
               "dupsDropped" : 0
            }
         }
      },
      {
         "nReturned" : 2,
         "executionTimeMillisEstimate" : 0,
         "totalKeysExamined" : 3,
         "totalDocsExamined" : 3,
         "executionStages" : {
            "stage" : "FETCH",
            "filter" : {
               "type" : {
                  "$eq" : "apparel"
               }
            },
            "nReturned" : 2,
            "executionTimeMillisEstimate" : 0,
            "works" : 4,
            "advanced" : 2,
            "needTime" : 1,
            "needYield" : 0,
            "saveState" : 0,
            "restoreState" : 0,
            "isEOF" : 1,
            "docsExamined" : 3,
            "alreadyHasObj" : 0,
            "inputStage" : {
               "stage" : "IXSCAN",
               "nReturned" : 3,
               "executionTimeMillisEstimate" : 0,
               "works" : 4,
               "advanced" : 3,
               "needTime" : 0,
               "needYield" : 0,
               "saveState" : 0,
               "restoreState" : 0,
               "isEOF" : 1,
               "keyPattern" : {
                  "quantity" : 1
               },
               "indexName" : "quantity_1",
               "isMultiKey" : false,
               "multiKeyPaths" : {
                  "quantity" : [ ]
               },
               "isUnique" : false,
               "isSparse" : false,
               "isPartial" : false,
               "indexVersion" : 2,
               "direction" : "forward",
               "indexBounds" : {
                  "quantity" : [
                     "[5.0, inf.0]"
                  ]
               },
               "keysExamined" : 3,
               "seeks" : 1,
               "dupsTested" : 0,
               "dupsDropped" : 0
            }
         }
      }
   ],
   "candidatePlanScores" : [
      1.5003000000000002,
      1.5003000000000002
   ],
   "indexFilterSet" : false,
   "estimatedSizeBytes" : NumberLong(3160)      // Available starting in MongoDB 4.2.12, 4.0.23, 3.6.23
}

See also planCacheKey and queryHash.

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

© MongoDB, Inc 2008-present. MongoDB, Mongo, and the leaf logo are registered trademarks of MongoDB, Inc.