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

Improve $lookup explain to indicate query plan on the "from" collection

    • Query Execution

      The explain output of the $lookup stage doesn't show any information about the query that would be executed on the "from" collection. This can lead to confusion about whether or not $lookup is using an index when the document source cursor on the collection that the "aggregate" command was run on is performing a collection scan.


      Original description

      Lookup don't use indexes. Here is the test code:

      db.goods.drop();
      db.persons.drop();
      db.goods_persons.drop();
      
      db.goods.insert({'id': 1, 'name': 'good1'});
      db.goods.insert({'id': 2, 'name': 'good2'});
      
      db.persons.insert({'id': 1, 'name': 'john'});
      db.persons.insert({'id': 2, 'name': 'tom'});
      
      db.goods_persons.insert({'good': 1, 'person': 1});
      db.goods_persons.insert({'good': 2, 'person': 1});
      
      db.goods_persons.insert({'good': 1, 'person': 2});
      
      
      //create all probably indexes
      db.goods.ensureIndex({'id':1});
      db.persons.ensureIndex({'id':1});
      db.goods_perons.ensureIndex({'good':1});
      db.goods_perons.ensureIndex({'person':1});
      db.goods.ensureIndex({'goods_persons.person':1});
      db.goods.ensureIndex({'goods_persons.good':1});
      
      //get buyers
      db.goods.aggregate(
      [
          {$lookup:{
              from: "goods_persons",
              localField: "id",
              foreignField: "good",
              as: "goods_persons"
          }},
          {$match:{
              "goods_persons.person": 1
          }}
      ], {'explain': true}); //COLLSCAN   -  index not used
      

            Assignee:
            christopher.harris@mongodb.com Chris Harris
            Reporter:
            kazankov Sergey Kazankov
            Votes:
            51 Vote for this issue
            Watchers:
            63 Start watching this issue

              Created:
              Updated: