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

$elemMatch doesn't effectively use indexes

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

      I'm using 2.2.0, and attempting to find documents which have an array element that meets two criteria: a string match, and a date range. I have a compound index on those fields, yet I'm seeing huge scans that indicate that the date range part of the query is not using the index. Given my understanding of how mongodb indexes, I'd expect it to be possible to use an index to retrieve the results I'm looking for.

      I'm running the following:

      db.item.find({
          work_ops: {
              $elemMatch: {
                  worker_name: "Carly",
                  created_dt: {
                      $gte: new Date(2013, 8, 23)
                  }
              }
          }
      }).explain()
      

      where items have an array of work_ops, each of which have a worker_name and a created_dt.

      The result I get is

      {
      	"cursor" : "BtreeCursor work_ops.worker_name_1_work_ops.created_dt_1",
      	"isMultiKey" : true,
      	"n" : 1323,
      	"nscannedObjects" : 48480,
      	"nscanned" : 48480,
      	"nscannedObjectsAllPlans" : 48480,
      	"nscannedAllPlans" : 48480,
      	"scanAndOrder" : false,
      	"indexOnly" : false,
      	"nYields" : 241,
      	"nChunkSkips" : 0,
      	"millis" : 634,
      	"indexBounds" : {
      		"work_ops.worker_name" : [
      			[
      				"Carly",
      				"Carly"
      			]
      		],
      		"work_ops.created_dt" : [
      			[
      				{
      					"$minElement" : 1
      				},
      				{
      					"$maxElement" : 1
      				}
      			]
      		]
      	}
      }
      

      (running the query with verbosity: true doesn't add any additional plans).

      Is this a problem with my code, a problem with mongodb, or is there some deep complexity I'm missing?

            Assignee:
            Unassigned Unassigned
            Reporter:
            mattowen Matthew Owen
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated:
              Resolved: