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

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

XMLWordPrintableJSON

    • 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

        1. lookup_test.js
          1.0 kB

            Assignee:
            Unassigned Unassigned
            Reporter:
            kazankov Sergey Kazankov
            Votes:
            53 Vote for this issue
            Watchers:
            70 Start watching this issue

              Created:
              Updated: