Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-20302

Strange execution plan for range queries with $elemMatch

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 3.0.3
    • Component/s: Querying
    • None
    • ALL
    • Hide

      Script to reproduce:

      var createDoc = function(i) {
  return {
    name : 'object' + i,
    value : i,
  };
}

var createDocs = function(i) {
  return [
  	createDoc(i),
  	createDoc(i + 1),
  	createDoc(i + 2),
  	createDoc(i + 3),
  ];
}

for (var i = 0; i < 10000; ++i) {
  db.test.insert({
    collection : createDocs(i)
  })
}

db.test.createIndex({ 'collection.value' : 1 })
db.test.find({ collection : { $elemMatch : { value : { $gte : 9000, $lte : 9010 } } } }).explain()

      I see the following output:

      { 
    "queryPlanner" : {
        "plannerVersion" : NumberInt(1), 
        "namespace" : "smartcat_isaev_dev.test", 
        "indexFilterSet" : false, 
        "parsedQuery" : {
            "collection" : {
                "$elemMatch" : {
                    "$and" : [
                        {
                            "value" : {
                                "$lte" : NumberInt(9010)
                            }
                        }, 
                        {
                            "value" : {
                                "$gte" : NumberInt(9000)
                            }
                        }
                    ]
                }
            }
        }, 
        "winningPlan" : {
            "stage" : "FETCH", 
            "filter" : {
                "collection" : {
                    "$elemMatch" : {
                        "$and" : [
                            {
                                "value" : {
                                    "$lte" : NumberInt(9010)
                                }
                            }, 
                            {
                                "value" : {
                                    "$gte" : NumberInt(9000)
                                }
                            }
                        ]
                    }
                }
            }, 
            "inputStage" : {
                "stage" : "IXSCAN", 
                "keyPattern" : {
                    "collection.value" : NumberInt(1)
                }, 
                "indexName" : "collection.value_1", 
                "isMultiKey" : true, 
                "direction" : "forward", 
                "indexBounds" : {
                    "collection.value" : [
                        "[-inf.0, 9010.0]"
                    ]
                }
            }
        }, 
        "rejectedPlans" : [

        ]
    }, 
    "serverInfo" : {
        "host" : "mongodb-dev", 
        "port" : NumberInt(27017), 
        "version" : "3.0.3", 
        "gitVersion" : "b40106b36eecd1b4407eb1ad1af6bc60593c6105"
    }, 
    "ok" : NumberInt(1)
}
      Show
      Script to reproduce: var createDoc = function (i) { return { name : 'object' + i, value : i, }; } var createDocs = function (i) { return [ createDoc(i), createDoc(i + 1), createDoc(i + 2), createDoc(i + 3), ]; } for ( var i = 0; i < 10000; ++i) { db.test.insert({ collection : createDocs(i) }) } db.test.createIndex({ 'collection.value' : 1 }) db.test.find({ collection : { $elemMatch : { value : { $gte : 9000, $lte : 9010 } } } }).explain() I see the following output: { "queryPlanner" : { "plannerVersion" : NumberInt(1), "namespace" : "smartcat_isaev_dev.test" , "indexFilterSet" : false , "parsedQuery" : { "collection" : { "$elemMatch" : { "$and" : [ { "value" : { "$lte" : NumberInt(9010) } }, { "value" : { "$gte" : NumberInt(9000) } } ] } } }, "winningPlan" : { "stage" : "FETCH" , "filter" : { "collection" : { "$elemMatch" : { "$and" : [ { "value" : { "$lte" : NumberInt(9010) } }, { "value" : { "$gte" : NumberInt(9000) } } ] } } }, "inputStage" : { "stage" : "IXSCAN" , "keyPattern" : { "collection.value" : NumberInt(1) }, "indexName" : "collection.value_1" , "isMultiKey" : true , "direction" : "forward" , "indexBounds" : { "collection.value" : [ "[-inf.0, 9010.0]" ] } } }, "rejectedPlans" : [ ] }, "serverInfo" : { "host" : "mongodb-dev" , "port" : NumberInt(27017), "version" : "3.0.3" , "gitVersion" : "b40106b36eecd1b4407eb1ad1af6bc60593c6105" }, "ok" : NumberInt(1) }

      It seems like the query planner ignores one of the range ends, and scans the index from the beginning. Obviously the query is unreasonably slow on large collections - up to 1 hour on the collection with ~20M records. Am i missing something or is it a bug?

            Assignee:
            sam.kleinman Sam Kleinman (Inactive)
            Reporter:
            nameless_twice Sergey Isaev
            Votes:
            1 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: