[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:
Duplicate
is duplicated by SERVER-14206 Use index is order dependent Closed
Operating System: ALL
Participants:

 Description   

mongodb (2.6.1) use index is order dependent.

in mongodb (2.4.10):
indexes: (a_1), (a_1, b_1)

db.test.find(

{a: 1, b: 1}

).explain() -->
indexBounds: (a_1, b_1)

but in mongodb (2.6.1):
indexes: (a_1), (a_1, b_1)

db.test.find(

{a: 1, b: 1}

).explain() -->
indexBounds: (a_1)

if I build index in reverse order:
indexes: (a_1, b_1), (a_1)

db.test.find(

{a: 1, b: 1}

).explain() -->
indexBounds: (a_1, b_1)



 Comments   
Comment by Michael Yang [ 09/Jun/14 ]

thanks.

Comment by David Storch [ 09/Jun/14 ]

Hi fates.lachesis@gmail.com,

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,
Dave

Comment by Michael Yang [ 09/Jun/14 ]

the problem is mongodb log report wait a/b lock when findAndModify document,
I have 200000 document in same collection,
and have 200 connection in the same time.
the document like below:
{
time: isodate(),
state: 1,
false_retry_count: 10,
get_retry_count: 10,
.............
}

index if like below:
idx1: (time_1, state_1, get_retry_count_1), idx2: (time_1, false_retry_count_1)

db.test.find({false_retry_count: 1, time:{$gt: ....}}),explain();
mongodb (2.6.1) chose idx1 not idx2,
but mongodb (2.4.10) chose idx2.

the way to avoid the a/b lock problem,
I have change the order of index like below:
idx1: (state_1, time_1, get_retry_count_1), idx2: (time_1, false_retry_count_1)
--> let prefix match idx2.
--> and I don't see the problem again.

if I chose time as shard key,
other index with time and build after time index build,
will be non usable.

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,
Dave

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