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

    • Improvement
    • Status: Backlog
    • Major - P3
    • Resolution: Unresolved
    • None
    • None
    • Aggregation Framework

    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

              backlog-query-execution Backlog - Query Execution
              kazankov Sergey Kazankov
              Votes:
              47 Vote for this issue
              Watchers:
              56 Start watching this issue

              Dates

                Created:
                Updated: