mongodb - $lookup pipeline using COLLSCAN instead of index

Question

I'm trying to use indexing on my $lookup pipeline but it doesn't seem to be working as intended.

Here's my query:

db.map_levels.explain().aggregate([
    {
        $lookup:
        {
            from: 
                "map_level_revisions",
            pipeline:
            [
                {
                    $match:
                    {
                        $expr:
                        {
                            $eq:
                            [
                                "$account_id",
                                ObjectId("5b66ca21d6b54f479bef62a4")
                            ]
                        }
                    }
                }
            ],
            as:
                "revisions"
        }
    },
])

and here's the explanation:

{
    "stages" : [
        {
            "$cursor" : {
                "query" : {

                },
                "queryPlanner" : {
                    "plannerVersion" : 1,
                    "namespace" : "test-creator.map_levels",
                    "indexFilterSet" : false,
                    "parsedQuery" : {

                    },
                    "winningPlan" : {
                        "stage" : "COLLSCAN",
                        "direction" : "forward"
                    },
                    "rejectedPlans" : [ ]
                }
            }
        },
        {
            "$lookup" : {
                "from" : "map_level_revisions",
                "as" : "revisions",
                "let" : {

                },
                "pipeline" : [
                    {
                        "$match" : {
                            "$expr" : {
                                "$eq" : [
                                    "$account_id",
                                    ObjectId("5b66ca21d6b54f479bef62a4")
                                ]
                            }
                        }
                    }
                ]
            }
        }
    ],
    "ok" : 1
}

How do I make it use an index instead?

Just a note, the query does return the document.

Answer 1

The collection scan in your explain output is referring to the map_levels collection, as noted in the queryPlanner.namespace value. The $lookup stage merges data from another collection into the current pipeline. Since you haven't specified any query stages before the $lookup, the map_levels collection will be iterated using a collection scan. If an entire collection is being loaded without any filtering or sort criteria, a collection scan has less overhead than iterating an index and fetching the documents.

You can avoid the current collection scan by adding a $match stage before your $lookup (assuming you don't want to process the full map_levels collection).

How can I check the index used by $lookup?

Unfortunately query explain output does not (as at MongoDB 4.0) indicate index usage for $lookup stages. A workaround for this would be running explain using your lookup's pipeline as a top level aggregation query.

There's a relevant issue to watch/upvote in the MongoDB Issue tracker: SERVER-22622: Improve $lookup explain to indicate query plan on the "from" collection.