[mongodb-user] $query, $regex vs /^\Q\E/ and performance questions

kellypw

2015-03-13 18:10:42 UTC

Turns out that the $query syntax shouldn't be mixed - so running this
produced the proper explain plan

db.coll203.find({ $query: {name : /^\Q800716\E/ }, $orderby: { "type": 1 },
$explain: true}).pretty();
{
"cursor" : "BtreeCursor name_1 multi",
"isMultiKey" : false,
"n" : 4,
"nscannedObjects" : 4,
"nscanned" : 5,
"nscannedObjectsAllPlans" : 12,
"nscannedAllPlans" : 13,
"scanAndOrder" : true,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"name" : [
[
"800716",
"800717"
],
[
/^\Q800716\E/,
/^\Q800716\E/
]
]
},
"allPlans" : [
{
"cursor" : "BtreeCursor name_1 multi",
"n" : 4,
"nscannedObjects" : 4,
"nscanned" : 5,
"indexBounds" : {
"name" : [
[
"800716",
"800717"
],
[
/^\Q800716\E/,
/^\Q800716\E/
]
]
}
},
{
"cursor" : "BtreeCursor type_1",
"n" : 0,
"nscannedObjects" : 4,
"nscanned" : 4,
"indexBounds" : {
"type" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
}
},
{
"cursor" : "BasicCursor",
"n" : 0,
"nscannedObjects" : 4,
"nscanned" : 4,
"indexBounds" : {

}
}
],
"server" : "myserver"
}

Post by kellypw
Running version 2.4.12 - I have a curious case where depending on what
syntax is used for a regex expression, proper indexes are used. This
collection is approx 6.9 million documents. Regex is left anchored in both
cases, so I would expect the explain plan to be the same, utilizing the
index. However, it appears that using $query with /^\Q\E/ results in a
collection scan, while using $regex properly uses the index. This is legacy
code so I'd prefer not to have to modify it, but will do so if it's
known/expected behavior. Any insights would be appreciated.
myserver:SECONDARY> db.version()
2.4.12
myserver:SECONDARY> db.coll203.count()
6894117
myserver:SECONDARY> db.coll203.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"ns" : "myns.coll203",
"name" : "_id_"
},
{
"v" : 1,
"key" : {
"name" : 1
},
"ns" : "myns.coll203",
"name" : "name_1"
},
{
"v" : 1,
"key" : {
"type" : 1
},
"ns" : "myns.coll203",
"name" : "type_1",
"background" : true
}
]
myserver:SECONDARY> db.coll203.find({ $query: {name : /^\Q800716\E/ },
$orderby: { "type": 1 }}).explain()
{
"cursor" : "BasicCursor",
"isMultiKey" : false,
"n" : 0,
"nscannedObjects" : 6894110,
"nscanned" : 6894110,
"nscannedObjectsAllPlans" : 6894110,
"nscannedAllPlans" : 6894110,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 93,
"nChunkSkips" : 0,
"millis" : 11646,
"indexBounds" : {
},
"server" : "myserverip"
}
myserver:SECONDARY> db.coll203.find({ "name" : {$regex: /^800716/}
}).sort({"type": 1}).explain()
{
"cursor" : "BtreeCursor name_1",
"isMultiKey" : false,
"n" : 4,
"nscannedObjects" : 4,
"nscanned" : 4,
"nscannedObjectsAllPlans" : 12,
"nscannedAllPlans" : 12,
"scanAndOrder" : true,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"name" : [
[
"800716",
"800717"
]
]
},
"server" : "myserver"
}
Note that even removing the sort/orderby in both cases does not change the
query path for either
myserver:SECONDARY> db.coll03.find({ $query: {name : /^\Q800716\E/
}}).explain()
{
"cursor" : "BasicCursor",
"isMultiKey" : false,
"n" : 0,
"nscannedObjects" : 6894117,
"nscanned" : 6894117,
"nscannedObjectsAllPlans" : 6894117,
"nscannedAllPlans" : 6894117,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 76,
"nChunkSkips" : 0,
"millis" : 12712,
"indexBounds" : {
},
"server" : "myserver"
}
myserver:SECONDARY> db.coll203.find({ "name" : {$regex: /^800716/}
}).explain()
{
"cursor" : "BtreeCursor name_1",
"isMultiKey" : false,
"n" : 4,
"nscannedObjects" : 4,
"nscanned" : 4,
"nscannedObjectsAllPlans" : 4,
"nscannedAllPlans" : 4,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"name" : [
[
"800716",
"800717"
]
]
},
"server" : "myserver"
}

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
Visit this group at http://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/170f0922-4178-47ad-92bd-1871a81438f9%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.