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

XMLWordPrintableJSON

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

      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

            Assignee:
            [DO NOT USE] Backlog - Query Optimization
            Reporter:
            Marco
            Votes:
            16 Vote for this issue
            Watchers:
            32 Start watching this issue

              Created:
              Updated: