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

Lookup sub-pipeline is not using index with the $in operator

    • Type: Icon: Improvement Improvement
    • Resolution: Unresolved
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: Aggregation Framework
    • Labels:
    • Query Optimization


      Suppose we have the following documents in the emails collection:

      [{_id: ObjectId(), subject: 'Hello there', to: ['address1@example.com', 'address2@example.com']}]

      With the index:

       { { "v" : 2, "key" : { "to" : -1 }, "name" : "to_-1", "ns" : "test.emails" }

      And the following documents in the collection users:

       [{_id: ObjectId(), name: 'John Doe', address: 'address1@example.com'}]

      Then, if we use the following pipeline on 'users' in order to insert all emails sent to our users into 'sentEmails':

        {$lookup: {
          from: 'emails',
          let: {userAddress: '$address'},
          as: 'sentEmails',
          pipeline: [
            {$match: {$expr: {
              $in: ['$$userAdress', '$to']      

      Or, even if we extracted the emails sub-pipeline into an equivalent pipeline for our first user (John Doe):

       {$match: {$expr: {
         $in: ['address1@example.com', '$to'] 


      Its not using the index on the emails collection.


      However, if you do the same without using the $expr, the index is used properly:

      db.emails.aggregate([{$match: {to: 'address1@example.com'}}], {explain: true}) 
       "winningPlan" : { "winningPlan" : { "stage" : "FETCH", "inputStage" : { "stage" : "IXSCAN",

      Observed results

      The index is not used when the $in operator is used inside an $expr on a $match

      Expected results

      The index should be used to perform the $match stage

            backlog-query-optimization [DO NOT USE] Backlog - Query Optimization
            Mcat Marco
            15 Vote for this issue
            27 Start watching this issue