Query Plan Cache Commands

MongoDB planCacheClear command

The planCacheClear command is used to remove the cached query plans for a collection. It declares the query shape to remove the cached query plan for that shape.

Syntax:

snippet

db.runCommand(
   {
      planCacheClear: <collection name>,
      query: <query here>,
      sort: <sorting algo>,
      projection: <projection>
   }
)

Command Fields

Field	Type	Description
query	document	It contains the prefix for the shape query.
projection	document	It contains the projection details associated with the query shape.
sort	document	It contains the sorting details associated with the query shape.

Example:

We have the following query shape:

snippet

{
  "query" : { "qty" : { "$gt" : 10 } },
  "sort" : { "ord_date" : 1 },
  "projection" : { },
  "queryHash" : "9AAD95BE" 
}

The following code clears the query plan cached for the shape:

snippet

db.runCommand(
   {
      planCacheClear: "orders",
      query: { "qty" : { "$gt" : 10 } },
      sort: { "ord_date" : 1 }
   }
)

MongoDB planCacheClearFilters command

The planCacheClearFilters command removes index filters on a collection. We can also clear existing index filters with this command while index filters only exist for the duration of the server process and don't persist after shutdown.

Syntax:

snippet

db.runCommand(
   {
      planCacheClearFilters: <collection>,
      query: <query pattern>,
      sort: <sort specification>,
      projection: <projection specification>
   }
)

Command Fields:

Field	Type	Description
planCache ClearFilters	string	It declares the name of the collection.
query	document	It contains the query predicate that is associated with the filter to remove.
sort	document	It contains the sorting criteria associated with the filter to remove.
projection	document	It contains the projection details that are associated with the filter to remove.

Example:

The books collection contains the following two filters:

snippet

{
  "query" : { "status" : "A" },
  "sort" : { "ord_date" : -1 },
  "projection" : { },
  "indexes" : [ { "status" : 1, "cust_id" : 1 } ]
}

{
  "query" : { "status" : "A" },
  "sort" : { },
  "projection" : { },
  "indexes" : [ { "status" : 1, "cust_id" : 1 } ]
}

The following command will remove the first index filter:

snippet

db.runCommand(
   {
      planCacheClearFilters: "orders",
      query: { "status" : "A" }
   }
)

MongoDB planCacheListFilters command

The palncacheListFilters command lists the index filters associated with query shapes for a collection.

Syntax:

snippet

db.runCommand( { planCacheListFilters: <collection> } )

Command Field:

Field	Type	Description
planCacheListFilters	string	It declares the name of the collection

Example:

snippet

{
   "filters" : [
      {
         "query" : <query>
         "sort" : <sort>,
         "projection" : <projection>,
         "indexes" : [
            <index1>,
            ...
         ]
      },
      ...
   ],
   "ok" : 1
}

MongoDB palnCacheSetFilter command

This command set an index filter for a collection. It is the index filter that already exists for the query shape. The planCacheSetFilter overwrites the previous index filter.

Syntax:

snippet

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

Command Filters

Field	Type	Description
planCacheSetFilter	string	This filed declares the name of the collection.
query	document	It contains the query predicate associated with the index filter.
sort	document	It contains the sorting details associated with the filter.
projection	document	It contains the projection associated with the filter.
indexes1	array	It contains the array of the index for the specified query shape.

Example:

The example creates an index filter on the books collection such that for queries that contains only of an equality match on the purchase field without any projection and sort.

snippet

db.runCommand(
   {
      planCacheSetFilter: "books",
      query: { purchase: "MongoDB" },
      indexes: [
         { stud_id: 123, purchase: 2 },
         { purchage: 5, order_date: 01102020 }
      ]
   }
)