$planCacheStats

Definition

  • $planCacheStats

New in version 4.2.

Returns plan cache information for acollection. The stage returns a document for each plan cache entry.

The $planCacheStats stage must be the first stage in thepipeline. The stage takes an empty document as a parameter and hasthe following syntax:

  1. { $planCacheStats: { } }

Note

The $planCacheStats aggregation stage is preferred overthe 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 aggregationpipeline.

Restrictions

Access Control

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

Output

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

  1. {
  2.    "createdFromQuery" : <document>,
  3.    "queryHash" : <hexadecimal string>,
  4.    "planCacheKey" : <hexadecimal string>,
  5.    "isActive" :  <boolean>,
  6.    "works" : <NumberLong>,
  7.    "cachedPlan" : {
  8.       "stage" : <STAGE1>,
  9.       "filter" : <document>,
  10.       "inputStage" : {
  11.          "stage" : <STAGE2>,
  12.          ...
  13.       }
  14.    },
  15.    "timeOfCreation" : <date>,
  16.    "creationExecStats" : [   // Exec Stats Document for each candidate plan
  17.       {
  18.          "nReturned" : <num>,
  19.          "executionTimeMillisEstimate" : <num>
  20.          "totalKeysExamined" : <num>
  21.          "totalDocsExamined" :<num>
  22.          "executionStages" : {
  23.             "stage" : <STAGE A>,
  24.             ...
  25.             "inputStage" : {
  26.                "stage" : <STAGE B>,
  27.                ...
  28.             }
  29.          }
  30.       },
  31.       ...
  32.    ],
  33.    "candidatePlanScores" : [
  34.       <number>,
  35.       ...
  36.    ],
  37.    "indexFilterSet" : <boolean>
  38. }

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

FieldDescription
createdFromQueryA document that contains the specific query that resulted inthis cache entry; i.e.
  1. {  "query" : <document>,  "sort" : <document>,  "projection" : <document>}
isActiveA boolean that indicates whether the entry is active or inactive.- If active, the query planner is currently using the entry togenerate query plans.- If inactive, the query planner is not currently using theentry to generate query plans.See alsoPlan Cache Entry State
queryHashA hexadecimal string that represents the hash of the queryshape. For more information, seeexplain.queryPlanner.queryHash
planCacheKeyA hexadecimal string that represents the hash of the key used tofind the plan cache entry associated with this query. The plancache key is a function of both the query shape and thecurrently available indexes for that shape. For more information, seeexplain.queryPlanner.planCacheKey
cachedPlanThe details of the cached plan. See explain.queryPlanner.
worksThe number of “work units” performed by the query execution planduring the trial period when the query planner evaluatescandidate plans. For more information, seeexplain.executionStats.executionStages.works
timeOfCreationTime of creation for the entry.
creationExecStatsAn array of execution stats documents. The array contains adocument for each candidate plan.For details on the execution stats, seeexplain.executionStats.
candidatePlanScoresAn array of scores for the candidate plans listed in thecreationExecStats array.
indexFilterSetA boolean that indicates whether the an index filter exists for the query shape.

Examples

The examples in this section use the following orders collection:

  1. db.orders.insert([
  2.    { "_id" : 1, "item" : "abc", "price" : NumberDecimal("12"), "quantity" : 2, "type": "apparel" },
  3.    { "_id" : 2, "item" : "jkl", "price" : NumberDecimal("20"), "quantity" : 1, "type": "electronics" },
  4.    { "_id" : 3, "item" : "abc", "price" : NumberDecimal("10"), "quantity" : 5, "type": "apparel" },
  5.    { "_id" : 4, "item" : "abc", "price" : NumberDecimal("8"), "quantity" : 10, "type": "apparel" },
  6.    { "_id" : 5, "item" : "jkl", "price" : NumberDecimal("15"), "quantity" : 15, "type": "electronics" }
  7. ])

Create the following indexes on the collection:

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

Note

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

Run some queries against the collection:

  1. db.orders.find( { item: "abc", price: { $gte: NumberDecimal("10") } } )
  2. db.orders.find( { item: "abc", price: { $gte: NumberDecimal("5") } } )
  3. db.orders.find( { quantity: { $gte: 20 } } )
  4. db.orders.find( { quantity: { $gte: 5 }, type: "apparel" } )

Return Information for All Entries in the Query Cache

The following aggregation pipeline uses $planCacheStats toreturn information on the plan cache entries for the collection:

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

The operation returns all entries in the cache:

  1. {                                               // Plan Cache Entry 1
  2.    "createdFromQuery" : {
  3.       "query" : { "quantity" : { "$gte" : 5 }, "type" : "apparel" },
  4.       "sort" : { },
  5.       "projection" : { }
  6.    },
  7.    "queryHash" : "4D151C4C",
  8.    "planCacheKey" : "DD67E353",
  9.    "isActive" : true,
  10.    "works" : NumberLong(4),
  11.    "cachedPlan" : {
  12.       ...
  13.    },
  14.    "timeOfCreation" : ISODate("2019-02-04T20:30:10.414Z"),
  15.    "creationExecStats" : [
  16.       {
  17.          ... // Exec Stats for Candidate 1
  18.       },
  19.       {
  20.          ... // Exec Stats for Candidate 2
  21.       }
  22.    ],
  23.    "candidatePlanScores" : [
  24.       1.5003000000000002,
  25.       1.5003000000000002
  26.    ],
  27.    "indexFilterSet" : false
  28. }
  29. {                                               // Plan Cache Entry 2
  30.    "createdFromQuery" : {
  31.       "query" : { "quantity" : { "$gte" : 20 } },
  32.       "sort" : { },
  33.       "projection" : { }
  34.    },
  35.    "queryHash" : "23B19B75",
  36.    "planCacheKey" : "6F23F858",
  37.    "isActive" : true,
  38.    "works" : NumberLong(1),
  39.    "cachedPlan" : {
  40.       ...
  41.    },
  42.    "timeOfCreation" : ISODate("2019-02-04T20:30:10.412Z"),
  43.    "creationExecStats" : [
  44.       {
  45.          ... // Exec Stats for Candidate 1
  46.       },
  47.       {
  48.          ... // Exec Stats for Candidate 2
  49.       }
  50.    ],
  51.    "candidatePlanScores" : [
  52.       1.0003000000000002,
  53.       1.0003000000000002
  54.    ],
  55.    "indexFilterSet" : false
  56. }
  57. {                                               // Plan Cache Entry 3
  58.    "createdFromQuery" : {
  59.       "query" : { "item" : "abc", "price" : { "$gte" : NumberDecimal("5") } },
  60.       "sort" : { },
  61.       "projection" : { }
  62.    },
  63.    "queryHash" : "117A6B10",
  64.    "planCacheKey" : "A1824628",
  65.    "isActive" : true,
  66.    "works" : NumberLong(4),
  67.    "cachedPlan" : {
  68.       ...
  69.    },
  70.    "timeOfCreation" : ISODate("2019-02-04T20:30:10.410Z"),
  71.    "creationExecStats" : [
  72.       {
  73.          ... // Exec Stats for Candidate 1
  74.       },
  75.       {
  76.          ... // Exec Stats for Candidate 2
  77.       }
  78.    ],
  79.    "candidatePlanScores" : [
  80.       1.7503000000000002,
  81.       1.7503000000000002
  82.    ],
  83.    "indexFilterSet" : false
  84. }
  85. {                                               // Plan Cache Entry 4
  86.    "createdFromQuery" : {
  87.       "query" : { "item" : "abc", "price" : { "$gte" : NumberDecimal("10") } },
  88.       "sort" : { },
  89.       "projection" : { }
  90.    },
  91.    "queryHash" : "117A6B10",
  92.    "planCacheKey" : "2E6E536B",
  93.    "isActive" : true,
  94.    "works" : NumberLong(3),
  95.    "cachedPlan" : {
  96.       ...
  97.    },
  98.    "timeOfCreation" : ISODate("2019-02-04T20:30:10.408Z"),
  99.    "creationExecStats" : [
  100.       {
  101.          ... // Exec Stats for Candidate 1
  102.       },
  103.       {
  104.          ... // Exec Stats for Candidate 2
  105.       },
  106.       {
  107.          ... // Exec Stats for Candidate 3
  108.       }
  109.    ],
  110.    "candidatePlanScores" : [
  111.       1.6669666666666663,
  112.       1.6669666666666665,
  113.       1.6669666666666665
  114.    ],
  115.    "indexFilterSet" : false
  116. }

See also planCacheKey.

List Query Shapes

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

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

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

The operation returns the following query shapes:

  1. { "createdFromQuery" : { "query" : { "item" : "abc", "price" : { "$gte" : NumberDecimal("5") } }, "sort" : {  }, "projection" : {  } }, "queryHash" : "117A6B10" }
  2. { "createdFromQuery" : { "query" : { "quantity" : { "$gte" : 5 }, "type" : "apparel" }, "sort" : {  }, "projection" : {  } }, "queryHash" : "4D151C4C" }
  3. { "createdFromQuery" : { "query" : { "quantity" : { "$gte" : 20 } }, "sort" : {  }, "projection" : {  } }, "queryHash" : "23B19B75" }
  4. { "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 $planCacheStatsfollowed by a $match and $project to returnspecific information for a particular query shape:

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

The operation returns the following:

  1. {
  2.    "createdFromQuery" : {
  3.       "query" : { "quantity" : { "$gte" : 5 }, "type" : "apparel" },
  4.       "sort" : { },
  5.       "projection" : { }
  6.    },
  7.    "queryHash" : "4D151C4C",
  8.    "planCacheKey" : "DD67E353",
  9.    "isActive" : false,
  10.    "works" : NumberLong(4),
  11.    "cachedPlan" : {
  12.       "stage" : "FETCH",
  13.       "inputStage" : {
  14.          "stage" : "IXSCAN",
  15.          "keyPattern" : {
  16.             "quantity" : 1,
  17.             "type" : 1
  18.          },
  19.          "indexName" : "quantity_1_type_1",
  20.          "isMultiKey" : false,
  21.          "multiKeyPaths" : {
  22.             "quantity" : [ ],
  23.             "type" : [ ]
  24.          },
  25.          "isUnique" : false,
  26.          "isSparse" : false,
  27.          "isPartial" : false,
  28.          "indexVersion" : 2,
  29.          "direction" : "forward",
  30.          "indexBounds" : {
  31.             "quantity" : [
  32.                "[5.0, inf.0]"
  33.             ],
  34.             "type" : [
  35.                "[\"apparel\", \"apparel\"]"
  36.             ]
  37.          }
  38.       }
  39.    },
  40.    "timeOfCreation" : ISODate("2019-07-10T21:39:04.580Z"),
  41.    "creationExecStats" : [
  42.       {
  43.          "nReturned" : 2,
  44.          "executionTimeMillisEstimate" : 0,
  45.          "totalKeysExamined" : 3,
  46.          "totalDocsExamined" : 2,
  47.          "executionStages" : {
  48.             "stage" : "FETCH",
  49.             "nReturned" : 2,
  50.             "executionTimeMillisEstimate" : 0,
  51.             "works" : 4,
  52.             "advanced" : 2,
  53.             "needTime" : 1,
  54.             "needYield" : 0,
  55.             "saveState" : 0,
  56.             "restoreState" : 0,
  57.             "isEOF" : 1,
  58.             "docsExamined" : 2,
  59.             "alreadyHasObj" : 0,
  60.             "inputStage" : {
  61.                "stage" : "IXSCAN",
  62.                "nReturned" : 2,
  63.                "executionTimeMillisEstimate" : 0,
  64.                "works" : 4,
  65.                "advanced" : 2,
  66.                "needTime" : 1,
  67.                "needYield" : 0,
  68.                "saveState" : 0,
  69.                "restoreState" : 0,
  70.                "isEOF" : 1,
  71.                "keyPattern" : {
  72.                   "quantity" : 1,
  73.                   "type" : 1
  74.                },
  75.                "indexName" : "quantity_1_type_1",
  76.                "isMultiKey" : false,
  77.                "multiKeyPaths" : {
  78.                   "quantity" : [ ],
  79.                   "type" : [ ]
  80.                },
  81.                "isUnique" : false,
  82.                "isSparse" : false,
  83.                "isPartial" : false,
  84.                "indexVersion" : 2,
  85.                "direction" : "forward",
  86.                "indexBounds" : {
  87.                   "quantity" : [
  88.                      "[5.0, inf.0]"
  89.                   ],
  90.                   "type" : [
  91.                      "[\"apparel\", \"apparel\"]"
  92.                   ]
  93.                },
  94.                "keysExamined" : 3,
  95.                "seeks" : 2,
  96.                "dupsTested" : 0,
  97.                "dupsDropped" : 0
  98.             }
  99.          }
  100.       },
  101.       {
  102.          "nReturned" : 2,
  103.          "executionTimeMillisEstimate" : 0,
  104.          "totalKeysExamined" : 3,
  105.          "totalDocsExamined" : 3,
  106.          "executionStages" : {
  107.             "stage" : "FETCH",
  108.             "filter" : {
  109.                "type" : {
  110.                   "$eq" : "apparel"
  111.                }
  112.             },
  113.             "nReturned" : 2,
  114.             "executionTimeMillisEstimate" : 0,
  115.             "works" : 4,
  116.             "advanced" : 2,
  117.             "needTime" : 1,
  118.             "needYield" : 0,
  119.             "saveState" : 0,
  120.             "restoreState" : 0,
  121.             "isEOF" : 1,
  122.             "docsExamined" : 3,
  123.             "alreadyHasObj" : 0,
  124.             "inputStage" : {
  125.                "stage" : "IXSCAN",
  126.                "nReturned" : 3,
  127.                "executionTimeMillisEstimate" : 0,
  128.                "works" : 4,
  129.                "advanced" : 3,
  130.                "needTime" : 0,
  131.                "needYield" : 0,
  132.                "saveState" : 0,
  133.                "restoreState" : 0,
  134.                "isEOF" : 1,
  135.                "keyPattern" : {
  136.                   "quantity" : 1
  137.                },
  138.                "indexName" : "quantity_1",
  139.                "isMultiKey" : false,
  140.                "multiKeyPaths" : {
  141.                   "quantity" : [ ]
  142.                },
  143.                "isUnique" : false,
  144.                "isSparse" : false,
  145.                "isPartial" : false,
  146.                "indexVersion" : 2,
  147.                "direction" : "forward",
  148.                "indexBounds" : {
  149.                   "quantity" : [
  150.                      "[5.0, inf.0]"
  151.                   ]
  152.                },
  153.                "keysExamined" : 3,
  154.                "seeks" : 1,
  155.                "dupsTested" : 0,
  156.                "dupsDropped" : 0
  157.             }
  158.          }
  159.       }
  160.    ],
  161.    "candidatePlanScores" : [
  162.       1.5003000000000002,
  163.       1.5003000000000002
  164.    ],
  165.    "indexFilterSet" : false
  166. }

See also planCacheKey and queryHash.