-
Type: Improvement
-
Resolution: Won't Do
-
Priority: Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
None
-
Query Optimization
-
(copied to CRM)
For certain patterns of queries, we can run into a cycle of cache->evict->replan->cache for the same query shape and same plan depending on the parameters. By default, the planner requires a 10x increase in works to trigger replanning, which manifests in the slow query logs as "replanReason":"cached plan was less efficient than expected: expected trial execution to take X reads but it took at least Y reads".
However, when the higher work plan triggers the eviction, the cache entry remains in an "inactive" state. If the next query to activate it has a lower works value, then we'll use the new one. This puts us in a state where its like the higher work query never happened, and its possible to evict and replan again.
Note that the notion of an inactive cache entry was put in place for the opposite case: when a potentially sub-optimal, very high works plan gets cached. See SERVER-32452 for more details on that.