Query Plans

For a query, the MongoDB query optimizer chooses and caches the mostefficient query plan given the available indexes. The evaluation of themost efficient query plan is based on the number of “work units”(works) performed by the query execution plan when the query plannerevaluates candidate plans.

The associated plan cache entry is used for subsequent queries with thesame query shape.

Plan Cache Entry State

Starting in MongoDB 4.2, the cache entry is associated with a state:

StateDescription
MissingNo entry for this shape exists in the cache.For a query, if the cache entry state for a shape isMissing:- Candidate plans are evaluated and a winning plan is selected.- The selected plan is added to the cache in Inactive state with its works value.
InactiveThe entry in the cache is a placeholder entry for this shape.That is, the planner has seen the shape and calculated its cost(works value) and stored as a placeholder entry but the queryshape is not used to generate query plans.For a query, if the cache entry state for a shape isInactive:-Candidate plans are evaluated and a winning plan is selected.-The selected plan’s works value is comparedto the Inactive entry’s. If theselected plan’s works value is: - - Less than or equal to the Inactive entry’s, -The selected plan replaces the placeholderInactive entry and has anActive state.If before the replacement happens, the Inactive entry becomes Active (for example, due to another queryoperation), the newly active entry will only be replacedif its works value is greater than the selected plan. - - Greater than the Inactive entry’s, -The Inactive entry remainsbut its works value is incremented.
ActiveThe entry in the cache is for the winning plan. The planner canuse this entry to generate query plans.For a query, if the cache entry state for a shape isActive:The active entry is used to generate query plans.The planner also evaluates the entry’s performance and if itsworks value no longer meets the selection criterion, it willtransition to Inactive state.

See Plan Cache Flushes for additional scenarios that triggerchanges to the plan cache.

Query Plan and Cache Information

To view the query plan information for a given query, you can usedb.collection.explain() or the cursor.explain() .

Starting in MongoDB 4.2, you can use the $planCacheStatsaggregation stage to view plan cache information for a collection.

Plan Cache Flushes

The query plan cache does not persist if a mongodrestarts or shuts down. In addition:

  • Catalog operations like index or collection drops clear the plancache.
  • Least recently used (LRU) cache replacement mechanism clears theleast recently accessed cache entry, regardless of state.

Users can also:

See also

queryHash and planCacheKey

queryHash and planCacheKey

queryHash

To help identify slow queries with the same query shape,starting in MongoDB 4.2, each query shape is associated witha queryHash. The queryHash is ahexadecimal string that represents a hash of the query shape andis dependent only on the query shape.

Note

As with any hash function, two different query shapes may resultin the same hash value. However, the occurrence of hashcollisions between different query shapes is unlikely.

planCacheKey

To provide more insight into the query plan cache, MongoDB 4.2 introduces the planCacheKey.

planCacheKey is a hash of the key for the plan cache entryassociated with the query.

Note

Unlike the queryHash, the planCacheKey is a function ofboth the query shape and the currently available indexes for theshape. That is, if indexes that can support the query shape areadded/dropped, the planCacheKey value may change whereas thequeryHash value would not change.

For example, consider a collection foo with the following indexes:

  1. db.foo.createIndex( { x: 1 } )
  2. db.foo.createIndex( { x: 1, y: 1 } )
  3. db.foo.createIndex( { x: 1, z: 1 }, { partialFilterExpression: { x: { $gt: 10 } } } )

The following queries on the collection have the same shape:

  1. db.foo.explain().find( { x: { $gt: 5 } } )  // Query Operation 1
  2. db.foo.explain().find( { x: { $gt: 20 } } ) // Query Operation 2

Given these queries, the index with the partial filter expression can support query operation 2 but _not_support query operation 1. Since the indexes available to support query operation 1differs from query operation 2, the two queries have differentplanCacheKey.

If one of the indexes were dropped, or if a new index { x: 1, a: 1} were added, the planCacheKey for both query operations willchange.

Availability

The queryHash and planCacheKey are available in:

Index Filters

New in version 2.6.

Index filters determine which indexes the optimizer evaluates for aquery shape. A query shape consists of a combination of query,sort, and projection specifications. If an index filter exists for agiven query shape, the optimizer only considers those indexesspecified in the filter.

When an index filter exists for the query shape, MongoDB ignores thehint(). To see whether MongoDB applied an indexfilter for a query shape, check the indexFilterSetfield of either the db.collection.explain() or thecursor.explain() method.

Index filters only affects which indexes the optimizer evaluates; theoptimizer may still select the collection scan as the winning plan fora given query shape.

Index filters exist for the duration of the server process and do notpersist after shutdown. MongoDB also provides a command to manually removefilters.

Because index filters override the expected behavior of the optimizeras well as the hint() method, use index filterssparingly.

See planCacheListFilters,planCacheClearFilters, and planCacheSetFilter.

See also

Indexing Strategies