[SERVER-2953] Use dotted indexes for embedded document queries Created: 14/Apr/11  Updated: 06/Dec/22

Status: Backlog
Project: Core Server
Component/s: Index Maintenance
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Alvin Richards (Inactive) Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 3
Labels: query, rewrite
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
is duplicated by SERVER-5904 embedded doc matches could use dotted... Closed
Related
is related to SERVER-5904 embedded doc matches could use dotted... Closed
Assigned Teams:
Query Optimization
Participants:
Case:

 Description   

Setup:

> db.users.save({first_name:"Foo", ext_id :{ id:"6708526",type:2}});
> db.users.ensureIndex({'ext_id.id':1,'ext_id.type':1})


Using the dot-notation, the explain plan shows that the index is used:

> db.users.find({"ext_id.id":"6708526", "ext_id.type":2}).explain()
{
	"cursor" : "BtreeCursor ext_id.id_1_ext_id.type_1",
	"nscanned" : 1,
	"nscannedObjects" : 1,
	"n" : 1,
	"millis" : 0,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"isMultiKey" : false,
	"indexOnly" : false,
	"indexBounds" : {
		"ext_id.id" : [
			[
				"6708526",
				"6708526"
			]
		],
		"ext_id.type" : [
			[
				2,
				2
			]
		]
	}
}

However, expressing the query with a document results in the index not being used:

> db.users.find({'ext_id':{'id':"6708526", 'type':2}}).explain()
{
	"cursor" : "BasicCursor",
	"nscanned" : 1,
	"nscannedObjects" : 1,
	"n" : 1,
	"millis" : 0,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"isMultiKey" : false,
	"indexOnly" : false,
	"indexBounds" : {
		
	}
}

If there are indexed fields representing any of embedded fields in the query, in dot-notation, they should be used. This can be done to filter out the documents needed before the exact embedded document matching is done for the actual query criteria.



 Comments   
Comment by Asya Kamsky [ 09/Oct/17 ]

SERVER-5904 was asking for fundamentally something very similar. Not sure if this is something we will consider implementing in the future.

Comment by Gaetan Voyer-Perrault [ 15/Apr/11 ]

Sample script (for clarification / unit test purposes):
> db.foo.insert( { x :

{ a : 1, b: 2, c : 3 }

} )
> db.foo.insert( { x :

{ a : 1, b: 2 }

} )
> db.foo.ensureIndex(

{ 'x.a' : 1, 'x.b' : 1 }

)

// query #1
> db.foo.find(

{ 'x.a' : 1, 'x.b' : 2 }

).explain().cursor
BtreeCursor x.a_1_x.b_1

// query #2
> db.foo.find( { x :

{ a : 1, b : 2 }

} ).explain().cursor
BasicCursor

------
In practice that second query could use the index. Documents matching query #2 are a direct subset of query #1.

It looks like this logic may be missing when selecting an index for the query.

For this bug to be fixed I would expect the output of #1 and #2 to be the same.

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