[SERVER-14207] Use index is order dependent Created: 09/Jun/14 Updated: 07/Apr/23 Resolved: 09/Jun/14 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | 2.6.1 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Michael Yang | Assignee: | David Storch |
| Resolution: | Done | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||
| Operating System: | ALL | ||||||||
| Participants: | |||||||||
| Description |
|
mongodb (2.6.1) use index is order dependent. in mongodb (2.4.10): db.test.find( {a: 1, b: 1}).explain() --> but in mongodb (2.6.1): db.test.find( {a: 1, b: 1}).explain() --> if I build index in reverse order: db.test.find( {a: 1, b: 1}).explain() --> |
| Comments |
| Comment by Michael Yang [ 09/Jun/14 ] |
|
thanks. |
| Comment by David Storch [ 09/Jun/14 ] |
|
Thanks for your prompt response! This is more of a question about index selection in MongoDB rather than a bug in the server. I recommend redirecting your question to either StackOverflow (http://stackoverflow.com/questions/tagged/mongodb) or the MongoDB user Google Group (https://groups.google.com/forum/#!forum/mongodb-user). Some further resources that may help with indexing design are:
It looks to me like the two indices are tying during plan ranking, leading to the selection of index {time: 1, state: 1, get_retry_count: 1} rather than {time: 1, false_retry_count: 1}. For this reason, I am closing as Works as Designed. Best, |
| Comment by Michael Yang [ 09/Jun/14 ] |
|
the problem is mongodb log report wait a/b lock when findAndModify document, index if like below: db.test.find({false_retry_count: 1, time:{$gt: ....}}),explain(); the way to avoid the a/b lock problem, if I chose time as shard key, |
| Comment by David Storch [ 09/Jun/14 ] |
|
Hi flachesis, Thanks for the bug report. It looks as though in 2.6.1 the two indices, {a: 1} and {a: 1, b: 1} are tying during plan ranking. Assuming that the indices are equally good candidates to answer the query, the ranker will choose one of the tying plans arbitrarily. It so happens as an implementation artifact that the ranker will choose the index that was built first (but this is not documented behavior, and should not be relied upon). If my suspicion about tying plans is correct, then this is working as designed. A little bit of background on why this behavior changed: in 2.4.x versions, there was a hack in which an index would always be used if the query shape exactly matched the index key pattern. This hack was removed as part of the query engine refactor for 2.6 in favor of a much more general plan ranker. What is the data in your collection? Are your test collections empty other than the indices? Best, |