Author:

Message: SERVER-5063 $in limit efficiency.
Branch: master
https://github.com/mongodb/mongo/commit/7cacde89bb37051549b38abaf4d6b591b199b243

Out of interest, what was the speed difference for $in queries of this type after this change, do you have any benchmark results?

Hi James,

The performance improvement you'll see will be very dependent on your data distribution, and it's possible to generate a benchmark with an arbitrary improvement multiple by varying the amount of data in your test. (For example I just ran a simple test for this optimization that showed a four order of magnitude performance improvement.)

Is there a real world example data set you'd like to test?

I would just like to share my experience, where I have the following indexes:

"indexSizes" : {
"id" : 9026304,
"created_at_-1" : 6344576,
"feelings_1_location.state_1_created_at_-1" : 10285408
},

The query:
db.dev06.find({
'feelings':

,
'location.state' :

}).sort(

).limit(10).explain();

In v2.0.6 the results were:
http://pastie.org/private/frfkmfuli7uqi6fwiykag

while in v2.1.2 there was a significant increase in the number of objects scanned:
http://pastie.org/private/6dtbsxb6vofzh8yslmicca

Even if I hint in v.2.0.6 the same index used in v2.1.2, it still scans less documents:
http://pastie.org/private/ejgiavgf1reqlq5hwhgpw

Hi Artur,

I think you may be encountering a difference the explain behavior between 2.0 and 2.1. In 2.1 the nscanned reported for the overall query includes the nscanned values of all candidate plans attempted. In 2.0 only the nscanned of the "winning" query plan is reported.

Could you please send the output of:

db.dev06.find( ... ).explain( true )
db.dev06.find( ... ).hint(

).explain( true )

for both 2.0 and 2.1? Also, it would be helpful for clarity if you could include the command you enter into the shell with the shell output you post.

Thanks,
Aaron

Hi Aaron,

Thanks for your prompt reply. Please find the information you asked for:
v2.0.6
http://pastie.org/private/dhsoanhnzlvgtkx71o9nq

v2.1.2
http://pastie.org/private/0a33rnyxxqeukdwbevurw

Indeed, as you said, v2.1 is reporting the nscanned values of all candidates. My fault for not reading the changelog throughly.

Anyways, I was expecting a performance boost in v2.1 for this specific query given the compound index, but I think I'll have to wait until SERVER-3310 is solved.

Thank you once again!

Hi Artur,

No problem. It looks like your 'feelings_1_location.state_1_created_at_-1' index is multikey, which would explain why the optimization is not being used. (It is not supported for multikey indexes.)