[SERVER-10880] $elemMatch doesn't effectively use indexes Created: 24/Sep/13  Updated: 25/Sep/13  Resolved: 25/Sep/13

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

Type: Bug Priority: Major - P3
Reporter: Matthew Owen Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-10436 wrong index ranges when using compoun... Closed
Operating System: ALL
Participants:

 Description   

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?



 Comments   
Comment by Scott Hernandez (Inactive) [ 25/Sep/13 ]

dup of SERVER-10436

Generated at Thu Feb 08 03:24:18 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.