When a CWI index is used with a $ne: <non-null scalar> predicate on an indexed non-wildcard field alongside a wildcard-field predicate, the planner inserts an avoidable FETCH stage. This means a more constrained query ($ne: 2) performs worse than a less constrained one ($lt: 2), both using the same index.

The distinction for non-wildcard field is because the non-wildcard field is non-sparse and includes entries in the CWI when that field is missing.

That is why the same improvement does not apply to sparse non-wildcard indexes: for those, a document where the indexed field is absent has no index entry, so tight $ne: <scalar> bounds would silently exclude documents that should match.

Example

db.demo.drop();
db.demo.createIndex({ "w.$**": 1, unit: 1 });
db.demo.insertMany([
{ w: { x: "A" }, unit: 1 },
{ w: { x: "A" }, unit: 2 },
{ w: { x: "B" }, unit: 3 },
{ w: { x: "B" }, unit: 4 },
]);

The following query is covered (totalDocsExamined = 0):

db.demo.aggregate([
{ $match: { "w.x": "A", unit: { $lt: 2 } } },
{ $project: { _id: 0, unit: 1 } },
]);

But the $ne variant produces PROJECTION_SIMPLE with a FETCH stage and totalDocsExamined = 4:

db.demo.aggregate([
{ $match: { "w.x": "A", unit: { $ne: 2 } } },
{ $project: { _id: 0, unit: 1 } },
]);

This has been present since CWI planning support was introduced.

Solution

QueryPlannerIXSelect::_compatible rejects $not predicates on sparse indexes when the comparison value is non-null, via nodeIsSupportedBySparseIndex, because a sparse index omits entries for documents where the indexed field is missing.

CWI non-wildcard fields do not share this property: BtreeKeyGenerator::extractNonArrayElementAtPath (uassert 7246301) enforces at write time that these fields are never arrays, and every matched document receives an index entry regardless of whether the non-wildcard field is present (missing values are stored as null). If we recognise this in the code, that would allow tight $ne: <scalar> bounds to be used on CWI non-wildcard fields without a FETCH.

Note: this improvement likely depends on, or should be considered together with SERVER-128260 since that bug is about a blanket exclusion on $not.