Partial index eligibility does not recognize predicates wrapped in $expr

XMLWordPrintableJSON

    • Type: Bug
    • Resolution: Works as Designed
    • Priority: Minor - P4
    • None
    • Affects Version/s: 9.0.0
    • Component/s: None
    • ALL
    • Hide

      Setup:

      db.orders.createIndex(
        {customer_id: 1, order_id: 1},
        {partialFilterExpression: {is_cancelled: false}}
      )
      db.orders.createIndex({customer_id: 1, region: 1})  // non-partial, for contrast

      Repro:

      Plain predicate — partial index used, as expected:
      db.orders.aggregate([
        {$match: {customer_id: ObjectId("..."), is_cancelled: false, order_id: ObjectId("...")}}
      ]).explain("executionStats")
      // winningPlan.inputStage.indexName === "customer_id_1_order_id_1"  (the partial index)

      Same predicate, with customer_id/is_cancelled wrapped in $expr (e.g. required inside a $lookup pipeline to also correlate a field against a let variable):
      db.orders.aggregate([
        {$match: {$expr: {$and: [
          {$eq: ["$customer_id", ObjectId("...")]},
          {$eq: ["$is_cancelled", false]}
        ]}}}
      ]).explain("executionStats")
      // winningPlan.inputStage.indexName is instead "customer_id_1_region_1" (non-partial),
      // bounding only on customer_id. "customer_id_1_order_id_1" never appears in rejectedPlans —
      // it is not considered a candidate at all.

      Show
      Setup: db.orders.createIndex(   {customer_id: 1, order_id: 1},   {partialFilterExpression: {is_cancelled: false}} ) db.orders.createIndex({customer_id: 1, region: 1})  // non-partial, for contrast Repro: Plain predicate — partial index used, as expected: db.orders.aggregate([   {$match: {customer_id: ObjectId("..."), is_cancelled: false, order_id: ObjectId("...")}} ]).explain("executionStats") // winningPlan.inputStage.indexName === "customer_id_1_order_id_1"  (the partial index) Same predicate, with customer_id/is_cancelled wrapped in $expr (e.g. required inside a $lookup pipeline to also correlate a field against a let variable): db.orders.aggregate([   {$match: {$expr: {$and: [     {$eq: ["$customer_id", ObjectId("...")] },     {$eq: ["$is_cancelled", false] }   ]}}} ]).explain("executionStats") // winningPlan.inputStage.indexName is instead "customer_id_1_region_1" (non-partial), // bounding only on customer_id. "customer_id_1_order_id_1" never appears in rejectedPlans — // it is not considered a candidate at all.
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      MongoDB version: 9.0.0 (Atlas, queryFramework: sbe/classic both observed), also reproduced on 8.0.29

      Summary: A predicate that would make a partial index eligible when written as a plain field: value match is silently excluded from the candidate index set when the same predicate is wrapped in $expr — even though $expr predicates otherwise generate normal index bounds (via $_internalExprEq) and get used against non-partial indexes without issue.

      Question for support: Is this intentional/documented behavior (partial-index subsumption checking is syntactic and only inspects the plain MatchExpression tree, not $expr), or a planner gap? If intentional, request it be called out explicitly in the partial index docs — we only found it via explain comparison, not documentation.

            Assignee:
            Unassigned
            Reporter:
            Francesco Intoci (EXT)
            Votes:
            0 Vote for this issue
            Watchers:
            8 Start watching this issue

              Created:
              Updated:
              Resolved: