Details
-
Bug
-
Resolution: Done
-
Major - P3
-
None
-
None
-
None
-
ALL
Description
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.