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