-
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
-
None
-
None
-
None
-
None
-
None
-
None
-
None
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
-