I'm using MongoDB 4.4 pipeline aggregate to retrieve documents sorted by distance from a collection of around 1.5M documents. While I'm only retrieving 12 documents at the time, I CANNOT limit the maxDistance used as I'm sorting and paging results.

The following query is very slow (unless the second argument in the $or sub-query is removed). The culprit is clearly the $or sub-query. Running the $or sub-query independently yields very fast results so I suspect it has to do with the combination of $geonear and $or.

I have created indices created for location, reference and _id of course. I also have composite indices for all three

db.mydocs.aggregate([{ "$geoNear": { "query": { "$or": [{ "_id": { "$in": ["2cf98ef9-419b-4e77-b556-631837d429dc"] } }, { "reference": "6013eb0ab006f82c6bf54003" } ] }, "maxDistance": 3.1357118857747963, "distanceMultiplier": 6378.137, "near": [-0.12775, 51.50735], "spherical": true, "distanceField": "distance", "key": "location" } }, { "$skip": 0 }, { "$limit": 12 } ])

I cannot understand why this query is slow when I have created the appropriate indexes. Using explain() shows that a composite index is being used but the query is still too slow.

This was also report on https://stackoverflow.com/questions/66576357/mongodb-aggregate-geonear-function-very-slow-when-using-with-or-sub-query