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

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

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major - P3
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: Backlog
    • Component/s: Aggregation Framework
    • Labels:

      Description

      Description

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

      db.users.aggregate([
        {$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):

      db.emails.aggregate([
       {$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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              backlog-query-optimization Backlog - Query Optimization
              Reporter:
              Mcat Marco
              Participants:
              Votes:
              13 Vote for this issue
              Watchers:
              24 Start watching this issue

                Dates

                Created:
                Updated: