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

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

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Major - P3
    • Resolution: Won't Fix
    • 2.2.3, 2.4.0-rc0
    • None
    • Querying
    • None
    • Windows Server 2008 R2
    • Windows

    Description

      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.

      Attachments

        Issue Links

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved: