$lookup in a .aggregate() query doesn't have an option to specify a hint

Working with Data Drivers
,
1

Recently, I’ve been fighting with this aggregation pipeline that likes to forget to use an index. From looking online, my best option to solve this “forgetting an index” is by specifying a hint. The only problem is that the lookup where the hint would be relevant is inside of a descendant $lookup query within that pipeline.

I’ve been looking all over the place, but I cant seem to find anything on how to add a hint inside the aggregation pipeline. Maybe this is a feature request?

For context, here’s the pipeline (node.js)

// Aggregation run on Tag collection
[
  {
    '$match': {
      '_id': new ObjectId('63e3155705656aeea6f81258')
    }
  }, {
    '$lookup': { // Find all connections for a Many-to-Many relation. 
      'from': 'TagOnFeedback', 
      'let': {
        'tagId': '$_id'
      }, 
      'pipeline': [
        {
          '$match': {
            '$expr': {
              '$eq': [
                '$tagId', '$$tagId'
              ]
            }
          }
        }
      ], 
      'as': 'TagOnFeedback'
    }
  }, {
    '$lookup': { // Find all feedback where the feedback matches the ID specified in the linker
      'from': 'Feedback', 
      'let': {
        'linkId': '$TagOnFeedback'
      }, 
      'pipeline': [
        {
          '$sort': { // Sort doesn't use the index, as the index gets dropped from the system's memory randomly
            'createdAt': -1, 
            '_id': 1
          }
        }, {
          '$match': {
            '$and': [
              {
                '$expr': {
                  '$in': [
                    '$_id', '$$linkId.feedbackId'
                  ]
                }
              }, {
                '$expr': { // For pagination
                  '$lt': [
                    '$_id', new ObjectId('63e3f0f218ee31386a627adc')
                  ]
                }
              }
            ]
          }
        }, {
          '$limit': 10
        }
      ], 
      'as': 'Feedback'
    }
  }, {
    '$unset': [
      'TagOnFeedback'
    ]
  }
]

If this isn’t possible, how should I force mongo to use my index for the “Feedback” collection $lookup?

Thanks

2

I would first simply the $lookup stages to use localField/foreignField such as:

Replace

with

{ '$lookup' : {
      'from': 'TagOnFeedback', 
      'localField': '_id' ,
      'foreignField': 'tagId' ,
      'as': 'TagOnFeedback'
} }

Also replace

with

{ '$lookup': { // Find all feedback where the feedback matches the ID specified in the linker
      'from' : 'Feedback' , 
      'localField' : 'TagOnFeedback.feedbackId' ,
      'foreignField' : '_id' ,
      'pipeline':
      [
          { '$sort' : {
              'createdAt': -1, 
              '_id': 1
          } } ,
          { '$match': {
              '_id' , { '$lt': new ObjectId('63e3f0f218ee31386a627adc') }
          } } ,
          {
              '$limit': 10
          }
      ] , 
      'as': 'Feedback'
} }

Avoiding $expr, depending of the version of mongod, increases the odd of hitting an index.

As for

we will need the indexes that are defined and sample documents from all collections. I have seen in the past people complaining that indexes were not used and indeed they were not used because there was typo in the field names or the order of the field was not appropriate for the query. We also need the explain plan.

And finally about

I am suspicious about the randomly part. Please share your observations. Are you alone working on the instance? Do you have any automated script running?

1 Like