planCacheSetFilter

On this page

Definition

Compatibility
Syntax
Required Access
Examples

MongoDB 5.0 is end of life as of October 2024. This version of the documentation is no longer supported. To upgrade your 5.0 deployment, see the MongoDB 6.0 upgrade procedures.

Definition

planCacheSetFilter: Set an index filter for a collection. If an index filter already exists for the query shape, the command overrides the previous index filter.

Compatibility

This command is available in deployments hosted in the following environments:

MongoDB Atlas: The fully managed service for MongoDB deployments in the cloud

Important

This command is not supported in M0, M2, and M5 clusters. For more information, see Unsupported Commands.

MongoDB Enterprise: The subscription-based, self-managed version of MongoDB
MongoDB Community: The source-available, free-to-use, and self-managed version of MongoDB

Syntax

The command has the following syntax:

db.runCommand(
   {
      planCacheSetFilter: <collection>,
      query: <query>,
      sort: <sort>,
      projection: <projection>,
      indexes: [ <index1>, <index2>, ...],
      comment: <any>
   }
)

The planCacheSetFilter command has the following field:

Field	Type	Description
`planCacheSetFilter`	string	The name of the collection.
`query`	document	The query predicate associated with the index filter. Together with the `sort` and the `projection`, the `query` predicate make up the query shape for the specified index filter. Only the structure of the predicate, including the field names, are significant; the values in the query predicate are insignificant. As such, query predicates cover similar queries that differ only in the values.
`sort`	document	Optional. The sort associated with the filter. Together with the `query` and the `projection`, the `sort` make up the query shape for the specified index filter.
`projection`	document	Optional. The projection associated with the filter. Together with the `query` and the `sort`, the `projection` make up the query shape for the specified index filter.
`indexes`	array	An array of index filters for the specified query shape. Specify the index filters as either: an array of index specification documents, e.g. `[ { x : 1 }, ... ]` an array of index names, e.g. `[ "x_1", ... ]` Because the query optimizer chooses among the collection scan and these indexes, if the specified indexes are non-existent or hidden, the optimizer will choose the collection scan. In cases of multiple indexes with the same key pattern, you must specify the index by name.
`comment`	any	Optional. A user-provided comment to attach to this command. Once set, this comment appears alongside records of this command in the following locations: mongod log messages, in the `attr.command.cursor.comment` field. Database profiler output, in the `command.comment` field. `currentOp` output, in the `command.comment` field. A comment can be any valid BSON type (string, integer, object, array, etc).

Index filters only exist for the duration of the server process and do not persist after shutdown; however, you can also clear existing index filters using the planCacheClearFilters command.

Required Access

A user must have access that includes the planCacheIndexFilter action.

Examples

Set Filter on Query Shape Consisting of Predicate Only

The following example creates an index filter on the orders collection such that for queries that consist only of an equality match on the status field without any projection and sort, the query optimizer evaluates only the two specified indexes and the collection scan for the winning plan:

db.runCommand(
   {
      planCacheSetFilter: "orders",
      query: { status: "A" },
      indexes: [
         { cust_id: 1, status: 1 },
         { status: 1, order_date: -1 }
      ]
   }
)

In the query predicate, only the structure of the predicate, including the field names, are significant; the values are insignificant. As such, the created filter applies to the following operations:

db.orders.find( { status: "D" } )
db.orders.find( { status: "P" } )

To see whether MongoDB will apply an index filter for a query shape, check the indexFilterSet field of either the db.collection.explain() or the cursor.explain() method.

Set Filter on Query Shape Consisting of Predicate, Projection, and Sort

The following example creates an index filter for the orders collection. The filter applies to queries whose predicate is an equality match on the item field, where only the quantity field is projected and an ascending sort by order_date is specified.

db.runCommand(
   {
      planCacheSetFilter: "orders",
      query: { item: "ABC" },
      projection: { quantity: 1, _id: 0 },
      sort: { order_date: 1 },
      indexes: [
         { item: 1, order_date: 1 , quantity: 1 }
      ]
   }
)

For the query shape, the query optimizer will only consider indexed plans which use the index { item: 1, order_date: 1, quantity: 1 }.