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

COLLSCAN when using lookup with partial index match

XMLWordPrintableJSON

    • 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: