Note: This ticket describes a consequence of some design choices made in the 6.0 cycle. This is not a new problem, but given what we've learned since then (change in plans with Bonsai, "multi planning storms", parity with classic performance being important, etc), it's an area worth revisiting.

High level Description
The query system has a "plan cache" which maps a query's shape to the query plan to be used for that shape. This lets us avoid repeating work for queries which are similar. There are two versions of the plan cache: the classic engine cache, and the SBE plan cache. They differ both in the cache key and the cache value.

The Classic Cache maps a generously "parameterized" version of the query to a description of which index(es) to use to answer the query. That is, each entry in the classic plan cache corresponds to a lot of queries. When a cache miss occurs, all of the candidate plans are generated, run through the stage builders, and then multi-planned. When a cache hit occurs, we get to skip the work of multi-planning. A new query execution tree is built, and the query is run.

The SBE plan cache maps a more conservatively parameterized query shape to a an actual SBE plan which can be cloned and then run. Each entry in the SBE plan cache (generally) corresponds to a smaller set of eligible queries.

There are tradeoffs to each approach: With the classic cache, each cache entry covers a larger space in the set of possible queries, generally meaning a higher cache hit rate. In the SBE cache, each cache entry covers a smaller space, but we save the work of multi-planning as well as stage-building/lowering when there's a cache hit.

Given multi-planning is extremely expensive (see SERVER-87128) it's worth asking the question "How much more often will SBE have to multi-plan compared to classic?" Of course, the answer depends on the workload, but it's easy to come up with a workload where the answer is "an unbounded amount."

Detailed Example

As an example, we could take queries:

Query1: find({a: "foo", b: [1]})
Query2: find({a: "bar", b: [2]})

In the classic plan cache, Query1's cache key would be roughly a:?,b:?. Query 2's key would be the same. If a user ran query 1, then query 2, query 2 would use the cache entry (and multi-planning result) generated by Query 1.

On the other hand, in SBE Query 1 and Query 2 have different shapes. So if a user ran Query 1 and then query 2, query 2 would have to multi-plan again.

The reason Query 1 and 2 have different SBE cache keys is that arrays are not parameterized in the SBE plan cache. So the SBE cache key for Query 1 is a:?, b:[1] and the SBE cache entry for Query 2 is a:?,b:[2]. So, after multi-planning, we'd add a cache entry for the a:"bar",b:[2] query, but you would get repeated cache misses for each unique value of i when querying by b:[i].

Querying by an array is just one way that can cause the cache keys to differ. There are several others.

There may be users with workloads where the number of classic cache keys is reasonable, but each SBE cache key is unique, meaning that enabling SBE would cause every query to multi-plan.

Goals for this jira ticket

• Determine whether this could be an issue in practice
• Discuss with product/stakeholders whether we want a "no worse than classic" guarantee here, or whether we're okay with it as designed
• Investigate relationship with SERVER-87128
  • If SBE uses a more fine-grained key, then multi-planning operations which are run in serial in classic under 87128 will get run in parallel with SBE

One straightforward solution to this would be to use classic plan cache as a caching layer behind the SBE plan cache. This would give us a "no worse than classic" guarantee in terms of how often multi-planning happens.