Using covered query in lookup stage in aggregation pipeline

Working with Data
,
1

Does MongoDB lookup stage in aggregation support covered query optimization? For example, with pipeline

    db.MyColl.aggregate([
    {$lookup: {
        let: {"a": "$ma", "b": "$mb"},
        pipeline: [
            {$match: {
                $expr: {$and: [{$eq: ["$la", "$$a"]}, {$eq: ["$lb", "$$b"]}]}
            }},
            {$project: { la: 1, lb: 1, _id: 0}}
        ],
        from: "MyOtherColl",
        as: "subs"
    }}
    ])

and index

db.MyOtherColl.createIndex({“la”: 1, “lb”: 1})

inner query is not covered according to docsExamined.

If I explain() inner query itself, it seems that $expr breaks this: .aggregate([{$match: {la: "va", lb: "vb", _id: 0}}, {$project:...}]) will be covered, while equivalent with $expr (.aggregate([{$match: {$expr: ...}}, {$project:...}]) will be not.

Is this a limitation of covered queries or am I missing something?

2

Hi @Ales_Kete,

I don’t believe that with this query shape and the expr the engine will know to do a covered query.

Thanks
Pavel

3

I have my own interrogation with this.

The query is not covered but does the index still used?

From what I understand in https://docs.mongodb.com/manual/reference/operator/query/expr/#behavior, the index should be used as the query involves equality matches.

Would keysExamined be a more accurate measure of the work done compared to docsExamined?

Fetching the matching documents for the subsequent stages should not be a major issue unless the documents are huge.

4

@steevej: Ratio of keysExamined and docsExamined is approximately one, so I assume index is used. But, IMHO, fetching of small documents can also impact performance in situation, when index itself would fit into memory, but index and documents would not and documents in collection are not sorted by index used in query. In such case, fetching document would cause cache pages reads and eviction and thus would impact performance.

5

First, thank you for your input, Pavel!

Is there perhaps a form of this (or similar) query in which such subquery would be covered or is $expr by itself showstopper?

6

Just for future reference, if anyone finds this useful:

I ended up doing separate queries into MyColl and MyOtherColl with records sorted by lookup-match keys and doing the rest of the work in code:

db.MyColl.createIndex(...) // index supporting covered query for `a`
a = db.MyColl.find({}, {...}).sort({"ma": 1, "mb": 1})
b = db.MyOtherColl.find({}, {...}).sort({"la": 1, "lb": 1})

// Then advancing through a and/or b depending on (ma, mb) to (la, lb) comparison result