Inefficient use of indexes on $or queries.

When using "$or" in queries the query optimizer don't seem to take the statements outside the "$or" section in to consideration.

Setting up the collection:

db.foo.insert({'Key': 1, 'Range': 100000});

db.foo.insert({'Key': 2, 'Range': 200000});

db.foo.insert({'Key': 3, 'Range': 300000});

db.foo.insert({'Key': 4, 'Range': 400000});

db.foo.insert({'Key2': 1, 'Range': 100000});

db.foo.insert({'Key2': 2, 'Range': 200000});

db.foo.insert({'Key2': 3, 'Range': 300000});

db.foo.insert({'Key2': 4, 'Range': 400000});

db.foo.ensureIndex({'Key': 1,'Range': 1});

db.foo.ensureIndex({'Key2': 1,'Range': 1});

If I query the collection in the following way the optimizer will ignore the "Range" field in the "indexBounds".

db.foo.find({$or:[{'Key': 1},{'Key2': 1}], 'Range': {$gt: 10, $lt: 100000}}).explain(true);

The work around is to include the range filed for each "$or" clause but it feel like this is something the query optimizer should be able to handle by itself?

db.foo.find({$or:[{'Key': 1, 'Range': {$gt: 10, $lt: 100000}},{'Key2': 1, 'Range': {$gt: 10, $lt: 100000}}]}).explain(true);

There is a SERVER ticket for this - to enable the query optimizer to rewrite queries to take advantage of this unfortunately our experiments showed it sometimes improves performance but sometimes makes it worse, so we don't have a perfect solution to this problem yet.

What version are you on?   There was a related but fixed for 2.6.5 - if you're not on that, I suggest upgrading...