Database Manual / Reference / mongosh Methods / Query Plan Caches

PlanCache.list() (mongosh method方法)

Definition定义

PlanCache.list(<pipeline>)

Returns an array of plan cache entries for a collection.返回集合的计划缓存条目数组。

The method is only available from the plan cache object of a specific collection; i.e.该方法仅在特定集合的计划缓存对象中可用；即。

db.collection.getPlanCache().list(<pipeline>)
Parameter参数Type类型Description描述
pipelineArray数组

Optional. 可选。Aggregation pipeline to filter/process the plan cache query shapes.用于筛选/处理计划缓存查询形状的聚合管道。

You can run this method from a mongod or mongos instance. 您可以从mongod或mongos实例运行此方法。When run on a sharded cluster, the method 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.另请参见读取首选项。

The method PlanCache.list() wraps the $planCacheStats aggregation pipeline. That is,PlanCache.list()方法封装了$planCacheStats聚合管道。也就是说，

db.collection.getPlanCache().list([<stage1>, <stage2>, ...] )

is equivalent to等同于

db.collection.aggregate([ <$planCacheStats stage>, <stage1>, <stage2>, ... ]).toArray();

For details on the output, see $planCacheStats output.有关输出的详细信息，请参阅$planCacheStats输出。

Not all queries automatically place a query plan in the cache. 并非所有查询都会自动将查询计划放入缓存中。PlanCache.list() returns an empty array if there are currently no plan cache query shapes with cached query plans.并非所有查询都会自动将查询计划放入缓存中。如果当前没有具有缓存查询计划的计划缓存查询形状，PlanCache.list()将返回一个空数组。

Tip

Compatibility兼容性

This method is available in deployments hosted in the following environments:此方法在以下环境中托管的部署中可用：

  • MongoDB Atlas: The fully managed service for MongoDB deployments in the cloud：云中MongoDB部署的完全托管服务
  • MongoDB Enterprise: The subscription-based, self-managed version of MongoDB：MongoDB的基于订阅的自我管理版本
  • MongoDB Community: The source-available, free-to-use, and self-managed version of MongoDB：MongoDB的源代码可用、免费使用和自我管理版本

Considerations注意事项

Query Settings查询设置

Starting in MongoDB 8.0, use query settings instead of adding index filters. Index filters are deprecated starting in MongoDB 8.0.从MongoDB 8.0开始，使用查询设置，而不是添加索引筛选器。索引筛选器从MongoDB 8.0开始就被弃用。

Query settings have more functionality than index filters. Also, index filters aren't persistent and you cannot easily create index filters for all cluster nodes. 查询设置比索引筛选器具有更多功能。此外，索引筛选器不是持久的，您无法轻松为所有集群节点创建索引筛选器。To add query settings and explore examples, see setQuerySettings.要添加查询设置并探索示例，请参阅setQuerySettings。

Restrictions限制

PlanCache.list() is not allowed in transactions.在事务中不允许。

Query Hash and Plan Cache Query Shapes查询哈希和计划缓存查询形状

To help identify slow queries with the same plan cache query shape, each plan cache query shape is associated with a query hash. The plan cache query shape hash is a hexadecimal string that represents a hash of the query shape and is dependent only on the query shape.为了帮助识别具有相同计划缓存查询形状的慢速查询，每个计划缓存查询形都与一个查询哈希相关联。计划缓存查询形状哈希是一个十六进制字符串，表示查询形状的哈希，仅依赖于查询形状。

Note

As with any hash function, two different query shapes may result in the same hash value. However, the occurrence of hash collisions between different query shapes is unlikely.与任何哈希函数一样，两个不同的查询形状可能会产生相同的哈希值。然而，不同查询形状之间不太可能发生哈希冲突。

Starting in MongoDB 8.0, the existing queryHash field is duplicated in a new field named planCacheShapeHash. If you're using an earlier MongoDB version, you'll only see the queryHash field. 从MongoDB 8.0开始，现有的queryHash字段被复制到一个名为planCacheShapeHash的新字段中。如果你使用的是早期的MongoDB版本，你只会看到queryHash字段。Future MongoDB versions will remove the deprecated queryHash field, and you'll need to use the planCacheShapeHash field instead.未来的MongoDB版本将删除弃用的queryHash字段，您将需要使用planCacheShapeHash字段。

The query optimizer only caches the plan for a plan cache query shape that can have more than one viable plan.查询优化器只缓存可以有多个可行计划的计划缓存查询形状的计划。

Each entry in the plan cache is associated with a planCacheShapeHash.计划缓存中的每个条目都与一个planCacheShapeHash相关联。

Warning

Starting in MongoDB 8.0, the existing queryHash field is duplicated in a new field named planCacheShapeHash. 从MongoDB 8.0开始，现有的queryHash字段被复制到一个名为planCacheShapeHash的新字段中。If you're using an earlier MongoDB version, you'll only see the queryHash field. 如果你使用的是早期的MongoDB版本，你只会看到queryHash字段。Future MongoDB versions will remove the deprecated queryHash field, and you'll need to use the planCacheShapeHash field instead.未来的MongoDB版本将删除弃用的queryHash字段，您将需要使用planCacheShapeHash字段。

Read Preference读取首选项

PlanCache.list() 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 PlanCache.list() 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.然而，在副本集或分片集群上运行PlanCache.list()会遵守正常的读取首选项规则。也就是说，在副本集上，操作仅从副本集的一个成员集合计划缓存信息，而在分片集群上，操作只从每个分片副本集的某个成员集合规划缓存信息。也就是说，在副本集上，操作仅从副本集的一个成员集合计划缓存信息，而在分片集群上，操作只从每个分片副本集的某个成员集合规划缓存信息。

Required Access所需访问权限

On systems running with authorization, the user must have the planCacheRead privilege for the collection.在authorization运行的系统上，用户必须具有该集合的planCacheRead权限。

Examples示例

Note

  • Not all queries automatically place a query plan in the cache. PlanCache.list() returns an empty array if there are currently no query shapes with cached query plans.并非所有查询都会自动将查询计划放入缓存中。如果当前没有具有缓存查询计划的查询形状，PlanCache.list()将返回一个空数组。
  • For details on the output, see $planCacheStats output.有关输出的详细信息，请参阅$planCacheStats输出。

The examples in this section use the following orders collection:本节中的示例使用以下orders集合：

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( { item: 1, price: 1 }, { partialFilterExpression: { price: { $gte: Decimal128("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 Decimal128("10").索引{ item: 1, price: 1 }是部分索引，仅对price字段大于或等于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" } )

Return Information for All Entries in the Query Cache返回查询缓存中所有条目的信息

The following returns the plan cache query shapes that have cached plans for the orders collection:以下返回已缓存orders集合计划的计划缓存查询形状：

db.orders.getPlanCache().list()

The method returns an array of the plan cache query shapes currently in the cache. In the example, the orders collection had cached query plans associated with the following shapes:该方法返回当前缓存中的计划缓存查询形状的数组。在该示例中，orders集合缓存了与以下形状关联的查询计划：

[
   {
      "createdFromQuery" : {
         "query" : { "quantity" : { "$gte" : 5 }, "type" : "apparel" },
         "sort" : { },
         "projection" : { }
      },
      "planCacheShapeHash" : "4D151C4C",
      "planCacheKey" : "DD67E353",
      "isActive" : false,
      "works" : Long(4),
      "cachedPlan" : {
         "stage" : "FETCH",
         "filter" : { "type" : { "$eq" : "apparel" } },
         "inputStage" : {
            "stage" : "IXSCAN",
            "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]" ] }
         }
      },
      "timeOfCreation" : ISODate("2020-02-06T15:57:18.219Z"),
      "creationExecStats" : [
         {
            "nReturned" : 2,
            "executionTimeMillisEstimate" : 0,
            "totalKeysExamined" : 3,
            "totalDocsExamined" : 3,
            "executionStages" : {
               "stage" : "FETCH",
               "filter" : { "type" : { "$eq" : "apparel" } },
               "nReturned" : 2,
               "executionTimeMillisEstimate" : 0,
               "works" : 4,
               ...
            }
         },
         {
            "nReturned" : 2,
            "executionTimeMillisEstimate" : 0,
            "totalKeysExamined" : 3,
            "totalDocsExamined" : 2,
            "executionStages" : {
               "stage" : "FETCH",
               "nReturned" : 2,
               "executionTimeMillisEstimate" : 0,
               "works" : 4,
               ...
            }
         }
      ],
      "candidatePlanScores" : [
         1.5002,
         1.5002
      ],
      "indexFilterSet" : false,
      "estimatedSizeBytes" : Long(3160),   // Available starting in MongoDB 5.0
      "host" : "mongodb1.example.net:27018",
      "shard" : "shardA"                         // Available if run on sharded cluster
   },

   {
      "createdFromQuery" : {
         "query" : { "quantity" : { "$gte" : 20 } },
         "sort" : { },
         "projection" : { }
      },
      "planCacheShapeHash" : "23B19B75",
      "planCacheKey" : "6F23F858",
      "isActive" : false,
      "works" : Long(1),
      ...
   },
   {
      "createdFromQuery" : {
         "query" : { "item" : "abc", "price" : { "$gte" : Decimal128("5") } },
         "sort" : { },
         "projection" : { }
      },
      "planCacheShapeHash" : "117A6B10",
      "planCacheKey" : "A1824628",
      "isActive" : false,
      "works" : Long(4),
      ...
   },
   {
      "createdFromQuery" : {
         "query" : { "item" : "abc", "price" : { "$gte" : Decimal128("10") } },
         "sort" : { },
         "projection" : { }
      },
      "planCacheShapeHash" : "117A6B10",
      "planCacheKey" : "2E6E536B",
      "isActive" : false,
      "works" : Long(3),
      ...
   }
]

Warning

Starting in MongoDB 8.0, the existing queryHash field is duplicated in a new field named planCacheShapeHash. 从MongoDB 8.0开始，现有的queryHash字段被复制到一个名为planCacheShapeHash的新字段中。If you're using an earlier MongoDB version, you'll only see the queryHash field. 如果你使用的是早期的MongoDB版本，你只会看到queryHash字段。Future MongoDB versions will remove the deprecated queryHash field, and you'll need to use the planCacheShapeHash field instead.未来的MongoDB版本将删除弃用的queryHash字段，您将需要使用planCacheShapeHash字段。

For details on the output, see $planCacheStats output.有关输出的详细信息，请参阅$planCacheStats输出。

List Plan Cache Query Shapes列出计划缓存查询形状

To obtain a list of all of the query shapes for which there is a cached plan, you can use the PlanCache.list(). 要获取有缓存计划的所有查询形状的列表，可以使用PlanCache.list()。For example, the following operation passes in a pipeline with a $project stage to only output the createdFromQuery field and the planCacheShapeHash field.例如，以下操作在带有$project阶段的管道中传递，仅输出createdFromQuery字段和planCacheShapeHash字段。

db.orders.getPlanCache().list( [ { $project: {createdFromQuery: 1, planCacheShapeHash: 1 } } ] )

Warning

Starting in MongoDB 8.0, the existing queryHash field is duplicated in a new field named planCacheShapeHash. 从MongoDB 8.0开始，现有的queryHash字段被复制到一个名为planCacheShapeHash的新字段中。If you're using an earlier MongoDB version, you'll only see the queryHash field. 如果你使用的是早期的MongoDB版本，你只会看到queryHash字段。Future MongoDB versions will remove the deprecated queryHash field, and you'll need to use the planCacheShapeHash field instead.未来的MongoDB版本将删除弃用的queryHash字段，您将需要使用planCacheShapeHash字段。

The operation returns the following plan cache query shapes:该操作返回以下计划缓存查询形状：

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

For details on the output, see $planCacheStats output.有关输出的详细信息，请参阅$planCacheStats输出。

Find Cache Entry Details for a Plan Cache Query Shape查找计划缓存查询形状的缓存条目详细信息

To return plan cache information for a particular query shape, pass in a pipeline with a $match on the planCacheKey field.要返回特定查询形状的计划缓存信息，请在planCacheKey字段上传递一个带有$match的管道。

db.orders.getPlanCache().list([ { $match: { planCacheKey: "DD67E353"} } ] )

The operation returns the following:该操作返回以下内容：

[
   {
      "createdFromQuery" : {
         "query" : {
            "quantity" : {
               "$gte" : 5
            },
            "type" : "apparel"
         },
         "sort" : {

         },
         "projection" : {

         }
      },
      "planCacheShapeHash" : "4D151C4C",
      "planCacheKey" : "DD67E353",
      "isActive" : false,
      "works" : Long(4),
      "cachedPlan" : {
         "stage" : "FETCH",
         "filter" : {
            "type" : {
               "$eq" : "apparel"
            }
         },
         "inputStage" : {
            "stage" : "IXSCAN",
            "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]"
               ]
            }
         }
      },
      "timeOfCreation" : ISODate("2020-02-11T17:14:33.873Z"),
      "creationExecStats" : [
         {
            "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
               }
            }
         },
         {
            "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
               }
            }
         }
      ],
      "candidatePlanScores" : [
         1.5002,
         1.5002
      ],
      "indexFilterSet" : false,
      "estimatedSizeBytes" : Long(3160),   // Available starting in MongoDB 5.0
      "host" : "mongodb1.example.net:27018",
      "shard" : "shardA"                         // Available if run on sharded cluster如果在分片集群上运行，则可用
   }
]

Warning

Starting in MongoDB 8.0, the existing queryHash field is duplicated in a new field named planCacheShapeHash. If you're using an earlier MongoDB version, you'll only see the queryHash field. 从MongoDB 8.0开始，现有的queryHash字段被复制到一个名为planCacheShapeHash的新字段中。如果你使用的是早期的MongoDB版本，你只会看到queryHash字段。Future MongoDB versions will remove the deprecated queryHash field, and you'll need to use the planCacheShapeHash field instead.未来的MongoDB版本将删除弃用的queryHash字段，您将需要使用planCacheShapeHash字段。

For details on the output, see $planCacheStats output.有关输出的详细信息，请参阅$planCacheStats输出。