-
Type: Bug
-
Resolution: Duplicate
-
Priority: Major - P3
-
None
-
Affects Version/s: 2.6.3
-
Component/s: Performance, Querying
-
None
-
ALL
Given this query:
{ $and: [ { tags: "A" }, { tags: { $ne: "B" } } ] }
Where tags is an array and indexed, the query is much slower in 2.6.3 when compared to 2.4.10.
I have attached a JS script that reproduces my test.
I tested with a basic PSS replica-set, 1 million documents, with a pseudo-random set of 5 values (single letter of the alphabet as a string) per document for the tags array. Indexed.
Sample output in 2.4.10:
db.test.find( { $and: [ { tags: "A" }, { tags: { $ne: "B" } } ] } ).explain() { "cursor" : "BtreeCursor tags_1", "isMultiKey" : true, "n" : 152168, "nscannedObjects" : 178651, "nscanned" : 178651, "nscannedObjectsAllPlans" : 178651, "nscannedAllPlans" : 178651, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 1, "nChunkSkips" : 0, "millis" : 378, "indexBounds" : { "tags" : [ [ "A", "A" ] ] }, "server" : "Boomtime:27119" }
Sample output in 2.6.3:
db.test.find( { $and: [ { tags: "A" }, { tags: { $ne: "B" } } ] } ).explain() { "cursor" : "BtreeCursor tags_1", "isMultiKey" : true, "n" : 152168, "nscannedObjects" : 1000000, "nscanned" : 4452528, "nscannedObjectsAllPlans" : 1000000, "nscannedAllPlans" : 4452644, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 36831, "nChunkSkips" : 0, "millis" : 5725, "indexBounds" : { "tags" : [ [ { "$minElement" : 1 }, "B" ], [ "B", { "$maxElement" : 1 } ] ] }, "server" : "Boomtime:27119", "filterSet" : false, "stats" : { "type" : "KEEP_MUTATIONS", "works" : 4455900, "yields" : 36831, "unyields" : 36831, "invalidates" : 0, "advanced" : 152168, "needTime" : 4300359, "needFetch" : 3372, "isEOF" : 1, "children" : [ { "type" : "FETCH", "works" : 4455900, "yields" : 36831, "unyields" : 36831, "invalidates" : 0, "advanced" : 152168, "needTime" : 4300359, "needFetch" : 3372, "isEOF" : 1, "alreadyHasObj" : 0, "forcedFetches" : 0, "matchTested" : 152168, "children" : [ { "type" : "IXSCAN", "works" : 4452527, "yields" : 36831, "unyields" : 36831, "invalidates" : 0, "advanced" : 1000000, "needTime" : 3452527, "needFetch" : 0, "isEOF" : 1, "keyPattern" : "{ tags: 1.0 }", "boundsVerbose" : "field #0['tags']: [MinKey, \"B\"), (\"B\", MaxKey]", "isMultiKey" : 1, "yieldMovedCursor" : 0, "dupsTested" : 4452527, "dupsDropped" : 3452527, "seenInvalidated" : 0, "matchTested" : 0, "keysExamined" : 4452528, "children" : [ ] } ] } ] } }
Hinting $natural performs better on 2.6.3 (for me).
- duplicates
-
SERVER-12281 When choosing multikey index bounds, never choose a superset if a subset is available
- Backlog