Clustered Collections as lookup target

Working with Data
1

Hi, I wanted to ask if there are limitations with the Clustered Collections when they are the target of a lookup.

If I run a Find with _id I see the index is used correctly:

    "winningPlan": {
      "stage": "CLUSTERED_IXSCAN",
      "filter": {
        "_id": {
          "$eq": 15205202308
        }
      },

But if I do the same with a lookup, this does not use the index:

{
   $lookup:
     {
       from: "ClusteredCollection",
       localField: "myId",
       foreignField: "_id",
       as: "tss"

explain:

    {
      "$lookup": {
        "from": "ClusteredCollection",
        "as": "tss",
        "localField": "myId",
        "foreignField": "_id",
        "unwinding": {
          "preserveNullAndEmptyArrays": false
        }
      },
      "totalDocsExamined": NumberLong(400),
      "totalKeysExamined": NumberLong(0),
      "collectionScans": NumberLong(200),
      "indexesUsed": [],
      "nReturned": NumberLong(200),
      "executionTimeMillisEstimate": NumberLong(2143)
    },

How can I take advantage of the _id index?

Thanks

2

Hello @Giacomo_Benvenuti ,

Thanks for reporting the issue. I’m able to reproduce the same issue that you’re seeing and opened SERVER-82079. Feel free to watch/up-vote the ticket to receive notification on it.

Regards,
Tarun

3

Thank you very much Tarun.

1 Like