This version of the documentation is archived and no longer supported.

Query Plans

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:

Diagram of query planner logic

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

You can use the db.collection.explain() or the cursor.explain() method to view statistics about the query plan for a given query. This information can help as you develop indexing strategies.

db.collection.explain() provides information on the execution of other operations, such as db.collection.update(). See db.collection.explain() for details.

Changed in version 2.6: explain() operations no longer read from or write to the query planner cache.

Plan Cache Flushes

Catalog operations like index or collection drops flush the plan cache.

The plan cache does not persist if a mongod restarts or shuts down.

New in version 2.6: MongoDB provides Query Plan Cache Methods to view and modify the cached query plans. The PlanCache.clear() method flushes the entire plan cache. Users can also clear particular plan cache entries using PlanCache.clearPlansByQuery().

Plan Cache Debug Info Size Limit

Starting in 3.6.23, 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 with stripped debug information.

When plan cache entries are stored with stripped debug information, the planCacheListQueryShapes command output displays an empty document in the shapes array for each each plan cache entry for which debug information was stripped. Similarly, detailed execution statistics for the candidate plans are omitted from the planCacheListPlans command output when plan cache entries are stored with stripped debug information. When debug info is present, you can find these detailed statistics in the plans.reason field.

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

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 override the expected behavior of the optimizer as well as the hint() method, use index filters sparingly.

See planCacheListFilters, planCacheClearFilters, and planCacheSetFilter.