-
Type:
Task
-
Resolution: Fixed
-
Priority:
Minor - P4
-
Affects Version/s: 6.0.0-rc0, 5.3.1
-
Component/s: Query Planning
-
Labels:None
-
Query Optimization
-
Fully Compatible
-
QO 2024-02-05
Neither the existing pcre library nor the new pcre2 library support ICU collation. Therefore predicates that contain a regex value/s should not be eligible to use indexes with non-simple collations for index scans, as the index will not be selective and all strings in the index will be used:
For example, notice this explain output and the final index bounds used:
replSet:PRIMARY> db.coll.createIndex({x: 1}, {collation: {locale: "en", strength: 1}})
replSet:PRIMARY> db.coll.explain().find({x: {$regex: /^foo$/, $options: "i"}})
{
"explainVersion" : "2",
"queryPlanner" : {
"namespace" : "newer.coll",
"indexFilterSet" : false,
"parsedQuery" : {
"x" : {
"$regex" : "^foo$",
"$options" : "i"
}
},
...
"winningPlan" : {
"queryPlan" : {
"stage" : "FETCH",
"planNodeId" : 2,
"filter" : {
"x" : {
"$regex" : "^foo$",
"$options" : "i"
}
},
"inputStage" : {
"stage" : "IXSCAN",
"planNodeId" : 1,
"keyPattern" : {
"x" : 1
},
"indexName" : "x_1",
"collation" : {
"locale" : "en",
"caseLevel" : false,
"caseFirst" : "off",
"strength" : 1,
"numericOrdering" : false,
"alternate" : "non-ignorable",
"maxVariable" : "punct",
"normalization" : false,
"backwards" : false,
"version" : "57.1"
},
...
"indexBounds" : {
"x" : [
"[CollationKey(0x), {})",
"[/^foo$/i, /^foo$/i]"
]
}
...
