Issue with understand aggregate with $lookup and foreign field _id

Working with Data
1

I have an aggregate which uses $lookup to check a foreign field in another table which is the _id field. The query runs slowly and seems to be checking every document during lookup. The $lookup stage takes ~3 seconds. I figured this step would take milliseconds since it is checking an indexed field. I think I fundamentally misunderstand how $lookup works.

I am using lookup to go from the deck collection to the card collection. The deck has a reference to the _id key in the card collection.

$lookup: {
  from: "cards",
  localField: "deck",
  foreignField: "_id",
  as: "card"
}

I’m seeing these results for the lookup stage, however:

    {
      "$lookup": {
        "from": "cards",
        "as": "card",
        "localField": "deck",
        "foreignField": "_id",
        "unwinding": {
          "preserveNullAndEmptyArrays": false
        }
      },
      "totalDocsExamined": 30600,
      "totalKeysExamined": 30600,
      "collectionScans": 0,
      "indexesUsed": [
        "_id_"
      ],
      "nReturned": 30600,
      "executionTimeMillisEstimate": 2488
    },

How can I get this execution time down?

3

Hello @Eric_Hurt ,

Welcome to The MongoDB Community Forums! :wave:

Can you please share below details to understand your use case better?

  • MonoDB Version
  • Sample Documents
  • Expected Output
  • Please run your query with explain in executionStats mode (e.g. `db.collection.explain(‘executionStats’).aggregate(…)) and share the output.

Regards,
Tarun