[SERVER-12965] $geoIntersects is very slow compared to 2.4 Created: 28/Feb/14  Updated: 10/Dec/14  Resolved: 05/Mar/14

Status: Closed
Project: Core Server
Component/s: Geo
Affects Version/s: 2.6.0-rc0
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Pavel Baranov Assignee: Amalia Hawkins
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: File geotest.py    
Operating System: ALL
Participants:

 Description   

Here are the indexes:

db.usa.getIndexes();
[
        {
                "v" : 1,
                "key" : {
                        "_id" : 1
                },
                "ns" : "zcta.usa",
                "name" : "_id_"
        },
        {
                "v" : 1,
                "key" : {
                        "coords" : "2dsphere"
                },
                "ns" : "zcta.usa",
                "name" : "coords_2dsphere"
        }
]

Mongo 2.4 explain

db.usa.find( {"coords":{"$geoIntersects":{"$geometry":{"type":"Point","coordinates":[-73.909472,40.876547]}}}}, {"city":1} ).explain();
{
        "cursor" : "S2Cursor",
        "isMultiKey" : true,
        "n" : 1,
        "nscannedObjects" : 1,
        "nscanned" : 55,
        "nscannedObjectsAllPlans" : 1,
        "nscannedAllPlans" : 55,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 34,
        "indexBounds" : {
 
        },
        "nscanned" : 55,
        "matchTested" : NumberLong(9),
        "geoTested" : NumberLong(9),
        "cellsInCover" : NumberLong(1),
        "server" : ""
}

Mongo 2.6 explain:

db.usa.find( {"coords":{"$geoIntersects":{"$geometry":{"type":"Point","coordinates":[-73.909472,40.876547]}}}}, {"city":1} ).explain();
{
        "cursor" : "BtreeCursor coords_2dsphere",
        "isMultiKey" : true,
        "n" : 1,
        "nscannedObjects" : 9,
        "nscanned" : 58,
        "nscannedObjectsAllPlans" : 63,
        "nscannedAllPlans" : 112,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 31,
        "nChunkSkips" : 0,
        "millis" : 4118,
        "indexBounds" : {
                "coords" : [
                        [
                                "4f1032011",
                                "4f1032011"
                        ],
                        [
                                "4f10320113",
                                "4f10320113"
                        ],
                        [
                                "4f103201132",
                                "4f103201132"
                        ],
                        [
                                "4f1032011321",
                                "4f1032011321"
                        ],
                        [
                                "4f10320113213",
                                "4f10320113214"
                        ]
                ]
        },
        "server" : "",
        "filterSet" : false
}

2 second difference.



 Comments   
Comment by Pavel Baranov [ 05/Mar/14 ]

Awesome. Thank you!

Comment by Amalia Hawkins [ 05/Mar/14 ]

Actually, after further investigation, I believe the commit responsible for fixing this may be a3d910925350d2f0204b41ea145e24f74e5c39ce. Let us know if you see anything else!

Comment by Amalia Hawkins [ 05/Mar/14 ]

Ahh, I see now, my mistake. I will keep an eye on this in the next release candidate in case of regression. Thank you for the heads up.

Comment by Pavel Baranov [ 04/Mar/14 ]

I see, but was more referring to:

"cursor" : "BtreeCursor coords_2dsphere",
"isMultiKey" : true,
"n" : 1,
"nscannedObjects" : 9,
"nscanned" : 58,
"nscannedObjectsAllPlans" : 9,
"nscannedAllPlans" : 58,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 1,
"nChunkSkips" : 0,
"millis" : 21

VS.

"cursor" : "BtreeCursor coords_2dsphere",
"isMultiKey" : true,
"n" : 1,
"nscannedObjects" : 9,
"nscanned" : 58,
"nscannedObjectsAllPlans" : 63,
"nscannedAllPlans" : 112,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 31,
"nChunkSkips" : 0,
"millis" : 4118,

It's the same dataset, same index, just 2.6.0-rc0 vs nightly. quite a difference in numbers and everything executes faster - just saying I'm glad it's working though (might have been a fix that we're not aware of)

Thanks!

Comment by Amalia Hawkins [ 04/Mar/14 ]

Yes, it does – good catch. We're in the process of revamping the explain output to provide more useful information, so if you keep an eye on the nightlies you'll see some new stats appearing.

Comment by Pavel Baranov [ 04/Mar/14 ]

Btw, this is the explain from the following version: mongodb-linux-x86_64-914a26f51ff7ffeb28922273925318da6a6eba71-2014-02-28

db.usa.find( {"coords":{"$geoIntersects":{"$geometry":{"type":"Point","coordinates":[-73.909472,40.876547]}}}}, {"city":1} ).explain();
{
        "cursor" : "BtreeCursor coords_2dsphere",
        "isMultiKey" : true,
        "n" : 1,
        "nscannedObjects" : 9,
        "nscanned" : 58,
        "nscannedObjectsAllPlans" : 9,
        "nscannedAllPlans" : 58,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 1,
        "nChunkSkips" : 0,
        "millis" : 21,
        "indexBounds" : {
                "coords" : [
                        [
                                "4f1032011",
                                "4f1032011"
                        ],
                        [
                                "4f10320113",
                                "4f10320113"
                        ],
                        [
                                "4f103201132",
                                "4f103201132"
                        ],
                        [
                                "4f1032011321",
                                "4f1032011321"
                        ],
                        [
                                "4f10320113213",
                                "4f10320113214"
                        ]
                ]
        },
        "server" : "",
        "filterSet" : false,
        "stats" : {
                "type" : "PROJECTION",
                "works" : 55,
                "yields" : 1,
                "unyields" : 1,
                "invalidates" : 0,
                "advanced" : 1,
                "needTime" : 0,
                "needFetch" : 0,
                "isEOF" : 1,
                "children" : [
                        {
                                "type" : "KEEP_MUTATIONS",
                                "works" : 55,
                                "yields" : 1,
                                "unyields" : 1,
                                "invalidates" : 0,
                                "advanced" : 1,
                                "needTime" : 53,
                                "needFetch" : 0,
                                "isEOF" : 1,
                                "children" : [
                                        {
                                                "type" : "FETCH",
                                                "works" : 55,
                                                "yields" : 1,
                                                "unyields" : 1,
                                                "invalidates" : 0,
                                                "advanced" : 1,
                                                "needTime" : 53,
                                                "needFetch" : 0,
                                                "isEOF" : 1,
                                                "alreadyHasObj" : 0,
                                                "forcedFetches" : 0,
                                                "matchTested" : 1,
                                                "children" : [
                                                        {
                                                                "type" : "IXSCAN",
                                                                "works" : 54,
                                                                "yields" : 1,
                                                                "unyields" : 1,
                                                                "invalidates" : 0,
                                                                "advanced" : 9,
                                                                "needTime" : 45,
                                                                "needFetch" : 0,
                                                                "isEOF" : 1,
                                                                "keyPattern" : "{ coords: \"2dsphere\" }",
                                                                "boundsVerbose" : "field #0['coords']: [\"4f1032011\", \"4f1032011\"], [\"4f10320113\", \"4f10320113\"],  \"4f103201132\", \"4f103201132\"], [\"4f1032011321\", \"4f1032011321\"], [\"4f10320113213\", \"4f10320113214\")",
                                                                "isMultiKey" : 1,
                                                                "yieldMovedCursor" : 0,
                                                                "dupsTested" : 54,
                                                                "dupsDropped" : 45,
                                                                "seenInvalidated" : 0,
                                                                "matchTested" : 0,
                                                                "keysExamined" : 58,
                                                                "children" : [ ]
                                                        }
                                                ]
                                        }
                                ]
                        }
                ]
        }
}

Looks different from 2.6.0-rc0

Comment by Amalia Hawkins [ 04/Mar/14 ]

I was unable to confirm any loss of performance in $geoIntersects between 2.4.9 and the current 2.6.0-rc0. I've attached the basic script I used for testing in case anyone wants to perform further investigation. Closing this ticket for now.

Comment by Daniel Pasette (Inactive) [ 01/Mar/14 ]

Thanks for the report Pavel. One-off tests using explain are often not a very reliable query latency experiment. The results are impacted by whether working set is in memory and time spent in query planning.

We'll do some additional testing to make sure.

Comment by Pavel Baranov [ 01/Mar/14 ]

Just tried the latest version ( mongodb-linux-x86_64-914a26f51ff7ffeb28922273925318da6a6eba71-2014-02-28 )
And it seems a lot better.

{
        "cursor" : "BtreeCursor coords_2dsphere",
        "isMultiKey" : true,
        "n" : 1,
        "nscannedObjects" : 9,
        "nscanned" : 58,
        "nscannedObjectsAllPlans" : 9,
        "nscannedAllPlans" : 58,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 1,
        "nChunkSkips" : 0,
        "millis" : 29,
        "indexBounds" : {
                "coords" : [
                        [
                                "4f1032011",
                                "4f1032011"
                        ],
                        [
                                "4f10320113",
                                "4f10320113"
                        ],
                        [
                                "4f103201132",
                                "4f103201132"
                        ],
                        [
                                "4f1032011321",
                                "4f1032011321"
                        ],
                        [
                                "4f10320113213",
                                "4f10320113214"
                        ]
                ]
        },
        "server" : "",
        "filterSet" : false
}

Comment by Pavel Baranov [ 28/Feb/14 ]

Actually 4 seconds (4118) in this particular one, my other tests were 2 seconds.

Generated at Thu Feb 08 03:30:13 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.