Query Plans¶

On this page

Query Plan and Cache Information
Plan Cache Flushes
queryHash and planCacheKey
Index Filters

The MongoDB query optimizer processes queries and chooses the most efficient query plan for a query given the available indexes. The query system then uses this query plan each time the query runs.

The query optimizer only caches the plans for those query shapes that can have more than one viable plan.

For each query, the query planner searches the query plan cache for an entry that fits the query shape. If there are no matching entries, the query planner generates candidate plans for evaluation over a trial period. The query planner chooses a winning plan, creates a cache entry containing the winning plan, and uses it to generate the result documents.

If a matching entry exists, the query planner generates a plan based on that entry and evaluates its performance through a replanning mechanism. This mechanism makes a pass/fail decision based on the plan performance and either keeps or evicts the cache entry. On eviction, the query planner selects a new plan using the normal planning process and caches it. The query planner executes the plan and returns the result documents for the query.

The following diagram illustrates the query planner logic:

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.

Users can also:

Manually clear the entire plan cache using the PlanCache.clear() method.
Manually clear specific plan cache entries using the PlanCache.clearPlansByQuery() method.

`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:

copy

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:

copy

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:

explain() output fields: queryPlanner.queryHash and queryPlanner.planCacheKey
profiler log messages and diagnostic log messages (i.e. mongod/mongos log messages) when logging slow queries.
$planCacheStats aggregation stage (New in MongoDB 4.2)
PlanCache.listQueryShapes() method/planCacheListQueryShapes command
PlanCache.getPlansByQuery() method/planCacheListPlans command

Index Filters¶

New in version 2.6.

Index filters determine which indexes the optimizer 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 optimizer 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 affects which indexes the optimizer evaluates; the optimizer 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 overrides the expected behavior of the optimizer as well as the hint() method, use index filters sparingly.

See planCacheListFilters, planCacheClearFilters, and planCacheSetFilter.