Performance: all-buckets checkpoint reads use inefficient negative filter (BucketId != :rb)

[AGiorgetti]

Summary

The all-buckets checkpoint scan excludes the recycle bin via BucketId != :rb. Negative inequality filters are less index-friendly than positive inclusion filters and may bypass the primary checkpoint index, causing a measurable gap vs bucket-qualified reads.

Why this matters

• Negative filters (!=) are less index-friendly than positive inclusion.
• Bucket-qualified reads use the primary checkpoint index; all-buckets reads may bypass it.
• The performance gap is already visible in benchmarks: ~3× slower than bucket-qualified reads.

Baseline (net10, 2026-05-20)

Benchmark	Mean
ReadFromBucketCheckpoint(CommitsPerBucket=1000, ExtraBuckets=3)	22.142 ms
ReadFromAllBucketsCheckpoint(CommitsPerBucket=1000, ExtraBuckets=3)	67.011 ms

Ratio: all-buckets is ~3× slower than bucket-qualified.

Proposed investigation

• Run MongoDB explain() on both queries and compare index usage.
• Determine whether the gap is from the negative filter, a missing index, or COLLSCAN.
• Evaluate replacing the negative filter with an explicit bucket-inclusion strategy or a dedicated index.

Reference

See docs/Performance-Investigation.md → Finding #3 for full context.

[AGiorgetti]

I checked the MongoDB query plans for issue #75 using explain("executionStats") on the current checkpoint read shapes.

Findings:

• The all-buckets path is not doing a COLLSCAN. MongoDB chooses FETCH + IXSCAN.
• The winning index for the all-buckets query is _id_, not GetFrom_Checkpoint_Index.
• So the gap is not a missing index problem; it is the negative filter shape (BucketId != :rb) preventing use of the bucket-qualified checkpoint index.
• I also tried a simple explicit inclusion rewrite (BucketId in [active buckets]) and MongoDB still chose _id_ in the tested shape.
• A dedicated partial _id index is not a viable shortcut here because MongoDB rejects partialFilterExpression on _id.

Conclusion:

• This remains a real performance smell, but the main issue is plan shape, not an outright table scan.
• If we keep pursuing it, the next useful step is a rewrite that actually changes scan order, not just the bucket predicate form.

[AGiorgetti]

Follow-up investigation summary:

• Re-ran the existing explain audit against a scratch MongoDB database. The all-buckets checkpoint read still plans as FETCH + IXSCAN, not COLLSCAN.
• The current plan uses _id_ for global checkpoint order and fetches documents to apply BucketId != ":rb".
• The benchmark comparison is not apples-to-apples: ReadFromBucketCheckpoint reads only Bucket.Default, while ReadFromAllBucketsCheckpoint reads every active bucket. With ExtraBuckets=3, all-buckets returns 4x as many commits, so the 67 ms vs 22 ms result is not by itself proof of a 3x per-commit regression.
• Rewriting BucketId != ":rb" as an $or split around :rb did not help. MongoDB still chose _id_; forcing GetFrom_Checkpoint_Index added SORT + OR, which is worse for this scan shape.
• A partial compound index like { _id: 1, BucketId: 1 } with partialFilterExpression: { BucketId: { $ne: ":rb" } } is rejected because $ne is not supported in partial indexes.
• A non-partial { _id: 1, BucketId: 1 } index is legal and can reduce totalDocsExamined by filtering recycle-bin documents from index keys before fetch, but it still scans the same checkpoint key range and adds write/storage cost.
• In a recycle-bin-heavy scratch shape, MongoDB may prefer GetFrom_Checkpoint_Index plus an explicit SORT when the active bucket set is tiny. That confirms this is workload-dependent, not a simple missing-index fix.

Recommendation: do not change the engine for #75 based on current evidence. If this stays open, the next useful step is a dedicated recycle-bin-heavy all-buckets benchmark before considering { _id: 1, BucketId: 1 }. Regular recycle-bin cleanup remains the preferred operational mitigation.