[SERVER-12248] Change in query plan for $elemMatch query Created: 03/Jan/14  Updated: 10/Dec/14  Resolved: 16/Jan/14

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

Type: Bug Priority: Major - P3
Reporter: Thomas Boyd Assignee: David Storch
Resolution: Done Votes: 0
Labels: nqf, query_triage
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

db.serverBuildInfo()
"version" : "2.5.5-pre-",
"gitVersion" : "04221630900335fc6ee0d9922edae9d29f02d66d",


Operating System: ALL
Participants:

 Description   

Testing query plans for $elemMatch queries. (Want to validate that adhoc queries supported by the array of

{k: key, v: value}

subdocuments still performs.

Generation of test data:

for (i=1; i<100000; i++) {
   searchTerms = [];
   for (j=1; j<=100 ; j++) {
      val = (i+j) % 10
      searchTerms[j-1] = {k: j, v: val}
   }
   db.searchTest.insert({"payload": "document " + i, "searchTerms": searchTerms});
}
 
db.searchTest.ensureIndex({"searchTerms.k": 1, "searchTerms.v": 1});

2.4.6 explain output:

 
> db.searchTest.find({searchTerms: {$elemMatch: {k:1, v: 7}}}).explain()
{
	"cursor" : "BtreeCursor searchTerms.k_1_searchTerms.v_1",
	"isMultiKey" : true,
	"n" : 10000,
	"nscannedObjects" : 10000,
	"nscanned" : 10000,
	"nscannedObjectsAllPlans" : 10000,
	"nscannedAllPlans" : 10000,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 50,
	"indexBounds" : {
		"searchTerms.k" : [
			[
				1,
				1
			]
		],
		"searchTerms.v" : [
			[
				7,
				7
			]
		]
	},
	"server" : "xxx:27018"
}
 

2.5.5-pre explain output

 
> db.searchTest.find({searchTerms: {$elemMatch: {k:1, v: 7}}}).explain()
{
	"cursor" : "BasicCursor",
	"n" : 10000,
	"nscannedObjects" : 99999,
	"nscanned" : 99999,
	"nscannedObjectsAllPlans" : 100099,
	"nscannedAllPlans" : 100099,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 781,
	"nChunkSkips" : 0,
	"millis" : 1566,
	"server" : "xxx:27017"
}
 

Original index creation and explain testing was done with index intersection enabled, but I have since restarted mongod without index intersection enabled and 2.5.5-pre still fails to use the index.



 Comments   
Comment by David Storch [ 16/Jan/14 ]

The indexed solution and the collection scan solutions in this scenario are of approximately equal cost---both have to return 10,000 documents, fetching all 10,000 from disk. In fact, the indexed solution might even be slower because it ends up scanning the entire btree.

The query optimizer for the old query system ending up selecting an indexed solution. The new query optimizer, however, compares the indexed plan to the collection scan and decides that the collection scan is more efficient. On my system the collection scan indeed runs faster (7517 milliseconds versus 8909 milliseconds).

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