Bad performance when querying indexed field with "$ne" operator

539 views
Skip to first unread message

Paulo

unread,
Oct 23, 2015, 12:17:54 PM10/23/15
to mongodb-user
Hi, 

I have a small sample (2000 docs) of a collection with more than 10 millions of docs, and add an index on the field "i".

In this sample collection I have 1000 docs with {"i":"n/a"}, and another 1000 with another value for field "i".

When I querying DB like this: db.TEST.find( { i : { "$ne" : "n/a"} } ), the index is used, but all documents are scanned and then only the 1000 docs with "i" not equal "n/a" are returned.

In the docs they say that "$ne" and "$nin" operators are not very selective since they often match a large portion of the index. As a result, in many cases, a $nin or $ne query with an index may perform no better than a $nin or $ne query that must scan all documents in a collection.

Is there any workaround to perform queries on indexed fields with "$ne" or "$nin" operators?

Best Regards 
Paulo

Rhys Campbell

unread,
Oct 26, 2015, 4:10:47 AM10/26/15
to mongodb-user
I think you're options are limited here as I'm assuming it's not possible to make the query less selective (as that's the data you want). Post the explain for the query, there might be something useful.

If the query is disk bound you might benefit from using wiredTiger storage engine (if you're not already).

Dwight Merriman

unread,
Oct 26, 2015, 4:48:02 PM10/26/15
to mongodb-user
what version?
Message has been deleted
Message has been deleted

Paulo

unread,
Oct 27, 2015, 7:30:50 AM10/27/15
to mongodb-user
3.0.6 wiredTiger

I made another test and the index was used.

db.TESTE.find({i:{$ne:"n/a"}}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "live_network.TESTE",
"indexFilterSet" : false,
"parsedQuery" : {
"$not" : {
"i" : {
"$eq" : "n/a"
}
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"i" : 1
},
"indexName" : "i_1",
"isMultiKey" : false,
"direction" : "forward",
"indexBounds" : {
"i" : [
"[MinKey, \"n/a\")",
"(\"n/a\", MaxKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1000,
"executionTimeMillis" : 3,
"totalKeysExamined" : 1001,
"totalDocsExamined" : 1000,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 1000,
"executionTimeMillisEstimate" : 0,
"works" : 1002,
"advanced" : 1000,
"needTime" : 1,
"needFetch" : 0,
"saveState" : 7,
"restoreState" : 7,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 1000,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 1000,
"executionTimeMillisEstimate" : 0,
"works" : 1001,
"advanced" : 1000,
"needTime" : 1,
"needFetch" : 0,
"saveState" : 7,
"restoreState" : 7,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"i" : 1
},
"indexName" : "i_1",
"isMultiKey" : false,
"direction" : "forward",
"indexBounds" : {
"i" : [
"[MinKey, \"n/a\")",
"(\"n/a\", MaxKey]"
]
},
"keysExamined" : 1001,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0,
"matchTested" : 0
}
}
},
"serverInfo" : {
"host" : "xxxxxxxxxxxxxx
"port" : 27017,
"version" : "3.0.6",
"gitVersion" : "1ef45a23a4c5e3480ac919b28afcba3c615488f2"
},
"ok" : 1
}
Reply all
Reply to author
Forward
0 new messages