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

$elemMatch (objects in array) not using index range correctly

    • Type: Icon: Bug Bug
    • Resolution: Won't Fix
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 2.2.3, 2.4.0-rc0
    • Component/s: Querying
    • None
    • Environment:
      Windows Server 2008 R2
    • Windows

      Let's say I have a collection of 100.000 objects like this:

      {"user":"bill","created":"2013-10-10","updates":[

      {"uDate":"2013-10-10",...}

      ,

      {"uDate":"2013-10-11",...}

      ]}

      And the following index that should be used on my queries:

      {"updates.uDate":1}

      The query:

      db.users.find({"updates":{$elemMatch:{"uDate":{$gte:"2013-10-10",$lt:"2013-10-12"}}}}).count();

      if I try to query this way, the results are correct but the usage of index is not, that's why it takes so much time to get results.
      This has a total of 559 results, what is correct, but to try to understand the delay on this query, I tried:

      db.users.find({"updates":{$elemMatch:{"uDate":{$gte:"2013-10-10",$lt:"2013-10-12"}}}}).explain();

      So I got:

      {
      "cursor" : "BtreeCursor userDate",
      "isMultiKey" : true,
      "n" : 559,
      "nscannedObjects" : 434513,
      "nscanned" : 434513,
      "nscannedObjectsAllPlans" : 434513,
      "nscannedAllPlans" : 434513,
      "scanAndOrder" : false,
      "indexOnly" : false,
      "nYields" : 3,
      "nChunkSkips" : 0,
      "millis" : 4547,
      "indexBounds" : {
      "updates.uDate" : [
      [
      "2013-10-10",
      {

      }
      ]
      ]
      },
      "server" : "fzdv1:27017"
      }

      After thinking a lot, I could not realise why the index does not have an End (2013-10-12), just a Start (2013-10-10).. a RANGE.
      It could be because it's a MultiKey index, so what about forcing the index to work as expected:

      db.users.find().min(

      {"updates.uDate":"2013-10-10"}

      ).max(

      {"updates.uDate":"2013-10-12"}

      ).hint(

      {"updates.uDate":1}

      ).explain();

      {
      "cursor" : "BtreeCursor userDate",
      "isMultiKey" : true,
      "n" : 559,
      "nscannedObjects" : 559,
      "nscanned" : 560,
      "nscannedObjectsAllPlans" : 559,
      "nscannedAllPlans" : 560,
      "scanAndOrder" : false,
      "indexOnly" : false,
      "nYields" : 0,
      "nChunkSkips" : 0,
      "millis" : 9,
      "indexBounds" : {
      "start" :

      { "updates.uDate" : "2013-10-10" }

      ,
      "end" :

      { "updates.uDate" : "2013-10-12" }

      },
      "server" : "fzdv1:27017"
      }

      Perfect..it worked really fast and with a correct result of 559 objects. My "workaround" would solve my problem but not yet.
      What I need is to use aggregate function so that I can $unwind the "updates.uDate".
      Using $match produces the same index error/delay and, as far as I know, there is no way to force index range ("hint"/"min"/"max") on an aggregate $match.

      Because of that, my only solution is to break my users collection into two, "users" and "users_updates". Then I could use something like:

      db.users.find({"created":{$gte:"2013-10-10",$lt:"2013-10-12"}}).explain();

      {
      "cursor" : "BtreeCursor created",
      "isMultiKey" : false,
      "n" : 126,
      "nscannedObjects" : 126,
      "nscanned" : 126,
      "nscannedObjectsAllPlans" : 126,
      "nscannedAllPlans" : 126,
      "scanAndOrder" : false,
      "indexOnly" : false,
      "nYields" : 0,
      "nChunkSkips" : 0,
      "millis" : 8,
      "indexBounds" :

      { "created" : [ [ "2013-10-10", "2013-10-12" ] ] }

      ,
      "server" : "fzdv1:27017"
      }

      Why $elemMatch queries only works with indexes (range - start/end) as expected when forced to? Why querying objects in an array of objects does not uses indexes as normal queries since it is technicaly possible, as shown above?

      Here is the query that would solve my problem if it worked properly with its index range (start/END).

      db.users.aggregate(
      {$match : {"updates":{$elemMatch:{"uDate":{$gte:"2013-10-10",$lt:"2013-10-12"}}}}},
      {$unwind : "$updates"},
      {$project : {"updates.uDate":1}},
      {$match : {"updates.uDate":{$gte:"2013-10-10",$lte:"2013-10-12"}}},
      {$sort : {"updates.uDate":1}}
      );

      Am I missing something or it's a real issue?

      Thanks.

            Assignee:
            aaron Aaron Staple
            Reporter:
            jemanso Eduardo Manso
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Created:
              Updated:
              Resolved: