[SERVER-2348] $all/$elemMatch doesn't use index correctly Created: 12/Jan/11  Updated: 06/Dec/22  Resolved: 20/Aug/19

Status: Closed
Project: Core Server
Component/s: Index Maintenance, Querying
Affects Version/s: 1.6.3
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Klaus Lehner Assignee: Backlog - Query Team (Inactive)
Resolution: Done Votes: 11
Labels: query-44-grooming
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Windows, Unix


Assigned Teams:
Query
Participants:

 Description   

I have a collection called PartOffer with a multikey called 'p'. Typical PartOffer looks like:

{title:"My offer", p:{carFuel:9023, engine:232}}

I created an index on p:

db.PartOffer.ensureIndex({p:1})

But when using dot notation to find all offers depending on a property inside p, then the index is not used:

> db.PartOffer.find({"p.carFuel":9086}).explain()
{
        "cursor" : "BasicCursor",
        "nscanned" : 65424,
        "nscannedObjects" : 65424,
        "n" : 1139,
        "millis" : 126,
        "indexBounds" : {
 
        }
}

But when not using dot notation, the index is used:

> db.PartOffer.find({p:{carFuel:9086}}).explain()
{
        "cursor" : "BtreeCursor p_1",
        "nscanned" : 21,
        "nscannedObjects" : 21,
        "n" : 21,
        "millis" : 0,
        "indexBounds" : {
                "p" : [
                        [
                                {
                                        "carFuel" : 9086
                                },
                                {
                                        "carFuel" : 9086
                                }
                        ]
                ]
        }
}



 Comments   
Comment by David Storch [ 20/Aug/19 ]

The Query Team has reviewed this case and believes that it no longer tracks a specific actionable improvement to the code base. Therefore, I am closing this ticket as "Gone Away". If you have a specific request for an optimization related to this ticket, please feel free to file a new ticket describing your needs.

Finally, note that the recent GA of version 4.2 offers wildcard indexes. Such indexes are useful for schemas that can contain arbitrary fields that are not known a priori, such as the p field in this ticket which may contain any number of subfields that are not known in advance.

Comment by Asya Kamsky [ 05/Feb/18 ]

The later given example also works and uses index on "p" if you query against object correctly:

db.PartOffer.find( { p : { $all : [ { $elemMatch :{ $gte :{carCC: 5 }, $lt : {carCC: 200}} } , { $elemMatch : { carKm : 20 } } ] } } )
{ "_id" : ObjectId("5a78c626841826511293bc85"), "p" : [ { "carCC" : 5 }, { "carKm" : 20 }, { "carManu" : 23 } ] }
test@127.0.0.1:27017(3.6.0) > db.PartOffer.find( { p : { $all : [ { $elemMatch :{ $gte :{carCC: 5 }, $lt : {carCC: 200}} } , { $elemMatch : { carKm : 20 } } ] } } ).explain()
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.PartOffer",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"$and" : [
				{
					"p" : {
						"$elemMatch" : {
							"carKm" : {
								"$eq" : 20
							}
						}
					}
				},
				{
					"p" : {
						"$elemMatch" : {
							"$lt" : {
								"carCC" : 200
							},
							"$gte" : {
								"carCC" : 5
							}
						}
					}
				}
			]
		},
		"winningPlan" : {
			"stage" : "FETCH",
			"filter" : {
				"$and" : [
					{
						"p" : {
							"$elemMatch" : {
								"$lt" : {
									"carCC" : 200
								},
								"$gte" : {
									"carCC" : 5
								}
							}
						}
					},
					{
						"p" : {
							"$elemMatch" : {
								"carKm" : {
									"$eq" : 20
								}
							}
						}
					}
				]
			},
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"p" : 1
				},
				"indexName" : "p_1",
				"isMultiKey" : true,
				"multiKeyPaths" : {
					"p" : [
						"p"
					]
				},
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"p" : [
						"[{ carCC: 5.0 }, { carCC: 200.0 })"
					]
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"serverInfo" : {
		"host" : "Asyas-MacBook-Pro.local",
		"port" : 27017,
		"version" : "3.6.0",
		"gitVersion" : "a57d8e71e6998a2d0afde7edc11bd23e5661c915"
	},
	"ok" : 1
}

Comment by Asya Kamsky [ 05/Feb/18 ]

The original example given can use index on full object if the comparison is done as follows:

db.PartOffer.find({p:{$gte:{carFuel:9086, engine:MinKey},$lte:{carFuel:9086, engine:MaxKey}}}).explain()
{
	"queryPlanner" : {
              ...
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"p" : 1
				},
				"indexName" : "p_1",
				"isMultiKey" : false,
				"multiKeyPaths" : {
					"p" : [ ]
				},
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"p" : [
						"[ { carFuel: 9086.0, engine: MinKey }, 
                                                   { carFuel: 9086.0, engine: MaxKey }]"
					]
				}
			}
		},
...

Comment by Aaron Staple [ 08/Mar/13 ]

eliot Is the suggested behavior that the query

db.PartOffer.find( { p : { $all : [ { $elemMatch : { carCC : { $gt : 5 , $lt : 200 } } } , { $elemMatch : { carKm : 20 } } ] } } )

use the index bounds [[

{carKm:20}

,

{carKm:20}

]] on the p:1 index?

I don't believe these index bounds should be used, because the

{carKm:20}

object in this context represents a query expression indicating that the carKm field has a value of 20 rather than an object to be matched. Instead, if I understand correctly, the user might choose to write the following query:

db.PartOffer.find( { $and:[ { p:{ carKm:20 } }, { p:{ $elemMatch:{ carCC:{ $gt:5, $lt:200 } } } } ] } )

Comment by Eliot Horowitz (Inactive) [ 13/Jan/11 ]

Ok, that's a bug,
Fixing the case up

Comment by Klaus Lehner [ 13/Jan/11 ]

The simpler queries can use the index:

> db.PartOffer.find( { p : { $all : [

{ carCC : 5 }

,

{ carKm: 20 }

] } } ).explain()
{
"cursor" : "BtreeCursor p_1",
"nscanned" : 1,
"nscannedObjects" : 1,
"n" : 1,
"millis" : 0,
"indexBounds" : {
"p" : [
[

{ "carCC" : 5 }

,

{ "carCC" : 5 }

]
]
}
}

and also that one:

> db.PartOffer.find( { p : { $gt :

{ carCC : 5 }

, $lt :

{ carCC : 10 }

} } ).explain()
{
"cursor" : "BtreeCursor p_1",
"nscanned" : 1,
"nscannedObjects" : 0,
"n" : 0,
"millis" : 0,
"indexBounds" : {
"p" : [
[

{ "carCC" : 5 }

,

{ "carCC" : 10 }

]
]
}
}

but not the mixture of both.

Comment by Klaus Lehner [ 13/Jan/11 ]

that does return the correct results, yes, but again does not use the index on p:

> db.PartOffer.insert({p: [

{carCC:5}

,

{carKm:20}

,

{carManu:23}

]})
> db.PartOffer.ensureIndex(

{p:1}

)
> db.PartOffer.find( { p : { $all : [ { $elemMatch : { carCC :

{ $gt : 5 , $lt : 200 }

} } , { $elemMatch :

{ carKm : 20 }

} ] } } ).explain()
{
"cursor" : "BasicCursor",
"nscanned" : 1,
"nscannedObjects" : 1,
"n" : 0,
"millis" : 0,
"indexBounds" : {

}
}

Comment by Eliot Horowitz (Inactive) [ 13/Jan/11 ]

You can do that

> db.PartOffer.find( { p : { $all : [ { $elemMatch : { carCC :

{ $gt : 5 , $lt : 200 }

} } , { $elemMatch :

{ carKm : 20 }

} ] } } )

Comment by Klaus Lehner [ 13/Jan/11 ]

With $elemMatch I can only search for one entry in the array as described here:

http://www.mongodb.org/display/DOCS/Advanced+Queries#AdvancedQueries-%24elemMatch

thus I cannot do things like the ones I added in the comment above:

no, $elemMatch isn't suitable as well, cause I can't do things like that:
db.PartOffer.insert({p: [

{carCC:5}

,

{carKm:20}

,

{carManu:23}

]})
that document won't be returned with the following query:
find({p:{$elemMatch:

{carCC:5,carKm:20}

}})

----------

as a summary, what I need is the ability to search by a number of properties with one query, using equality and min/max ranges and using one multi-index. seems this is not possible.

Comment by Eliot Horowitz (Inactive) [ 13/Jan/11 ]

Right, you need to use $elemMatch.

> db.PartOffer.insert({p: [

{carCC:5}

,

{carKm:20}

,

{carManu:23}

]})
> db.PartOffer.find( { p : { $elemMatch : { carCC :

{ $gt : 5 , $lt : 200 }

} } } )
> db.PartOffer.insert({p: [

{carCC:6}

,

{carKm:20}

,

{carManu:23}

]})
> db.PartOffer.find( { p : { $elemMatch : { carCC :

{ $gt : 5 , $lt : 200 }

} } } )
{ "_id" : ObjectId("4d2ec73eec5855af36e28668"), "p" : [

{ "carCC" : 6 }

,

{ "carKm" : 20 }

,

{ "carManu" : 23 }

] }

Comment by Klaus Lehner [ 13/Jan/11 ]

Sorry, your last query is not correct, it even returns the document if the ranges don't match:

> db.PartOffer.find({p:{$gt:

{carFuel:100}

,$lt:

{carFuel:200}

}})

returned:

{ "_id" : ObjectId("4d2eb51bbb5b0e6decdf2499"), "p" : [

{ "carFuel" : 5 }

,

{ "carABC" : 9 }

,

{ "carManu" : 8 }

]}

and 5 is not between 100 and 200.

and what I also can't do with that construct is to mix exact matches with range matches; of course I could simulate an exact match with a range match by setting min==max but I don't think that's very performant

with dot-notation that seems easier:

find({"p.carFuel":{$gt:4,$lt:10},"p.carManu":8})

works fine, but again without being able to use the multi-index on p

Comment by Eliot Horowitz (Inactive) [ 12/Jan/11 ]

To match

db.PartOffer.insert({p: [

{carCC:5}

,

{carKm:20}

,

{carManu:23}

]})

you would do

find( { p : { $all : [

{ carCC : 5 }

,

{ catKm: 20 }

] } } )

for lt, gt

find( { p : { $gt :

{ catCC : 5 }

, $lt :

{ catCC : 10 }

} } )

Comment by Klaus Lehner [ 12/Jan/11 ]

no, $elemMatch isn't suitable as well, cause I can't do things like that:

db.PartOffer.insert({p: [

{carCC:5}

,

{carKm:20}

,

{carManu:23}

]})

that document won't be returned with the following query:

find({p:{$elemMatch:

{carCC:5,carKm:20}

}})

use case behind: a document can have a number of properties (p) and the user may filter the list of all documents by one to n such properties.

it works with the dot-notation:

find(

{"p.carCC":5,"carKm":20}

)

but then again without the possibiliy of a multi-index on p

Comment by Eliot Horowitz (Inactive) [ 12/Jan/11 ]

Take a look at $elemMatch as well

Comment by Klaus Lehner [ 12/Jan/11 ]

Hm, $all does not work If I'm not querying for exact matches in p but for min-max values:

p :{ "$all" : [ { "carCC" : { "$lte" : 2200 , "$gte" : 500}}]}

here I need to again use

{p.carCC : { "$lte" : 2200 , "$gte" : 500}}

but then I again have no index for that query

Comment by Klaus Lehner [ 12/Jan/11 ]

Ah, just found out that it might work using $all.

Hopefully not running into other issues there (http://jira.mongodb.org/browse/SERVER-1745)

I'll let you know.

Comment by Eliot Horowitz (Inactive) [ 12/Jan/11 ]

You can use $all, though I'm not really sure what your data looks like given snippets.

Comment by Klaus Lehner [ 12/Jan/11 ]

Eliot,

thanks for your hint, I tried that:

Having one entry:

> db.PartOffer.find()
{ "_id" : ObjectId("4d2deac121b0c02f6c9dd289"), "p" : [

{ "carFuel" : 5 }

,

{ "carManu" : 8 }

] }

ensuring index on p:

> db.PartOffer.ensureIndex(

{p:1}

)

and then querying for exact match:

> db.PartOffer.find(

{"p.carFuel":5,"p.carManu":8}

).explain()
{
"cursor" : "BasicCursor",
"nscanned" : 1,
"nscannedObjects" : 1,
"n" : 1,
"millis" : 0,
"indexBounds" : {

}
}

Using the following query uses the index:

> db.PartOffer.find({p:{"carFuel":5}}).explain()
{
"cursor" : "BtreeCursor p_1",
"nscanned" : 1,
"nscannedObjects" : 1,
"n" : 1,
"millis" : 1,
"indexBounds" : {
"p" : [
[

{ "carFuel" : 5 }

,

{ "carFuel" : 5 }

]
]
}
}

but the problem here is that I can't query by more than one item inside p. index is then still used, but no results are returned:

> db.PartOffer.find({p:{"carFuel":5,"carManu":8}}).explain()
{
"cursor" : "BtreeCursor p_1",
"nscanned" : 0,
"nscannedObjects" : 0,
"n" : 0,
"millis" : 0,
"indexBounds" : {
"p" : [
[

{ "carFuel" : 5, "carManu" : 8 }

,

{ "carFuel" : 5, "carManu" : 8 }

]
]
}
}

http://www.mongodb.org/display/DOCS/Advanced+Queries#AdvancedQueries-ValueinanArray says:
"Note that a single array element must match all the criteria specified"

I'm not sure if that is 100% what I experience here.

Thanks again for your effort!

Comment by Eliot Horowitz (Inactive) [ 12/Jan/11 ]

For that case we recomned

{ p : [

{ carFuel : 1 }

,

{ foo : 2 }

] }

Comment by Klaus Lehner [ 12/Jan/11 ]

Ok, but then this should be pointed out in the docs, as to me it looked like

{ "p.carFuel" : 1 }

is just an abbreviation for {p:{carFuel:9086}} but considering indexes this is then not the case, and multikeys as described here (http://www.mongodb.org/display/DOCS/Using+Multikeys+to+Simulate+a+Large+Number+of+Indexes) then also don't work with dot notation.

just read in the docs (http://www.mongodb.org/display/DOCS/Dot+Notation+%28Reaching+into+Objects%29) that querying subdocuments only works if they match entirely (last section in "Dot Notation vs. Subobjects"), that means that I can't use my construction anyways.

Having an index on p.carFuel is not an option for me, as the number of properties in p is variable.

Comment by Eliot Horowitz (Inactive) [ 12/Jan/11 ]

That is correct behavior
You need to put an index on (

{ "p.carFuel" : 1 }

)

Generated at Thu Feb 08 02:59:42 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.