CODEWITHSUNDEEP
mongodbmongodb-querygeospatialmongodb-aggregation
Eric
Eric

Reputation: 1557

Mongodb geonear and aggregate very slow

My current mongo version is 2.4.9 and collection have around 2.8millions rows. My query take super long to finish when using $geonear in the query.

Example of my collection

"loc" : {
    "type" : "Point",
    "coordinates" : [ 
        100.46589473, 
        5.35149077
    ]
},
"email" : "[email protected]"

loc index

{
    "v" : 1,
    "key" : {
        "loc" : "2dsphere"
    },
    "ns" : "test.collect",
    "name" : "loc_2dsphere",
    "background" : true
}

Tested this query will took around 10 to 15minute to finish

db.getCollection('collect').aggregate(
  [
  { '$match':
         {'loc':
             {'$geoNear':
                 {'$geometry':
                     {'type':'Point','coordinates':[101.6862,3.0829],'$maxDistance':10000}
                  }
             }
         } 
  },
  {'$group':{'_id':'email', 'email':{'$last':'$email'},'loc':{'$last':'$loc'} }}
  ])

Below are explain result

{
"serverPipeline" : [ 
    {
        "query" : {
            "loc" : {
                "$geoNear" : {
                    "$geometry" : {
                        "type" : "Point",
                        "coordinates" : [ 
                            101.6862, 
                            3.0829
                        ],
                        "$maxDistance" : 10000
                    }
                }
            }
        },
        "projection" : {
            "email" : 1,
            "loc" : 1,
            "_id" : 0
        },
        "cursor" : {
            "cursor" : "S2NearCursor",
            "isMultiKey" : true,
            "n" : 111953,
            "nscannedObjects" : 111953,
            "nscanned" : 96677867,
            "nscannedObjectsAllPlans" : 111953,
            "nscannedAllPlans" : 96677867,
            "scanAndOrder" : false,
            "indexOnly" : false,
            "nYields" : 183,
            "nChunkSkips" : 0,
            "millis" : 895678,
            "indexBounds" : {},
            "nscanned" : NumberLong(96677867),
            "matchTested" : NumberLong(3472481),
            "geoMatchTested" : NumberLong(3472481),
            "numShells" : NumberLong(53),
            "keyGeoSkip" : NumberLong(93205386),
            "returnSkip" : NumberLong(20148837),
            "btreeDups" : NumberLong(0),
            "inAnnulusTested" : NumberLong(3472481),
            "allPlans" : [ 
                {
                    "cursor" : "S2NearCursor",
                    "n" : 111953,
                    "nscannedObjects" : 111953,
                    "nscanned" : 96677867,
                    "indexBounds" : {}
                }
            ],
            "server" : "xxx:xxx"
        }
    }, 
    {
        "$group" : {
            "_id" : {
                "$const" : "email"
            },
            "email" : {
                "$last" : "$email"
            },
            "loc" : {
                "$last" : "$loc"
            }
        }
    }
],
"ok" : 1
}

Is my query inappropriate, anything else I can do to improve the speed??

Upvotes: 1

Views: 1918

Answers (3)

gaurav arora
gaurav arora

Reputation: 315

$geoNear with aggregate framework works way faster than below(find query)

"$near": {
        "$geometry": {
            "type": "Point",
            "coordinates": [lng,lat],
        },
},

somewhere my mongodb also went to "no socket available" by the latter, but former worked great (<20ms) [tested also from golang + mgo drivers]

Upvotes: 0

kevinadi
kevinadi

Reputation: 13815

To add to venkat's answer (using $geoNear in the aggregation pipeline directly, instead of using it under a $match):

Also, MongoDB 2.4 was released in March 2013, and is not supported anymore. If possible, I would recommend you to upgrade to the latest version (currently 3.2.10). There are many bugfixes and performance improvements in the newer version.

Upvotes: 1

venkat.s
venkat.s

Reputation: 140

Try to use $geoNear aggregation pipeline directly

https://docs.mongodb.com/v2.4/reference/operator/aggregation/geoNear/

Upvotes: 3

Related Questions