[SERVER-36133] Option "query" of pipeline "$geoNear" can not use the indexes Created: 14/Jul/18  Updated: 09/Feb/20  Resolved: 27/Jul/18

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Trung Le [X] Assignee: Kyle Suarez
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Sprint: Query 2018-08-13
Participants:

 Description   

I have an aggregation to find players have score near someone's score:

 

db.collection.aggregate([

   {

      $geoNear: {
         limit: 10,
         near: [0, 0],
         distanceField: "range",
         query: {playerId: {$ne: ObjectId("5b3d905d0e79f323249ff14e")}, score: {$gt: 0}, seasonId: ObjectId("5b447af4d4e83558ee3b30d3")}
      }

   }

]);

 

I created indexes for playerId field and seasonId field. When I do a normal find query with playerId or seasonId, I use the indexes of them. But when I run the above aggregation query, the option query of "$geoNear" can not use the indexes.

 

So, please improve this feature, thanks so much.



 Comments   
Comment by Dennis Smal' [ 09/Feb/20 ]

How about elemMatch in query inside geoNear?
It seems MongoDB doesn't support this index.

https://stackoverflow.com/questions/55716968/performance-when-filtering-after-geonear-query

Comment by Kyle Suarez [ 27/Jul/18 ]

Hey Rubilk,

It's been a while since we've heard from you, so I'm going to close this ticket. If you still have a problem, please re-open this ticket and elaborate the details on what queries aren't using indexes.

Thanks,
Kyle

Comment by Kyle Suarez [ 16/Jul/18 ]

Hi Rubilk,

The $geoNear aggregation stage requires the use of a geospatial index so that it can return results sorted by increasing distance from the "near" field. If you would like the "query" option to be satisfied by the index, you can create a compound index on both the geo field and the query field. For example, if you had a query like

> db.coll.aggregate({$geoNear: {near: [24.00, -50.00], query: {type: "restaurant"}, distanceField: "dist", spherical: true}})

You could create this index:

> db.coll.createIndex({location: "2dsphere", type: 1})
{
        "createdCollectionAutomatically" : true,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}

The aggregation would then be a covered query because the index can satisfy both the geospatial portion as well as the predicate on "type":

{
  // ...
  "winningPlan": {
    "stage" : "PROJECTION",
    "transformBy" : {"$dis" : {"$meta" : "geoNearDistance"}},
    "inputStage" : {
      "stage" : "GEO_NEAR_2DSPHERE",
      "keyPattern" : {"location" : "2dsphere", "type" : 1},
      "indexName" : "location_2dsphere_type_1",
      "indexVersion" : 2
    }
  }
}

Does this satisfy your use case?

Best,
Kyle

Generated at Thu Feb 08 04:42:08 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.