Why is MongoDB not using the right index?

Question

I have a MongoDB query that I'm trying to optimize. I created an index that matches the fields in the query, but I can't seem to get MongoDB's query planner to use the index without an explicit hint(), even though the nscanned and millis are better for the hinted version.

Here are the indexes, the query (with and without the hint), and a verbose explain:

http://paste.roguecoders.com/p/4face5649612e840da04c5fea0491c9b.txt

One additional bit of info: this index is in a large-ish collection, in a replica set, so I built the index using the offline method. It's now present in all nodes.

(Originally posted on MongoDB-User.)

Answer 1

This seems to be to a regression in 2.4 which affects SERVER-5063 fix when one of the values in the {$in:[ ]} clause is "null". I filed it as new ticket https://jira.mongodb.org/browse/SERVER-9495 which I hope will be triaged and fixed soon.

Meanwhile, depending on why there are null values (or absence of field?) along with true/false you have several options, some involving changing the query some changing the data. I would not advise downgrading to 2.2 just for that, but it's also a possibility.