Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-33506

COLLSCAN when using lookup with partial index match

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: Aggregation Framework
    • None
    • ALL
    • Hide

      The following aggregation uses a matching index for column "matchKey" on the "other" collection:

      db.collection.aggregate([
          {
              "$lookup": {
                  "from": "other",
                  "let": {
                      "id": "$_id"
                  },
                  "pipeline": [
                      {
                          "$match": {
                              "$expr": {
                                  "$eq": ["$matchKey","$$id"]
                              }
                          }
                      }
                  ]
              }
          }
      ])
      

      If you add another property to the lookup expression like this:

      db.collection.aggregate([
          {
              "$lookup": {
                  "from": "other",
                  "let": {
                      "id": "$_id"
                  },
                  "pipeline": [
                      {
                          "$match": {
                              "$expr": {
                                  $and:[{"$eq": ["$matchKey","$$id"]},{"$eq":["otherproperty",1]}]
                              }
                          }
                      }
                  ]
              }
          }
      ])
      

      The partially matching index is completely disregarded and a COLLSCAN is executed

      Show
      The following aggregation uses a matching index for column "matchKey" on the "other" collection: db.collection.aggregate([ { "$lookup": { "from": "other", "let": { "id": "$_id" }, "pipeline": [ { "$match": { "$expr": { "$eq": ["$matchKey","$$id"] } } } ] } } ]) If you add another property to the lookup expression like this: db.collection.aggregate([ { "$lookup": { "from": "other", "let": { "id": "$_id" }, "pipeline": [ { "$match": { "$expr": { $and:[{"$eq": ["$matchKey","$$id"]},{"$eq":["otherproperty",1]}] } } } ] } } ]) The partially matching index is completely disregarded and a COLLSCAN is executed

      Version 3.6.3 fixed full index match for $lookup and $match $expr aggregations.
      Whenever there's a partial index match, execution falls back to COLLSCAN, instead of using the partial matched index

            Assignee:
            kelsey.schubert@mongodb.com Kelsey Schubert
            Reporter:
            danielp@bompracredito.com.br Daniel Polistchuck
            Votes:
            0 Vote for this issue
            Watchers:
            8 Start watching this issue

              Created:
              Updated:
              Resolved: