Launching soon: The world's first vendor agnostic bug scrubLearn more & join waitlist
OPERATIONAL DEFECT DATABASE
...
...
Querying for null values looks like it is filtering records with fetch before hitting the index, even when the null field is part of the index. This stack overflow question seems to be related: http://stackoverflow.com/questions/30319805/mongodb-extremely-slow-at-counting-null-values-or-exists-false
rassi@10gen.com commented on Thu, 27 Aug 2015 18:35:04 +0000: This is indeed a dup of SERVER-18653. The index scan is happening "first", and each index key returned by the index scan is fetched before returning to the user. Index scans generated by "equality to null" predicates are slightly different from other equality predicates in that they generate INEXACT_FETCH bounds (which means that the predicate needs to be re-checked after the fetch); SERVER-18653 has an explanation as to why. ramon.fernandez commented on Thu, 27 Aug 2015 18:28:11 +0000: This does look expected as per SERVER-18653, but I can't quite wrap my head around it. Intuitively I would expect the index scan to happen first for performance reasons, but not everything in query is as intuitive as one would expect jason.rassi, david.storch, can you confirm whether this is a dup of SERVER-18653 and therefore expected behavior? Thanks, Ramón.
If I create an index: { a: 1, b: 1 } And then perform query like so: { a: 'something', b: null } or { a: 'something', b: { $exists: false } My query plan looks like this: { "queryPlanner" : { ... "winningPlan" : { "stage" : "FETCH", "filter" : { "b" : { "$eq" : null } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "a" : 1, "b" : 1 }, "indexName" : "a_1_b_1", "isMultiKey" : false, "direction" : "forward", "indexBounds" : { "a" : [ "['something', 'something']" ], "b" : [ "[\"null\", \"null\"]" ] } } }, ... }, ... } This performs badly, I'm assuming because of the filter on the initial fetch. If I remove that by updating all my records with { b: null } , to have { b: "null" } (note the string) and now query for the string null rather than actual null, the plan looks like this. { "queryPlanner" : { ... "winningPlan" : { "stage" : "FETCH", "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "a" : 1, "b" : 1 }, "indexName" : "a_1_b_1", "isMultiKey" : false, "direction" : "forward", "indexBounds" : { "a" : [ "[\"something\", \"something\"]" ], "b" : [ "[\"null\", \"null\"]" ] } } }, ... }, ... } The filter is missing and the query now performs as expected.