planCacheSetFilter
On this page
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.
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:FieldTypeDescriptionplanCacheSetFilter
stringThe name of the collection.query
documentThe query predicate associated with the index filter. Together with the
sort
and theprojection
, thequery
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
documentOptional. The sort associated with the filter. Together with thequery
and theprojection
, thesort
make up the query shape for the specified index filter.projection
documentOptional. The projection associated with the filter. Together with thequery
and thesort
, theprojection
make up the query shape for the specified index filter.indexes
arrayAn 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
anyOptional. 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 thecommand.comment
field.
A comment can be any valid BSON type (string, integer, object, array, etc).
New in version 4.4.
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 }
.