In MongoDB 8.0, the new setQuerySettings command allows administrators to enforce index hints and other behavior based on the query shape hash. This gives users partial control over query plan selection.

 

However, the current implementation still allows the optimizer to re-evaluate multiple plans under certain conditions (e.g., plan cache eviction, plan ranking strategy). Additionally, the planCacheKey, which is the actual determinant for plan reuse, is generated based on more granular factors than the query shape hash (e.g., sort, collation, or exact index hint details).

 

This FR proposes an extension to the query settings system to allow a user to pin a planCacheKey to a specific query shape hash, effectively enabling full plan determinism.

 

Motivation:

 

• Avoid costly re-evaluations of plans in performance-sensitive workloads.

• Ensure plan stability in critical production queries (especially where cardinality changes lead to suboptimal re-planning).

• Enable long-term query plan binding without relying on indirect behaviors (e.g., index hinting alone).

• Improve control for query latency-sensitive systems and reduce unpredictability in the plan cache.

 

 

Proposed Behavior:

 

• Add a planCacheKeyOverride field to the setQuerySettings command syntax.

• When specified, MongoDB will force the use of a specific plan cache entry for all queries matching the given queryShapeHash.

• This plan must already exist in the plan cache (validated at the time of setting).

• Subsequent query executions will bypass re-planning and use the fixed plan unless the plan cache entry becomes invalid (e.g., due to index drop or schema changes).

• Introduce an admin command to verify the pinned plan for a given shape.