[SERVER-16169] $elemMatch with nested $or doesn't use index on 2.4 Created: 15/Nov/14  Updated: 24/Jan/15  Resolved: 11/Jan/15

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

Type: Bug Priority: Minor - P4
Reporter: Alexander Komyagin Assignee: David Storch
Resolution: Won't Fix Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Backwards Compatibility: Fully Compatible
Operating System: ALL
Participants:

 Description   

It's technically possible to nest an $or under $elemMatch with the intent of finding array elements that match at least one criteria. However, in 2.4 the index is not used for that search. The new query engine on 2.6.5 handles the case as expected.

Here is a repro from 2.4:

db.ggg.drop()
db.ggg.insert({party:[{a:5,b:10},{a:3,b:2}]})
db.ggg.insert({party:[{a:5,b:10},{a:4,b:3}]})
db.ggg.insert({party:[{a:5,b:0},{a:4,b:3}]})
db.ggg.insert({party:[{a:1,b:0},{a:4,b:3}]})
db.ggg.insert({party:[{a:0,b:1},{a:2,b:0}]})
db.ggg.ensureIndex({"party.a":1,"party.b":1})

Output from 2.4:

> db.ggg.find({ "party": { $elemMatch: { $or: [ {  "a" : 5,  "b" : 10 },{  "a" : 3,  "b" : 2 } ]} } }).explain()
{
	"cursor" : "BasicCursor",
	"isMultiKey" : false,
	"n" : 2,
	"nscannedObjects" : 5,
	"nscanned" : 5,
	"nscannedObjectsAllPlans" : 5,
	"nscannedAllPlans" : 5,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
 
	},
	"server" : "AD-MAC10G.local:27017"
}

Output from 2.6 for comparison:

db.ggg.find({ "party": { $elemMatch: { $or: [ {  "a" : 5,  "b" : 10 },{  "a" : 3,  "b" : 2 } ]} } }).explain()
{
	"clauses" : [
		{
			"cursor" : "BtreeCursor party.a_1_party.b_1",
			"isMultiKey" : true,
			"n" : 2,
			"nscannedObjects" : 2,
			"nscanned" : 2,
			"scanAndOrder" : false,
			"indexOnly" : false,
			"nChunkSkips" : 0,
			"indexBounds" : {
				"party.a" : [
					[
						5,
						5
					]
				],
				"party.b" : [
					[
						10,
						10
					]
				]
			}
		},
		{
			"cursor" : "BtreeCursor party.a_1_party.b_1",
			"isMultiKey" : true,
			"n" : 1,
			"nscannedObjects" : 1,
			"nscanned" : 1,
			"scanAndOrder" : false,
			"indexOnly" : false,
			"nChunkSkips" : 0,
			"indexBounds" : {
				"party.a" : [
					[
						3,
						3
					]
				],
				"party.b" : [
					[
						2,
						2
					]
				]
			}
		}
	],
	"cursor" : "QueryOptimizerCursor",
	"n" : 2,
	"nscannedObjects" : 3,
	"nscanned" : 3,
	"nscannedObjectsAllPlans" : 3,
	"nscannedAllPlans" : 3,
	"scanAndOrder" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"server" : "AD-MAC10G.local:37017",
	"filterSet" : false
}



 Comments   
Comment by Daniel Pasette (Inactive) [ 11/Jan/15 ]

This is not present in vesions >= 2.6 when the query optimizer was re-written.

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