$planCacheStats does not return all the information expected

Other MongoDB Topics
1

Hi all,
I am trying to analyze the query execution plan to drill down into the use of some indexes but using the $planCacheStats command I am not getting all the information I would expect.

I am using a server with MongoDb 4.2 and running the following command

db.getCollection("test").aggregate( [
   { $planCacheStats: { } }
] )

with the database root user, I get this information:

[
{
    "queryHash" : "60879Z27",
    "planCacheKey" : "E72AZZA6",
    "isActive" : true,
    "works" : 12,
    "timeOfCreation" : ISODate("2023-04-21T15:27:07.473+0000"),
    "indexFilterSet" : false,
    "estimatedSizeBytes" : 70964
}
{
    "queryHash" : "F70XYDE4",
    "planCacheKey" : "C98ZZ425",
    "isActive" : true,
    "works" : 2,
    "timeOfCreation" : ISODate("2023-04-21T14:32:39.608+0000"),
    "indexFilterSet" : false,
    "estimatedSizeBytes" : 60312
}
{
    "queryHash" : "0XY01C75",
    "planCacheKey" : "7232ZZ56",
    "isActive" : false,
    "works" : 14,
    "timeOfCreation" : ISODate("2023-05-17T07:13:12.752+0000"),
    "indexFilterSet" : false,
    "estimatedSizeBytes" : 61997
}
...
]

The values createdFromQuery, cachedPlan, … are not returned as shown in documentation https://www.mongodb.com/docs/manual/reference/operator/aggregation/planCacheStats/#output

Does anyone know the reason for this behavior?

Thank you in advance

A

2

Hi @Andrea_Cervellin - great question. My name is Chris, I’m a Senior Product Manager here at MongoDB and I think I can help out.

The additional fields that you mention can sometimes get pretty verbose. Therefore the database will stop capturing them after a certain point. You can check on the memory usage of the plan cache by using the following command in the shell:

db.serverStatus().metrics.query.planCacheTotalSizeEstimateBytes

While you can increase the memory limit for the plan cache in order to capture more information, that wouldn’t necessarily be the first action I would advise taking. Instead I’d like to find out what your purpose for inspecting the plan cache is. Are you trying to diagnose a performance issue? Any additional context that you can provide may allow us to give some recommendations on how to achieve the desired outcome.

Best,
Chris