[SERVER-16145] Compound find with sort picks the wrong index Created: 14/Nov/14  Updated: 16/Feb/18  Resolved: 23/Jan/15

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

Type: Bug Priority: Major - P3
Reporter: Petr Bela Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Steps To Reproduce:

With the following indexes:

> db.accounts.getIndexes()
[
	{
		"v" : 1,
		"key" : {
			"_id" : 1
		},
		"name" : "_id_",
		"ns" : "socialdb_prod.accounts"
	},
	{
		"v" : 1,
		"unique" : true,
		"key" : {
			"service" : 1,
			"uid" : 1
		},
		"name" : "service_1_uid_1",
		"background" : true,
		"dropDups" : true,
		"ns" : "socialdb_prod.accounts"
	}
]

Run a search:

db.accounts.find({"service": 0, "uid": "26119042"}).sort({_id: 1})

Uses _id index instead of the obvious

{ service: 1, uid: 1 }

2014-11-13T19:29:57.910+0000 [conn1530188] query socialdb_prod.accounts query: { query: { service: 0.0, uid: "26119042" }, orderby: { _id: 1.0 } } planSummary: IXSCAN { _id: 1 } ntoreturn:0 ntoskip:0 nscanned:3575682 nscannedObjects:3575682 keyUpdates:0 numYields:4 locks(micros) r:8240040 nreturned:1 reslen:334 4285ms

Participants:

 Description   

When finding a document using multiple attributes while specifying a sort, instead of using the compound index for the find, the planner uses the sort's index instead. This is despite the fact that the query actually only has one result.

Strangely enough, when explain() is run on the query, it chooses the correct index.

Also, by using a hint the optimal index can be enforced.



 Comments   
Comment by Jon Linn [ 16/Feb/18 ]

Is there any more info on how this was (partially?) resolved and in what versions it was resolved?

Comment by J Rassi [ 14/Nov/14 ]

Could you run the following the next time you're able to reproduce the issue of the _id index being picked, and paste the output into a comment on this ticket?

db.getSiblingDB("socialdb_prod").accounts.getPlanCache().getPlansByQuery({service: 1, uid: 1}, {}, {_id: 1});
db.getSiblingDB("socialdb_prod").accounts.getPlanCache().clear(); // Clears the query plan cache for this collection.
db.getSiblingDB("socialdb_prod").accounts.getPlanCache().getPlansByQuery({service: 1, uid: 1}, {}, {_id: 1});

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