-
Type: Bug
-
Resolution: Done
-
Priority: Minor - P4
-
Affects Version/s: 2.2.3, 3.0.0-rc6
-
Component/s: JavaScript, Querying
-
None
-
Environment:Amazon linux ami
-
Fully Compatible
-
ALL
When a $ regex query is issued with $options: "s" mongo does not choose the most appropriate query plan. For example the first query below returns in 15000 ms with nscanned: 932956 the same query without $options: "s" return in 1ms with "nscanned" : 61. Finally I ran the first query again with a hint to use the correct index but it gives a warning: unknown regex flag:s and uses the same index as the first query.
mongos> db.CA.find({ "a": "acme.com", "att": { $elemMatch: { "e.a": {$regex:"^x", $options: "s"} }, start: { $gte: new Date(1363737600000) }}}).explain() { "cursor" : "BtreeCursor a_1_uId_1", "isMultiKey" : false, "n" : 0, "nscannedObjects" : 932956, "nscanned" : 932956, "nscannedObjectsAllPlans" : 2798869, "nscannedAllPlans" : 2798869, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 729, "nChunkSkips" : 0, "millis" : 14817, "indexBounds" : { "a" : [ [ "acme.com", "acme.com" ] ], "uId" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ] }, "server" : "rs11", "millis" : 14817 } mongos> db.CA.find({ "a": "acme.com", "att": { $elemMatch: { "e.a": {$regex:"^x"} }, start: { $gte: new Date(1363737600000) }}}).explain() { "cursor" : "BtreeCursor a_1_att.e.a_1 multi", "isMultiKey" : true, "n" : 0, "nscannedObjects" : 60, "nscanned" : 61, "nscannedObjectsAllPlans" : 180, "nscannedAllPlans" : 181, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 1, "indexBounds" : { "a" : [ [ "acme.com", "acme.com" ] ], "att.e.a" : [ [ "x", "y" ], [ /^x/, /^x/ ] ] }, "server" : "rs11", "millis" : 1 } mongos> db.CA.find({ "a": "acme.com", "att": { $elemMatch: { "e.a": {$regex:"^x", $options: "s"} }, start: { $gte: new Date(1363737600000) }}}).hint("a_1_att.e.a_1").explain() Wed Mar 20 18:09:53 warning: unknown regex flag:s Wed Mar 20 18:09:53 warning: unknown regex flag:s { "cursor" : "BtreeCursor a_1_att.e.a_1 multi", "isMultiKey" : true, "n" : 0, "nscannedObjects" : 1103778, "nscanned" : 1103778, "nscannedObjectsAllPlans" : 1103778, "nscannedAllPlans" : 1103778, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 8163, "nChunkSkips" : 0, "millis" : 5359, "indexBounds" : { "a" : [ [ "acme.com", "acme.com" ] ], "att.e.a" : [ [ "", { } ], [ /^x/, /^x/ ] ] }, "server" : "rs11", "millis" : 5359 } mongos>
- is duplicated by
-
SERVER-16884 Regex/index optimization should allow "s" option
- Closed
- related to
-
CSHARP-862 Regex Queries /s suffix
- Closed