Performance: cache reusable physical query plans

Summary

#2905 builds a shared query IR and already caches/reuses several expensive front-end steps:

• recurring LINQ shapes,
• parsed text expressions,
• recurring SQL SELECT templates,
• aggregate templates.

Its own notes explicitly leave a physical-plan cache as separate work.

Once parsing/translation becomes cheap, repeated queries can still redo planning work: inspect live collection metadata, choose indexes, analyze scalar/multikey semantics, derive bounds/unions, decide loaders/projections/sort strategy, and construct the executable pipeline.

Proposal: add a bounded, dependency-aware physical query plan cache that reuses an already optimized execution plan when the query shape and all plan-relevant database metadata are unchanged.

Related: #2905, #2807, #1706, #2876, #2775.

Goals

• Repeated parameterized queries should reuse the physical plan, not only their parsed/logical representation.
• Parameters remain late-bound; values must not be captured into the cached plan.
• Cache entries must never survive a metadata change that could alter correctness or index choice.
• Cache memory is bounded.
• Cache hits should avoid most planner allocations and metadata traversal.

What can be cached

A cache entry could contain immutable/rebindable plan information such as:

• selected index and traversal mode,
• normalized predicate/range/union structure,
• covered/index-only loader decision,
• sort/group strategy,
• projection field requirements,
• borrowed/materialized execution choice from work such as #2910,
• precomputed plan-local helpers that do not depend on current parameter values.

The execution still binds current parameters and obtains a fresh snapshot/cursor.

Cache key / dependencies

The key should be based on query shape, not literal values. It likely needs:

• logical/shared IR identity,
• collection identity,
• collation identity,
• relevant mapper/expression metadata where applicable,
• execution flags such as include/order/offset/limit shape.

Each cached plan should also carry dependency stamps/generations for metadata that can invalidate it, for example:

• collection create/drop/rename,
• index create/drop/redefinition,
• collation/rebuild changes,
• vector-index changes,
• any file-format or runtime comparer compatibility state that changes usable indexes.

Prefer cheap generation checks over rescanning all indexes on every hit.

Invalidation correctness

A stale plan must never:

• use a dropped/replaced index,
• keep an index after its expression/uniqueness/collation semantics changed,
• miss a newly better/required plan when a metadata generation says replanning is required,
• capture parameter values or caller-owned objects,
• reuse snapshot-specific page addresses or cursors.

If dependency validation fails, fall back to normal planning and replace/re-admit the cache entry.

Scope / admission

Start conservatively:

• SELECT / Find / Count / Exists queries with stable collection metadata,
• no volatile expressions,
• no FOR UPDATE, SELECT INTO or operations whose plan embeds mutable execution state,
• bounded cache with admission/eviction similar in spirit to the existing expression/query caches.

Unsupported shapes simply use the current planning path.

Performance acceptance

Measure both cold and warm execution for repeated parameterized query shapes:

• point lookup,
• range,
• IN/BETWEEN/OR union,
• covered projection,
• Count/Exists,
• sort + limit,
• nested/member-path query.

Record:

• planner time,
• end-to-end query time,
• allocations,
• metadata/index inspections,
• cache hit/miss/replan counts,
• throughput with several threads sharing the same database.

#2905 already gets recurring point/text queries into the tens-of-microseconds range in its benchmarks, so planner overhead becomes increasingly visible there. The target should be a measurable reduction in repeated-query CPU/allocation without changing result sets or plans.

Tests

• Same shape + different parameter values reuses one plan and returns correct results.
• Concurrent callers do not share mutable execution state.
• EnsureIndex/DropIndex invalidates or rejects the cached dependency.
• Collection rename/drop/recreate cannot reuse the old plan.
• Collation/rebuild changes force replan.
• Cache is bounded under thousands of distinct shapes.
• Exceptions and unsupported/volatile expressions preserve current behavior.
• Compare cached vs forced-fresh planning across randomized queries and assert identical results and equivalent EXPLAIN semantics.

Why this matters

#2905 removes a large part of parse/translation overhead. A physical-plan cache is the next layer: it can make hot parameterized queries pay mainly for binding + index traversal + result production, instead of repeatedly rediscovering the same execution strategy.