[SERVER-28183] Find slow with regex, geoWithin and sort Created: 03/Mar/17  Updated: 31/May/17  Resolved: 13/Mar/17

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

Type: Bug Priority: Major - P3
Reporter: Sergey Kazankov Assignee: Mark Agarunov
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Participants:

 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.



 Comments   
Comment by Mark Agarunov [ 09/Mar/17 ]

Hello kazankov,

Thank you for the report. Looking over the output provided, it seems that a compound index would be far more optimal for this query. The currently existing indices will not be fully utilized with the type of query shown.

Please note that SERVER project is for reporting bugs or feature suggestions for the MongoDB server. For MongoDB-related support discussion please post on the mongodb-user group or Stack Overflow with the mongodb tag. A question like this involving more discussion would be best posted on the mongodb-user group. Additionally, see also our Technical Support page for additional support resources.

Thanks,
Mark

Comment by Sergey Kazankov [ 03/Mar/17 ]

P.S.:

> db.collection.stats();
{
       ...
        "size" : 19387943337,
        "count" : 26001370,
        "avgObjSize" : 745,
        "storageSize" : 9354919936,
        "capped" : false,
        "wiredTiger" : {
...

Comment by Sergey Kazankov [ 03/Mar/17 ]

"66427 is much better than 70" -> "66427 is much bigger than 70"

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