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

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

    XMLWordPrintable

    Details

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

      Description

      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
      

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                27 Vote for this issue
                Watchers:
                30 Start watching this issue

                Dates

                • Created:
                  Updated: