-
Type:
Improvement
-
Resolution: Unresolved
-
Priority:
Major - P3
-
None
-
Affects Version/s: 2.4.9, 2.6.0-rc0
-
Component/s: Index Maintenance
-
Query Optimization
Restore the database from the attached BSON data (I called it BookStore FWIW).
Then this query without indexes takes about 300 ms and uses basic cusor as you would expect:
> db.Book.find({ "Ratings" : { "$elemMatch" : { "Value" : { "$gte" : 0 } } } } )
Running explain we see it is using a cursor and it takes 891 ms.
{
"cursor" : "BtreeCursor Ratings.Value_1",
"isMultiKey" : true,
"n" : 270171,
"nscannedObjects" : 270171,
"nscanned" : 443781,
"nscannedObjectsAllPlans" : 270270,
"nscannedAllPlans" : 443880,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 3467,
"nChunkSkips" : 0,
"millis" : 891,
"indexBounds" : {
"Ratings.Value" : [
[
0,
Infinity
]
]
},
"server" : "WIN-7S2IMPQ2TOE:27017",
"filterSet" : false
}
>
Now, remove that index:
> db.Book.dropIndex("Ratings.Value_1")
{ "nIndexesWas" : 7, "ok" : 1 }
And you will see it runs in 300ms!
{
"cursor" : "BasicCursor",
"isMultiKey" : false,
"n" : 270171,
"nscannedObjects" : 271380,
"nscanned" : 271380,
"nscannedObjectsAllPlans" : 271380,
"nscannedAllPlans" : 271380,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 2120,
"nChunkSkips" : 0,
"millis" : 317,
"server" : "WIN-7S2IMPQ2TOE:27017",
"filterSet" : false
}
It seems like mongo should either:
- Not use the index
- Acheive at least as good of perf
To make matters much worse, I was actually running just a count operation via the C# driver and go even worse perf. Seems like there should be almost no perf cost for a count operation on an index set.
- is duplicated by
-
SERVER-29227 $nin operation choose IXSCAN but not COLLSCAN stag
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-