Docs Menu
Docs Home
/
MongoDB Manual
/
MongoDB CRUD Operations
/
MongoDB CRUD Concepts

Query Plans

On this page

  • Plan Cache Entry State
  • queryHash
  • planCacheKey
  • Availability

For any given query, the MongoDB query planner chooses and caches the most efficient query plan given the available indexes. The evaluation of the most efficient query plan is based on the number of "work units" (works) performed by the query execution plan when the query planner evaluates candidate plans. In general, the MongoDB query planner selects query plans with fewer works as the winning plan.

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

The following diagram illustrates the query planner logic:

A diagram of MongoDB's query planner logic.
click to enlarge

Note

Using explain ignores all existing plan cache entries and prevents the MongoDB query planner from creating a new plan cache entry.

Plan Cache Entry State

Starting in MongoDB 4.2, each query shape is associated with one of three states in the cache:

State
Description

No entry for this shape exists in the cache.

For a query, if the cache entry state for a query shape is Missing:

  1. Candidate plans are evaluated and a winning plan is selected.

  2. The cache creates an entry for the query shape in state Inactive with its works value.

The 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 query shape is not used to generate query plans.

For a query, if the cache entry state for a shape is Inactive:

  1. Candidate plans are evaluated and a winning plan is selected.

  2. The selected plan's works value is compared to the Inactive entry's. If the selected plan's works value is:

    • Less than or equal to the Inactive entry's,

      The selected plan replaces the placeholder Inactive entry and has an Active state.

      If before the replacement happens, the Inactive entry becomes Active (for example, due to another query operation), the newly active entry will only be replaced if its works value is greater than the selected plan.

    • Greater than the Inactive entry's,
      The Inactive entry remains but its works value is incremented.

The entry in the cache is for the winning plan. The planner can use this entry to generate query plans.

For a query, if the cache entry state for a shape is Active:

The active entry is used to generate query plans.

The planner also evaluates the entry's performance and if its works value no longer meets the selection criterion, it will transition to Inactive state.

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

Query Plan and Cache Information

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

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

Plan Cache Flushes

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

  • Catalog operations like index or collection drops clear the plan cache.

  • Least recently used (LRU) cache replacement mechanism clears the least recently accessed cache entry, regardless of state.

Users can also:

Tip

See also:

queryHash and planCacheKey

Plan Cache Debug Info Size Limit

Starting in MongoDB 5.0, the plan cache will save full plan cache entries only if the cumulative size of the plan caches for all collections is lower than 0.5 GB. When the cumulative size of the plan caches for all collections exceeds this threshold, additional plan cache entries are stored without the following debug information:

The estimated size in bytes of a plan cache entry is available in the output of $planCacheStats.

queryHash and planCacheKey

queryHash

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

Note

As with any hash function, two different query shapes may result in the same hash value. However, the occurrence of hash collisions 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 entry associated with the query.

Note

Unlike the queryHash, the planCacheKey is a function of both the query shape and the currently available indexes for the shape. That is, if indexes that can support the query shape are added/dropped, the planCacheKey value may change whereas the queryHash value would not change.

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

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

The following queries on the collection have the same shape:

db.foo.explain().find( { x: { $gt: 5 } } )  // Query Operation 1
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 1 differs from query operation 2, the two queries have different planCacheKey.

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

Availability

The queryHash and planCacheKey are available in:

Index Filters

Index filters are set with the planCacheSetFilter command and determine which indexes the planner evaluates for a query shape. A query shape consists of a combination of query, sort, and projection specifications. If an index filter exists for a given query shape, the planner only considers those indexes specified in the filter.

When an index filter exists for the query shape, MongoDB ignores the hint(). To see whether MongoDB applied an index filter for a query shape, check the indexFilterSet field of either the db.collection.explain() or the cursor.explain() method.

Index filters only affect which indexes the planner evaluates; the planner may still select the collection scan as the winning plan for a given query shape.

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

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

Starting in MongoDB 6.0, an index filter uses the collation previously set using the planCacheSetFilter command.

Tip

See also:

Back

Interpret Explain Plan Results