-
Type:
Bug
-
Resolution: Done
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: Performance
-
Labels:None
-
ALL
I have the unstable query which can take from 1 second to 400 (!!!) seconds. Here it is:
db.collection.find({
"name":/^gra/,
"geoPoint":{"$geoWithin":{"$geometry":{"type":"Polygon","coordinates":[[[-3.8889538293339,40.311977368327],[-3.5179162894955,40.311977368327],[-3.5179162894955,40.643729266463],[-3.8889538293339,40.643729266463],[-3.8889538293339,40.311977368327]]], }}},
"verified":true})
.sort({'rank': -1}).limit(10);
indexes:
db.collection.ensureIndex({'geoPoint': '2dsphere'});
db.collection.ensureIndex({'rank':-1});
db.collection.ensureIndex({'name': 1});
db.collection.ensureIndex({'verified': 1});
statistics:
Without limit:
> db.collection.find({ "name":/^gra/,...).explain('executionStats');
...
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 70,
"executionTimeMillis" : 9037,
"totalKeysExamined" : 66427,
"totalDocsExamined" : 66426,
...
66427 is much better than 70 and it is probably bad.
With other name:
> db.collection.find({"name":/^hue/ ...).explain('executionStats');
...
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 17,
"executionTimeMillis" : 452,
"totalKeysExamined" : 3940,
"totalDocsExamined" : 3939,
...
Much faster! But totalDocsExamined is only 3939.
And with limit! :
> db.collection.find({"name":/^gra/,...).limit(10).explain('executionStats');
...
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 10,
"executionTimeMillis" : 401500,
"totalKeysExamined" : 66427,
"totalDocsExamined" : 66426,
...
401 seconds! With all indexes.