[SERVER-17221] Query on key-val array using $all and $elemMatch does not seem to correctly use indexes Created: 07/Feb/15  Updated: 09/Feb/15  Resolved: 09/Feb/15

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

Type: Bug Priority: Major - P3
Reporter: Pep Martinez Assignee: David Storch
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-16256 $all clause with elemMatch uses wider... Closed
Operating System: ALL
Participants:

 Description   

Having a collection with elements with a 'tags' array, where each element in the array is {k:<key>, v:<value>}, with an index {'tags.k': 1, 'tags.v': 1}

A query of the form
db.coll.find({tags : { "$elemMatch" : { k : "somekey", v : "somevalue" } } })

correctly makes use of the index. However, a query like:
db.coll.find({tags : {$all: [ { "$elemMatch" : { k : "somekey", v : "somevalue" } } ] } })

does not make use of the index and produces a full scan
(note: the use of $all is to allow search matching on more than one tag)



 Comments   
Comment by Pep Martinez [ 09/Feb/15 ]

perfect, thanks a lot

Comment by David Storch [ 09/Feb/15 ]

Hi pep.martinez,

Thanks for the additional info! I've identified this report as a duplicate of SERVER-16256, which has already been fixed for the upcoming 3.0.0 release. A fix is still pending approval for the 2.6 branch. Please watch SERVER-16256 for further updates.

Best,
Dave

Comment by Pep Martinez [ 09/Feb/15 ]

as it seems, the problem appears when the index covers something else, and not just the tags field, see below:

> db.version()
2.6.7
> db.foo.drop()
true
> db.foo.ensureIndex({u:1,"tags.k": 1, "tags.v": 1})
{
        "createdCollectionAutomatically" : true,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}
> db.foo.insert({u:'tst', tags: [{k: 3, v: 8}, {k: 6, v: 77}]})
WriteResult({ "nInserted" : 1 })
> db.foo.insert({u:'tst', tags: [{k: 1, v: 1}, {k: 9, v: 77}]})
WriteResult({ "nInserted" : 1 })
> db.foo.find({u:'tst', tags: {$elemMatch: {k: 1, v: 1}}}).explain()
{
        "cursor" : "BtreeCursor u_1_tags.k_1_tags.v_1",
        "isMultiKey" : true,
        "n" : 1,
        "nscannedObjects" : 1,
        "nscanned" : 1,
        "nscannedObjectsAllPlans" : 1,
        "nscannedAllPlans" : 1,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 0,
        "indexBounds" : {
                "u" : [
                        [
                                "tst",
                                "tst"
                        ]
                ],
                "tags.k" : [
                        [
                                1,
                                1
                        ]
                ],
                "tags.v" : [
                        [
                                1,
                                1
                        ]
                ]
        },
        "server" : "xana:27017",
        "filterSet" : false
}
> db.foo.find({u:'tst',tags: {$all: [{$elemMatch: {k: 1, v: 1}}]}}).explain()
{
        "cursor" : "BtreeCursor u_1_tags.k_1_tags.v_1",
        "isMultiKey" : true,
        "n" : 1,
        "nscannedObjects" : 2,
        "nscanned" : 4,
        "nscannedObjectsAllPlans" : 2,
        "nscannedAllPlans" : 4,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 0,
        "indexBounds" : {
                "u" : [
                        [
                                "tst",
                                "tst"
                        ]
                ],
                "tags.k" : [
                        [
                                {
                                        "$minElement" : 1
                                },
                                {
                                        "$maxElement" : 1
                                }
                        ]
                ],
                "tags.v" : [
                        [
                                {
                                        "$minElement" : 1
                                },
                                {
                                        "$maxElement" : 1
                                }
                        ]
                ]
        },
        "server" : "xana:27017",
        "filterSet" : false
}
> 

Comment by David Storch [ 09/Feb/15 ]

Hi pep.martinez,

Thanks for the bug report. However, I have not been able to reproduce this issue. The shell session below suggests that the planner is generating the correct query plan both with and without $all.

> db.version()
2.6.7
> db.foo.drop()
false
> db.foo.ensureIndex({"tags.k": 1, "tags.v": 1})
{
	"createdCollectionAutomatically" : true,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}
> db.foo.insert({tags: [{k: 1, v: 1}, {k: 2, v: 2}]})
WriteResult({ "nInserted" : 1 })
> db.foo.find({tags: {$elemMatch: {k: 1, v: 1}}}).explain()
{
	"cursor" : "BtreeCursor tags.k_1_tags.v_1",
	"isMultiKey" : true,
	"n" : 1,
	"nscannedObjects" : 1,
	"nscanned" : 1,
	"nscannedObjectsAllPlans" : 1,
	"nscannedAllPlans" : 1,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"tags.k" : [
			[
				1,
				1
			]
		],
		"tags.v" : [
			[
				1,
				1
			]
		]
	},
	"server" : "dstorch-desktop:27017",
	"filterSet" : false
}
> db.foo.find({tags: {$all: [{$elemMatch: {k: 1, v: 1}}]}}).explain()
{
	"cursor" : "BtreeCursor tags.k_1_tags.v_1",
	"isMultiKey" : true,
	"n" : 1,
	"nscannedObjects" : 1,
	"nscanned" : 1,
	"nscannedObjectsAllPlans" : 1,
	"nscannedAllPlans" : 1,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"tags.k" : [
			[
				1,
				1
			]
		],
		"tags.v" : [
			[
				1,
				1
			]
		]
	},
	"server" : "dstorch-desktop:27017",
	"filterSet" : false
}

Both explains indicate that the index {"tags.k": 1, "tags.v": 1} is being used to answer the query. Furthermore, both explains have the same index bounds:

"indexBounds" : { "tags.k" : [ [ 1, 1 ] ], "tags.v" : [ [ 1, 1 ] ] }

These are the expected index bounds for this query.

Here are some follow up questions which hopefully will help us track down the issue:

  1. Could you post more detailed steps for reproducing the issue?
  2. Can you confirm that you saw this bug on a server at version 2.6.7?
  3. Could you please post the output of .explain() on the problematic query?

Best,
Dave

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