Navigation
This is an upcoming (i.e. in progress) version of the manual.

$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

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

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.

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

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

Examples

The examples in this section use the following orders collection:

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:

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:

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:

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

The operation returns all entries in the cache:

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

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.

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:

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
}

See also planCacheKey and queryHash.