planCacheSetFilter

Definition

  • planCacheSetFilter

New in version 2.6.

Set an index filter for a collection. Ifan index filter already exists for the query shape, thecommand overrides the previous index filter.

The command has the following syntax:

  1. db.runCommand(
  2.    {
  3.       planCacheSetFilter: <collection>,
  4.       query: <query>,
  5.       sort: <sort>,
  6.       projection: <projection>,
  7.       indexes: [ <index1>, <index2>, ...]
  8.    }
  9. )

The planCacheSetFilter command has the following field:

FieldTypeDescriptionplanCacheSetFilterstringThe name of the collection.querydocumentThe query predicate associated with the index filter. Together withthe sort and the projection, the query predicate make upthe query shape for the specified index filter.

Only the structure of the predicate, including the field names, aresignificant; the values in the query predicate areinsignificant. As such, query predicates cover similar queries thatdiffer only in the values.sortdocumentOptional. The sort associated with the filter. Together withthe query and the projection, the sort make upthe query shape for the specified index filter.projectiondocumentOptional. The projection associated with the filter. Together withthe query and the sort, the projection make upthe query shape for the specified index filter.indexesarrayAn 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 amongthe collection scan and these indexes, if the indexes are non-existent,the optimizer will choose the collection scan.

In cases of multiple indexes with the same key pattern, you mustspecify the index by name.

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

Required Access

A user must have access that includes theplanCacheIndexFilter action.

Examples

Set Filter on Query Shape Consisting of Predicate Only

The following example creates an index filter on the orderscollection such that for queries that consist only of an equalitymatch on the status field without any projection and sort, thequery optimizer evaluates only the two specified indexes and thecollection scan for the winning plan:

  1. db.runCommand(
  2.    {
  3.       planCacheSetFilter: "orders",
  4.       query: { status: "A" },
  5.       indexes: [
  6.          { cust_id: 1, status: 1 },
  7.          { status: 1, order_date: -1 }
  8.       ]
  9.    }
  10. )

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

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

To see whether MongoDB will apply an index filter for a query shape,check the indexFilterSet field of eitherthe 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 orderscollection. The filter applies to queries whose predicate is anequality match on the item field, where only the quantity fieldis projected and an ascending sort by order_date is specified.

  1. db.runCommand(
  2.    {
  3.       planCacheSetFilter: "orders",
  4.       query: { item: "ABC" },
  5.       projection: { quantity: 1, _id: 0 },
  6.       sort: { order_date: 1 },
  7.       indexes: [
  8.          { item: 1, order_date: 1 , quantity: 1 }
  9.       ]
  10.    }
  11. )

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

See also